Updating a Datagrid
Updating a Datagrid
Updating a Datagrid
Updating a Datagrid
Updating a Datagrid
Updating a Datagrid Updating a Datagrid Updating a Datagrid Updating a Datagrid Updating a Datagrid Updating a Datagrid Updating a Datagrid Updating a Datagrid
Updating a Datagrid Updating a Datagrid
Updating a Datagrid
Go Back  Xtreme Visual Basic Talk > > > Updating a Datagrid


Reply
 
Thread Tools Display Modes
  #1  
Old 05-27-2004, 12:04 PM
MGOOSE MGOOSE is offline
Regular
 
Join Date: May 2004
Posts: 64
Default Updating a Datagrid


How can I update data in a datagrid? I load the values into the datagrid from an Access XP database, loading goes ok but when i make changes to the data within the datagrid it doesnt save these changes.

I have tried the following code to save the changes on Exit

Private Sub dfclsSiteMain_Close(ByVal s As String, ByVal ds As DataSet, ByVal olapAdaptor As OleDbDataAdapter)

Dim dt As New DataTable

dt = ds.Tables("SiteMain").GetChanges(DataRowState.Modified)
If Not (dt Is Nothing) Then
Dim dr As DataRow
For Each dr In dt.Rows
olapAdaptor.UpdateCommand.Parameters("@Site No").Value = CType(dr("Site No"), String)
olapAdaptor.UpdateCommand.Parameters("@Site Name").Value = CType(dr("Site Name"), String)
olapAdaptor.UpdateCommand.Parameters("@DayStart").Value = CType(dr("DayStart"), String)
olapAdaptor.UpdateCommand.Parameters("@DayEnd").Value = CType(dr("DayEnd"), String)
olapAdaptor.UpdateCommand.Parameters("@NightStart").Value = CType(dr("NightStart"), String)
olapAdaptor.UpdateCommand.Parameters("@NightEnd").Value = CType(dr("NightEnd"), String)
olapAdaptor.UpdateCommand.Parameters("@OccTime").Value = CType(dr("OccTime"), String)
olapAdaptor.UpdateCommand.Parameters("@VacTime").Value = CType(dr("VacTime"), String)
olapAdaptor.UpdateCommand.Parameters("@Staff No").Value = CType(dr("Staff No"), String)
olapAdaptor.UpdateCommand.Parameters("@Staff Name").Value = CType(dr("Staff Name"), String)
olapAdaptor.UpdateCommand.ExecuteNonQuery()
Next
End If

End Sub

This doesnt work though and I'm a bit stuck.

Any suggestions most welcome.

Thanks

Mark.
Reply With Quote
  #2  
Old 05-28-2004, 06:26 AM
MKoslof's Avatar
MKoslofUpdating a Datagrid MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

What are you trying to do here?

I see you are looping each row in your DataTable..that is fine. But, are you trying to update the values in the DataTable? If so, you need to use the .BeginEdit, .EndEdit flags to put the DataTable in edit mode. And, why are you executing a non query? What is the command text you are firing? Parameters need to go with the actual command text, such as "UPDATE myTable SET ?param = ?param2" etc...

And, are you trying to update values in the DataTable get current values from your .Net datatable and send changes to the underlying database via the DataAdapter.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #3  
Old 05-28-2004, 07:03 AM
MGOOSE MGOOSE is offline
Regular
 
Join Date: May 2004
Posts: 64
Default Thanks - Will look into this further

Ok - thanks for the advice. I need to update the changes to the Access Database at the back end, I know that the dataset and data adapter need to be updated but havent been able to find a good example of this. I will look into the begin edit and end edit methods - I have an ADO.Net book so there should be something in there for using these methods to update.

Thanks,

Mark
Reply With Quote
  #4  
Old 05-28-2004, 08:26 AM
MKoslof's Avatar
MKoslofUpdating a Datagrid MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

A nice place to start:

Visual Basic.NET Books

And review this thread regarding updating a database and dataset. I provide a code sample and some advice regarding storing your command text objects:

Updating Using a DataRow?
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown

Last edited by MKoslof; 05-28-2004 at 08:33 AM.
Reply With Quote
  #5  
Old 05-30-2004, 02:51 PM
MGOOSE MGOOSE is offline
Regular
 
Join Date: May 2004
Posts: 64
Default Dataset Update

Hi,

I have adapted your code for updating a dataset (barely any changes), but cannot seem to get the update to work. I have made it so the data updates on form closed event, but it isnt updating - all changes in the datagrid are lost.

