Error: "System.Data.OleDb.OleDbException: No value given for one or more required parameters"

[Brian Foree]

I am developing an ASP.NET application that uses Access 2000 as its backend,
and have just started getting the following error on 2 ASP.NET pages that
had been working until late last week (and I don't think I made any changes
to either page other than changing the user control that creates the
header).

Server Error in '/myApp' Application.
----------------------------------------------------------------------------
----

No value given for one or more required parameters.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: No value given for one
or more required parameters.

Source Error:

Line 721:
Line 722: myDataAdapter.SelectCommand.Parameters("Thing_ID").Value =
CInt(Session("Thing_ID"))
Line 723: m_intNum1 = myDataAdapter.Fill(myDataSet1, "Thing")
Line 724:


----------------------------------------------------------------------------
----


I'm not having any other data access problems in the application - other
pages do lookups to populate drop down lists, and I can add records to other
tables in the database. I've checked the table that the code in question is
trying to read, and it seems to be fine. I've debugged it, and the value of
the parameter (which is the only parameter for that command) IS set and it
IS an integer, which is the type of the parameter. It seems that the problem
is NOT because of the value of the parameter, but something else - does
anyone have any ideas?

 

[Craig Deelsnyder]

I am developing an ASP.NET application that uses Access 2000 as its
backend,
and have just started getting the following error on 2 ASP.NET pages that
had been working until late last week (and I don't think I made any
changes
to either page other than changing the user control that creates the
header).

Server Error in '/myApp' Application.
----------------------------------------------------------------------------
----

No value given for one or more required parameters.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: No value given for
one
or more required parameters.

Source Error:

Line 721:
Line 722:
myDataAdapter.SelectCommand.Parameters("Thing_ID").Value =
CInt(Session("Thing_ID"))
Line 723: m_intNum1 = myDataAdapter.Fill(myDataSet1, "Thing")
Line 724:


----------------------------------------------------------------------------
----


I'm not having any other data access problems in the application - other
pages do lookups to populate drop down lists, and I can add records to
other
tables in the database. I've checked the table that the code in question
is
trying to read, and it seems to be fine. I've debugged it, and the value
of
the parameter (which is the only parameter for that command) IS set and
it
IS an integer, which is the type of the parameter. It seems that the
problem
is NOT because of the value of the parameter, but something else - does
anyone have any ideas?

Can you include the lines of code surrounding these lines? Essentially
where you set your command text and the parameters? Can't tell anything
without those lines.

 

[Brian Foree]

--
--

Can you include the lines of code surrounding these lines? Essentially
where you set your command text and the parameters? Can't tell anything
without those lines.

Craig-

The command was generated by Visual Studio .NET (2003) as a result of
configuring a DataAdapter - here's the relevant code:

========================From .aspx.vb:========================
Protected WithEvents cnnThing As System.Data.OleDb.OleDbConnection
Protected WithEvents myDataAdapter As System.Data.OleDb.OleDbDataAdapter
Protected WithEvents OleDbSelectCommand1 As System.Data.OleDb.OleDbCommand

#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.

<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.cnnThing = New System.Data.OleDb.OleDbConnection
Me.myDataAdapter = New System.Data.OleDb.OleDbDataAdapter
Me.OleDbSelectCommand1 = New System.Data.OleDb.OleDbCommand
'
'cnnThing
'
Me.cnnThing.ConnectionString =
CType(configurationAppSettings.GetValue("cnn_Access.ConnectionString",
GetType
(System.String)), String)
'
'myDataAdapter
'
Me.myDataAdapter.SelectCommand = Me.OleDbSelectCommand1
Me.myDataAdapter.TableMappings.AddRange(New
System.Data.Common.DataTableMapping() {New
System.Data.Common.DataTableMapping
("Table", "Thing", New System.Data.Common.DataColumnMapping()
{New System.Data.Common.DataColumnMapping("Thing_ID",
"Thing_ID"), New
System.Data.Common.DataColumnMapping("Thing_Person", "Thing_Person"), New
System.Data.Common.DataColumnMapping("Thing_Name",
"Thing_Name"), New System.Data.Common.DataColumnMapping
("Thing_Number", "Thing_Number"), New
System.Data.Common.DataColumnMapping("Thing_People", "Thing_People")})})
'
'OleDbSelectCommand1
'
Me.OleDbSelectCommand1.CommandText = "SELECT Thing_ID,
Thing_Person_FName + ' ' + Thing_Person_LName AS " & _
"Thing_Person, Thing_Name, Thing_Number, Thing_People FROM Thing
WHERE (" & _
"Thing_ID = ?)"
Me.OleDbSelectCommand1.Connection = Me.cnnThing
Me.OleDbSelectCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Thing_ID",
System.Data.OleDb.OleDbType.Integer, 0,
"Thing_ID"))
End Sub

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
'Code to dynamically set the path to the Access database:
cnnThing.ConnectionString = String.Format(cnnThing.ConnectionString,
Server.MapPath("Thing.mdb"))
End Sub
===========================================================

=======================From web.config:=======================
<appSettings>
<!-- User application and configured property settings go here.-->
<!-- Example: <add key="settingName" value="settingValue"/> -->

<add key="cnnDPMO_Access.ConnectionString" value="Jet OLEDB:Global
Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDBatabase Locking
Mode=1;Jet OLEDBatabase Password=;Data Source='{0}';Password=;Jet
OLEDB:Engine Type=5;Jet OLEDB:Global Bulk
Transactions=1;Provider=&quot;Microsoft.Jet.OLEDB.4.0&quot;;Jet OLEDB:System
database=;Jet OLEDB:SFP=False;Extended Properties=;Mode=Share Deny None;Jet
OLEDB:New Database Password=;Jet OLEDB:Create System Database=False;Jet
OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica
Repair=False;User ID=Admin;Jet OLEDB:Encrypt Database=False" />

</appSettings>
===========================================================

 

[Brian Foree]

Well, no thanks to the (not-in-the-least-bit-) informative error message, I
figured it out!

The error message led me to look at the lone parameter for the DataAdapter's
SelectCommand, which did indeed have a value, so after much digging around,
I looked back at the query for the SelectCommand and realized that a few
columns had been moved to another table, so what the message really meant is
that I was trying to SELECT non-existent columns! Why didn't it just say so
in the first place?!

Thanks to Oliver of the Austin (TX) .NET User's Group (www.adnug.org) for
steering me in the right direction.