CODEWITHSUNDEEP
mysqlvb.net
Sky Scraper
Sky Scraper

Reputation: 185

How to insert data in 2 tables at the same time

Public Sub SaveNames(ByRef SQLStatement As String)
    Dim cmd As MySqlCommand = New MySqlCommand
    With cmd
        .CommandText = SQLStatement
        .CommandType = CommandType.Text
        .Connection = SQLConnection
        .ExecuteNonQuery()
    End With

    SQLConnection.Close()
    MsgBox("succesfully  added")
    SQLConnection.Dispose()
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)    Handles Button1.Click
    Dim cmd As MySqlCommand = New MySqlCommand

    Dim SQLStatement As String
    SQLStatement = "INSERT INTO pres(name) VALUES('" & txtPres.Text & "') "
    SQLStatement &= "INSERT INTO vpres(vname) VALUES('" & txtVice.Text & "') "
    SaveNames(SQLStatement)
    cmd.CommandText = SQLStatement

End Sub

this is my code, when i run this one,it runs smooth but when i save the records, i get the .ExecuteNonQuery() error on the public sub.

this is what it says

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO vpres(vname) VALUES('aaa')' at line 1

Upvotes: 2

Views: 143

Answers (2)

Mad Dog Tannen
Mad Dog Tannen

Reputation: 7244

You can use them in the same like this. Make sure you end the statement with ;. This DELIMITER tells MySQL when the syntax ends.

Dim SQLStatement As String
    SQLStatement = "INSERT INTO pres(name) VALUES('" & txtPres.Text & "');"
    SQLStatement = SQLStatment + "INSERT INTO vpres(vname) VALUES('" & txtVice.Text & "');"
    SaveNames(SQLStatement)
    cmd.CommandText = SQLStatement

For a little extra security you should replace ' with ''

 Dim SQLStatement As String
    SQLStatement = "INSERT INTO pres(name) VALUES('" & Replace(txtPres.Text,"'","''") & "');"
    SQLStatement = SQLStatment + "INSERT INTO vpres(vname) VALUES('" & Replace(txtVice.Text,"'","''") & "');"
    SaveNames(SQLStatement)
    cmd.CommandText = SQLStatement

Also, Your event for the button has code that is not needed.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)    Handles Button1.Click

    Dim SQLStatement As String
    SQLStatement = "INSERT INTO pres(name) VALUES('" & txtPres.Text & "') "
    SQLStatement = SQLStatment + "INSERT INTO vpres(vname) VALUES('" & txtVice.Text & "') "
    SaveNames(SQLStatement)

End Sub

All the database stuff happens in SaveNames.

Upvotes: 1

Mych
Mych

Reputation: 2553

You could be open to SQL Injection by using txtbox values. You would be better off using parametrised sql.

But Try....

Dim SQLStatement As String
SQLStatement = "INSERT INTO pres(name) VALUES('" & txtPres.Text & "'); "
SQLStatement += "INSERT INTO vpres(vname) VALUES('" & txtVice.Text & "'); "
SaveNames(SQLStatement)

But better still try....

Dim SQLStatement As New StringBuilder
SQLStatement.Append("INSERT INTO pres(name) VALUES('").Append(txtPres.Text).Append("'); ")
SQLStatement.Append("INSERT INTO vpres(vname) VALUES('").Append(txtVice.Text).Append("'); ")
SaveNames(SQLStatement.ToString)

Upvotes: 0

Related Questions