Solved

How do I save changes in my db from my datagrid?

Posted on 2006-11-21
11
186 Views
Last Modified: 2010-04-23
Dear Experts..
What I'm trying to do is modify my program so that when the user edits a value in the datagrid, it automatically changes the table in the db. Any help on this matter would be greatly appreciated. Here is the code I use to bind the datagrid to the db..

As you can see I'm using a bindingSource object to link both the datagrid and the bindingNavigator..

Private bindingSource1 As New BindingSource

Public Sub bindTable()
        'dodatagridinit()
        Dim dbAdapter = New OleDbDataAdapter()
        Dim dtResults = New DataTable("Results")
        dbAdapter.Fill(dtResults, mydb.myRecordSet)
        Me.lblTotal.Text = "Total: " & mydb.myRecordSet.RecordCount
        Me.bindingSource1.DataSource = dtResults
        Me.BindingNavigator1.BindingSource = bindingSource1
        Me.mydatagrid.DataSource = bindingSource1
        Me.mydatagrid.AutoResizeColumns()
        dbAdapter = Nothing
        dtResults = Nothing
    End Sub
0
Comment
Question by:JeffreyDurham
  • 6
  • 3
  • 2
11 Comments
 
LVL 13

Expert Comment

by:newyuppie
ID: 17993100
i think the appropiate way to do it is handling the CellValidated event, such that you only update to the database once the edit has been validated for errors.

Private Sub CellValidated(ByVal sender As Object, _
    ByVal e As DataGridViewCellEventArgs) _
    Handles DataGridView1.CellValidated

    bindingsource1.EndEdit()
    dbAdapter.Update(dtResults)
End Sub

there are some other changes you should make for this to work.
1)from your bindTable sub, remove the
dbAdapter = Nothing
dtResults = Nothing
lines

and
2) from your bindTable sub, remove the
Dim dbAdapter = New OleDbDataAdapter()
Dim dtResults = New DataTable("Results")

and place them outside any sub, but inside of the class (first code after class declaration)
Private dbAdapter = New OleDbDataAdapter()
Private dtResults = New DataTable("Results")

0
 
LVL 34

Accepted Solution

by:
Sancler earned 300 total points
ID: 17994155
In principle I agree with newyuppie, but there are some other points.

1)  The code as you post it wouldn't work.  It moves straight from

        Dim dbAdapter = New OleDbDataAdapter()

to

        dbAdapter.Fill(dtResults, mydb.myRecordSet)

At that point dbAdapter has neither a SelectCommand nor a Connection.

2)  Even if the code did work so far as filling the datatable and datagridview was concerned it would not, even with newyuppie's suggested amendments, update the database because dbAdapter is never given the necessary update commands to do that.  You need to use a command builder before that can happen.

3)  While there is no "rule" against immediately saving changed data back to the database, for general purposes it strikes me as overkill.  Every update consumes time and resources in making a connection.  The datatable stores all changes that are made.  So lots can be made at one time.  For example, all on the close of the application; or at timed intervals (e.g. so that all unsaved changes are not lost if the app or system crashes); or in response to user action - e.g. clicking a "save" button - or whenever else you choose.  In some circumstances - e.g. a fast-changing multi-user database - it might be necessary to keep it bang up-to-date on a cell by cell basis: otherwise at the very least it seems sensible to wait until a whole row, rather than just a single cell, has been edited.

So ...

Your initial declaration should be

   Private dbAdapter As OleDbDataAdapter

Within your BindTable sub you will need

   Dim con As New OleDbConnection("<your connection string>")
   Dim sql As String ("<your select statement>")
   dbAdapter = New OleDbAdapter(sql, con)
   Dim cb As New OleDbCommandBuilder(dbAdapter)

And, while newyuppie's code should then work, you ought to consider whether it might be better to move it from the CellValidated sub to some other sub that would not be called quite so frequently.

Roger
0
 
LVL 13

Expert Comment

by:newyuppie
ID: 17995049
i basically agree with roger in his suggestions as well, especially suggestion number 3). i think the whole purpose of existance of ADO.NET is to work offline as often as you possibly can. and you should exploit that, for it will make your application a lot faster, responsive and efficient in general. the dataset can hold information the same as the database, so there really is no practical need (unless you have very specific requirements) to update to database all the time.

and as he suggests too, you have to use a CommandBuilder in your code, unless you will set the update command manually.

NY
0
 

Author Comment

by:JeffreyDurham
ID: 17997347
2) from your bindTable sub, remove the
Dim dbAdapter = New OleDbDataAdapter()
Dim dtResults = New DataTable("Results")

and place them outside any sub, but inside of the class (first code after class declaration)
Private dbAdapter = New OleDbDataAdapter()
Private dtResults = New DataTable("Results")

Something very strange happened.. When I tried to do this it came up with the error:
   'Type OleDbDataAdapter() not defined'

At the top of my class I have:
   Imports System.Data.OleDb

So I'm not sure what's up with that.
However, I changed it to
   private dbadapter = new OleDb.OleDbDataAdapter()
and that seems to work

Thank you both newyuppie & sancler for your help with this issue. The other question I had in relation to your replies..
I do establish a connection in my 'mydb' module right before I call the databind.. should I attempt to use that connection, or does the:
    dbAdapter = New OleDbAdapter(sql, con)
    Dim cb As New OleDbCommandBuilder(dbAdapter)
require that I use a new connection.

