Solved

How to insert,delete,update datagrids to MS Access database

Posted on 2006-05-08
20
262 Views
Last Modified: 2010-04-23
Hey guys how is everyone today?

I have a small issue with datagrids so far I'm able to retrieve data from MS access and populate the grid. The user let say puts in an id number and the grids
gets populated...now I want the user to be able to edit certain attributes from grid itself for example (lastname ,firstname, data of birth etc)

I am able to actually edit in the grid, but I don't know how to rebind it back once I press a submit button or better yet how am i able to reconnect all the new
fresh data that has been either inserted,deleted,update in the grid itself.

There are much more fields in this grid, but I decided to keep it nice and simple by using only 4 fields .

Well look at my noobish code and tell me what you think guys?

////////////////

 Dim connString As String = "Provider = Microsoft.JET.OLEDB.4.0; Data Source = X:\members.mdb; "
        Dim myConnection As OleDbConnection = New OleDbConnection
        Dim insertCmd As OleDbCommand = New OleDbCommand

        myConnection.ConnectionString = connString
        myConnection.Open()

        With insertCmd

            .Connection = myConnection
            .CommandText = " Select CreditApp_ID,FirstName,MiddleName,LastName,DOB From CreditApplication " & _
            "Where CreditApp_ID=@CreditApp_ID"

            .CommandType = CommandType.Text




            .Parameters.Add(New OleDbParameter("@CreditApp_ID", OleDbType.Char, 10))
            .Parameters.Add(New OleDbParameter("@FirstName", OleDbType.Char, 30))
            .Parameters.Add(New OleDbParameter("@MiddleName", OleDbType.Char, 1))
            .Parameters.Add(New OleDbParameter("@LastName", OleDbType.Char, 30))
            .Parameters.Add(New OleDbParameter("@DOB", OleDbType.Date))


                 .Parameters("@CreditApp_ID").Value = CreditID
            .Parameters("@FirstName").Value = CreditID
            .Parameters("@MiddleName").Value = CreditID
            .Parameters("@LastName").Value = CreditID

 End With



        'Now to declare a OLEDBDataAdapter object
        Dim Adapter As New OleDbDataAdapter

        'Now to declare a dataset
        Dim DataSet As New DataSet

        'Now to apply my Command to the DataAdapter
        Adapter.SelectCommand = insertCmd



        Try

            Adapter.Fill(DataSet)
           

            'To bind the Dataset to the DataGrid :)
            grdResults.DataSource = DataSet

            'Tell the DataGrid which table in the Dataset to use
            grdResults.DataMember = DataSet.Tables(0).TableName


            'Setting the Alternating Colors property to the Grid
            grdResults.AlternatingBackColor = Color.WhiteSmoke

            'Set the GridLineStyle Property :)
            grdResults.GridLineStyle = DataGridLineStyle.None

            'Set the SelectionBackColor and the Selection ForeColor Properties
            grdResults.SelectionBackColor = Color.LightGray
            grdResults.SelectionForeColor = Color.Black

           

        Catch OleDbExceptionErr As OleDbException
            Debug.WriteLine(OleDbExceptionErr.Message)
        Catch InvalidOperationExceptionErr As InvalidOperationException
            Debug.WriteLine(InvalidOperationExceptionErr.Message)



        End Try


        'Cleaning up the Mess
        insertCmd.Dispose()
        insertCmd = Nothing
        Adapter.Dispose()
        Adapter = Nothing
        DataSet.Dispose()
        DataSet = Nothing

        myConnection.Dispose()
        myConnection = Nothing

///////////////////////////////

Keep in mind guys the code works well so there is no coding trouble to troubleshoot here....more like solution problem lol

Again objective is to have another button rebind the grid after user is done updating,deleting,inserting etc.

Im a noob at this whole binding stuff and would like to learn the best way to do this stuff.

Thankyou in advance as always :)












0
Comment
Question by:masterat03
  • 11
  • 9
