Passing parameter value to table adapter problem

[celestedicillo]

I am really new at this VB.net, so I'd appredicate some help. I'm trying to pass a parameter to a table adapter select command which is command type stored procedure (SQL server 2000). My stored procedure takes one parameter. But when I click on the parameters collection ellipses in the properties viewer, I find no way to set the input parameter to the selected value of another control on the form. So do I have to write some code for this? If I do, I haven't been able to figure out how to access the parameters collection for the table adapter. Can someone help me or point me in the right direction.

 

[jmcilhinney]

You don't access the parameters collection of a TableAdapter. When you add a query to a TableAdapter it creates methods that you call to execute the query. Each argument to those methods corresponds to a parameter in the SQL code. I've only ever done it with text commands but I'd assume that sprocs are the same. Here's some reading material for you.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs05/html/tableadapters.asp

 

[celestedicillo]

Actually, I added the query to the TableAdapter but still, there seems to be no way to set the parameter. I can get to the Parameters Collection Editor view and the parameter is listed, but no way to set it's source to a control. So if you go to the DataSet.xsd, right click the table, add a query, then right click the query name which has been added to the table representation in the .xsd, open the parameters collection, select the parameter, no way to set the source as a control.

 

[agroom]

I'm pretty new to this myself, but let me help take a stab at it.

I don't think you can set the parameters to a control in design, you have to do it though code.

For exmple, I recently made a query in the table adaptor to pull records from a data table dependant on a specific ID # and called it FillByID. When you set the parameters in the designer, you just seup a generic name for the paramter "@paramname". Then through code you call:

Me.TableAdaptor.FillByID(Parameter)

Where instead of Parameter you'd pull the value from your control.

Me.TableAdaptor.FillByID(me.textbox.text)

 

[cjard]

agroom is nearly correct;

The DataSet designer wont let you bind a parameter to a control.. why not? because DataSets are logically very different from controls.. they dont interact and a dataset is not supposed to have any knowledge of what a control is

Filling a DataTable inside a data set is a manual operation.. your code MUST contain some FillBy command somewhere, hence the usage is:

TableAdapter.FillByXXX(DataTable, x, y, z ...)


So to provide 100% correctness to agroom's example, you must call the FillBy method yourself, in code, passing in the table that you want filled and the parameters to fill it with:

MyTableAdapter.FillByXXX(MyDataSetInstance.MyDataTable, TextBox1.Text)


in this case the datatable is filled according to the way the query is set up, and the parameter is filled with whatever is typed in the text box.


The tableadapter's DB Direct methods for inserting and updating can be used ina similar way, as they have overloads that list for every known parameter in the relevant sql statement. it is more usual however, that you will use the parameters collection to specify which column in the data table maps to which parameter in the sql (the designer cant always work this out by itself) and then you will pass a datarow containing the values to insert or update. The table mappings are used to fill the parameters list.

For more inro, be sure to ask..