473,695 Members | 2,592 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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 OleDbDataAdapte r
Dim cmbCN As New OleDbCommandBui lder(daCN)

CNQrySel = "Select CNDate,CNPart,C NLoc,CNDesc,CNA mt,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.SelectComm and = New OleDbCommand(CN QrySel, cnCN)
daCN.SelectComm and.Parameters. Add("CNDate", OleDbType.Date, 8)
daCN.SelectComm and.Parameters. Add("CNPart", OleDbType.VarCh ar, 15)
daCN.SelectComm and.Parameters. Add("CNLoc", OleDbType.VarCh ar, 6)
' Create the UpdateCommand and parameters.
daCN.UpdateComm and = New OleDbCommand(CN QryUpd, cnCN)
daCN.UpdateComm and.Parameters. Add("@CNDesc", OleDbType.VarCh ar, 25, "CNDesc")
daCN.UpdateComm and.Parameters. Add("@CNAmt", OleDbType.Singl e, 4, "CNAmt")
daCN.UpdateComm and.Parameters. Add("@CNValue", OleDbType.Singl e, 4, "CNValue")
daCN.UpdateComm and.Parameters. Add("@CNDate", OleDbType.Date, 8, "CNDate")
daCN.UpdateComm and.Parameters. Add("@CNPart", OleDbType.VarCh ar, 15, "CNPart")
daCN.UpdateComm and.Parameters. Add("@CNLoc", OleDbType.VarCh ar, 6, "CNLoc")

cnCN.Open()

daCN.SelectComm and.Parameters( "CNDate").V alue = dtRES.Rows(Idx) .Item("STDate")
daCN.SelectComm and.Parameters( "CNPart").V alue = dtRES.Rows(Idx) .Item("STPart")
daCN.SelectComm and.Parameters( "CNLoc").Va lue = dtRES.Rows(Idx) .Item("STLoc")
CNCount = daCN.Fill(dtCN)

daCN.UpdateComm and.Parameters( "@CNDesc").Valu e = dtRES.Rows(Idx) .Item("STDesc")
daCN.UpdateComm and.Parameters( "@CNAmt").V alue = dtRES.Rows(Idx) .Item("STAmt")
daCN.UpdateComm and.Parameters( "@CNValue").Val ue = dtRES.Rows(Idx) .Item("STValue" )
daCN.UpdateComm and.Parameters( "@CNDate").Valu e = dtRES.Rows(Idx) .Item("STDate")
daCN.UpdateComm and.Parameters( "@CNPart").Valu e = dtRES.Rows(Idx) .Item("STPart")
daCN.UpdateComm and.Parameters( "@CNLoc").V alue = dtRES.Rows(Idx) .Item("STLoc")

dtCN.Rows(I).It em("CNDate") = dtRES.Rows(Idx) .Item("STDate")
dtCN.Rows(I).It em("CNPart") = dtRES.Rows(Idx) .Item("STPart")
dtCN.Rows(I).It em("CNLoc") = dtRES.Rows(Idx) .Item("STLoc")
dtCN.Rows(I).It em("CNDesc") = dtRES.Rows(Idx) .Item("STDesc")
dtCN.Rows(I).It em("CNAmt") = dtRES.Rows(Idx) .Item("STAmt")
dtCN.Rows(I).It em("CNValue") = dtRES.Rows(Idx) .Item("STValue" )

Try
daCN.Update(dtC N)
Catch ex As Exception
'An exception occurred
MsgBox(ex.ToStr ing)
End Try
dtCN.AcceptChan ges()

cnCN.Close()
Aug 10 '06 #1
2 2625
Hexman,

AFAIK does OleDB despite of the given samples on MSDN not use named
parameters.
>daCN.SelectCom mand.Parameters .Add("CNDate", OleDbType.Date, 8)
this is valid as well
daCN.SelectComm and.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.c om...
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 OleDbDataAdapte r
Dim cmbCN As New OleDbCommandBui lder(daCN)

CNQrySel = "Select CNDate,CNPart,C NLoc,CNDesc,CNA mt,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.SelectComm and = New OleDbCommand(CN QrySel, cnCN)
daCN.SelectComm and.Parameters. Add("CNDate", OleDbType.Date, 8)
daCN.SelectComm and.Parameters. Add("CNPart", OleDbType.VarCh ar, 15)
daCN.SelectComm and.Parameters. Add("CNLoc", OleDbType.VarCh ar, 6)
' Create the UpdateCommand and parameters.
daCN.UpdateComm and = New OleDbCommand(CN QryUpd, cnCN)
daCN.UpdateComm and.Parameters. Add("@CNDesc", OleDbType.VarCh ar, 25,
"CNDesc")
daCN.UpdateComm and.Parameters. Add("@CNAmt", OleDbType.Singl e, 4, "CNAmt")
daCN.UpdateComm and.Parameters. Add("@CNValue", OleDbType.Singl e, 4,
"CNValue")
daCN.UpdateComm and.Parameters. Add("@CNDate", OleDbType.Date, 8, "CNDate")
daCN.UpdateComm and.Parameters. Add("@CNPart", OleDbType.VarCh ar, 15,
"CNPart")
daCN.UpdateComm and.Parameters. Add("@CNLoc", OleDbType.VarCh ar, 6, "CNLoc")

