Passing parameters to SQL

[Cyberpro60]

Here is what I am trying to achieve:

I have an access database with a table [GroupTable] with 3 fields [RecNo, Group, SubGroup] (Group & SubGroup use integers)

I want, under program control, to populate a table adapter with a subset of all records
eg I want to be able to create a table with just those records where Group = 1 or 2 etc
The user will have the ability to select which group is required.

If I write the following SQL code using the Dataset Designer

SELECT RecNo, Group, SubGroup FROM GroupTable 
WHERE Group = 1

everything works just fine. There are hundreds of possible values of Group however and 'hard coding' them all is not practical. Hence I want to replace the hard coded "1" in the above example with a variable the value of which the user can determine. This is where the nightmare begins.

According to several references the following code is supposed to work:

SELECT RecNo, Group, SubGroup FROM GroupTable 
WHERE Group = @SelectedGroup

where SelectedGroup is a Public Variable the value of which is user determined.
Needless to say this code doesn't work and produces error messages about a required value being missing or of the wrong datatype etc.

Somebody else suggested the call to populate the table adapter should be modified viz:

Me.GroupTableTableAdapter.Fill(Me.UserDataSet.GroupTable, 'SelectedGroup')

However this does not work either and produces error messages to the effect that there are too many paramaters etc. If I remove "SelectedGroup" then it tells me that I don't have enough so I have NO clue what to try next.

From hours and hours of reading about this problem I note that lots of other people have asked the same or fairly similar questions about the same thing but that there aren't a lot of workable replies (if any). I am hoping Daniweb users will be little more forthcoming in that department.

In anticipation .... :)

[hollystyles]

You need to add the parameter to the parameters collection of the selectcommand object of the adapter.

GroupTableTableAdapter.SelectCommand.Parameters.AddWithValue("@selectedGroup", selected group)

To make this SQl work:

SELECT RecNo, Group, SubGroup FROM GroupTable 
WHERE Group = @SelectedGroup

The @selectedGroup will be substituted by the actual value when executed. The SQL String 'marks' the parameter, the parameters collection in the command object is where the actual value goes.

[Cyberpro60]

I could not get this command to work, hollystyles

The command I used was:

GroupTableTableAdapter.FillWithSelectedGroup.Parameters.AddWithValue("@selectedGroup", Selected Group)

The error message provided was

Argument not specified for parameter 'dataTable' of 'Public Overridable Overloads Function FillBySelectedGroup(dataTable As UserDataSet.GroupTableDataTable) As Integer'.

The last parameter "selected group" also produced a slew of error messages!

[hollystyles]

Ah hang on you've created a typed dataset ?
Sorry I thought we were just dealing with a bog standard SqlDataAdapter.

Double click your xsd file in your App_Code folder to open it in the designer. Right click above where the Fill Method is for your groupTable and click add query. Choose Use SQl Statements click next, Choose Select which returns rows click next, put your SQl statement in the box click next.

SELECT RecNo, Group, SubGroup FROM GroupTable 
WHERE Group = @SelectedGroup

For the method names put something like FillBySelectedGroup and GetDataBySelectedGroup click next and click finish.

in your code behind: (sorry this is C# so you may have to fiddle a little VB makes my teeth rattle sorry)

//This is your custom adapter created in the designer when you dragged your GroupTable
//into the designer
GroupTableTableAdapter groupAdapter = new GroupTableTableAdapter();
//This is the strongly typed DataTable class generated by VS
GroupTable groupTable = new GroupTable();

//Here is your custom method it requires your table to fill, and a variable for the //SelectedGroup parameter
groupAdapter.FillBySelectedGroup(groupTable, <put your user selected group variable here>);

Basically you create an instance of your sytrongly typed datatable and tableadapter classes that VS built for you from the designer. Then use your custom method to fill the table by the given parameter.