David David - 3 months ago 11
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

Dc.ExecuteNonQuery()
, 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)

Try
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)

Dc.ExecuteNonQuery()

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

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

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.