Error: No value given for one or more required parameters

try putting the fields that contain that between []

minimum and maximum are reserved words

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"

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...

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! :(

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

I tried both with an empty table, and with a populated table. Makes no difference

And with that many fields, what's wrong with using Select * ?

Bob

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

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

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

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.

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)

Did you just delete the dataset under the form and did you also delete the dataset xml schema from solution explorer ?

Yes, I deleted both the dataset and the associated XML schema

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

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

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

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?

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 ?