Insert the deleted data into table history using MSSQL in visual basic 2008

[nurman_1]

I want to insert the deleted data into another table (Like table history) in sql server database using visual basic 2008 by scanning QR Code data. What I have done in my source code program is like this;

Private Sub btnDel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDel.Click
    Dim DivNo As String = eDivNo.Text
    Dim InvID As String = eInvID.Text.Trim
    Dim sql As String = String.Empty
    sql &= "INSERT INTO dbo.H_WhsTrans"
    sql &= " SELECT (SELECT dbo.f_GetNewTransID(substring(convert(varchar,getdate(),112),3,6)) as ID)"
    sql &= ",1"
    sql &= ",InvID"
    sql &= ",DivNo"
    sql &= ",SectionID"
    sql &= ",ItemID"
    sql &= ",ProcessID"
    sql &= ",LineID"
    sql &= ",[Location]"
    sql &= ",LotNo"
    sql &= ",RefNo1"
    sql &= ",RefNo2"
    sql &= ",RefNo3"
    sql &= ",Qty"
    sql &= ",Pcs"
    sql &= ",Unit"
    sql &= ",WIP"
    sql &= ",StartingDate"
    sql &= ",[ExpireDate]"
    sql &= ",SADate"
    sql &= ",SANote"
    sql &= ",Reservation1"
    sql &= ",Reservation2"
    sql &= ",Reservation3"
    sql &= ",[Status]"
    sql &= ",''"
    sql &= ",0"
    sql &= ",'NURMAN'"
    sql &= ",0"
    sql &= ",convert(varchar,GETDATE(),120)"
    sql &= ",'" & Setting.SettingHelper.HostName & "'"
    sql &= ",convert(varchar,GETDATE(),120)"
    sql &= ",'" & Setting.SettingHelper.HostName & "')"
    sql &= "FROM dbo.R_Warehouse WHERE InvID = @InvID AND InvDate = HMCS.dbo.f_GetInvDate()"

    sql &= "DELETE FROM dbo.R_Warehouse"
    sql &= "WHERE InvDate = HMCS.dbo.f_GetInvDate()"
    sql &= "AND InvID = @InvID AND DivNo = @DivNo"

    Dim dbConn As New DatabaseConnectionForPDA.DatabaseConnection
    Using con As New SqlClient.SqlConnection(DatabaseConnectionForPDA.DatabaseConnection.CONNECT_RO_FOR_COMMON)
        Using cmd As New SqlCommand()
            With cmd
                .Connection = con
                .CommandType = CommandType.Text
                .CommandText = sql
                .Parameters.AddWithValue("@InvID", InvID)
                .Parameters.AddWithValue("@DivNo", DivNo)
            End With
            Try
                con.Open()
                Dim msg As String = "Do you want to delete all data？"
                Dim msgRet As DialogResult = MessageBox.Show(msg, "", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2)
                If msgRet = Windows.Forms.DialogResult.Yes Then
                    cmd.ExecuteNonQuery()
                    MessageBox.Show("Data has been deleted")
                    Me.ClearInput()
                End If
            Catch ex As Exception
                MessageBox.Show(ex.Message.ToString(), "Error Message")
                con.Close()
                Return
            End Try
        End Using
    End Using
End Sub

When I try to run the query in sql database, it's running well. But unfortunately, it does not go as well when I try to execute it in visual basic, it's always show me the Error Message SqlException. Does anyone here could help me to resolve this problem ?

[pritaeas]

Lines 39 and 41 concatenate both queries into one, resulting in an invalid query.

IIRC you should separate them by a semi-colon. If that doesn't work, execute the queries separately, preferably within a transaction.