cnCN.Open()

daCN.SelectComm and.Parameters( "CNDate").V alue =
dtRES.Rows(Idx) .Item("STDate")
daCN.SelectComm and.Parameters( "CNPart").V alue =
dtRES.Rows(Idx) .Item("STPart")
daCN.SelectComm and.Parameters( "CNLoc").Va lue =
dtRES.Rows(Idx) .Item("STLoc")
CNCount = daCN.Fill(dtCN)

daCN.UpdateComm and.Parameters( "@CNDesc").Valu e =
dtRES.Rows(Idx) .Item("STDesc")
daCN.UpdateComm and.Parameters( "@CNAmt").V alue =
dtRES.Rows(Idx) .Item("STAmt")
daCN.UpdateComm and.Parameters( "@CNValue").Val ue =
dtRES.Rows(Idx) .Item("STValue" )
daCN.UpdateComm and.Parameters( "@CNDate").Valu e =
dtRES.Rows(Idx) .Item("STDate")
daCN.UpdateComm and.Parameters( "@CNPart").Valu e =
dtRES.Rows(Idx) .Item("STPart")
daCN.UpdateComm and.Parameters( "@CNLoc").V alue =
dtRES.Rows(Idx) .Item("STLoc")

dtCN.Rows(I).It em("CNDate") = dtRES.Rows(Idx) .Item("STDate")
dtCN.Rows(I).It em("CNPart") = dtRES.Rows(Idx) .Item("STPart")
dtCN.Rows(I).It em("CNLoc") = dtRES.Rows(Idx) .Item("STLoc")
dtCN.Rows(I).It em("CNDesc") = dtRES.Rows(Idx) .Item("STDesc")
dtCN.Rows(I).It em("CNAmt") = dtRES.Rows(Idx) .Item("STAmt")
dtCN.Rows(I).It em("CNValue") = dtRES.Rows(Idx) .Item("STValue" )

Try
daCN.Update(dtC N)
Catch ex As Exception
'An exception occurred
MsgBox(ex.ToStr ing)
End Try
dtCN.AcceptChan ges()

cnCN.Close()

Aug 10 '06 #2
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.ht m

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.SelectCo mmand.Parameter s.Add("CNDate", OleDbType.Date, 8)
this is valid as well
daCN.SelectCom mand.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 OleDbDataAdapte r
Dim cmbCN As New OleDbCommandBui lder(daCN)

CNQrySel = "Select CNDate,CNPart,C NLoc,CNDesc,CNA mt,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.SelectCom mand = New OleDbCommand(CN QrySel, cnCN)
daCN.SelectCom mand.Parameters .Add("CNDate", OleDbType.Date, 8)
daCN.SelectCom mand.Parameters .Add("CNPart", OleDbType.VarCh ar, 15)
daCN.SelectCom mand.Parameters .Add("CNLoc", OleDbType.VarCh ar, 6)
' Create the UpdateCommand and parameters.
daCN.UpdateCom mand = New OleDbCommand(CN QryUpd, cnCN)
daCN.UpdateCom mand.Parameters .Add("@CNDesc", OleDbType.VarCh ar, 25,
"CNDesc")
daCN.UpdateCom mand.Parameters .Add("@CNAmt", OleDbType.Singl e, 4, "CNAmt")
daCN.UpdateCom mand.Parameters .Add("@CNValue" , OleDbType.Singl e, 4,
"CNValue")
daCN.UpdateCom mand.Parameters .Add("@CNDate", OleDbType.Date, 8, "CNDate")
daCN.UpdateCom mand.Parameters .Add("@CNPart", OleDbType.VarCh ar, 15,
"CNPart")
daCN.UpdateCom mand.Parameters .Add("@CNLoc", OleDbType.VarCh ar, 6, "CNLoc")

cnCN.Open()

daCN.SelectCom mand.Parameters ("CNDate").Valu e =
dtRES.Rows(Idx ).Item("STDate" )
daCN.SelectCom mand.Parameters ("CNPart").Valu e =
dtRES.Rows(Idx ).Item("STPart" )
daCN.SelectCom mand.Parameters ("CNLoc").Va lue =
dtRES.Rows(Idx ).Item("STLoc")
CNCount = daCN.Fill(dtCN)

