[RESOLVED] Update Acces DB Will Trying a diffrent approach Work?

I am working on a signout system I have a good start but am trying to solve a small problem.
Signing out equipment I fill part of a form with student data from the dataset with Fillby, fill the other half with equipment data using Fillby1 I then save this combined data to my Access 2007 Database using a OleDb.OleDbConnection.

This works great!

to sign equipment back in I use the following;

Code:

---


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


        Do



            Me.Show()
            Me.Activate()


            Dim BARCODE As String = InputBox("Enter Equipment Barcode")
            'storing the text entered in a string
            If BARCODE <> "" Then


                'Load the Record into my form

                'Fillby loads record where RETURNDATE IS NULL and BARCODE = @BARCODE

                Try
                    Me.SignedOutEquipmentTableAdapter.FillBy(Me.SignoutDataSet.SignedOutEquipment, BARCODE)
                    If Me.SignoutDataSet.SignedOutEquipment.Rows.Count = 0 Then
                        MsgBox("That Item Was Not Signed Out", MsgBoxStyle.Critical, "Equipment Not Signed OUT")



                    End If
                    'Here I insert todays date into the form
                    Dim now As Date = Date.Now
                    now.ToString("F")
                    RETURNDATE.Text = now

                    'Now I want to Update the record with the RETURNDATE Field
                    'and save it back to the Database

                    Using dbConn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= C:\Documents and Settings\wfrisbie\My Documents\Visual Studio 2008\Projects\Signout\Signout\Signout.accdb")

                        dbConn.Open()

                        Using Command As New OleDbCommand("UPDATE SignedOutEquipment " + _
                  "SET RETURNDATE = #" + Me.RETURNDATE.Text + "# WHERE BARCODE = '" + _
                  Me.BARCODE.Text & "' AND RETURNDATE is NULL", dbConn)

                            Command.ExecuteNonQuery()

                        End Using
                        dbConn.Close()
                    End Using
                Catch ex As OleDbException
                    MessageBox.Show(ex.Message)

                Catch ex As System.Exception
                    System.Windows.Forms.MessageBox.Show(ex.Message)


                End Try

            Else : Me.Close()
                Exit Do
            End If

        Loop
    End Sub

---

This also works great with one exception Unless I restart my app the equipment I just signed out will not gives me my "that equipment was not signed out" yet I know the record is in the access db and after restarting my app it will find ant sign the equipment in.

I can not find out anywhere how to reload the dataset from access I have tried several ways Clear/Reset ect. none seems to work.

So I was thinking that Maybe I could use the OleDb.OleDbConnection to get the records directly from the Access DB instead of the Dataset something like;

Code:

---

            Me.Show()
            Me.Activate()


            Dim BARCODE As String = InputBox("Enter Equipment Barcode")
            'storing the text entered in a string
            If BARCODE <> "" Then
                'Load the Record into my form
                Try
                    Dim dbconn As OleDb.OleDbConnection
                    dbconn = New OleDb.OleDbConnection
                    dbconn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= C:\Documents and Settings\wfrisbie\My Documents\Visual Studio 2008\Projects\Signout\Signout\Signout.accdb"
                    dbconn.Open()

                    Dim db As String = ("Select IDNUM,LNAME,FNAME,PHONE,EMail,BARCODE,TYPE,DESCR,DATEOUT,DUEDATE,RETURNDATE FROM SignedOutEquipment WHERE BARCODE=@BARCODE and RETURNDATE is NULL")

---

I stopped right there because A. I'm not sure I am on the right track B. I started thinking about binding sources for my form. C. I am a Novice and am really not sure how to proceed.

If someone has some answers or suggestions I would really appreciate them as well as a push in the right direction.
as I said I am new at this learning as I go
Sorry this was a long post but I wanted to clearly explain my issue

Thanks

I don't have the best answer, because I see you are using tableadapters, and I haven't used those. Interestingly, you don't use them all that much, either. After all, you have the tableadapter managing the dataset, then for the update, you aren't letting the tableadapter handle that, but are performing the update directly on the DB via a commandobject.

There are two typical ways to interact with DBs (actually, there are several more now, but ignore that). The first way is to do all of your interactions in a rather 'manual' fashion using calls to SELECT data from the DB (possibly getting a reader, a single value, or a datatable), UPDATE and INSERT to the DB, or even DELETE from the DB. This type of interaction goes through a commandobject.

The second pattern would be to bring all the data into a dataset, manipulate the dataset (updates, inserts, and deletes), then use the dataset to update the DB.

You are combining the two. You use the dataset to hold the data, but you don't manipulate the data in the dataset, but instead go manipulate it directly in the DB. By doing this, the dataset has no knowledge about the changes in the DB. You would need to refresh the data in the dataset. Unfortunately, having not used TableAdapters, I can't say how that should be done.

Thats funny, I knew that. I could not get the table adapters to update the Access tables, and now I can't get the dataset to update from the Access tables so I guess I am on the right track by using the OleDb.OleDbConnection to both fill my forms and update the DB. As I said I am new at this and am not quite sure about Query structure and getting the results to fill my form (DataBinding?) I know it will be a little more difficult to write, example's I find on the web all seem to be of the Add or Update type and none I can find actually Fill a form from the query maybe you could give me a little push from my stopping point I can figure out the rest of the app myself.

Actually, what I tend to do is fill a datatable in a dataset using a dataadapter, which is older technology than the tableadapter, and less versatile. I then make all my changes to the dataset rather than making changes to the database, and push all the changes out to the database in one step by calling Update. There are caveats to that approach. For instance, you need to have valid Update, Insert, and Delete queries available for the dataadapter, which is generally done via a CommandBuilder object, but that requires that all the queries from the DB contain no joins (and some other less important restrictions apply).

Is that the best way to go? Probably not. I'm just stuck behind the times a bit for reasons that aren't entirely under my control, and which are not worth going into.

I think the best approch for me would be to fill my form directly from the access data table and write my edits the same way but I am not quite sure how. If somone could give me a hint or a push or direct me to an example I would appreciate it most of the examples I find use a data grid view but I need to fill a form.