CODEWITHSUNDEEP
vb.netms-access-2007
Harabati
Harabati

Reputation: 133

Update in new column after inserting into 2 columns in a db

I am using following code to insert records into a table from another table....

Private Sub InsDuplicateDB()

    Dim strInsDup As String = "INSERT INTO Duplicate_srno(Sr_no,chalan_no) SELECT sr_no,chaln_no FROM Vendor_Machine where sr_no=@srno"
    Dim comm_InsDup As OleDb.OleDbCommand = New OleDb.OleDbCommand(strInsDup, cnnOLEDB)
    comm_InsDup.Parameters.AddWithValue("@srno", cmbSn_no.Text)
    comm_InsDup.ExecuteNonQuery()

    Dim strUpdDup As String = "UPDATE Duplicate_srno SET sr_no = @srno,chaln_no =@chn_no,Problem=@problemWHERE sr_no = @srno AND chalan_no=@chn_no"
    Dim comm_Update As OleDb.OleDbCommand = New OleDb.OleDbCommand(strUpdDup, cnnOLEDB)

    comm_Update.Parameters.AddWithValue("@srno", cmbSn_no.Text)
    comm_Update.Parameters.AddWithValue("@chn_no", cmbChal_no.Text)
    comm_Update.Parameters.AddWithValue("@problem",strProb)

    comm_Update.ExecuteNonQuery()

End Sub

Here strProb is a string whose value is assigned in anoter sub function.. Here it gives error as "No values given for one or more required parameter.."

Please resolve my problem

Upvotes: 0

Views: 265

Answers (1)

Steve
Steve

Reputation: 216302

I don't know if this is a typo or not, but the update string lacks of a space between the parameter name @problem and the WHERE clause 

Dim strUpdDup As String = "UPDATE Duplicate_srno SET sr_no = @srno,chaln_no =@chn_no," & _
                          "Problem=@problem WHERE sr_no = @srno AND chalan_no=@chn_no"
                                           ^

However the update string could be simplified because you are updating two fields with the same values used in the where clause

Dim strUpdDup As String = "UPDATE Duplicate_srno SET Problem=@problem " & _
                          "WHERE sr_no = @srno AND chalan_no=@chn_no"

Apart from the missing space the error message says that the engine expects more parameters.
In OleDb the parameters are not recognized by their name. You need the same number of parameter that are defined by the placeholders in the string. In your original text, you have 5 placeholders but you add only 3 parameters. It doesn't matter if two of them are the same.

The revised code coould be the following

Dim strUpdDup As String = "UPDATE Duplicate_srno SET Problem=@problem " & _
                          "WHERE sr_no = @srno AND chalan_no=@chn_no"

Dim comm_Update As OleDb.OleDbCommand = New OleDb.OleDbCommand(strUpdDup, cnnOLEDB)
comm_Update.Parameters.AddWithValue("@problem",strProb)
comm_Update.Parameters.AddWithValue("@srno", cmbSn_no.Text)
comm_Update.Parameters.AddWithValue("@chn_no", cmbChal_no.Text)

Notice how I have added the @problem parameter as first in the collection. Now, the parameter collection is in the same order in which the placeholders are present in the command text.

Upvotes: 1

Related Questions