Need to Select table name at runtime

[pri1493]

i need to insert values to a table that i can select at runtime.. here is my code

 Dim ConnectionString As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=***\ClubDatabase.mdf;Integrated Security=True;User Instance=True"
        Dim pid, name, qty, cp As String
        Const strInsert As String = "INSERT INTO [111]([Name], [Qty], [Price/unit], [Total]) values (@CN,@CD,@PN,@D1)"
        Dim con1 As New SqlClient.SqlConnection(ConnectionString)
        Dim cmdInsert As New SqlClient.SqlCommand(strInsert, con1)
        con1.Open()
        For Each DGR As DataGridViewRow In _111DataGridView.Rows
            If Not DGR.IsNewRow Then
                pid = DGR.Cells(0).Value.ToString()
                name = DGR.Cells(1).Value.ToString()
                qty = DGR.Cells(2).Value.ToString()
                cp = DGR.Cells(3).Value.ToString()


                With cmdInsert
                    .Parameters.Clear()
                    .Parameters.AddWithValue("@CN", pid)
                    .Parameters.AddWithValue("@CD", name)
                    .Parameters.AddWithValue("@PN", qty)
                    .Parameters.AddWithValue("@D1", cp)
                    .ExecuteNonQuery()
                End With
            End If
        Next

Please help

 

[jmcilhinney]

To answer the question as asked, your SQL statement is just a String so you can treat it like any other String. Presumably you know how to join multiple Strings together to create a new one. In this case, you would most likely use String.Format, e.g.

Dim sql = String.Format("INSERT INTO [{0}] (...) VALUES (...)", tableName)

You should almost certainly get a list of valid table names from the database yourself.

That said, if you're changing the table then how can you not have to change all the columns as well? If you have multiple tables with the same schema then it's highly likely that your database design is bad. Would you care to explain a little about what's actually going on because I strongly suspect that you should only have one table in the first place?

 

[pri1493]

Thanks Again

:apologetic: i dont exactly understand the term schema... i dont no how to create 1 either. i am working on vb for the first time, therefore i dont exactly understand most of the terms. the code tat i gave above enables me to store data in the table... n i hav 4 tables wid same column names.
so now i require som code that helps me navigate between those tables that have the same column names.
i went through many threads and all of them spoke about something called as "stored procedure". now my problem is that i dont know WHERE to create a stored procedure.


And lastly.. thank u so much for your help eaceful: this thread is helping me out a lot

 

[jmcilhinney]

"Schema" basically means the structure of the database, i.e. what tables the database contains and what columns they contain. Why do you have four tables with the same columns? Most likely that is poor design and should be changed. If you change it so that you only have one table then your original problem disappears.

When you want to interact with a database from an application, you need to use SQL statements to do so. That SQL code can either be in your application or in the database. If you choose to put it in the database then you need to create stored procedures. A stored procedure is something like a Sub or Function in VB code. There are differing opinions as to whether it is more appropriate to use stored procedures or keep the SQL code as part of your application. Personally, I would suggest to you not to use them until you understand the implications of doing so and can make an informed decision.

 

[pri1493]

Wow that was really really helpful!! :highly_amused:

like i told before, i am totally new to vb... n my problem was i had to design my project in such a way where the bill for each customer is saved in a table in 1 computer and later on from a master computer this table is retrieved and the bill is printed. i cudnt figure out any other way to do so for each customer... hence i decided to make multiple tables with same column names.
if possible help me to figure out any other method using a single table. thank u!


by the way i figured out how to select the tables at runtime :adoration: so my problem is solved.

 

[jmcilhinney]

The whole point of a relational database is the relations between the data. If you have customers then you create a Customer table. If you have transactions then you create a Transaction table. The Customer table has a CustomerID column to identify each customer and the Transaction table has a CustomerID column to identify which customer a transaction belongs too. You don't have a separate table for each customer's transactions. You have one table for all transactions and use the CustomerID to specify the customer for each transaction. If you want all the transactions for a particular customer then you simply query the Transaction table and filter by CustomerID.

 

[pri1493]

can u provide me a code through wich i can display the contents of a database into a datagridview?

 

[jmcilhinney]

There are already many such examples on the web so I'm not going to provide yet another. You might start with the Data Walkthroughs link in my signature.