Linking VB Program To MS Acess Database, Adding and Updating Rows

Your asking a ver opened ended question. It's kinda like taking your car to a mechanic and saying 'it's broke.'

Very specifically - what is failing?

I tried something that I dont have any more, more or less by posing this question I am trying to get diffrent ways to do this without ado.  How can I do this, I will try any sugestion that is posted here.

Again - what do you need?

Why are you not using ado? What is failing?

What I want to do is, through a program I have made, on one form add customer and return information, on the other lookup this information.  This information needs to be stored in my access database.  Im new, dont understand this well because I havent done much with programing, I do the other aspects of computers like cgi and web design.  I ended up getting this tossed my way because it needed to be done and money is short.  I have made the forms in vb .net already as well as the access database.

Where do I go from here?

This was my latest atempt, the program builds and durring debugging i get a santax error...


Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'Make sure the user entered a customer name in TextBox2
        If CFNTA.Text.Length < 1 Then

            'If not, give them a warning message
            MessageBox.Show("Supply Customer First Name", "No Customer Name", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)

        Else

            'If there is a customer name, try the update

            'Create the connection
            Dim conn As New OleDb.OleDbConnection
            'Create the command
            Dim upd As New OleDb.OleDbCommand

            'Set the connection string
            conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\Term20\pps\Open.mdb"

            'Set the command properties
            upd.CommandType = CommandType.Text
            upd.Connection = conn
            upd.CommandText = "UPDATE Open SET CFNTA= " & CFNTA.Text & ",CLNTA='" & CLNTA.Text & ",SMNTA='" & SMNTA.Text & ",OINTA='" & OINTA.Text & ",RINTA='" & RINTA.Text & ",RMATA='" & RMATA.Text & ",RSNTA='" & RSNTA.Text & ",OPNY='" & OPNY.Text & ",OPNN='" & OPNN.Text & ",CIFTA1='" & CIFTA1.Text & ",CIFTA2='" & CIFTA2.Text & ",Q1Y='" & Q1Y.Text & ",Q1N='" & Q1N.Text & ",Q2UPSCONF='" & Q2UPSCONF.Text & ",Q3Y='" & Q3Y.Text & ",Q3N='" & Q3N.Text & ",DMGY='" & DMGY.Text & ",DMGN='" & DMGN.Text & ",Q4PN1='" & Q4PN1.Text & ",Q4PN2='" & Q4PN2.Text & ",Q4PN3='" & Q4PN3.Text & ",Q4PN4='" & Q4PN4.Text & ",Q5PN1='" & Q5PN1.Text & ",Q5PN2='" & Q5PN2.Text & ",Q5PN3='" & Q5PN3.Text & ",Q5PN4='" & Q5PN4.Text & ",RSYES='" & RSYES.Text & ",RSNO='" & RSNO.Text & ",SCPY='" & SCPY.Text & ",SCPN='" & SCPN.Text & ",SCTA='" & SCTA.Text & ",QSTA='" & QSTA.Text & ",QBTA='" & QBTA.Text & ",PRTA='" & PRTA.Text & ",CPNTA='" & CPNTA.Text & "' WHERE CPNTA='" & CPNTA.Text & "'"

            'Store the number of records affected by the query
            Dim results As Integer

            'Try to open the connection and update the table
            Try

                conn.Open()
                results = upd.ExecuteNonQuery

            Catch ex As Exception

                'Show the error if either the open or execute fails
                MessageBox.Show(ex.Message)

            Finally

                'Whether the open and execute are successful or not,
                'check the state of the connection and close it if necessary
                If Not conn.State = ConnectionState.Closed Then conn.Close()

            End Try

            'Report the number of updated records to the user
            MessageBox.Show(String.Format("There were {0} record(s) updated", results))

        End If
    End Sub

What was the error?

I do not recognize that commandtext structure.

Ill run it agian and give you all of the information

It says Syntax error in UPDATE statement.

Ok lets try this, walk me through this, in any way possible, oviously im not understinding the tutorials i went through.  Maybe im just that dense.  Sorry to be a pain.

A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Is the true error, i wasnt looking at the error list when it said built, it didnt show an error untill 20 or so seconds after it built and the program popped up in debug mode.

I guess I kinda suspected that. Here is a good ADO tutorial:
http://www.startvbdotnet.com/ado/default.aspx

It looks like you maybe struggling with SQL statements and ADO. A simple update statement might look something like this:

Dim sqlUpdateString as String
sqlUpdateString = "Update MyTable Set Column1 = Value1 Where ColumnID = Value2"

Your most likely not going to lean ADO .net and SQL with just a couple questions on this forum. Your either going to need some formal training or some serious reading.

Maybe, but I need to get this program going,  so I will keep trying, eventully Ill get a working model,  I am quite sure its not a very simple solution but I have to find one.  Thanks for you help.

Ok, went through a tutorial and a test, passed both, but when i try to apply thier way of coding it, this is what i end up with, first the code, then the error:

and the error is "Drum roll"
IErrorInfo.GetDescription failed with E_FAIL(0x80004005).
Under help it states:
Verify that the server name is correct and that the server is running.
Make sure that you are using the correct server name, and that the server can be reached. For more information, see OleDbErrorCollection.

Remarks
This exception is thrown whenever the .NET Framework Data Provider for OLE DB encounters an error generated by the server.

If the severity of the error is too great, the server may close the OleDbConnection. However, the user can reopen the connection and continue.

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\Term20\pps\return.mdb"
        con.Open()
 
        sql = "SELECT * FROM Open"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "re")
 
        con.Close()
        MaxRows = ds.Tables("re").Rows.Count
        inc = -1
    End Sub

Select allOpen in new window

Is 'Open' the name of your table?

Yes Open is the name of the table,  the actual database name is return

right-mouse click on your Access database and select Properties. Copy the location path into the source of your connection string:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\Term20\pps\return.mdb is the location, and i can access using windows explorer.  Does it not like the way im addressing it?  I have the folder its in mapped as a network drive too, so what I can do is use that as the address.. But I want to be sure that is the issue.

Ill try it that way give me a minute.

IErrorInfo.GetDescription failed with E_FAIL(0x80004005).

So now ill try the local file, give me a minute.

No i put the file locally and changed the source via the string

con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\sa001\Desktop\return.mdb"

didnt work same error

the problem is with this string.  But it worked before, in fact it worked right along with the tutorial.

da.Fill(ds, "return")

Select allOpen in new window

Your said 'return' was the name of your database? That parmameter for .Fill is suppose to be the table name. Try this:


 da.Fill(ds, "open")

I am attaching the code and the detailed error

System.Data.OleDb.OleDbException was unhandled
  ErrorCode=-2147467259
  Message="IErrorInfo.GetDescription failed with E_FAIL(0x80004005)."
  Source="System.Data"
  StackTrace:
       at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)    at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)    at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)    at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)    at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)    at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)    at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)    at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)    at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)    at WindowsApplication1.Form1.Form1_Load(Object sender, EventArgs e) in C:\Documents and Settings\sa001\My Documents\Visual Studio 2008\Projects\SMRTPG\SMRTPG\Form1.vb:line 20    at System.EventHandler.Invoke(Object sender, EventArgs e)    at System.Windows.Forms.Form.OnLoad(EventArgs e)    at System.Windows.Forms.Form.OnCreateControl()    at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)    at System.Windows.Forms.Control.CreateControl()    at System.Windows.Forms.Control.WmShowWindow(Message& m)    at System.Windows.Forms.Control.WndProc(Message& m)    at System.Windows.Forms.ScrollableControl.WndProc(Message& m)    at System.Windows.Forms.ContainerControl.WndProc(Message& m)    at System.Windows.Forms.Form.WmShowWindow(Message& m)    at System.Windows.Forms.Form.WndProc(Message& m)    at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)    at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)    at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)    at System.Windows.Forms.SafeNativeMethods.ShowWindow(HandleRef hWnd, Int32 nCmdShow)    at System.Windows.Forms.Control.SetVisibleCore(Boolean value)    at System.Windows.Forms.Form.SetVisibleCore(Boolean value)    at System.Windows.Forms.Control.set_Visible(Boolean value)    at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)    at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)    at System.Windows.Forms.Application.Run(ApplicationContext context)    at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()    at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()    at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)    at WindowsApplication1.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81    at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)    at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)    at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()    at System.Threading.ThreadHelper.ThreadStart_Context(Object state)    at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)    at System.Threading.ThreadHelper.ThreadStart()
  InnerException:

Imports System.Data
 
