• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 285
  • Last Modified:

Error: No value given for one or more required parameters

I have a VB.NET 2003 program. It has an OldDbDataAdapter with the following Select command:

Me.OleDbSelectCommand1.CommandText = "SELECT Forenames, Surname, Phone_Home, Phone_Work, Mobile, Fax, EMail, Physical_S" & _
        "treet, Physical_Suburb, Physical_City, Postal_Street, Postal_Suburb, Postal_City" & _
        ", Postal_Code, Wants_EMail, Wants_Fax, Wants_Postal, Wants_Physical, Minimum_Bed" & _
        "rooms, Maximum_Bedrooms, Minimum_Bathrooms, Maximum_Bathrooms, Minimum_Carports," & _
        " Maximum_Carports, Minimum_Garages, Maximum_Garages, Minimum_Property_Size, Maxi" & _
        "mum_Property_Size, Minimum_Price, Maximum_Price, Suburb, Area, City, Property_Ty" & _
        "pe_ID, Rent, Comments, ID FROM Prospective_Clients"

This command basically gets all the fields from an Access 2000 database. When I run it, I get the following error message:

     No value given for one or more required parameters

If I remove all the fields from the above query that start with the words Maximum_ or Minimum_ then it runs, but as soon as I reintroduce any of the fields I removed, it gives that error message again. Strangely enough, all the fields beginning with those two words are number fields. Everything else is either a Yes/No field or a Text field, with the exeption of Property_Type_ID and ID which are also number fields.
0
SETP
Asked:
SETP
  • 10
  • 9
  • 2
1 Solution
 
RonaldBiemansCommented:
try putting the fields that contain that between []




0
 
RonaldBiemansCommented:
minimum and maximum are reserved words
0
 
RonaldBiemansCommented:
so like

Me.OleDbSelectCommand1.CommandText = "SELECT Forenames, Surname, Phone_Home, Phone_Work, Mobile, Fax, EMail, Physical_S" & _
        "treet, Physical_Suburb, Physical_City, Postal_Street, Postal_Suburb, Postal_City" & _
        ", Postal_Code, Wants_EMail, Wants_Fax, Wants_Postal, Wants_Physical, [Minimum_Bed]" & _
        "rooms, [Maximum_Bedrooms], [Minimum_Bathrooms], [Maximum_Bathrooms], [Minimum_Carports]," & _
        " [Maximum_Carports], [Minimum_Garages], [Maximum_Garages], [Minimum_Property_Size], [Maxi" & _
        "mum_Property_Size], [Minimum_Price], [Maximum_Price], Suburb, Area, City, Property_Ty" & _
        "pe_ID, Rent, Comments, ID FROM Prospective_Clients"

0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
SETPAuthor Commented:
Hi Ronald. Thanks for your quick reply. I tried your suggestion but it still gives me that same error message.

