Question Update two tables with sql command text

[ranjit_dutt]

hi i have two or more tables with same fields to update in a button click...
i am using this code:

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Dim res As New DialogResult
        res = MsgBox("Do you want to save data?", MsgBoxStyle.YesNo, "Client Details")
        If res = DialogResult.Yes Then
            If Me.txtClientCode.Text = "" Then
                MsgBox("ClientCode is Compulsory Field", MsgBoxStyle.Critical, "Client Info")
            Else
                Save_Record()
                Set_Button(2)
                res = MsgBox("Do you want to upadate the record in master?", MsgBoxStyle.YesNo, "Client Details")
                If res = Windows.Forms.DialogResult.Yes Then
                    Dim conn As New OleDbConnection
                    Dim cmd As New OleDbCommand
                    
                    Try
                        conn = New OleDbConnection(Get_Constring)
                        conn.Open()
                        cmd.Connection = conn
                        cmd.CommandType = CommandType.Text
                        Dim sSQL As String = String.Empty
                        sSQL = "UPDATE PassportApplication SET ClientName=@ClientName WHERE ClientCode=@ClientCode; UPDATE AnnexureA SET ClientName=@ClientName WHERE ClientCode=@ClientCode"

                        cmd.CommandText = sSQL
                    
                        cmd.Parameters.Add("@ClientName", OleDbType.VarChar).Value = IIf(Len(Trim(ClientFullName)) > 0, ClientFullName, DBNull.Value)
                        cmd.Parameters.Add("@ClientCode", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txtClientCode.Text)) > 0, Me.txtClientCode.Text, DBNull.Value)
                        cmd.ExecuteNonQuery()

                        MsgBox("Done")
                    Catch ex As Exception
                        MsgBox(ErrorToString)
                    Finally
                        conn.Close()
                    End Try
                Else
                    Return
                End If
            End If
        End If
    End Sub

but this give no result all done but not update

please advice

one more thing i would like update is i am using access database for backend

 

[jmcilhinney]

What you have done would work if you were using SQL Server but not with Access I'm afraid. Neither the Jet nor ACE OLE DB provider supports multiple statements per command. If you want to execute two UPDATE statements then you must execute two commands. Also, even if you use names for your parameters in your SQL code, those names are ignored and just the position is used to map parameters. That means that, if you have "@ClientCode" twice in your SQL then you need to add two parameters to your command. You have four parameters in your SQL so, if you could execute two statements, you'd still need to add four parameters to your command.

If you take out that second UPDATE statement from your CommandText then what you have should work. You can then simply set the CommandText of the command again and call ExecuteNonQuery a second time to update the second table. The parameters still exist so they will work as intended.

Ideally, you should probably wrap the two calls in a transaction, so as to ensure that they either both complete or neither do.

 

[ranjit_dutt]

thx jmcilhinney
my problem solved....

i have used this code

Dim sSQL1 As String = "UPDATE Table1 SET ClientName=@ClientName WHERE ClientCode=@ClientCode"
Dim sSQL2 As String = "UPDATE Table2 SET ClientName=@ClientName WHERE ClientCode=@ClientCode"
cmd.Parameters.Add("@ClientName", OleDbType.VarChar).Value = IIf(Len(Trim(ClientFullName)) > 0, ClientFullName, DBNull.Value)
cmd.Parameters.Add("@ClientCode", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txtClientCode.Text)) > 0, Me.txtClientCode.Text, DBNull.Value)

cmd.CommandText = sSQL1
cmd.ExecuteNonQuery()
cmd.CommandText = sSQL2
cmd.ExecuteNonQuery()

thx again