Updating Datagrids

D

DonnaF

Active member
Joined
Mar 20, 2003
Messages
30
I have an Access Database that is used to keep track of tasks for each day. On a form, I display the calendar and the tasks for that day in a datagrid. Since Im pretty new to VB Net, I would like to find out if it is possible to add new tasks, change existing tasks, or delete existing tasks using just the datagrid. It looks like I can type in the datagrid, to add a new task. It also looks like I can change existing tasks information, but I dont know how to identify the changes so I can update the database.

For adds - how can I identify what line on the datagrid has been added, so I can do an insert on the database? For updates or deletes, how can I identify which line on the datagrid I have updated or marked to be deleted?

Thanks, Donna
 
Assuming you have bound your datagrid to a dataset:

Code: 
With DataGrid1
    .Datasource = Dataset11
    .AllowSorting = True
    .SetDataBinding(Dataset11, "MyTable")
End With


The following block will update your datagrid (i.e. force the control to paint any currently invalid areas...a cosmetic event)
and then updates the data source.

Code: 
datagrid1.update()
SqlDataAdapter1.update(DataSet11)

Jon
 
I still need help on this. When Im displaying the data from the access database in the datagrid on the form, I want to be able to click on the record I want to change, make the changes right on the datagrid, and then update the database and the datagrid(by clicking on a command button). How can I identify which record Im currently physically on in the datagrid? I would need to know that, so I can retrieve the record from the database to update it. For adding new records, I was going to use a textbox for the user to enter data, and do an insert on the database. Is there a way, instead, where they can just key it on the next line on the datagrid? If so, then again, how do I get to that data on the datagrid, so that I can do an insert on the data base.

Any help you can give me would really be appreciated.

Thanks, Donna
 
Originally posted by DonnaF
When Im displaying the data from the access database in the datagrid on the form, I want to be able to click on the record I want to change, make the changes right on the datagrid, and then update the database and the datagrid(by clicking on a command button).
Click to expand...
Using a dataadapter and dataset, you can do this with the code I wrote above. If youre using the dataadapter configuration wizard, on the "Advanced SQL Generation Options" page, select "Generate Insert, Update and Delete statements" and the wizard will write your sql statements for you. Manipulating the datagrid then is manipulating the dataset and when you call the dataadapter objects update method, the dataadapter examines the datasets rowstate propertiy and executes the required insert, update or delete statement.
If you dont want to use the wizard, you can create a commandbuilder object to automatically generate sql statemnts for single-table updates or, of course, you can write the sql yourself.
How can I identify which record Im currently physically on in the datagrid? I would need to know that, so I can retrieve the record from the database to update it.
Click to expand...
As noted above, no you dont. The dataset magic takes care of that for you via the rowstate property.

For adding new records, I was going to use a textbox for the user to enter data, and do an insert on the database.
Click to expand...
Its the way Id do it also.

Jon
 
Jon, I tried your suggestion:
datagrid1.update()
SqlDataAdapter1.update(DataSet11)
but it didnt work. It didnt save the changes made in the datagrid. I made a change in one of the records in the datagrid, and then clicked on an Update Button to execute the code you suggested, but when I displayed the datagrid again, it had the original values.

I did use the dataAdapter configuration wizard, so shouldnt your suggested code work? Is there something else I should be doing to get the changed record to update the database? I apologize for being so dense about this, but Ive only been writing VB Net code for a couple of months, and I still have alot to learn.

Thanks for your help that you have given me so far.

Donna
 
Update

try with this--->

Code: 
DataSet11.EndCurrentEdit()
datagrid1.update()
SqlDataAdapter1.update(DataSet11)
 
CORRECTION

Code: 
me.BindingContext(datagrid1).EndCurrentEdit()
me.BindingContext(Dataset11).EndCurrentEdit()
SqlDataAdapter1.update(DataSet11)
 
INSERT,DELETE,ADD

You could loop thru your Datatable and eximine RowState.
If your RowState is DataRowState.Added then use Insert query,
if your RowState is DataRowState.Modified then use Update quuery,
and so on...

example:
Code: 
for i=0 to myDataTable.Rows.Count - 1
if myDataTable(i).RowState = DataRowState.Added Then
 UPDATE QUERY

elseIf myDataTable(i).RowState = DataRowState.Deleted Then
DELETE QUERY

and so on


EndIf
Next i
 
Back
Top