• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 443
  • Last Modified:

How to design interface using data adapter/dataset binding to a single record.

First, I am new to VB.NET programming and ADO.NET in particular is giving me trouble.  please bear with me, i know just enough to be dangerous.  

In the application I am trying to develop, navigation through the data is implemented using a tree structure, and if you want to add a record, edit a record, or delete a record, you can do this from the context menu for each object on the tree.  This all works, i have the basic data read and the context menu firing the appropriate commands with the appropriate id's passed to the procedures.  SO, now i want to be able to design an input form, that is used for adding new records or updating existing records, one at a time.  The deletions i think I can handle.  

i would like to design an input form, to be used for adding new or updating one existing record.  I want to bind the text fields on the input form to a dataset based on either a returned record for editing, or a new record for adding, but am having conceptual difficulty designing the objects to perform this.   I guess i need some help getting started.  

I can see two different approaches.  Ideally, I want to design a data access class, that will return a bindable object, but this seems a little over my head.  

So, I think the easiest way to accomplish what i want is use a dataset/dataadapter interface, binding the text fields to the dataset, and submitting updates using the command methods of the data adapter.   When an add happens, i don't know if i want to add the record using a command query, then open it in edit mode.  

The simplest table in my database is tblLab.  it has 3 fields, LabID (autoincrement, primary key, Random sequence)  
LabName is a string, and BldgName is a string.

i want to add a new lab or edit an existing lab (will know the LabID when form opens, if an add or edit, but not sure where i should add the new record, and how to do it vs. when i bind the data to the fields).

I do know how to create a typed dataset, and have played around with manually creating a dataset/dataadapter and modifying, and actually wrote an edit retrieval that worked be returning one row, but i keep thinking i am going the long way around, so i thought it was time to ask an expert.

I don't really expect anyone to fully code this out with steps.  what i need is an overview of a logical way to try to do this, and maybe a list of steps, things that i need to make sure happen.  maybe code examples for any "tricky" parts.   i understand this is quite a bit, so i will make it worth as many points as i can.




  • 2
  • 2
1 Solution
EricJamesOlsonAuthor Commented:
One comment i forgot in reading this over, on exiting the input form, i intend to have a Save button and a Cancel Update option.  

For an edit, Save would update the edited record, and Cancel would not update the edited record.  
For an Add, Save would either Save or Create the new record (depending if interface is adding or editing existing at this point), and ideally i would want to delete the existing record if Cancel.  
Hi EricJamesOlson,

Just one comment... while binding window controls to datasets is appealing because it simplifies your code a bunch, I rarely use this approach because it almost always causes problems on the database side with open connections, table locking and the like.  This is probably much improved under ADO.NET as all recordsets are basically "disconnected" anyway, but my preference is for a totally disconnected (ie unbound) approach from the get-go.

Our database is SQL Server on the back-end, and we do almost all of our database selects by calling stored procedures, and the same for database insert/updates and deletes.

I wrote a module with some global routines that pretty much wrap all of the low level database access code... basically, the programmer calls one of 4 or 5 functions that will send back either a datareader or a dataset (depending on what you want), will operate with a command object or execute some sql string provided, or execute an action query.  Each of these routines call another routine for opening the connection, then they run the query and close the connection returning the filled data object.

To call a stored proc that requires parameters, the programmer sets up a command object and appends parameter objects to the command before calling the function to execute the query... again, a global function buries all the steps necessary to create a parameter object, and then append it to the command object.

When I get the datareader (datareaders are faster, so I use this whenever possible) or dataset, I'll usually iterate through it manually loading data to the screen... on occassion, I'll set the data source of a grid to a table in a dataset.  For most updates, I take the data off the screen manually and put them into parameters and pass them to an update stored procedure.

If the user is updating data in a grid, most of the time, I do the record update in the rowcol_changed event of the grid (after verifying that it was indeed a row change event).  But, in the rare case where we let the user edit several things in a grid and only save once when finished with everything, I'll usually take the data out of a grid and put it into a dataset (perhaps created just for this purpose) and then extract everything using the XML property and pass the XML to the stored procedure as a parameter.  Then, in SQL Server, it's pretty easy to load the contents into a temp table using the xml prepare doc command.  Another approach to the "batch" update is to let the user edit the grid at will, keeping a hidden column with a flag to know which rows have been updated/added/deleted.  Then, when the user clicks Save, iterate one row at a time, looking at the flag and calling the update routine for the data in that row....

Performance is surprisingly not much of an issue on any of these screens.

The only downside to this whole approach is that you have to manually take data out of a dataset or datareader and put it on the screen, or load it into a grid, or combo box or whatever... but, that's not too difficult.

EricJamesOlsonAuthor Commented:
okay, makes sense.  was hoping there was an easier way to do this but i can follow what you are saying and give it a try.

Thanks, as I said, it's not too difficult.. if you have any difficulty with any one of these concepts, just post a follow-up comment to this question.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now