[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Error: No value given for one or more required parameters

Posted on 2005-04-20
21
Medium Priority
?
282 Views
Last Modified: 2010-04-23
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
Comment
Question by:SETP
  • 10
  • 9
  • 2
21 Comments
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 13824867
try putting the fields that contain that between []




0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 13824877
minimum and maximum are reserved words
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 13824911
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:SETP
ID: 13825090
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
 

Author Comment

by:SETP
ID: 13825173
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
 

Author Comment

by:SETP
ID: 13825222
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
 

Author Comment

by:SETP
ID: 13825248
I tried both with an empty table, and with a populated table. Makes no difference
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 13827034
And with that many fields, what's wrong with using Select * ?

Bob
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 13827056
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
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 13831612
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
 

Author Comment

by:SETP
ID: 13831699
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
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 13840946
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
 

Author Comment

by:SETP
ID: 13841337
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
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 13841473
Did you just delete the dataset under the form and did you also delete the dataset xml schema from solution explorer ?

0
 

Author Comment

by:SETP
ID: 13841650
Yes, I deleted both the dataset and the associated XML schema
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 13841728
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
 
LVL 25

Accepted Solution

by:
RonaldBiemans earned 2000 total points
ID: 13841859
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
 

Author Comment

by:SETP
ID: 13848916
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
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 13864787
You can send it to ronald dot biemans at compagnie dot be, and I'll have a look at it :-)
0
 

Author Comment

by:SETP
ID: 13865524
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
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 13865639
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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question