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

Can anyone out there help me?...I would really like to solve this issue.

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

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

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 :)

So umm....is anyone willing to give me a good insight for this issue?

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.

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.

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.


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

Do you have a primary key set in your access table?

Also, take out the da.Dispose() in the btnSave event.  Its not needed.

no primary key in the table

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?

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.

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.

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.

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

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

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.

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.