20 Comments
 

Author Comment

by:masterat03
ID: 16635880
Can anyone out there help me?...I would really like to solve this issue.
0
 
LVL 5

Expert Comment

by:maralans
ID: 16636002
You have already disposed of your dataset so set your datagrid.datasource = nothing and refill your dataset on the button event.

 Adapter.Fill(DataSet)
 grdResults.DataSource = DataSet
0
 

Author Comment

by:masterat03
ID: 16639566
Well that didnt really work for me because no update actually occur to the database. I did put th extra code in for the next button event
which is
///////

Adapter.Fill(DataSet)
 grdResults.DataSource = DataSet


////

But it doesnt update the database with the new data I edited in the grid itself
0
 

Author Comment

by:masterat03
ID: 16639707
I'm just taking a wild guess here, but in the original code I have the grid getting the results from SQL command which gets its data from 4 field names
and then populate them to the grid with its attributes....

Would it be the same I do the same thing for 2nd button when i click on submit I would have an UPDATE SQL command to get what ever i updated on the grid
itself to put it back on the MS access database or no.

Again im just a rookie, but I'm trying to make a good guess if that could be a way of handling it...I'm probably way off lol.

If anyone out there knows please don't hesitate to participate :)

0
 

Author Comment

by:masterat03
ID: 16640680
So umm....is anyone willing to give me a good insight for this issue?
0
 

Author Comment

by:masterat03
ID: 16640982
Well assuming if I'm correct on the theory I said before ...I decided to do this for my 2nd button which is a submit button.

I know im wrong, but I think this could be a way to do this..
I have the user edit what they need to edit out within the datagrid box once he/she is done then they hit a submit button
now the way I have the layout of the code is very similar to the first one , but this one contains an update SQL statement

I tried it out , but nothing happen...if there is anyone out there PLEASE!!! give me some good insight on how to do this.
Remember the objective here is the person gets data into the grid...does their edit/delete/update etc on the grid
and then  presses the submit button and the grid then takes the data and puts it back to the MS access DB.

this is what I tried to do for the 2nd button....eventually it didnt work , but Im trying my best to see if I can find the solution to this problem.

////////////////////////////

  Dim CreditID = txtCreditID.Text.Trim

        Dim connString As String = "Provider = Microsoft.JET.OLEDB.4.0; Data Source = X:\members.mdb; "
        Dim myConnection As OleDbConnection = New OleDbConnection
        Dim updateCmd As OleDbCommand = New OleDbCommand

        myConnection.ConnectionString = connString
        myConnection.Open()

        With updateCmd

            .Connection = myConnection
            .CommandText = " Update [CreditApplication]Set CreditApp_ID= @CreditApp_ID, FirstName =@FirstName,MiddleName =@MiddleName, LastName= @LastName, " & _
            "Where CreditApp_ID=@CreditApp_ID"

            .CommandType = CommandType.Text


            .Parameters.Add(New OleDbParameter("@CreditApp_ID", OleDbType.Char, 10))
            .Parameters.Add(New OleDbParameter("@FirstName", OleDbType.Char, 30))
            .Parameters.Add(New OleDbParameter("@MiddleName", OleDbType.Char, 1))
            .Parameters.Add(New OleDbParameter("@LastName", OleDbType.Char, 30))

             .Parameters.Add("@CreditApp_ID", Me.grdResults.DataSource)
            .Parameters.Add("@FirstName", Me.grdResults.DataSource)
            .Parameters.Add("@MiddleName", Me.grdResults.DataSource)
            .Parameters.Add("@LastName", Me.grdResults.DataSource)



