OledbDataAdapter Parameters

chrisbunch

New member
Joined
Jun 7, 2004
Messages
1
Hey guys, I hope this will be an easy elementary question. I am trying to write a parameterized query using an Access database.

There are two tables one contains header information about products and one contains components of each product. The windows form has a combobox where the user can select the product name. After a selection is made, a datagrid should fill with the components information for that product.

I can get it to work easily with SQL Server (just by putting '= @ PName' in the criteria), but the company's data is in Access. I am currently using '= ?' in the criteria and setting the myDataAdapter.Selectcommand.Parameters("?").Value=cboPName.Text.

I get the error:

Additional information: An OleDbParameter with ParameterName '?' is not contained by this OleDbParameterCollection.


I greatly appreciate any help you can offer.
 

Schenz

Well-known member
Joined
Sep 11, 2004
Messages
181
Location
Cincinnati, OH
Programming Experience
5-10
I'm just guessing here (based on my use with mySQL), but instead of using just a "?" use the ? as you would with the @ in a SQL query, then use myDataAdapter.Selectcommand.Parameters("PName").Value=cboPName.Text

query = "SELECT * from table where FIELD = ?PName"
myDataAdapter.Selectcommand.Parameters("PName").Value=cboPName.Text
 

JuggaloBrotha

VB.NET Forum Moderator
Staff member
Joined
Jun 3, 2004
Messages
4,524
Location
Lansing, MI; USA
Programming Experience
10+
this is simple:

I am currently using '= ?' in the criteria and setting the myDataAdapter.Selectcommand.Parameters("?").Value=cboPName.Text.

in this part '.Parameters("?").Value=' where you currently have the ? it should be the name of the field where you put the =? criteria in the data adapter

so if you've got a select * from table where Name = ? in the data adapter then the parameter is thus:

myDataAdapter.Selectcommand.Parameters("Name").Value=cboPName.Text
 

Paszt

Staff member
Joined
Jun 3, 2004
Messages
1,500
Location
Raleigh, NC - USA
Programming Experience
Beginner
OK this is how I use parameters with Access:

VB.NET:
Dim cmd As New OleDb.OleDbCommand("SELECT * FROM ForumThreads " & _
  "WHERE UserID = mUserID", cnn)
cmd.Parameters.Add("mUserID", 1)
da.SelectCommand = cmd
Dim dt As New DataTable()
cnn.Open()
da.Fill(dt)
cnn.Close()
DataGrid1.DataSource = dt
cnn is an OleDbConnection and da is an OleDbDataAdapter.

Note that the parameter named nUserID could have been named @UserID and I believe it is the standard to use the ampersand while naming parameters, but I like to doubleClick select and the ampersand isn't included in a doubleClick select (the way my settings are in the IDE anyway). Therefore I name my Parameters using an m+FieldName convention.

I think the problem chrisbunch is having is that the parameters collection for the command object is empty (his command details haven't been shown, so I'm not sure). Doing as above (using the parameters.Add method) adds the parameter to the collection and sets the value in the same line (in this case setting it equal to 1: cmd.Parameters.Add("mUserID", 1) ).
 
Top Bottom