OledbDataAdapter Parameters

[chrisbunch]

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]

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]

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]

OK this is how I use parameters with Access:

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