Solved

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

Posted on 2008-06-09
22
648 Views
Last Modified: 2013-12-13
I am trying to link my small program in vb to an access database, its for customer information on a return program.  I have created the access database and tryied a few diffrent ways to do this but have failed horribly.  
0
Comment
Question by:DerianWeidman
  • 14
  • 8
22 Comments
 
LVL 5

Expert Comment

by:thenrich
ID: 21746320
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?
0
 

Author Comment

by:DerianWeidman
ID: 21746419
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.
0
 
LVL 5

Expert Comment

by:thenrich
ID: 21746438
Again - what do you need?

Why are you not using ado? What is failing?
0
 

Author Comment

by:DerianWeidman
ID: 21746458
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?
0
 

Author Comment

by:DerianWeidman
ID: 21746469
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
0
 
LVL 5

Expert Comment

by:thenrich
ID: 21746499
What was the error?

I do not recognize that commandtext structure.
0
 

Author Comment

by:DerianWeidman
ID: 21746516
Ill run it agian and give you all of the information
0
 

Author Comment

by:DerianWeidman
ID: 21746567
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.
0
 

Author Comment

by:DerianWeidman
ID: 21746588
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.
0
 
LVL 5

Expert Comment

by:thenrich
ID: 21746616
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.
0
 

Author Comment

by:DerianWeidman
ID: 21746633
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.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:DerianWeidman
ID: 21747595
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

Open in new window

0
 
LVL 5

Expert Comment

by:thenrich
ID: 21750293
Is 'Open' the name of your table?
0
 

Author Comment

by:DerianWeidman
ID: 21751008
Yes Open is the name of the table,  the actual database name is return
0
 
LVL 5

Expert Comment

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

0
 

Author Comment

by:DerianWeidman
ID: 21751130
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

0
 

Author Comment

by:DerianWeidman
ID: 21751155
the problem is with this string.  But it worked before, in fact it worked right along with the tutorial.
da.Fill(ds, "return")

Open in new window

0
 
LVL 5

Expert Comment

by:thenrich
ID: 21751223
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")

0
 

Author Comment

by:DerianWeidman
ID: 21751255
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

Open in new window

0
 
LVL 5

Accepted Solution

by:
thenrich earned 250 total points
ID: 21751298
The only other thing I can think of is that terms like 'open' and 'return' might be reserved words in sql. The query execution might be having issues resolving your query string. I'd change the name of both yoiur database and table.
0
 

Author Comment

by:DerianWeidman
ID: 21751633
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.
0
 

Author Comment

by:DerianWeidman
ID: 21752606
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:

http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_23472733.html
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article discusses how to create an extensible mechanism for linked drop downs.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

708 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

19 Experts available now in Live!

Get 1:1 Help Now