This is how I establish my connection to the db:

    Public Sub initMyDatabase()
        myConnection = New ADODB.Connection
        myRecordSet = New ADODB.Recordset
        myCmdSet = New ADODB.Recordset
        Dim strConnect As String, strQuery As String
        strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;"
        strConnect = strConnect & "Persist Security Info=False;"
        strConnect = strConnect & ";Data Source=" & myDBPath
        myConnection.ConnectionString = strConnect
        myConnection.ConnectionTimeout = 10
        myConnection.CursorLocation = ADODB.CursorLocationEnum.adUseClient
        myConnection.Open()
        openMyRecordSet()
    End Sub

    Private Sub openmyrecordset()
        Dim myQuery$, myTableName$
        Dim temprs As ADODB.Recordset
        temprs = New ADODB.Recordset
        temprs.Open("SELECT * FROM datagridInfoTable", myConnection, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)
        On Error Resume Next
        temprs.MoveFirst()
        On Error GoTo 0
        myTableName = temprs.Fields("tableName").Value
        myQuery = temprs.Fields("Query").Value
        myQuery = Replace(myQuery, "myRecordSet", myTableName)
        temprs.Update()
        temprs.Close()
        temprs = Nothing
        On Error GoTo ErrHandler
        myRecordSet.Open(myQuery, myConnection, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)
        Exit Sub
errHandler:
        Stop
    End Sub

Anyways, I'm going to try to implement Sancler's idea first, because I think I want to go with both when they click the save button (I do have one in my bindingNavigator I was thinking I would do that with) and when it exits the program. I wasn't sure if I should do it per each change or at the end, which would be faster or what.
So I'm going to try out your code and see if I can get it to work, and then I'll come back to this.

Thanks again! ~Jeffrey
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17997591
There may be a bigger issue here.  I'm not sure that there are any "rules" against mixing ADODB and ADO.NET, but they are different things and I have never seen it done.

On specifics.  You will, to use an OleDbDataAdapter, need an OleDbConnection: no, your current connection won't work for that purpose.  But (I'm guessing here) I think you might get conflicts if you try to use your OleDbConnection while your ADODB one is open.

Roger
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:JeffreyDurham
ID: 17998027
Ok, I modified my module (mydb) so that now it simply uses ADODB to get the query and the tablename out of the db. Plus, it saves the connection string it uses, and now I'm using that in the ..new OleDBConnection(  <here>  )

This is the values of those two vars:
mydb.myDbVars.myConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;;Data Source=C:\Documents and Settings\Michael\Desktop\UI Programs\UI prog - Home Sales\UI&ML&HSdb.mdb"
mydb.myDbVars.myQueryStr = "SELECT * FROM myRSSelection;"

And this is what my bindtable looks like now, I've changed it to the code you gave me Roger.
Now, my only issue is that I don't know how to fill `dtResults', and the datagrid opens up empty. Plus, I'm not sure how to get the recordcount.

Public Sub bindTable()
        dodatagridinit()

        Dim con As New OleDbConnection(mydb.myDbVars.myConnectStr)
        dbAdapter = New OleDb.OleDbDataAdapter(mydb.myDbVars.myQueryStr, con)
        Dim cb As New OleDbCommandBuilder(dbAdapter)

    'dbAdapter.Fill(dtResults, mydb.myRecordSet)
    'Me.lblTotal.Text = "Total: " & mydb.myRecordSet.RecordCount
        Me.bindingSource1.DataSource = dtResults
        Me.BindingNavigator1.BindingSource = bindingSource1
        Me.mydatagrid.DataSource = bindingSource1
        Me.mydatagrid.AutoResizeColumns()
        'dbAdapter = Nothing
        'dtResults = Nothing
    End Sub

So what do I change this line to to make it work?

   'dbAdapter.Fill(dtResults, mydb.myRecordSet)
0
 

Author Comment

by:JeffreyDurham
ID: 17998034
But I do close the other connection, so now I should only be using the OleDBAdapter thing
0
 

Author Comment

by:JeffreyDurham
ID: 17998046
Plus, do I need to do anything with the commandBuilder, or does that automatically take care of updating now that I've declared it?
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17998074
>>
So what do I change this line to to make it work?

   'dbAdapter.Fill(dtResults, mydb.myRecordSet)
<<

     dbAdapter.Fill(dtResults)

You are now just working with a datatable - dtResults.  mydb.myRecordSet has nothing to do with anything in this context.

>>
do I need to do anything with the commandBuilder, or does that automatically take care of updating now that I've declared it?
<<

It should be automatic

Roger
0
 

Author Comment

by:JeffreyDurham
ID: 17998106
Ok, this seems to be mostly working. I was able to change one field which was a text field into the db and it saved it. I tried altering an 'autonumber' field, but that had no effect, was not saved (Not that I expected it to necessarily)

But the whole point of the datagrid is the first column, which is a checkbox. Whatever records they select by checking that first column are then later opened up in a different program using a query which only pulls up selected records.

For some reason, the checkbox is not saving each time. Is there a special way that this must be done?
0
 

Author Comment

by:JeffreyDurham
ID: 17998252
Sweet. :)

Ok, the issue with my last comment was that the field didn't exist in the db, just in the datagrid. So that is fixed. I'm going to award you the points Roger. Thank you very much for all your help! I know very little about .net and you really were a great help. Hope you have a good thanksgiving!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now