I dont suppose you can help me, can you see what I have done wrong in the following update function:

'Start Update Dataset Functions UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU
Private Sub dfclsSiteMain_Closed(ByVal s As String, ByVal ds As DataSet)

Dim dr As System.Data.DataRow
Dim command As New OleDbCommand
Dim updateCon = New OleDbConnection(s)
Dim olap As New OleDbDataAdapter
Dim strVar, strVar1, strVar2, strVar3, strVar4 As String
Dim strVar5, strVar6, strVar7, strVar8, strVar9 As String

updateCon.open()

For Each dr In ds.Tables(0).Rows
dr.BeginEdit()
'dr("Site No") = CStr(strVar)
'dr("Site Name") = CStr(strVar1)
'dr("Day Start") = CStr(strVar2)
'dr("Day End") = CStr(strVar3)
'dr("Night Start") = CStr(strVar4)
'dr("Night End") = CStr(strVar5)
dr("Occupation") = CStr(strVar6)
dr("Vacation") = CStr(strVar7)
'dr("Staff No") = CStr(strVar8)
'dr("Staff Name") = CStr(strVar9)
dr.EndEdit()
Next

command.CommandType = CommandType.Text
command.CommandText = "UPDATE ds.Tables(0) SET [Occupation] = '" & CStr(strVar6) & "', " & _
" [Vacation] = '" & CStr(strVar7) & "'"

command.Connection = updateCon

olap.UpdateCommand = command

olap.Update(ds, "SiteMain")

End Sub

The update only needs to update columns 6 & 7 as all the other are read only so have commented them out.

Thanks for any help,

Mark.
Reply With Quote
  #6  
Old 05-31-2004, 08:17 AM
MKoslof's Avatar
MKoslofUpdating a Datagrid MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

OK, don't pass in ds.Tables(0) as the table name. Notice this line here:

olap.Update(ds, "SiteMain")

This assumes "SiteMain" is the table in question.
1) When initially adding this table to the dataset asssign the appropriate name to the DataTable, you could do

ds.Tables(0).TableName = "SiteMain"

Now, in the DataSet collection, you have a direct correlation between your underlying table and the datatable within your .Net layer.

Because .Net will name any new DataTable by default. This default TableName will not automatically match the name of your underlying database table.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #7  
Old 05-31-2004, 12:45 PM
MGOOSE MGOOSE is offline
Regular
 
Join Date: May 2004
Posts: 64
Default

Ok I think I understand - but I've tried to amend the code it currently is as follows:

Private Sub dfclsSiteMain_Closed(ByVal s As String, ByVal ds As DataSet)

Dim dr As System.Data.DataRow
Dim command As New OleDbCommand
Dim updateCon = New OleDbConnection(s)
Dim olap As New OleDbDataAdapter
Dim strVar, strVar1, strVar2, strVar3, strVar4 As String
Dim strVar5, strVar6, strVar7, strVar8, strVar9 As String

updateCon.open()

ds.Tables(0).TableName = "SiteMain"

For Each dr In ds.Tables("SiteMain").Rows
dr.BeginEdit()
'dr("Site No") = CStr(strVar)
'dr("Site Name") = CStr(strVar1)
'dr("Day Start") = CStr(strVar2)
'dr("Day End") = CStr(strVar3)
'dr("Night Start") = CStr(strVar4)
'dr("Night End") = CStr(strVar5)
dr("Occupation") = CStr(strVar6)
dr("Vacation") = CStr(strVar7)
'dr("Staff No") = CStr(strVar8)
'dr("Staff Name") = CStr(strVar9)
dr.EndEdit()
Next

command.CommandType = CommandType.Text
command.CommandText = "UPDATE ds.Tables(SiteMain) SET [Occupation] = '" & CStr(strVar6) & "', " & _
" [Vacation] = '" & CStr(strVar7) & "'"

command.Connection = updateCon

olap.UpdateCommand = command

olap.Update(ds, "SiteMain")

ds.AcceptChanges()

This still doesnt update the database on form closed though? Not sure why not as the code seems ok? I had already done the ds.Tables(0).TableName = "SiteMain" line of code in the form load event so I cant understand why the update wont stick.
Reply With Quote
  #8  
Old 06-01-2004, 06:12 AM
MKoslof's Avatar
MKoslofUpdating a Datagrid MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

Do you receive any errors?

