Tableadaptor / Dataset Woes..

[john.clark]

ADO.NET TABLEADAPTOR method

Hi all,

Very new to using table adaptors, binding sources etc and am having a problem that I'm sure is going to be an easy one to solve....though for some reason the solution is evading me at the moment.

I have a small single tier application that has a dataset: chimeraDataset

In the dataset I have a datatable with the relevant tableadaptor: PCT_INVENTORY

I've added a method into the tableadaptor called: PCT_get_details_for_factory_object which is linked to a parameterized SQL 2005 stored procedure.

Previewing the data within the dataset designer works fine, however, I now need to use that data on my form.

Ok... form1 has BindingSource1 which is connected to an instance of ChimeraDataset it also has PCT_InventoryTableAdapter1

If i place the following code into the load event of the form, I get ALL of the data, presumably as it's using the default GETDATA:

PcT_InventoryTableAdapter1.Fill(ChimeraDataSet.PCT_Inventory)

My MAIN question is how do I tell the tableadaptor to fill with data from my method in the tableadaptor called PCT_get_details_for_factory_object ?

Many thanks,

JC

 

[john.clark]

I attempted to use:

PcT_InventoryTableAdapter1.Get_details_for_factory_object("ASD-011-0435Y") within my form load.

First of all with an argument for a factory object that I know doesn't exist. That ran, and as expected I got no data.

I then tried it as above and get a CONSTRAINTEXCEPTION was unhandled...although I've got CONSTRAINT CHECKING turned off on the dataset.

I've double checked that I have data in the related table with no nulls etc...

???

 

[Arg81]

Hi John, welcome to the forums!

This is pretty easy in VS2005 with table adapters.

OK, so your table adapter has a .fill query, which should return ALL data. You then should have a second query, which I presume is your PCT_get_details_for_factory_object. When setting up the query, you should see part of the wizard ask you for GetBy() and FillBy() . ALWAYS make sure you change this to something relevant to what you are trying to do, i.e. if you are filling by Customer ID, change to GetByCustomerID() and FillByCustomerID().

This then makes it easier for you when coding

You almost have this right...

PcT_InventoryTableAdapter1.Get_details_for_factory _object("ASD-011-0435Y") within my form load

OK, I'm assuming you renamed FillBy() to Get_details_for_factory _object?? Or did you rename the GetBy() ??
..This is where the above advice comes in handy!

What you need to do, is..

PcT_InventoryTableAdapter1.[B]FILLBYCOMMAND[/B](me.ChimeraDataSet.PCT_Inventory, "ASD-011-0435Y")

You basically missed out the code to fill the datatable in the dataset first (me.ChimeraDataSet.PCT_Inventory).

When using .FillBy() queries, when you code, after entering the dataset + datatable insert a comma (,) and it'll then prompt you for the variable. This then allows you to use a textbox or a combobox for your query variable, so you can programmatically change the data thats loaded instead of hardcoding it in.

Hopefully that can help and get you started, if you require any further help with getting started either drop me a PM or post to the forums - everyone's friendly and this is the place I learnt VB.net just over a year ago

Happy Coding!
Luke

 

[cjard]

PcT_InventoryTableAdapter1.Fill(ChimeraDataSet.PCT_Inventory)

My MAIN question is how do I tell the tableadaptor to fill with data from my method in the tableadaptor called PCT_get_details_for_factory_object ?

PcT_InventoryTableAdapter1.PCT_get_details_for_factory_object(ChimeraDataSet.PCT_Inventory, <parameter 1> ... <parameter N>)

Do note (and im not sure how your stored procedure does it) that the sproc needs to return a cursor that is the same schema as the datatable, for all the columns to be filled properly.. Youve implied that your original query is SELECT * FROM pct_inventory. You then state that your additional method uses a parameterised stored procedure (which is a database-side code object) so it is important that this stored procedure returns a cursor of the same schema as pct_inventory for the table adapter to be able to put the right data in the right columns.

If, perchance, your PCT_get_details_for_factory_object wraps query that looks like:
SELECT * FROM pct_inventory WHERE factory = @factory
then you should note that this is a parameterized query, but it is not a stored procedure..

Just incase youre confused as to terminology..

Oh, and tableadapters themselves dont fill with data. They are a device for filling datatables with data. THis is why it is necessary to pass the relevant datatable you want filling, along to the tableadapter. One TA can fill many different instances of the same type of datatable

 

[cjard]

*please, dont cross post.

This thread would have been accepted in either VB.NET DA or ADO.NET.. If you realise you have posted in completely the wrong section, you should request a moderator move the thread for you. To do this, click the /!\ warning triangle icon in the top right of your post, (to report it) and write a message saying you think you have posted i9t in the wrong section..

I'll be passing a message to the moderators, that this thread and the other crosspost in Data Access should be merged

 

[john.clark]

Thanks for all the help - isn't it typical that just as an email arrives I'd got to the solution myself....but never the less, the assistance is greatly appreciated!!!

Cheers,

JC