Help Needed. Update with parameters error - No value given for one or more parameters.

[Hexman]

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

[Cor Ligthert [MVP]]

Hexman,

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

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

this is valid as well
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" <He****@Binary.comschreef in bericht
news:4o********************************@4ax.com...

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]

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 :

>>CNQryUpd = "UPDATE CNMaster " & _
"SET @CNDesc = ?, @CNAmt = ?, @CNValue = ?" & _
"WHERE (CNDate = ? and CNPart = ? and CNLoc = ?) ; "

to this:

>>CNQryUpd = "UPDATE CNMaster " & _
"SET CNDesc = ?, CNAmt = ?, CNValue = ?" & _
"WHERE (CNDate = ? and CNPart = ? and CNLoc = ?) ; "

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]" <no************@planet.nlwrote:

>Hexman,

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

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

this is valid as well
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" <He****@Binary.comschreef in bericht
news:4o********************************@4ax.com.. .

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