Public Class Form1
    Dim inc As Integer
    Dim MaxRows As Integer
 
    Dim con As New OleDb.OleDbConnection
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim sql As String
 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = X:\return.mdb"
        con.Open()
 
        sql = "SELECT * FROM Open"
        da = New OleDb.OleDbDataAdapter(sql, con)
 
        da.Fill(ds, "open")
 
        con.Close()
 
        MaxRows = ds.Tables("return").Rows.Count
        inc = -1
    End Sub
 
    Private Sub NavigateRecords()
        fname.Text = ds.Tables("Open").Rows(inc).Item(1)
        lname.Text = ds.Tables("Open").Rows(inc).Item(2)
        act.Text = ds.Tables("Open").Rows(inc).Item(3)
        sm.Text = ds.Tables("Open").Rows(inc).Item(4)
        rsm.Text = ds.Tables("Open").Rows(inc).Item(5)
        inv.Text = ds.Tables("Open").Rows(inc).Item(6)
        rinv.Text = ds.Tables("Open").Rows(inc).Item(7)
        doi.Text = ds.Tables("Open").Rows(inc).Item(8)
        dor.Text = ds.Tables("Open").Rows(inc).Item(9)
        wpn.Text = ds.Tables("Open").Rows(inc).Item(10)
        pno1.Text = ds.Tables("Open").Rows(inc).Item(11)
        pno2.Text = ds.Tables("Open").Rows(inc).Item(12)
        pno3.Text = ds.Tables("Open").Rows(inc).Item(13)
        pno4.Text = ds.Tables("Open").Rows(inc).Item(14)
        pnr1.Text = ds.Tables("Open").Rows(inc).Item(15)
        pnr2.Text = ds.Tables("Open").Rows(inc).Item(16)
        pnr3.Text = ds.Tables("Open").Rows(inc).Item(17)
        pnr4.Text = ds.Tables("Open").Rows(inc).Item(18)
        dmg.Text = ds.Tables("Open").Rows(inc).Item(19)
        orr.Text = ds.Tables("Open").Rows(inc).Item(22)
        ups.Text = ds.Tables("Open").Rows(inc).Item(23)
        rt.Text = ds.Tables("Open").Rows(inc).Item(24)
        rtsi.Text = ds.Tables("Open").Rows(inc).Item(25)
        rma.Text = ds.Tables("Open").Rows(inc).Item(26)
        sci.Text = ds.Tables("Open").Rows(inc).Item(27)
        cbc.Text = ds.Tables("Open").Rows(inc).Item(28)
        rs.Text = ds.Tables("Open").Rows(inc).Item(30)
        issue.Text = ds.Tables("Open").Rows(inc).Item(31)
        opnclose.Text = ds.Tables("Open").Rows(inc).Item(32)
    End Sub
 
    Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
        If inc <> MaxRows - 1 Then
            inc = inc + 1
            NavigateRecords()
        Else
            MsgBox("Last Return")
        End If
    End Sub
 
    Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
        If inc > 0 Then
            inc = inc - 1
            NavigateRecords()
        Else
            MsgBox("First Return")
        End If
    End Sub
 
    Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
        If inc <> 0 Then
            inc = 0
            NavigateRecords()
        End If
    End Sub
 
    Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
        If inc <> MaxRows - 1 Then
            inc = MaxRows - 1
            NavigateRecords()
        End If
    End Sub
 
    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        ds.Tables("Open").Rows(inc).Item(1) = fname.Text
        ds.Tables("Open").Rows(inc).Item(2) = lname.Text
        ds.Tables("Open").Rows(inc).Item(3) = act.Text
        ds.Tables("Open").Rows(inc).Item(4) = sm.Text
        ds.Tables("Open").Rows(inc).Item(5) = rsm.Text
        ds.Tables("Open").Rows(inc).Item(6) = inv.Text
        ds.Tables("Open").Rows(inc).Item(7) = rinv.Text
        ds.Tables("Open").Rows(inc).Item(8) = doi.Text
        ds.Tables("Open").Rows(inc).Item(9) = dor.Text
        ds.Tables("Open").Rows(inc).Item(10) = wpn.Text
        ds.Tables("Open").Rows(inc).Item(11) = pno1.Text
        ds.Tables("Open").Rows(inc).Item(12) = pno2.Text
        ds.Tables("Open").Rows(inc).Item(13) = pno3.Text
        ds.Tables("Open").Rows(inc).Item(14) = pno4.Text
        ds.Tables("Open").Rows(inc).Item(15) = pnr1.Text
        ds.Tables("Open").Rows(inc).Item(16) = pnr2.Text
        ds.Tables("Open").Rows(inc).Item(17) = pnr3.Text
        ds.Tables("Open").Rows(inc).Item(18) = pnr4.Text
        ds.Tables("Open").Rows(inc).Item(19) = dmg.Text
        ds.Tables("Open").Rows(inc).Item(22) = orr.Text
        ds.Tables("Open").Rows(inc).Item(23) = ups.Text
        ds.Tables("Open").Rows(inc).Item(24) = rt.Text
        ds.Tables("Open").Rows(inc).Item(25) = rtsi.Text
        ds.Tables("Open").Rows(inc).Item(26) = rma.Text
        ds.Tables("Open").Rows(inc).Item(27) = sci.Text
        ds.Tables("Open").Rows(inc).Item(28) = cbc.Text
        ds.Tables("Open").Rows(inc).Item(30) = rs.Text
        ds.Tables("Open").Rows(inc).Item(31) = issue.Text
        ds.Tables("Open").Rows(inc).Item(32) = opnclose.Text
        da.Update(ds, "Open")
        MsgBox("Return Updated")
    End Sub
End Class

Select allOpen in new window

It worked, be it i had to do a little more troubleshooting.  got every thing i think but if i post back on this u know i didnt haha.  Thanks for the help.

Yeah posting agina, didnt fix the issue, ive got another thread open, figures, every thign runs smoothly untill i try to add or update, then it dosent work, here is the link to the other thread:

https://www.experts-exchange.com/questions/23472733/Updating-Adding-rows-in-an-access-databse-with-a-vb-net-written-program.html