Actually, I initially had the field names that begin with Maximum_ and Minimum_ as Max_ and Min_. Then I though maybe Max and Min were reserved keywords, so I changed all occurences of those words to Maximum_ and Minimum_. But alas, it made no difference. I don't understand what could be going wrong. I've created select statements like this a hundred times before with no problems other than the reserved keyword problem you spoke of. But even then I think the error message was different (don't quote me on that though).

Analysing the error message, it appears as though it is complaining about a parameter. But this OleDbSelectCommand has no parameters. I checked again just now just to make sure. In fact, the SQL query doesn't even have a WHERE clause!

I just don't know...
0
 
SETPAuthor Commented:
I've tried putting ALL fields into square brackets - didn't work. I then put all fields in the OleDbDeleteCommand, OleDbUpdateCommand and OleDbInsertCommand in square brackets just in case. Again no difference. And then I finally tried replacing the entire SQL query with the following:

SELECT * FROM Prospective_Clients

And gues what?..... NO DIFFERENCE! :(
0
 
SETPAuthor Commented:
This is what the Access database table looks like in design view:

ID                  AutoNumber
Forenames            Text
Surname                  Text
Physical_Street            Text
Physical_Suburb            Text
Physical_City            Text
Postal_Street            Text
Postal_Suburb            Text
Postal_City                            Text
Postal_Code            Text
Phone_Home            Text
Phone_Work            Text
Mobile                  Text
Fax                  Text
EMail                  Text
Minimum_Bedrooms                      Number
Maximum_Bedrooms                      Number
Minimum_Bathrooms                      Number
Maximum_Bathrooms                      Number
Minimum_Garages            Number
Maximum_Garages            Number
Minimum_Carports                      Number
Maximum_Carports                      Number
Minimum_Price            Number
Maximum_Price            Number
Minimum_Property_Size      Number
Maximum_Property_Size      Number
Suburb                  Text
Area                  Text
City                  Text
Property_Type_ID                      Number
Rent                  Yes/No
Wants_EMail            Yes/No
Wants_Fax                            Yes/No
Wants_Postal            Yes/No
Wants_Physical            Yes/No
Comments                            Text
0
 
SETPAuthor Commented:
I tried both with an empty table, and with a populated table. Makes no difference
0
 
Bob LearnedCommented:
And with that many fields, what's wrong with using Select * ?

Bob
0
 
Bob LearnedCommented:
Try to find out what parameters are expected by creating an OleDbAdapter by dragging/dropping on form, and then look at the SelectCommand, and the Parameters property.

Bob
0
 
RonaldBiemansCommented:
Hi SETP, I just recreated your table and ran your code, and it works just fine. Are you sure you don't get this error for some other reason ? Could you post the entire code
0
 
SETPAuthor Commented:
The code is very simple. I have this line in my Form_Load event:

daProspectiveClients.Fill(DsProspectiveClients1, "Prospective_Clients")

and that's where it bombs out. There is no other code that executes before that line, other than the Windows Form Designer Generated Code. I've pasted the most relevant part of the designer generated code below:

'
        'conProspectiveClients
        '
        Me.conProspectiveClients.ConnectionString = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database L" & _
        "ocking Mode=1;Data Source=""D:\My Documents\Visual Studio Projects\PCLet\PCLet Da" & _
        "tabase\PCLet.mdb"";Mode=Share Deny None;Jet OLEDB:Engine Type=5;Provider=""Microso" & _
        "ft.Jet.OLEDB.4.0"";Jet OLEDB:System database=;Jet OLEDB:SFP=False;persist securit" & _
        "y info=False;Extended Properties=;Jet OLEDB:Compact Without Replica Repair=False" & _
        ";Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create System Database=False;Jet OLE" & _
        "DB:Don't Copy Locale on Compact=False;User ID=Admin;Jet OLEDB:Global Bulk Transa" & _
        "ctions=1"
        '
        'daProspectiveClients
        '
        Me.daProspectiveClients.DeleteCommand = Me.OleDbDeleteCommand1
        Me.daProspectiveClients.InsertCommand = Me.OleDbInsertCommand1
        Me.daProspectiveClients.SelectCommand = Me.OleDbSelectCommand1
        Me.daProspectiveClients.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "Prospective_Clients", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("Forenames", "Forenames"), New System.Data.Common.DataColumnMapping("Surname", "Surname"), New System.Data.Common.DataColumnMapping("Phone_Home", "Phone_Home"), New System.Data.Common.DataColumnMapping("Phone_Work", "Phone_Work"), New System.Data.Common.DataColumnMapping("Mobile", "Mobile"), New System.Data.Common.DataColumnMapping("Fax", "Fax"), New System.Data.Common.DataColumnMapping("EMail", "EMail"), New System.Data.Common.DataColumnMapping("Physical_Street", "Physical_Street"), New System.Data.Common.DataColumnMapping("Physical_Suburb", "Physical_Suburb"), New System.Data.Common.DataColumnMapping("Physical_City", "Physical_City"), New System.Data.Common.DataColumnMapping("Postal_Street", "Postal_Street"), New System.Data.Common.DataColumnMapping("Postal_Suburb", "Postal_Suburb"), New System.Data.Common.DataColumnMapping("Postal_City", "Postal_City"), New System.Data.Common.DataColumnMapping("Postal_Code", "Postal_Code"), New System.Data.Common.DataColumnMapping("Wants_EMail", "Wants_EMail"), New System.Data.Common.DataColumnMapping("Wants_Fax", "Wants_Fax"), New System.Data.Common.DataColumnMapping("Wants_Postal", "Wants_Postal"), New System.Data.Common.DataColumnMapping("Wants_Physical", "Wants_Physical"), New System.Data.Common.DataColumnMapping("Minimum_Bedrooms", "Minimum_Bedrooms"), New System.Data.Common.DataColumnMapping("Maximum_Bedrooms", "Maximum_Bedrooms"), New System.Data.Common.DataColumnMapping("Minimum_Bathrooms", "Minimum_Bathrooms"), New System.Data.Common.DataColumnMapping("Maximum_Bathrooms", "Maximum_Bathrooms"), New System.Data.Common.DataColumnMapping("Minimum_Carports", "Minimum_Carports"), New System.Data.Common.DataColumnMapping("Maximum_Carports", "Maximum_Carports"), New System.Data.Common.DataColumnMapping("Minimum_Garages", "Minimum_Garages"), New System.Data.Common.DataColumnMapping("Maximum_Garages", "Maximum_Garages"), New System.Data.Common.DataColumnMapping("Minimum_Property_Size", "Minimum_Property_Size"), New System.Data.Common.DataColumnMapping("Maximum_Property_Size", "Maximum_Property_Size"), New System.Data.Common.DataColumnMapping("Minimum_Price", "Minimum_Price"), New System.Data.Common.DataColumnMapping("Maximum_Price", "Maximum_Price"), New System.Data.Common.DataColumnMapping("Suburb", "Suburb"), New System.Data.Common.DataColumnMapping("Area", "Area"), New System.Data.Common.DataColumnMapping("City", "City"), New System.Data.Common.DataColumnMapping("Property_Type_ID", "Property_Type_ID"), New System.Data.Common.DataColumnMapping("Rent", "Rent"), New System.Data.Common.DataColumnMapping("Comments", "Comments"), New System.Data.Common.DataColumnMapping("ID", "ID")})})
        Me.daProspectiveClients.UpdateCommand = Me.OleDbUpdateCommand1
        '
        'OleDbSelectCommand1
        '
        Me.OleDbSelectCommand1.CommandText = "SELECT Forenames, Surname, Phone_Home, Phone_Work, Mobile, Fax, EMail, Physical_S" & _
        "treet, Physical_Suburb, Physical_City, Postal_Street, Postal_Suburb, Postal_City" & _
        ", Postal_Code, Wants_EMail, Wants_Fax, Wants_Postal, Wants_Physical, Minimum_Bed" & _
        "rooms, Maximum_Bedrooms, Minimum_Bathrooms, Maximum_Bathrooms, Minimum_Carports," & _
        " Maximum_Carports, Minimum_Garages, Maximum_Garages, Minimum_Property_Size, Maxi" & _
        "mum_Property_Size, Minimum_Price, Maximum_Price, Suburb, Area, City, Property_Ty" & _
        "pe_ID, Rent, Comments, ID FROM Prospective_Clients"
        Me.OleDbSelectCommand1.Connection = Me.conProspectiveClients
