Click here to Skip to main content
15,354,574 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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 tried:

What I have done in my source code program is like this;

VB
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 ?
Posted
Updated 8-May-22 22:37pm

1 solution

Look at the string your code assembles:
SQL
INSERT INTO dbo.H_WhsTrans
 SELECT (SELECT dbo.f_GetNewTransID(substring(convert(varchar,getdate(),112),3,6)) as ID)
 1
,InvID
,DivNo
,SectionID
,ItemID
,ProcessID
,LineID
,[Location]
,LotNo
,RefNo1
,RefNo2
,RefNo3
,Qty
,Pcs
,Unit
,WIP
,StartingDate
,[ExpireDate]
,SADate
,SANote
,Reservation1
,Reservation2
,Reservation3
,[Status]
,''
,0
,'NURMAN'
,0
,convert(varchar,GETDATE(),120)
,' & Setting.SettingHelper.HostName & '
,convert(varchar,GETDATE(),120)
,' & Setting.SettingHelper.HostName & ')
FROM dbo.R_Warehouse WHERE InvID = @InvID AND InvDate = HMCS.dbo.f_GetInvDate()

DELETE FROM dbo.R_Warehouse
WHERE InvDate = HMCS.dbo.f_GetInvDate()
AND InvID = @InvID AND DivNo = @DivNo
The first bit is ... odd ...
SQL
INSERT INTO dbo.H_WhsTrans
 SELECT (SELECT ... as ID)
 1
,InvID
...

Why is there a "1" sitting there all alone?
   
Comments
nurman09 9-May-22 19:32pm
   
I forgot to put a comma (,) before 1.
I already changed my code, but it's still getting an error info while executing Catch ex As Exception
MessageBox.Show(ex.Message.ToString(), "Error Message"). I have no idea to resolve it.
OriginalGriff 10-May-22 1:11am
   
And what does the error say when the comma is fixed? Exactly what - use the debugger to look at the actual exception object and copy'n'paste the error detail.
nurman09 10-May-22 3:10am
   
I already solved it. The main problem is on DatabaseConnection. I used CONNECT_RO_FOR_COMMON instead of CONNECT_RW_FOR_COMMON. The difference between RO and RW is RO means ReadOnly and RW means ReadWrite.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900