daCN.UpdateCom mand.Parameters ("@CNDesc").Val ue =
dtRES.Rows(Idx ).Item("STDesc" )
daCN.UpdateCom mand.Parameters ("@CNAmt").Valu e =
dtRES.Rows(Idx ).Item("STAmt")
daCN.UpdateCom mand.Parameters ("@CNValue").Va lue =
dtRES.Rows(Idx ).Item("STValue ")
daCN.UpdateCom mand.Parameters ("@CNDate").Val ue =
dtRES.Rows(Idx ).Item("STDate" )
daCN.UpdateCom mand.Parameters ("@CNPart").Val ue =
dtRES.Rows(Idx ).Item("STPart" )
daCN.UpdateCom mand.Parameters ("@CNLoc").Valu e =
dtRES.Rows(Idx ).Item("STLoc")

dtCN.Rows(I).I tem("CNDate") = dtRES.Rows(Idx) .Item("STDate")
dtCN.Rows(I).I tem("CNPart") = dtRES.Rows(Idx) .Item("STPart")
dtCN.Rows(I).I tem("CNLoc") = dtRES.Rows(Idx) .Item("STLoc")
dtCN.Rows(I).I tem("CNDesc") = dtRES.Rows(Idx) .Item("STDesc")
dtCN.Rows(I).I tem("CNAmt") = dtRES.Rows(Idx) .Item("STAmt")
dtCN.Rows(I).I tem("CNValue") = dtRES.Rows(Idx) .Item("STValue" )

Try
daCN.Update(dtC N)
Catch ex As Exception
'An exception occurred
MsgBox(ex.ToStr ing)
End Try
dtCN.AcceptCha nges()

cnCN.Close()
Aug 10 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

46
5130
by: Kingdom | last post by:
In my data base I have a list of componet types e.g. type A - I have 8 off - type B I have 12 off etc. I'm using Set objRS = objDC.Execute("Select DISTINCT Component_Type FROM Parts_Table") to populate a drop down but would like to use several drop downs restricting the contents of each drop down to the records pertaining to one
8
5473
by: baustin75 | last post by:
Posted: Mon Oct 03, 2005 1:41 pm Post subject: cannot mail() in ie only when debugging in php designer 2005 -------------------------------------------------------------------------------- Hello, I have a very simple problem but cannot seem to figure it out. I have a very simple php script that sends a test email to myself. When I debug it in PHP designer, it works with no problems, I get the test email. If
25
4058
by: Neo Geshel | last post by:
This works: <form> <asp:TextBox id="name" /> <%= name.ClientID %> </form> But this DOES NOT work: <form>
3
3444
by: Shapper | last post by:
Hello, I have created 3 functions to insert, update and delete an Access database record. The Insert and the Delete code are working fine. The update is not. I checked and my database has all the necessary records in it when testing it. I get the error "No value given for one or more required parameters." when I try to update the database. Can you tell me what am I doing wrong?
15
2574
by: Jay | last post by:
I have a multi threaded VB.NET application (4 threads) that I use to send text messages to many, many employees via system.timer at a 5 second interval. Basically, I look in a SQL table (queue) to determine who needs to receive the text message then send the message to the address. Only problem is, the employee may receive up to 4 of the same messages because each thread gets the recors then sends the message. I need somehow to prevent...
10
2531
by: 60325 | last post by:
This is the page where I collect the data in drop-down boxes with values of 1-10 and send it to a submitted page to do calculations. Example: Employee1 TeamScore(1-10) Employee2 TeamScore(1-10) Employee3 TeamScore(1-10) Employee4 TeamScore(1-10) Then I submit this page with all the values in TeamScore for every employee and I want to perform a calculation based on the values in the drop-down and a
0
5567
by: gunimpi | last post by:
http://www.vbforums.com/showthread.php?p=2745431#post2745431 ******************************************************** VB6 OR VBA & Webbrowser DOM Tiny $50 Mini Project Programmer help wanted ******************************************************** For this teeny job, please refer to: http://feeds.reddit.com/feed/8fu/?o=25
0
4598
by: AxleWacl | last post by:
Hi, The below error is what I am receiving. The code im using is below the error, for the life of me, I can not see where any parameter is missing..... Server Error in '/FleetcubeNews' Application. -------------------------------------------------------------------------------- No value given for one or more required parameters. Description: An unhandled exception occurred during the execution of the current web request. Please...
0
8640
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8582
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
8860
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8832
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7670
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4348
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4587
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3018
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2280
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.