End With



        'Now to declare a OLEDBDataAdapter object
        Dim Adapter As New OleDbDataAdapter

        'Now to declare a dataset
        Dim DataSet As New DataSet


        'Now to apply my Command to the DataAdapter
        Adapter.SelectCommand = updateCmd




        Try

            Adapter.Fill(DataSet)
           
            'To bind the Dataset to the DataGrid :)
            grdResults.DataSource = DataSet

            'Tell the DataGrid which table in the Dataset to use
            grdResults.DataMember = DataSet.Tables(0).TableName


            'Setting the Alternating Colors property to the Grid
            grdResults.AlternatingBackColor = Color.WhiteSmoke

            'Set the GridLineStyle Property :)
            grdResults.GridLineStyle = DataGridLineStyle.None

            'Set the SelectionBackColor and the Selection ForeColor Properties
            grdResults.SelectionBackColor = Color.LightGray
            grdResults.SelectionForeColor = Color.Black

            'DataSet.Tables(0).Rows.Count()

        Catch OleDbExceptionErr As OleDbException
            Debug.WriteLine(OleDbExceptionErr.Message)
        Catch InvalidOperationExceptionErr As InvalidOperationException
            Debug.WriteLine(InvalidOperationExceptionErr.Message)



        End Try


        'Cleaning up the Mess
        updateCmd.Dispose()
        updateCmd = Nothing
        Adapter.Dispose()
        Adapter = Nothing
        DataSet.Dispose()
        DataSet = Nothing

        myConnection.Dispose()
        myConnection = Nothing

///////////////

Im assuming this would work because I have the grid take its data and put it back to the database with its new attributes that the user inserted while
it was at the grid....

Please anyone out there help an old rookie out.

0
 
LVL 5

Accepted Solution

by:
maralans earned 500 total points
ID: 16641318
Public Class Form1

    Dim cs As String = "provider=microsoft.jet.oledb.4.0;data source = x:\members.mdb "
    Dim cn As New OleDb.OleDbConnection(cs)
    Dim da As New OleDb.OleDbDataAdapter(" Select CreditApp_ID,FirstName,MiddleName,LastName,DOB From CreditApplication", cn)
    Dim ds As New DataSet
    Dim cm As CurrencyManager

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        da.Fill(ds)

        dg.DataSource = ds.Tables(0)

        cm = CType(BindingContext(dg.DataSource), CurrencyManager)
        Dim cb As OleDb.OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(da)

    End Sub

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        cm.EndCurrentEdit()
        cm.AddNew()
    End Sub

    Private Sub BtnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnDelete.Click
        cm.EndCurrentEdit()
        cm.RemoveAt(cm.Position)
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        da.Update(ds)
        da.Dispose()
    End Sub
End Class
0
 

Author Comment

by:masterat03
ID: 16641963
ok I tried out your code in a new form window
and here is the verdict...

When I try to save a the grid back the MS access I get an error
which is this
/////
An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

Additional information: Dynamic SQL generation for the DeleteCommand is not supported against a SelectCommand that does not return any key column information.

////////////////////////

Also what does the currencyManager suppose to do...what exactly is the currencymanager responsible for?...

I was able to add new data to the grid, Delete, again it is only when I try to save that I get that error messge.

Again thankyou for helping out this Rookie out...Im really trying to get the hang of this stuff.




0
 

Author Comment

by:masterat03
ID: 16641982
Also to give you better insight the error occurs here..

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        da.Update(ds) ///Error occurs here
       da.Dispose()
    End Sub
End Class

////Error Message ///

An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

Additional information: Dynamic SQL generation for the DeleteCommand is not supported against a SelectCommand that does not return any key column information.


////////////////////////

0
 
LVL 5

Expert Comment

by:maralans
ID: 16642335
Do you have a primary key set in your access table?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 5

Expert Comment

by:maralans
ID: 16642352
Also, take out the da.Dispose() in the btnSave event.  Its not needed.
0
 

Author Comment

by:masterat03
ID: 16642385
no primary key in the table
0
 
LVL 5

Expert Comment

by:maralans
ID: 16642407
That is probably the cause of the error.  You need to set a primary key in the table.  Do you know how to do that?
0
 

