Home All Groups Group Topic Archive Search About

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

microsoft.public.dotnet.languages.vb
Author
10 Aug 2006 2:55 AM
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()

Author
10 Aug 2006 5:31 AM
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

Show quoteHide quote
"Hexman" <Hex***@Binary.com> schreef in bericht
news:4o7ld2dvcafmj4r52e3fb4dmv50g6v7alq@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()
Author
10 Aug 2006 6:46 PM
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



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

>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" <Hex***@Binary.com> schreef in bericht
>news:4o7ld2dvcafmj4r52e3fb4dmv50g6v7alq@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()
>



Post Other interesting topics
MessageBox in Validating event cancels subsequent events
VB2005 Hiding Windows Forms
Drawstring question...
ErrorProvider in .Net 2003
Passing parameters best practice
Setup project in VB.NET ?
Description of Function
How do I send null values to an integer type variable?
declare API without direct file name
Launch External Program