Database search and update with ADO

Hi, all,

I'm developing an SQLServer2005 database for electronic components, and part of the job is providing a web interface to allow searches and edits to a number of the fields. There are a dozen or so tables in the database, and any given search will involve three of them. Two of the three hold data that's common to all components, and the third table contains data specific to a certain kind of component (diode, resistor, microprocessor, etc.) There are a large number of fields that can be searched, but any given query is likely to only use a small subset. I've done things like this in other environments, but I'm just moving into doing it with ADO.

What I'd like to do is look at the information that the user enters into the fields in the web interface, generate an SQL query that gets data from all three tables, display it in a GridView control, allow the user to edit fields in that control, and generate Update commands that write those changes back to the appropriate tables in the database. Should be simple, but I'm still getting familiar with how ADO sets things up.

From my reading, it looks like I'd set up a dataSet with representations of the tables that I'm working with, and do my queries and updates through the dataAdaptor. I've seen examples where the dataset tables are filled with the entire table from the database, and views are used to select the data to display. I'd prefer to issue a query to limit the amount of data brought in, but I'm a bit confused about how this is handled. Specifically, if I issue a Select command that joins three tables, is the information that's returned to the dataset sorted into the local copies of the tables, or is it put into the dataset as one large table representing the Join? Do I need to set up a view to get the data for the gridview, or do I assign the joined result as the datasource for the gridview?

When I do the edits, I can handle the edit events from the gridview control, and generate the Update commands (one per destination table). If I can figure out how the data is represented for a query, I'm sure that will go a long way towards helping me figure out how to handle the update.

So - would it be possible to briefly walk through doing a multitable search, and show how the data gets represented? Any references to examples would also be good - I've googled around, but somehow most examples out there don't seem to address what I'm trying to do, although it seems like a common enough task. Thanks for your help!

-Chris01
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories