Thread: Table name for ExecuteNonQuery()

Below is mycode to connect to Access database. Then it will create a table named "TABLENAME". Is there any way to make the table name based on user input..Please help my project here..


Dim strConnectString As String
Dim objConnection As OleDb.OleDbConnection
Dim strDbPath As String
Dim cmd As OleDb.OleDbCommand
Dim strDBName As String
strDBName = TextBox2.Text
Dim str As String

strDbPath = strDBName & ".mdb"
'================================================= =

strConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDbPath & ";"
objConnection = New OleDb.OleDbConnection(strConnectString)
str = "CREATE TABLE TABLENAME ([Haha] text(50) WITH Compression, " & _
"[Address1] text(150) WITH Compression, " & _
"[Address2] text(150) WITH Compression, " & _
"[City] text(50) WITH Compression, " & _
"[State] text(2) WITH Compression, " & _
"[PIN] text(6) WITH Compression, " & _
"[SIN] decimal(6))"
cmd = New OleDb.OleDbCommand(str, objConnection)

With objConnection
objConnection.Open()
cmd.ExecuteNonQuery()
End With

objConnection = Nothing

In short:

Dim SqlString As String = String.Format("CREATE TABLE [{0}]", TableNameHere)

Full example

Private Sub CreateTable(ByVal TableName As String)

  Dim objConnection As New OleDb.OleDbConnection(String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};", TextBox2.Text.Trim & ".mdb"))

  Dim SqlString As String = String.Format("CREATE TABLE [{0}]([Haha] text(50) WITH Compression, ", TableName.Trim) & _

    "[Address1] text(150) WITH Compression, " & _

    "[Address2] text(150) WITH Compression, " & _

    "[City] text(50) WITH Compression, " & _

    "[State] text(2) WITH Compression, " & _

    "[PIN] text(6) WITH Compression, " & _

    "[SIN] decimal(6))"



  Dim cmd As New OleDb.OleDbCommand(SqlString, objConnection)



  Try

    objConnection.Open()

    cmd.ExecuteNonQuery()

  Catch ex As Exception

    MessageBox.Show(ex.Message)

  Finally

    If objConnection.State <> ConnectionState.Closed Then objConnection.Close()

    cmd.Dispose()

    objConnection.Dispose()

  End Try



End Sub

I haven't used this class I made 6 years ago in quite a while now, but it has code that allows you to do all kinds of stuff like this from vb.net

Thnks...I had success doing modification and it run smoothly.
I changed the green...So everyone can try these..Save my time to google it..Very very helpful JuggaloBrotha

str = String.Format("CREATE TABLE [{0}]([Haha] text(50) WITH Compression, ", TextBox1.Text) & _
"[Address1] text(150) WITH Compression, " & _
"[Address2] text(150) WITH Compression, " & _
"[City] text(50) WITH Compression, " & _
"[State] text(2) WITH Compression, " & _
"[PIN] text(6) WITH Compression, " & _
"[SIN] decimal(6))"

Now I think about getting user input for the column name...Hehe ..Anyone?? or JuggaloBrotha??

Originally Posted by awakenblueheart

Now I think about getting user input for the column name...Hehe ..Anyone?? or JuggaloBrotha??

Um, what do you think the Text of a TextBox is? Doesn't the user input the text into the TextBox?

What I mean is user can control the column name with the textbox..not with code

str = String.Format("CREATE TABLE [{0}]([Haha] text(50) WITH Compression, ", TextBox1.Text) & _
"[Address1] text(150) WITH Compression, " & _
"[Address2] text(150) WITH Compression, " & _
"[City] text(50) WITH Compression, " & _
"[State] text(2) WITH Compression, " & _
"[PIN] text(6) WITH Compression, " & _
"[SIN] decimal(6))"

For example above, address1 is a code. But I want its value comes from textbox. I hope my explanation understandble..

Originally Posted by awakenblueheart

What I mean is user can control the column name with the textbox..not with code

str = String.Format("CREATE TABLE [{0}]([Haha] text(50) WITH Compression, ", TextBox1.Text) & _
"[Address1] text(150) WITH Compression, " & _
"[Address2] text(150) WITH Compression, " & _
"[City] text(50) WITH Compression, " & _
"[State] text(2) WITH Compression, " & _
"[PIN] text(6) WITH Compression, " & _
"[SIN] decimal(6))"

For example above, address1 is a code. But I want its value comes from textbox. I hope my explanation understandble..

So you'll want to have Textboxes and the like on a form where you can get that info from the user, then just use their values in the create table statement just like I showed how to let the user specify the table name.

You should keep in mind that it is quite dangerous allowing the user to enter SQL code directly, even if it's just partial. A cunning user could enter a value that would up deleting everything in your database. If you don't ensure that that can't happen in your code then you are at the mercy of your users and they at the mercy of whoever might get access to the application.

Yes..that's true Joggalobrotha..I want user input for column

According to jmcilhinney it is quite dangerous. So, no one can answer this?

Originally Posted by awakenblueheart

Yes..that's true Joggalobrotha..I want user input for column

According to jmcilhinney it is quite dangerous. So, no one can answer this?

We've both answered this, you'll need to somehow use a TextBox, alternatively you could use a DataGrid to allow the user to specify the attributes of the columns, like what the field type is, it's how MS Access and Sql Server lets you specify all of that.

jmc didn't say you shouldn't do this, he was simply warning that you should protect against sql injection. Using things like parametrized sql queries is a great first step against that.

We can't specifically tell you how you should go about getting that info from the user(s) because neither of us has a clue to what your app is, or what the purpose of any of this is. It's up to you to figure out how to go about doing this.