• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

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

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
JeffreyDurham
Asked:
JeffreyDurham
  • 6
  • 3
  • 2
1 Solution
 
newyuppieCommented:
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
 
SanclerCommented:
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
 
newyuppieCommented:
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
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
JeffreyDurhamAuthor Commented:
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
 
SanclerCommented:
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
 
JeffreyDurhamAuthor Commented:
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
 
JeffreyDurhamAuthor Commented:
But I do close the other connection, so now I should only be using the OleDBAdapter thing
0
 
JeffreyDurhamAuthor Commented:
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
 
SanclerCommented:
>>
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
 
JeffreyDurhamAuthor Commented:
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
 
JeffreyDurhamAuthor Commented:
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

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 6
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now