David David - 1 year ago 72
Vb.net Question

What is wrong with this query? I'm providing values for all of the parameters

I have a vb.net project with an update function in, where I am passing values into a database SQL function from the save button click method.

In the database table that I am updating, there are 10 fields. As you can see in the query below, I am providing 10 values, one for each field.

Yet, still, when it hits the line

, it outputs the error

No value given for one or more required parameters

I can't work out why - Can anybody else?

Public Shared Function UpdateFreight(ByVal OrderNumber As Integer, _
ByVal OrderRef As String, _
ByVal FreightNum As Integer, _
ByVal FreightVal As Double, _
ByVal FreightDesc As String, _
ByVal ProductCode As String, _
ByVal EstDelivery As DateTime, _
ByVal SCode As String, _
ByVal CCode As String, _
ByVal AddressCode As String, _
con As OleDbConnection)

Dim Dc As New OleDbCommand
Dc.Connection = con

Dc = New OleDb.OleDbCommand("UPDATE [Order_Freight] SET [Order_Number] = ?, _
[Order_Reference] = ?, _
[Freight_Number] = ?, [Freight_Val] = ?, _
[Freight_Desc] = ?, [Product_Code] = ?,_
[Est_Delivery] = ?, [Supplier_Code] = ?, _
[Customer_Code] = ?, [Address_Code] = ? _
WHERE [OrderNumber] = ?", con)

Dc.Parameters.AddWithValue("@ono", OrderNumber)
Dc.Parameters.AddWithValue("@oref", OrderRef)
Dc.Parameters.AddWithValue("@fnum", FreightNum)
Dc.Parameters.AddWithValue("@fval", FreightVal)
Dc.Parameters.AddWithValue("@fdesc", FreightDesc)
Dc.Parameters.AddWithValue("@pcode", ProductCode)
Dc.Parameters.AddWithValue("@estd", EstDelivery)
Dc.Parameters.AddWithValue("@scode", SCode)
Dc.Parameters.AddWithValue("@ccode", CCode)
Dc.Parameters.AddWithValue("@acode", AddressCode)
Dc.Parameters.AddWithValue("@onum", OrderNumber)


Catch ex As Exception
MsgBox("Failed to update data, refer to error log")

End Try
End Function

If it helps, this is where the function is called

database.UpdateFreight(OrderNumber, OrderRef, FNum, FreightVal, FreightDesc, PCode, EstDate, SCode, custCode, Address, con)

And to clarify that all parameters are being given values

enter image description here

(Also, it isn't to do with code formatting, I've adapted the formatting a ton just for the readability of the question, it doesn't look like this in vb)

Answer Source

I think the issue is because you have a column misnamed

UPDATE [Order_Freight] SET [Order_Number] = ?, 
[Order_Reference] = ?, 
[Freight_Number] = ?, [Freight_Val] = ?, 
[Freight_Desc] = ?, [Product_Code] = ?,
[Est_Delivery] = ?, [Supplier_Code] = ?, 
[Customer_Code] = ?, [Address_Code] = ? 
WHERE [OrderNumber] = ?

Notice SET [Order_Number] = ? and WHERE [OrderNumber] = ?. One of those is probably not right and is being interpreted as an unknown column and thus Access treats it as a parameter. Fix the spelling on your Order Number column to be right.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download