0
 
RonaldBiemansCommented:
Hi SETP, Could you send the xml file of the typed dataset DsProspectiveClients1, because I think it does not match your select statement. Have you changed the table after you created the typed dataset, or changed the typed dataset but not the select statement.

maybe try recreating your typed dataset.
0
 
SETPAuthor Commented:
Firstly, let me say thank you very much Ronald for your pesristant help and not giving up on me. I truly appreciate it. Here is the XML code of the schema for the DataSet as requested:

<?xml version="1.0" standalone="yes"?>
<xs:schema id="dsProspectiveClients" targetNamespace="http://www.tempuri.org/dsProspectiveClients.xsd" xmlns:mstns="http://www.tempuri.org/dsProspectiveClients.xsd" xmlns="http://www.tempuri.org/dsProspectiveClients.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" attributeFormDefault="qualified" elementFormDefault="qualified">
  <xs:element name="dsProspectiveClients" msdata:IsDataSet="true" msdata:Locale="en-ZA">
    <xs:complexType>
      <xs:choice maxOccurs="unbounded">
        <xs:element name="Prospective_Clients">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="Forenames" type="xs:string" minOccurs="0" />
              <xs:element name="Surname" type="xs:string" minOccurs="0" />
              <xs:element name="Phone_Home" type="xs:string" minOccurs="0" />
              <xs:element name="Phone_Work" type="xs:string" minOccurs="0" />
              <xs:element name="Mobile" type="xs:string" minOccurs="0" />
              <xs:element name="Fax" type="xs:string" minOccurs="0" />
              <xs:element name="EMail" type="xs:string" minOccurs="0" />
              <xs:element name="Physical_Street" type="xs:string" minOccurs="0" />
              <xs:element name="Physical_Suburb" type="xs:string" minOccurs="0" />
              <xs:element name="Physical_City" type="xs:string" minOccurs="0" />
              <xs:element name="Postal_Street" type="xs:string" minOccurs="0" />
              <xs:element name="Postal_Suburb" type="xs:string" minOccurs="0" />
              <xs:element name="Postal_City" type="xs:string" minOccurs="0" />
              <xs:element name="Postal_Code" type="xs:string" minOccurs="0" />
              <xs:element name="Wants_EMail" type="xs:boolean" minOccurs="0" />
              <xs:element name="Wants_Fax" type="xs:boolean" minOccurs="0" />
              <xs:element name="Wants_Postal" type="xs:boolean" minOccurs="0" />
              <xs:element name="Wants_Physical" type="xs:boolean" minOccurs="0" />
              <xs:element name="Minimum_Bedrooms" type="xs:int" minOccurs="0" />
              <xs:element name="Maximum_Bedrooms" type="xs:int" minOccurs="0" />
              <xs:element name="Minimum_Bathrooms" type="xs:int" minOccurs="0" />
              <xs:element name="Maximum_Bathrooms" type="xs:int" minOccurs="0" />
              <xs:element name="Minimum_Carports" type="xs:int" minOccurs="0" />
              <xs:element name="Maximum_Carports" type="xs:int" minOccurs="0" />
              <xs:element name="Minimum_Garages" type="xs:int" minOccurs="0" />
              <xs:element name="Maximum_Garages" type="xs:int" minOccurs="0" />
              <xs:element name="Minimum_Property_Size" type="xs:int" minOccurs="0" />
              <xs:element name="Maximum_Property_Size" type="xs:int" minOccurs="0" />
              <xs:element name="Minimum_Price" type="xs:decimal" minOccurs="0" />
              <xs:element name="Maximum_Price" type="xs:decimal" minOccurs="0" />
              <xs:element name="Suburb" type="xs:string" minOccurs="0" />
              <xs:element name="Area" type="xs:string" minOccurs="0" />
              <xs:element name="City" type="xs:string" minOccurs="0" />
              <xs:element name="Property_Type_ID" type="xs:int" minOccurs="0" />
              <xs:element name="Rent" type="xs:boolean" minOccurs="0" />
              <xs:element name="Comments" type="xs:string" minOccurs="0" />
              <xs:element name="ID" msdata:AutoIncrement="true" type="xs:int" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:choice>
    </xs:complexType>
    <xs:unique name="Constraint1" msdata:PrimaryKey="true">
      <xs:selector xpath=".//mstns:Prospective_Clients" />
      <xs:field xpath="mstns:ID" />
    </xs:unique>
  </xs:element>
