Cannot Connect to Database

[theboyandtheocean]

Hey There,

I'm a beginner when it comes to VB.net. I can do all the basic stuff, but I'm having issues with connecting to a database I've created. Ultimately, I need to feed data from a few text boxes to one table within the database, but I can't even get the connection open.

For Public Class Log, I have the following:

Public Class Log

    Dim con As New OleDb.OleDbConnection
    Dim conStr As String

I have the following code connected to a button click.

 Try

            con = New OleDb.OleDbConnection
            con.ConnectionString = "Provider = Microsoft.Jet.OleDB.4.0;" +
                "Data Source =c:\Users\God\QualityLog.accdb"

            con.Open()
            MsgBox("Connection Open")

            con.Close()
            MsgBox("Connection Closed")


        Catch ex As Exception
            MsgBox(ex.Message)

        End Try

From this, I get unreconized database format from this.

Right now, I just need to make sure im correctly opening the connection and then I'll worry about exporting the data from text boxes to the table in access. Any help is greatly appreciated!

[kimlong.khov]

If you use miscrosoft access 2000 or 2003 you code below:

con = New OleDb.OleDbConnection
con.ConnectionString = "Provider = Microsoft.Jet.OleDB.4.0;" +
"Data Source =c:\Users\God\QualityLog.accdb"

but if you use microsoft access 2007 or 2010 you should change provider to:

con = new OleDb.OleDbConnection
con.ConnectionString = "Provider = Microsoft.Jet.ACE.12.0;" + "Data Source = C:\Users\God\QualityLog.accdb"

Please feel free if it can help you.

[thewilf]

+1 to kimlong.khov's post, I had same issue and it sorted it out for me.
All you need to do then to input into it is create a command and input SQL into that along with connection.
E.g.
Dim SQL as String
SQL = "INSERT INTO table1 ('" & tbText1.text & "', '" & tbText2.text & "')"
Cmd = New OleDb.OleDbCommand(SQL, con)

Hope this helps
Will

[theboyandtheocean]

I have Windows 8, MS Access 2010, and Visual Studio 2010.

After changing the code, I now get provider "Microsoft.Jet.ACE.12.0" is not registered to the local machine.

could it be because im 64 bit windows 8?

[theboyandtheocean]

Ok I got the connection to the database to work.

I had to use Microsoft.Ace.Oledb.12.0 for it to work.

My problem is now I cannot get it to write into my database. I have the following code:

con = New OleDb.OleDbConnection

            con.ConnectionString = "PROVIDER = Microsoft.ACE.OLEDB.12.0; Data Source = C:\Users\God\QualityLog.accdb"
            con.Open()

            Dim SQL As String
            SQL = "INSERT INTO Order ('" & txtDesign.Text & "', '" & txtColor.Text & "', '" & d & "'," +
            "'" & txtQuality.Text & "', '" & txtTech.Text & "', '" &   txtInspector.Text & "', '" & runKey & "')"
            cmd = New OleDb.OleDbCommand(SQL, con)


            con.Close()
            MsgBox("Connection Closed")

Do i have to execute something else for it to save?

[theboyandtheocean]

bw, thanks for the awesome help so far!

[thewilf]

What error are you getting? Syntax error etc...?

Also I have put your code onto one line and made minor alterations as I find it easier to see the code on one line when dealing with so many ' and ". Also sorry, my fault I forgot to put the VALUES part of the statement in.

SQL = "INSERT INTO Order VALUES ('" & txtDesign.Text & "', '" & txtColor.Text & "', '" & d & "', '" & txtQuality.Text & "', '" & txtTech.Text & "', '" & txtInspector.Text & "', '" & runKey & "')"

Another common error is the type of fields you are passing data to via the SQL. are d and runKey declared as the same type as the field in the database to which you are passing, e.g. string to text, int to number etc...

Also the number of fields the database should match the number of pieces passed in the above statement. In not you need to specify which you are inserting data into. Link is for the W3C which has alot of handy stuff in reference SQL and syntax etc...

Click Here

[G_Waddell]

Did you execute the command? e.g.

cmd.ExecuteNonQuery()

[thewilf]

+1 to above. Need to execute. Good spot, I'm new to this helping lark, lol.

Cheers
Will

[theboyandtheocean]

THanks! I got it, I had to add [ ] around Order for it to save to that table. I have it appending now!

Also, I'm wondering if it's possible to append all the data contained in a DataGridView to my other table in the access database? I could have it do the same thing as the other button, but that doesn't allow the user to re-edit their entries if something is wrong. Basically, the first button we worked on saves the production run information for a run. The next data is the run log (defects, A grade material, trash) for the entire production run. I will then connect the two tables by the run key to display the log.

If this doesn't make sense, I can re-explain.

[thewilf]

Its up to you which one you prefer.

I have programmed a log for something similiar before and as was for small screen didnt want to have the full datagridview so I used an SQL update statement to edit incorrect records. Depends which one is more suitable for you?

Cheers
Will