Updating Datagrids

[DonnaF]

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 I'm 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 don't 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

[jfackler]

Assuming you have bound your datagrid to a dataset:

 

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.

 

datagrid1.update()
SqlDataAdapter1.update(DataSet11)

 

Jon

[DonnaF]

I still need help on this. When I'm 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 I'm 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

[jfackler]

When I'm 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).

Using a dataadapter and dataset, you can do this with the code I wrote above. If you're 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 object's update method, the dataadapter examines the datasets rowstate propertiy and executes the required insert, update or delete statement.

If you don't 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 I'm currently physically on in the datagrid? I would need to know that, so I can retrieve the record from the database to update it.

As noted above, no you don't. 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.

It's the way I'd do it also.

 

Jon

[DonnaF]

Jon, I tried your suggestion:

datagrid1.update()

SqlDataAdapter1.update(DataSet11)

but it didn't work. It didn't 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 shouldn't 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 I've 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

[jfackler]

Donna,

It's mothers day and I've got 5 kids. Don't have much time today.... I'm also an obstetrician so I take special interest in this day.

BUT: I think you'll find this link from Robby helpful.

http://www.xtremedotnettalk.com/showthread.php?s=&threadid=71636

 

Good luck.

Call your Mom, Grandmother or both.

 

I'll check back.

 

Jon

[sizer]

Update

 

try with this--->

 

DataSet11.EndCurrentEdit()
datagrid1.update()
SqlDataAdapter1.update(DataSet11)

[sizer]

CORRECTION

 

me.BindingContext(datagrid1).EndCurrentEdit()
me.BindingContext(Dataset11).EndCurrentEdit()
SqlDataAdapter1.update(DataSet11)

[sizer]

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:

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