Executing two cmd.commandtext in one line

Question

The program executes one cmd.commandtext at a time. Both of my tables are using the same primary keys which is ID.

How do I execute both cmd.commandtext at the same time?

Imports System.Data
Imports System.Data.OleDb

Public Class Form1
Dim cnnOLEDB As New OleDbConnection
Dim cmdInsert As New OleDbCommand
Dim cmdOLEDB As New OleDbCommand
Dim cmdUpdate As New OleDbCommand
Dim connection = "Provider=Microsoft.ACE.OLEDB.12.0;Data    Source=C:\Users\Harry\Documents\Database1.accdb;"

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    cnnOLEDB.ConnectionString = connection
    cnnOLEDB.Open()
End Sub

Private Sub btnReal_Click(sender As Object, e As EventArgs) Handles btnReal.Click
    If txtName.Text <> "" And txtId.Text <> "" And txtReceipt.Text <> "" Then

        cmdUpdate.CommandText = "UPDATE customer Set Stu_Name = '" & txtName.Text & " '   " & "WHERE ID = " & txtId.Text & " ;  "

        cmdUpdate.CommandText = "UPDATE admin Set receipt = '" & txtReceipt.Text & " '   " & "WHERE ID = " & txtId.Text & " ;  "

        cmdUpdate.CommandType = CommandType.Text
        cmdUpdate.Connection = cnnOLEDB
        cmdUpdate.ExecuteNonQuery()

        cmdUpdate.Dispose()

        MsgBox(txtName.Text + "Record Updated!")

    End If
End Sub

What is your database? What is the ADO.NET client classes in use here? (SqlCommand, OleDbCommand, MySqlCommand...) — Steve, Oct 21 '15 at 17:29
Hopefully there will be no students named "O'Brian" or "D'Angelo" — Ňɏssa Pøngjǣrdenlarp, Oct 21 '15 at 17:36
Practicaly it cannot happen because of db object locks. Unless you configure your db transaction in some sort of 'chaotic' isolation level. — Mykola, Oct 21 '15 at 17:50
see also [Reuse or Dispose DB Connections?](http://stackoverflow.com/a/28216964/1070452) — Ňɏssa Pøngjǣrdenlarp, Oct 21 '15 at 18:10

score 4 · Answer 1 · answered Oct 21 '15 at 17:38

The first thing to say here is: Do not use string concatenation to build sql queries, but use always a parameterized query. Without it you will find a lot of problem like parsing of single quotes in your name or worst an Sql Injection hack. Said that, I suppose that you are using OleDb (probably with an Access database). This scenario doesn't support the execution of multiple command in a single sql statement.
You are forced to call the ExecuteNonQuery two times after changing the command text

Private Sub btnReal_Click(sender As Object, e As EventArgs) Handles btnReal.Click
    If txtName.Text <> "" And txtId.Text <> "" And txtReceipt.Text <> "" Then
        cmdUpdate.Connection = cnnOLEDB
        cmdUpdate.CommandText = "UPDATE customer Set Stu_Name = @name " & _
                                "WHERE ID = @id"
        cmdUpdate.Parameters.Add("@name", OleDbType.VarWChar).Value =  txtName.Text 
        cmdUpdate.Parameters.Add("@id", OleDbType.Integer).Value = Convert.ToInt32(txtId.Text)
        cmdUpdate.ExecuteNonQuery()

        cmdUpdate.Parameters.Clear()
        cmdUpdate.CommandText = "UPDATE admin Set receipt = @recpt " & _ 
                                "WHERE ID = @id"
        cmdUpdate.Parameters.Add("@recpt", OleDbType.VarWChar).Value =  txtReceipt.Text 
        cmdUpdate.Parameters.Add("@id", OleDbType.Integer).Value = Convert.ToInt32(txtId.Text)
        cmdUpdate.ExecuteNonQuery()
    End If
End Sub

Not strictly related to your actual problem, but it seems pretty clear from your code that you have a global connection object and also a global command object. Don't do that. You gain very little in terms of performance but you will have problems with disposing connection, keep track of open/closed state and you will consume precious system resources. Always use a local connection object inside a using statement.

Executing two cmd.commandtext in one line

1 Answers1