Board index » Visual Studio » Help Needed. Update with parameters error - No value given for one or more parameters.

Visual Studio363
Hello All,



Well I'm stumped once more. Need some help. Writing a simple select and update program using VB.Net 2005 and an Access DB. I'm using parameters in

my update statement and when trying to update a record, I get a "No value given for one or more parameters." error message.



I use a Select with parameters and an Update with parameters. The select works fine. I thought I've tried everything (evidently not) to get this

working. Please show me the errors of my ways or a different way to solve. I purposely want to create the da, dt, cn, etc. in code so I will get

used to them.



Thanks,



Hexman



Here's the excerpt of the failing code. (dtRES contains the transactions to update dtCN. The index variables (I & Idx) are correct in their values.





Private cnCN As OleDbConnection

Private CNQrySel As String

Private CNQryAdd As String

Private CNQryUpd As String

Private CNQryDel As String

Private CNCount As Integer

Dim dtCN As New DataTable

Dim daCN As New OleDbDataAdapter

Dim cmbCN As New OleDbCommandBuilder(daCN)



CNQrySel = "Select CNDate,CNPart,CNLoc,CNDesc,CNAmt,CNValue " & _

"FROM CNMaster " & _

"WHERE (CNDate = ? and CNPart = ? and CNLoc = ?) ; "

CNQryAdd = " ; "

CNQryDel = " ; "

CNQryUpd = "UPDATE CNMaster " & _

"SET @CNDesc = ?, @CNAmt = ?, @CNValue = ?" & _

"WHERE (CNDate = ? and CNPart = ? and CNLoc = ?) ; "

' Create the SelectCommand and parameters.

daCN.SelectCommand = New OleDbCommand(CNQrySel, cnCN)

daCN.SelectCommand.Parameters.Add("CNDate", OleDbType.Date, 8)

daCN.SelectCommand.Parameters.Add("CNPart", OleDbType.VarChar, 15)

daCN.SelectCommand.Parameters.Add("CNLoc", OleDbType.VarChar, 6)

' Create the UpdateCommand and parameters.

daCN.UpdateCommand = New OleDbCommand(CNQryUpd, cnCN)

daCN.UpdateCommand.Parameters.Add("@CNDesc", OleDbType.VarChar, 25, "CNDesc")

daCN.UpdateCommand.Parameters.Add("@CNAmt", OleDbType.Single, 4, "CNAmt")

daCN.UpdateCommand.Parameters.Add("@CNValue", OleDbType.Single, 4, "CNValue")

daCN.UpdateCommand.Parameters.Add("@CNDate", OleDbType.Date, 8, "CNDate")

daCN.UpdateCommand.Parameters.Add("@CNPart", OleDbType.VarChar, 15, "CNPart")

daCN.UpdateCommand.Parameters.Add("@CNLoc", OleDbType.VarChar, 6, "CNLoc")



cnCN.Open()



daCN.SelectCommand.Parameters("CNDate").Value = dtRES.Rows(Idx).Item("STDate")

daCN.SelectCommand.Parameters("CNPart").Value = dtRES.Rows(Idx).Item("STPart")

daCN.SelectCommand.Parameters("CNLoc").Value = dtRES.Rows(Idx).Item("STLoc")

CNCount = daCN.Fill(dtCN)



daCN.UpdateCommand.Parameters("@CNDesc").Value = dtRES.Rows(Idx).Item("STDesc")

daCN.UpdateCommand.Parameters("@CNAmt").Value = dtRES.Rows(Idx).Item("STAmt")

daCN.UpdateCommand.Parameters("@CNValue").Value = dtRES.Rows(Idx).Item("STValue")

daCN.UpdateCommand.Parameters("@CNDate").Value = dtRES.Rows(Idx).Item("STDate")

daCN.UpdateCommand.Parameters("@CNPart").Value = dtRES.Rows(Idx).Item("STPart")

daCN.UpdateCommand.Parameters("@CNLoc").Value = dtRES.Rows(Idx).Item("STLoc")



dtCN.Rows(I).Item("CNDate") = dtRES.Rows(Idx).Item("STDate")

dtCN.Rows(I).Item("CNPart") = dtRES.Rows(Idx).Item("STPart")

dtCN.Rows(I).Item("CNLoc") = dtRES.Rows(Idx).Item("STLoc")

dtCN.Rows(I).Item("CNDesc") = dtRES.Rows(Idx).Item("STDesc")

dtCN.Rows(I).Item("CNAmt") = dtRES.Rows(Idx).Item("STAmt")

dtCN.Rows(I).Item("CNValue") = dtRES.Rows(Idx).Item("STValue")



Try

daCN.Update(dtCN)

Catch ex As Exception

'An exception occurred

MsgBox(ex.ToString)

End Try

dtCN.AcceptChanges()



cnCN.Close()


-

Hexman,



AFAIK does OleDB despite of the given samples on MSDN not use named

parameters.



daCN.SelectCommand.Parameters.Add("", OleDbType.Date, 8)



Maybe you can change your names in the setting and adding of the values to

the parameters to 0 to 5.



If it is than still not working: I once had your problem as well. I added an

extra parameter as a kind of dummy and the problem was gone. I never

investigated the reason.



I hope this helps,



Cor



"Hexman" <Hexman@Binary.com>schreef in bericht



-

Cor,



Thanks for the response. I changed my code to use positional parameters, but to no avail. But your message made me dig a bit deeper and i found my

answer at: ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.VisualStudio.v80.en/dv_raddata/html/195e0209-68d4-4e86-8a3b-f0d2f14332d8.htm



After reading about the update parameters I saw the error in my code. I changed :

Apparently I was setting the parameter (@CNDesc) to the same parameter rather than setting the column(CNDesc) to the parameter (@CNDesc).



Solved my problem. Although after reading I have more questions. I'll start a new thread for each on so they can be focused on.



Thanks,



Hexman







On Thu, 10 Aug 2006 07:31:12 +0200, "Cor Ligthert [MVP]" <notmyfirstname@planet.nl>wrote: