Multiple SQL statement using VB

Question

I have the following code, where I am trying to trigger multiple SQL statements.

I am new to SQL, can anybody help me with the correct syntax?

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

    Dim con As SqlConnection = New SqlConnection("server=barry-laptop\SQLEXPRESS; database=Test; integrated security=yes")
    Dim cmd As SqlCommand = New SqlCommand("CREATE TABLE " + TextBox2.Text + " " + "(myId INTEGER PRIMARY KEY," + "myName CHAR(50), myAddress CHAR(255), myBalance FLOAT)", con)
    Dim cmd2 As SqlCommand = New SqlCommand("Update Tarrifs Set Name='" & TextBox2.Text & "', con")

    con.Open()
    cmd.ExecuteNonQuery()
    cmd2.ExecuteNonQuery()
    con.Close()

    End Sub

Any help appreciated.

Thanks,

First things first, your code is vulnerable to [SQL injections](http://en.wikipedia.org/wiki/SQL_injection). You **must** fix that before proceeding, by using [prepared statements](http://stackoverflow.com/q/7351135/1968) instead of the string concatenation you are currently using. — Konrad Rudolph, Apr 28 '14 at 21:03
a) use parameter queries b) do you really want to create a new table each time a button is clicked c) how can there be anything to update in a new table d) what if the table already exists? d) the SQL syntax is wrong unless you want EVERY record's name to be set — Ňɏssa Pøngjǣrdenlarp, Apr 28 '14 at 21:04
a) can you please provide a sample with multiple queries? b) yes, the table name is a user specified field c) the update is for a different table d) It will throw an error, it doesn't need to be graceful, its an internal application d) I don't, my field is called "name". Thanks — user3580480, Apr 28 '14 at 21:11
your UPDATE SQL will change the Name for every row in the Tarrifs table. UPDATE queries almost always include a WHERE clause. It wont crash, it wont do what you probably expect it to do. — Ňɏssa Pøngjǣrdenlarp, Apr 28 '14 at 21:41

Steve · Accepted Answer · 2014-04-28T21:11:34.260

The string concatenation operator in VB.NET is the ampersand character (&).
You could use also the + symbol but it is not recommended because it could cause side effects when operands are ambiguos.

Said that, you could use just one SqlCommand passing the two command texts together separated by a semicolon.

Also note that you should always use a parameterized query when possible,
Alas the first query cannot be parameterized because you cannot use a parameter to express a table name. So please be sure to check the content of this TextBox2 before using it.

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    Using con  = New SqlConnection(........)
        Using cmd = New SqlCommand("CREATE TABLE " & TextBox2.Text & _
                    " (myId INTEGER PRIMARY KEY, myName CHAR(50), myAddress CHAR(255), " & _
                    " myBalance FLOAT);Update Tarrifs Set Name=@name", con)
             con.Open()
             cmd.Parameters.AddWithValue("@name", TextBox2.Text)
            cmd.ExecuteNonQuery()
        End Using
    End Using
End Sub

Another good practice is to apply the Using statement when you deal with Disposable objects like the SqlConnection and the SqlCommand. This will ensure the proper closing of the connection and the disposing of both the connection and the command

Multiple SQL statement using VB

1 Answers1