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
  #41  
Old 06-08-2004, 10:14 AM
MGOOSE MGOOSE is offline
Regular
 
Join Date: May 2004
Posts: 64
Default


Thats right, I have made 7 of the columns read only in the datagrid, but three columns are writeable so can accept changes or new inputs from the user. Its these changes I need to update into the dataset and then back to the database before refreshing the datagrid.
Reply With Quote
  #42  
Old 06-09-2004, 11:29 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, let me put a sample together for you. Give me some time, I need to do some things at work/personal stuff today.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #43  
Old 06-09-2004, 12:26 PM
MGOOSE MGOOSE is offline
Regular
 
Join Date: May 2004
Posts: 64
Default

That'd be great,

Thanks for all your help.

Mark
Reply With Quote
  #44  
Old 06-10-2004, 12:10 PM
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, basically create an oledbAdapter and oleDbConnection object for your application. If you go to the properties of these control, you will see that the OleDbAdapter has defined update, select and delete commands..it also takes an active connection. I think you should play with these to understand them. Basically, even if you use .GetChanges or .HasChanges, an exception will be thrown if you have no update command. You can code your commands. I would recomment looking into the oledbcommandbuilder class. But basically, something like this would work, but you need to modify it for your own needs, as is, this won't work. I would suggest, allowing the user to edit the DataGrid on a row by row basis. (IE - double click a record, a new form comes up on the screen...they can change values in this record, run an update, etc.).

Code:
Private Sub UpdateDataSet(ByVal d As DataTable) Dim dsModifiedRows As DataTable Dim oCommand As OleDb.OleDbCommand Me.OleDbConnection2.Open() oCommand = New OleDbCommand oCommand.CommandType = CommandType.Text oCommand.Connection = Me.OleDbConnection2 dsModifiedRows = d.GetChanges(DataRowState.Modified) Try If Not dsModifiedRows Is Nothing Then oCommand.CommandText = "UPDATE myTable SET myColumn = '" & dt(i) & "'" Me.OleDbDataAdapter1.UpdateCommand = oCommand Me.OleDbDataAdapter1.Update(dsModifiedRows) d.AcceptChanges() End If Catch e As Exception MsgBox(e.Message) Finally oCommand = Nothing dsModifiedRows.Dispose() End Try End Sub

EDIT: sorry I was typing kind of quick . DT(i) could be an array value or within a for loop of all datarows..I cut out the for loop in the code for simplicity. I see now how that might be confusing . Basically this is the structure you want, tweak it as needed.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown

Last edited by MKoslof; 06-10-2004 at 01:31 PM.
Reply With Quote
  #45  
Old 06-10-2004, 03:58 PM
MGOOSE MGOOSE is offline
Regular
 
Join Date: May 2004
Posts: 64
Thumbs up

lol - thanks, I'll try a for loop with the (i). It looks interesting and looks like it should work nicely. I will try to adapt it to my needs and hopefully put the update issue to bed.

Thanks

MG.
Reply With Quote
  #46  
Old 06-11-2004, 07:19 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, good luck.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #47  
Old 06-12-2004, 06:37 AM
MGOOSE MGOOSE is offline
Regular
 
Join Date: May 2004
Posts: 64
Default

Ah man - so near and yet so far.........

I have amended the example you gave and it kind of works, it updates the dataset and saves back to the database......but it isnt as selective as I'd like, for some reason (my logic must be wrong) it is making all rows in the dataset equal the modified row.

can you see anything in the code that is causing each row to be updated? I only want the rows to be updated if the primary key is the same as the primary key in the modified dataset.

The code is as follows:

Code:
    Private Sub UpdateDataSet()
        Dim dsModifiedRows As DataTable
        Dim oCommand As OleDb.OleDbCommand
        Dim i As Int32
        Dim n As Int32

        Me.OleDbConnection1.Open()
        oCommand = New OleDbCommand
        oCommand.CommandType = CommandType.Text
        oCommand.Connection = Me.OleDbConnection1

        dsModifiedRows = dt.GetChanges(DataRowState.Modified)

        Try
            If Not dsModifiedRows Is Nothing Then

                For n = 0 To dsModifiedRows.Rows.Count - 1
                    For i = 0 To dt.Rows.Count - 1
                        If dt.Rows(i).Item(0) Is dsModifiedRows.Rows(n).Item(0) Then

                            oCommand.CommandText = "UPDATE Staff SET [Name] = '" & dsModifiedRows.Rows(n).Item(1) & "', [Address] = '" & dsModifiedRows.Rows(n).Item(2) & "', [Date Of Birth] = '" & dsModifiedRows.Rows(n).Item(3) & "'"

                            Me.OleDbDataAdapter1.UpdateCommand = oCommand
                            Me.OleDbDataAdapter1.Update(dsModifiedRows)
                            dt.AcceptChanges()
                        End If
                    Next
                Next
                dsModifiedRows.Dispose()
            End If
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            oCommand = Nothing
            OleDbConnection1.Close()
        End Try

    End Sub
I probably dont need to tell you but item(0) is the primary key.

Thanks again.

Mark
Reply With Quote
  #48  
Old 06-12-2004, 12:14 PM
MGOOSE MGOOSE is offline
Regular
 
Join Date: May 2004
Posts: 64
Default Done It

I got it - it wasnt the VB that was wrong, I needed to put a where statement in the SQL.

I added WHERE [Staff Number] = '" & dsModifiedRows.Rows(n).Item(0) & "'"
and it works.........at long last.

Thanks for all the help in getting there.

MG

Reply With Quote
  #49  
Old 06-14-2004, 09:23 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

That's great news. Glad to help, good luck
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
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 10:41 AM
Updating DataGrid codegirl Database and Reporting 1 02-19-2003 10:27 PM
Problem updating db from DataGrid. Phil Johnson Database and Reporting 10 11-13-2002 08:23 AM
updating the datagrid table kinki Database and Reporting 0 06-19-2002 08: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
 
-->