</xs:schema>

Oh, and I did change the table after creating the dataset, but I then deleted the dataset and recreated it, overwriting the old one (the *.xsd file)
0
 
RonaldBiemansCommented:
Did you just delete the dataset under the form and did you also delete the dataset xml schema from solution explorer ?

0
 
SETPAuthor Commented:
Yes, I deleted both the dataset and the associated XML schema
0
 
RonaldBiemansCommented:
I still think it has something to do with you changing the table, Could you just try to remove the dataadapter, connection, dataset and schema from the project. refresh the tables in the  server explorer, and add them again.

I remember something like this happening to me, and I forgot to refresh the table in the solution explorer
0
 
RonaldBiemansCommented:
If that doesn't work could then send the project and the database to me ? (I hate to leave this, without getting it solved :-)
0
 
SETPAuthor Commented:
OK, tried deleting the connection, the dataadapter and the dataset, as well as the XML schema. Then I dragged-and-dropped a new conneciton (from the Server Explorer), a new DataAdapter and generated a new DataSet. But still same problem. If it is ok with you I could send you the project maybe you could look at it? Either way I've decided to give you the points since you've helped me so much already.

Thanks for all your support RonaldBiemans
0
 
RonaldBiemansCommented:
You can send it to ronald dot biemans at compagnie dot be, and I'll have a look at it :-)
0
 
SETPAuthor Commented:
Hi Ronald. You're not going to believe this. I created a new form, and slowly started copying-and-pasting code from the old form to the new one. I eventually identified the code that is causing that error. Believe it or not, it's the connection string for the Connection object. I don't understand it. It executes that code without a problem, but later, when I try to fill a Dataset with a DataAdapter using that Connection object, it gives that error I've been getting all along:

No value given for one or more required parameters

If I leave the connectionstring that is automatically generated by VB, then it works fine. But I can't use that because the database on the client's PC will be located in a different location. So I recreate the conneciton string by copying the existing connectionstring and just replacing the path to the database with a variable which contains the current path to the database as such:

conProspectiveClients.ConnectionString = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database L" & _
        "ocking Mode=1;Data Source=" & "" & mclsPubVars.gsDBFile & "" & ";Mode=Share Deny None;Jet OLEDB:Engine Typ" & _
        "e=5;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System database=;Jet OLEDB:SFP=" & _
        "False;persist security info=False;Extended Properties=;Jet OLEDB:Compact Without" & _
        " Replica Repair=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create System D" & _
        "atabase=False;Jet OLEDB:Don't Copy Locale on Compact=False;User ID=Admin;Jet OLE" & _
        "DB:Global Bulk Transactions=1"

As you can see, the variable gsDBFile in class mclsPubVars contains the current path to the database. I've checked it's value in run time and it is correct. Also, as I mentioned, the code executes without a problem and I am able to open the connection without a problem. It's only when I try filling in a DataSet with a DataAdapter using this connection that I get the error message.... Weird hey?
0
 
RonaldBiemansCommented:
That  is indeed weird, you would expect it to fail when trying to open the connection.
It isn't it that the database that the client connects to  has a different table layout then the one you are testing it on ?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 10
  • 9
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now