Author Comment

by:masterat03
ID: 16642757
I set it up and it works like a charm..but with a few bugs lol...example if I do an update and I try to do a search like in the original code I see the new update if I try to do something else to it again (like another update) and I hit submit...the update never occurs and for some reason the old data stays :(

Also to answer your other question you see in the table that I have its not required to have a primary key because the purpose of the table
is to know how many times this person applied for credit and we decided to have duplicate IDs ...i guess I will have to make a work around for that one because I notice your code only works with Primary keys if its available.

I though primary keys at somepoint could be able to copy the same one as long as its other attributes are not the same ..but I was wrong on that one lol.

0
 
LVL 5

Expert Comment

by:maralans
ID: 16643017
You can add another column with a primary key and have duplicate CreditApp_ID's.  If you ever have the need to expand this application where you might be joining tables a primary key will be necessary.  You are better off making the change now.   An alternative would be to make the CreditApp_ID the primary key and then search on the name.  I guess I don't understand why a person who applied for credit on 4/5/2000 and again on 5/4/2006 would have the same CreditApp_ID  number?  It would seem that everytime someone applied for credit there would be a different ID number.  And, where are you going to get this number from to look it up.  Is the customer going to supply it?

As far as the bugs go, you will need to explain that better as I am not having any problems.
0
 

Author Comment

by:masterat03
ID: 16643166
No I totally agree with you about the primary key, I ve always said primary key is the way to go , but sometimes those higher above seem to think differently lol.

As for the problem I don't know if you remember when I posted the first code which executes a query and fills the dataset.
Well when the load forms it gives me everything in creditapplication table. so when i put in the credit application id in the search it pretty much slims it down to the
particular person. now when i edit certain info within his/her attribute and save it , it doesnt save at all.

Don't get me wrong if i don't do the search like I said and I just open up the form and the info loads in and I decide to update and submit right then and there
everything works fine....

pretty much that is the little bug im facing right now lol.

0
 
LVL 5

Expert Comment

by:maralans
ID: 16643440
This will show only the CreditApp_ID's in the datagrid that match the value in a search textbox (tbsearch).

Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click

        Dim dv As New DataView()
        With dv
            .Table = ds.Tables(0)
            .RowFilter = "CreditApp_ID = " & tbsearch.Text
        End With
        dg.DataSource = dv

    End Sub
0
 
LVL 5

Expert Comment

by:maralans
ID: 16643559
To add or delete a record you will need to bind the datagrid back to the dataset.


 Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        dg.DataSource = ds.Tables(0)
        cm.EndCurrentEdit()
        cm.AddNew()
    End Sub

    Private Sub BtnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnDelete.Click
        dg.DataSource = ds.Tables(0)
        cm.EndCurrentEdit()
        cm.RemoveAt(cm.Position)
    End Sub
0
 

Author Comment

by:masterat03
ID: 16650435
Hey sorry maralan I didnt respond back right away. Anyhow I wanted to say I didnt know datagrid was this useful and powerful...
I thankyou for helping me overcome this issue and for taking your time to help this rookie out :)

I wanted to know what exactly is currencymanager?...And now I understand why Datagrid must always be binding all the time which is because it only takes a
small snapshot of the db and then disconnects itself.


Also I wanted to say if you can recommend anygood book for me please do so...I would love to get the hang of all this ADO stuff :)

as always thankyou for helping me and perhaps other noobs who had this trouble in the past and perhaps are reading these post.

0
 
LVL 5

Expert Comment

by:maralans
ID: 16652559
No problem at all.  Here is an explaination of the currencymanager:

http://support.microsoft.com/?kbid=311543


As far as books go, I can't really recommend any because I was waiting for more to come out on VB2005.  However, Barnes and Noble usually a pretty good selection for all user levels.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
This video discusses moving either the default database or any database to a new volume.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

747 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

11 Experts available now in Live!

Get 1:1 Help Now