1) You should run your code in a try/catch block to trap any potential errors
2) When you create the initial DataSet (when you initially fill it from the Adapter), name the initial table.
3) I don't see anything wrong with the overall code you have. However, if you run your code in debug mode, what is the value of strVar7 and strVar6? Do these have values? If not, you are not accessing your data. Use message boxes and step through your code to make sure you are passing values

4) I would try this same code away from the form close event. This event probably fires some disposal events, which may be a problem. If you simply move this into a command button click does it work?
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #9  
Old 06-01-2004, 06:21 AM
MGOOSE MGOOSE is offline
Regular
 
Join Date: May 2004
Posts: 64
Default

Thanks - I will look into these points. I tried it with a command button but cannot pass the variables s as string or ds as dataset - the .click at the end of the Private Sub decleration of the click event is underlined blue - it states "cannot handle event click because they do not have the same signiture.

I will look at error trapping and message boxes as you suggest to show variables are passing through - if not I should have a better idea of the problem.

Thanks,

Mark
Reply With Quote
  #10  
Old 06-01-2004, 06:22 AM
MKoslof's Avatar
MKoslofUpdating a Datagrid MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

And also, remove the .acceptChanges changes of the DataSet after the update call. This is an internal flag that is not needed. This might also be forcing the DataSet back to its previous state.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #11  
Old 06-02-2004, 06:03 AM
MGOOSE MGOOSE is offline
Regular
 
Join Date: May 2004
Posts: 64
Unhappy

Hello again,

I have amended the code slightly to include an error trap, it still doesnt update but the error trap works nicely (is that irony?). I get an error message in the message box which states there is a syntax error in the UPDATE statement? I cant see it though - I dont suppose you can see the mistake? Here's the code at present -


'Start Update Dataset Functions UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click

'Call Update Function
Call UpdateGrid()

End Sub


Private Sub UpdateGrid()

'create connection object
Dim updateCon = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp\Vanguard.mdb")
'create new DataAdapter Object to hold bridge database and dataset
Dim olap As New OleDbDataAdapter
'create new command object
Dim command As New OleDbCommand
'create data row object
Dim dr As System.Data.DataRow
'Dim strVar, strVar1, strVar2, strVar3, strVar4, strVar5 As String
Dim strVar6, strVar7 ', strVar8, strVar9 As String
Dim sSQL As String = "SELECT [Site No], [Site Name], Format([DayStart], 'hh:mm') as [Day Start], Format([DayEnd], 'hh:mm') as [Day End], Format([NightStart], 'hh:mm') as [Night Start], Format([NightEnd], 'hh:mm') as [Night End], Format([OccTime], 'hh:mm') as [Occupied], Format([VacTime], 'hh:mm') as [Vacated], [Staff No], [Staff Name] FROM [SiteMain]"

updateCon.open()
command.Connection = updateCon
command.CommandType = CommandType.Text
command.CommandText = sSQL
olap.SelectCommand = command

'create new connection to MS Access Database called Vanguard at C:\temp
Dim olapConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp\Vanguard.mdb")
'create new DataAdapter Object to hold bridge database and dataset
Dim olapAdaptor = New OleDbDataAdapter
'create new command object
Dim olapCommand = New OleDbCommand

'create new DataSet object to bridge dataAdapter and data table
Dim ds As New System.Data.DataSet

olap.Fill(ds)

'give a name to the table for future reference
ds.Tables(0).TableName = "SiteMain"

Try
For Each dr In ds.Tables("SiteMain").Rows
dr.BeginEdit()
'dr(0) = CStr(strVar)
'dr(1) = CStr(strVar1)
'dr(2) = CStr(strVar2)
'dr(3) = CStr(strVar3)
'dr(4) = CStr(strVar4)
'dr(5) = CStr(strVar5)
dr(6) = CStr(strVar6)
dr(7) = CStr(strVar7)
'dr(8) = CStr(strVar8)
'dr(9) = CStr(strVar9)
dr.EndEdit()
Next

'command.CommandType = CommandType.Text
command.CommandText = "UPDATE ds.Tables(SiteMain) SET [Occupation] = '" & CStr(strVar6) & "', " & _
" [Vacation] = '" & CStr(strVar7) & "'"

command.Connection = updateCon

olap.UpdateCommand = command

olap.Update(ds, "SiteMain")

Catch ex As Exception
MsgBox("Error Occurred. The Error Message is " & ex.Message)
End Try

End Sub

'End Update Dataset Functions UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU

Thanks for all your help,

Mark
Reply With Quote
  #12  
Old 06-02-2004, 06:15 AM
MKoslof's Avatar
MKoslofUpdating a Datagrid MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

Yes, the error is small , as I told you before, the command text object simply needs the table name, nothing else, so change it to this:

Code:
command = "UPDATE SiteMain SET [Occupation] = '" & strVar6 & "'"

Just use SiteMain as the table name
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #13  
Old 06-02-2004, 07:57 AM
MGOOSE MGOOSE is offline
Regular
 
Join Date: May 2004
Posts: 64
Default

Well it has chagned the error message i get through the error trap. I have used the

command.CommandText = "UPDATE SiteMain SET [Occupation] = '" & CStr(strVar6) & "', " & _
" [Vacation] = '" & CStr(strVar7) & "'"

line but it gives the following error message: "No value is given for one or more required parameters".
Reply With Quote
  #14  
Old 06-02-2004, 08:13 AM
MKoslof's Avatar
MKoslofUpdating a Datagrid MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

make sure your spacing is correct. And, remove the CSTR() from the UPDATE string, if you need to cast to a string, cast the variables before hand. This should work fine:

Code:
command.CommandType = CommandType.Text command.CommandText = "UPDATE myTable SET Field1 = '" & strVar & "',Field2 = '" & strVar1 & "'" command.Connection = updateCon olap.UpdateCommand = command olap.Update(ds, "myTable")
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #15  
Old 06-02-2004, 09:06 AM
MGOOSE MGOOSE is offline
Regular
 
Join Date: May 2004
Posts: 64
Default

where does the strVar come from? I had assumed that it contained any changes made in the datagrid, as all I am wanting to do is load values into a datagrid (which is dealt with seperatley by the load event) and then use the update function to save any changes the user has made to the values in the datgrid. I'm not sure how the strVar fits in as other than declaring it as a string it doesnt take any other values.
Reply With Quote
  #16  
Old 06-02-2004, 10:35 AM
MKoslof's Avatar
MKoslofUpdating a Datagrid MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

strVar is any string value..pass in whatever you need..this is just a string variable. This is sending your changes back to the underlying table via the DataAdapter. strVar represents any string value.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #17  
Old 06-02-2004, 10:51 AM
MGOOSE MGOOSE is offline
Regular
 
Join Date: May 2004
Posts: 64
Default

Im not clear on how strVar gets the changes from the datagrid? I havent moved and changes from the datgrid into strVar or StrVar1, 2 etc. So how are changes moved into strVar and then into the DataSet, DataAdapter, Database
Reply With Quote
  #18  
Old 06-02-2004, 10:59 AM
MKoslof's Avatar
MKoslofUpdating a Datagrid MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

What are you sending to the Database? How do you hold the changes made on the DataGrid. In your previous example, I see you are doing something like this (within the dataRow of a datatable)

d(0) = cstr(strVar6)


If you are trying to send this same Cstr(strVar6) value into your insert into statement/update, then that is what you do. Not sure, how else to explain it
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown

Last edited by MKoslof; 06-02-2004 at 12:54 PM.
Reply With Quote
  #19  
Old 06-02-2004, 11:12 AM
MGOOSE MGOOSE is offline
Regular
 
Join Date: May 2004
Posts: 64
Default

Lol Insert Into? Looks like i need to do a little more research - will look into this and if I cant work it out will call back. Thanks

MG
Reply With Quote
  #20  
Old 06-02-2004, 11:58 AM
MGOOSE MGOOSE is offline
Regular
 
Join Date: May 2004
Posts: 64
Default

I think I need to look into the get changes method, I am not adding any new rows as that is dealt with in another table. I need to getchanges and then apply these changes to the database at the back end. I will do more research on this area and hopefully get it to update the changes.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Updating Datagrid shofstetter Database and Reporting 1 01-09-2004 09:41 AM
Updating DataGrid codegirl Database and Reporting 1 02-19-2003 09:27 PM
Problem updating db from DataGrid. Phil Johnson Database and Reporting 10 11-13-2002 07:23 AM
updating the datagrid table kinki Database and Reporting 0 06-19-2002 07:11 AM

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Updating a Datagrid
Updating a Datagrid
Updating a Datagrid Updating a Datagrid
Updating a Datagrid
Updating a Datagrid
Updating a Datagrid Updating a Datagrid Updating a Datagrid Updating a Datagrid Updating a Datagrid Updating a Datagrid Updating a Datagrid
Updating a Datagrid
Updating a Datagrid
 
Updating a Datagrid
Updating a Datagrid
 
-->