connecting to ms access 2007database using visual basic 2008 express edition

[sphe_g]

Hi all
I need help with connecting to and manipulating a microsoft access 2007 database using code in visual basic 2008 express edition. It's for a project I'm doing for University that's due on the 12th of October:S. Please help me out in whatever way you can - links to informative Web pages, code snippets with explanations, etc.

Thanks!:icon_mrgreen:

[sphe_g]

Oh ya!
This is the code I have in the beginning of my program. It gives me an error at the moment - InvalidOperationException was unhandled, Conversion from type 'OleDbDataAdapter' to type 'String' is not valid.

Imports System.Data.OleDb

Public Class Form1
    Dim connStr As New OleDbConnection
    Dim selectadapt = New OleDbDataAdapter
    Dim dt As New DataTable()
    Dim dataAdapter As New OleDb.OleDbDataAdapter(selectadapt, connStr)
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        selectadapt = New OleDbDataAdapter("SELECT * FROM StudentDetails", connStr)
        connStr.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=DUTStudents.accdb"
        connStr.Open()
        dataAdapter.Fill(dt)
        dataAdapter.Dispose()


    End Sub

I'm still gonna take a crack at it and try to solve the error.

[kvprajapati]

Welcome sphe_g.

I appreciate your eagerness to learn Database programming. I suggest you to buy a good book and also visit MSDN online pages.

[JJCollins]

Here is where you're encountering the error

Dim selectadapt = New OleDbDataAdapter
Dim dataAdapter As New OleDb.OleDbDataAdapter(selectadapt, connStr) ' Here is the problem

You've defined selectadapt as a new data adapter.

You're also defining dataAdapter as a new data adapter, the parameters which need to be given for the adapter OleDb.OleDbDataAdapter(selectadapt, connStr) are firstly the SQL command to query the database and then the connection string. It should basically look something like this

Dim selectadapt as string ' Define selectadapt as a string
selectadapt = "SELECT * FROM StudentDetails" ' Set the string to a SQL select query
Dim dataAdapter As New OleDb.OleDbDataAdapter(selectadapt, connStr) ' Defines dataAdapter as a new OleDB Data adapter, uses selectadapt as the SQL query string to show data and connStr as the connection string

Your current code is telling the data adapter to try and query the database with another data adapter, which logically doesn't make any sense. (Hope that's a clear enough explaination, if not reply and i'll try and clarify)

Something along these lines should get you going :)

Dim con As OleDbConnection ' New DB Connection

Dim sql As String ' SQL Command String

str = “Provider=Microsoft.Jet.ACE.oledb.12.0;Data Source=DUTStudents.accdb" ' Connection String information

con = New OleDbConnection(str) ' New DB Connection using str as connection string       

sql = “SELECT * FROM StudentDetails" ' SQL Query to select all records from StudentDetails table/view

Dim cmd As OleDbCommand ' New DB Command   

Try  

   con.Open() ' Opens connection to the database.

   cmd = New OleDbCommand(sql, con)  ' The command to be executed on the database, the parameters are following in brackets, it will use the SQL query provided by the sql string variable and defines the connection to use as the con variable.

   cmd.ExecuteNonQuery() ' Executes the SQL query against the database

   con.Close() ' Closes the connection.

Let me know if that helps you

- Jordan

[sphe_g]

Here is where you're encountering the error

Dim selectadapt = New OleDbDataAdapter
Dim dataAdapter As New OleDb.OleDbDataAdapter(selectadapt, connStr) ' Here is the problem

You've defined selectadapt as a new data adapter.

You're also defining dataAdapter as a new data adapter, the parameters which need to be given for the adapter OleDb.OleDbDataAdapter(selectadapt, connStr) are firstly the SQL command to query the database and then the connection string. It should basically look something like this

Dim selectadapt as string ' Define selectadapt as a string
selectadapt = "SELECT * FROM StudentDetails" ' Set the string to a SQL select query
Dim dataAdapter As New OleDb.OleDbDataAdapter(selectadapt, connStr) ' Defines dataAdapter as a new OleDB Data adapter, uses selectadapt as the SQL query string to show data and connStr as the connection string

Your current code is telling the data adapter to try and query the database with another data adapter, which logically doesn't make any sense. (Hope that's a clear enough explaination, if not reply and i'll try and clarify)

Something along these lines should get you going :)

Dim con As OleDbConnection ' New DB Connection

Dim sql As String ' SQL Command String

str = “Provider=Microsoft.Jet.ACE.oledb.12.0;Data Source=DUTStudents.accdb" ' Connection String information

con = New OleDbConnection(str) ' New DB Connection using str as connection string       

sql = “SELECT * FROM StudentDetails" ' SQL Query to select all records from StudentDetails table/view

Dim cmd As OleDbCommand ' New DB Command   

Try  

   con.Open() ' Opens connection to the database.

   cmd = New OleDbCommand(sql, con)  ' The command to be executed on the database, the parameters are following in brackets, it will use the SQL query provided by the sql string variable and defines the connection to use as the con variable.

   cmd.ExecuteNonQuery() ' Executes the SQL query against the database

   con.Close() ' Closes the connection.

Let me know if that helps you

- Jordan

Sorry I didn't reply sooner. A few tweaks here and there, and the connection code works! WOOHOO!!! I actually jumped off my chair and screamed, while I was in the library! I would kiss you, but I don't know where you are, plus it might be kinda weird;) I'd like to bug you again, if you don't mind. How would I then manipulate the database by using statements such as SELECT, UPDATE, DELETE etc?

Thanks again for the help! Just had a huge program-finally-works high lol:D

[sphe_g]

Here is where you're encountering the error

Dim selectadapt = New OleDbDataAdapter
Dim dataAdapter As New OleDb.OleDbDataAdapter(selectadapt, connStr) ' Here is the problem

You've defined selectadapt as a new data adapter.

You're also defining dataAdapter as a new data adapter, the parameters which need to be given for the adapter OleDb.OleDbDataAdapter(selectadapt, connStr) are firstly the SQL command to query the database and then the connection string. It should basically look something like this

Dim selectadapt as string ' Define selectadapt as a string
selectadapt = "SELECT * FROM StudentDetails" ' Set the string to a SQL select query
Dim dataAdapter As New OleDb.OleDbDataAdapter(selectadapt, connStr) ' Defines dataAdapter as a new OleDB Data adapter, uses selectadapt as the SQL query string to show data and connStr as the connection string

Your current code is telling the data adapter to try and query the database with another data adapter, which logically doesn't make any sense. (Hope that's a clear enough explaination, if not reply and i'll try and clarify)

Something along these lines should get you going :)

Dim con As OleDbConnection ' New DB Connection

Dim sql As String ' SQL Command String

str = “Provider=Microsoft.Jet.ACE.oledb.12.0;Data Source=DUTStudents.accdb" ' Connection String information

con = New OleDbConnection(str) ' New DB Connection using str as connection string       

sql = “SELECT * FROM StudentDetails" ' SQL Query to select all records from StudentDetails table/view

Dim cmd As OleDbCommand ' New DB Command   

Try  

   con.Open() ' Opens connection to the database.

   cmd = New OleDbCommand(sql, con)  ' The command to be executed on the database, the parameters are following in brackets, it will use the SQL query provided by the sql string variable and defines the connection to use as the con variable.

   cmd.ExecuteNonQuery() ' Executes the SQL query against the database

   con.Close() ' Closes the connection.

Let me know if that helps you

- Jordan

Sorry I didn't reply sooner. A few tweaks here and there, and the connection code works! WOOHOO!!! I actually jumped off my chair and screamed, while I was in the library! I would kiss you, but I don't know where you are, plus it might be kinda weird;) I'd like to bug you again, if you don't mind. How would I then manipulate the database by using statements such as SELECT, UPDATE, DELETE etc?

Thanks again for the help! Just had a huge program-finally-works high lol:D

[sphe_g]

Welcome sphe_g.

I appreciate your eagerness to learn Database programming. I suggest you to buy a good book and also visit MSDN online pages.

Thank you adatapost. There is a prescribed textbook we're using, but it's somewhat vague. It doesn't explain everything clearly. I may look into getting another when my finances allow;)

[JJCollins]

Well you have various queries to achieve various things

i'll give you an example of the most common ones

SELECT

SELECT * FROM tblExample

That selects all the records from the database tblExample. To select a specific field from the database you would do

SELECT ExampleField from tblExample

Where ExampleField would be the field of your choice, e.g. Surname/Name/Address etc

To select multiple fields but NOT all the fields, you just have to specify which fields you want to see. For example:

SELECT Field1,Field2 from tblExample

The Insert is a bit more complicated (syntax-wise)

To Insert a record into a database with 2 fields you would do

INSERT INTO tblExample(Field1, Field2) VALUES (@Field1, @Field2)

The @ symbols are used for a Parameterized query, this is useful for security purposes and for ease of coding, stops your insert query becoming a miniature novel ;)

Hope that helps you a bit, I'd take some time out to search Google for SQL queries personally to help you get to grips with it but also feel free to post here and I'll help wherever I can.

- Jordan

[sphe_g]

Well you have various queries to achieve various things

i'll give you an example of the most common ones

SELECT

SELECT * FROM tblExample

That selects all the records from the database tblExample. To select a specific field from the database you would do

SELECT ExampleField from tblExample

Where ExampleField would be the field of your choice, e.g. Surname/Name/Address etc

To select multiple fields but NOT all the fields, you just have to specify which fields you want to see. For example:

SELECT Field1,Field2 from tblExample

The Insert is a bit more complicated (syntax-wise)

To Insert a record into a database with 2 fields you would do

INSERT INTO tblExample(Field1, Field2) VALUES (@Field1, @Field2)

The @ symbols are used for a Parameterized query, this is useful for security purposes and for ease of coding, stops your insert query becoming a miniature novel ;)

Hope that helps you a bit, I'd take some time out to search Google for SQL queries personally to help you get to grips with it but also feel free to post here and I'll help wherever I can.

- Jordan

I've been reading up on the Web. Regardless of how much I play around with it, this code still gives me a syntax error(OLeDbException was unhandled, syntax error in UPDATE statement)

con.Open()
            sql = "UPDATE StudentDetails SET First Name = '" & txtName.Text & "' WHERE Surname '" & txtSurname.Text & "'" 'this is where the problem is
            cmd = New OleDbCommand(sql, con)
            cmd.ExecuteNonQuery()
            MessageBox.Show("Data is saved", "Information")
            con.Close()

Can you spot what's the matter? I'm still gonna slog away at it and maybe I'll pull through before you reply

Thanks for helping me so much. Maybe you should be my lecturer:D

[JJCollins]

Yeah the problem is you've missed an '=' sign out in your query

It should be like this

"UPDATE StudentDetails SET First Name = '" & txtName.Text & "' WHERE Surname = '" & txtSurname.Text & "'"

Your old query wasn't defining what surname was, it was basically saying,

"Update the Student's Details, Set the First Name to be the value entered only if the Surname"

Obviously in English that doesnt make sense, it should be "only if the Surname is EQUAL to the value of txtSurname.text"

Also, if your column/field name has a space separating the 2 words, you should enclose them in square brackets like [First Name]

That is because SQL wont recognise the field name if it has spaces in it without the square brackets

:)

Hope that helps!

- Jordan

[sphe_g]

Yeah the problem is you've missed an '=' sign out in your query

It should be like this

"UPDATE StudentDetails SET First Name = '" & txtName.Text & "' WHERE Surname = '" & txtSurname.Text & "'"

Your old query wasn't defining what surname was, it was basically saying,

"Update the Student's Details, Set the First Name to be the value entered only if the Surname"

Obviously in English that doesnt make sense, it should be "only if the Surname is EQUAL to the value of txtSurname.text"

Also, if your column/field name has a space separating the 2 words, you should enclose them in square brackets like [First Name]

That is because SQL wont recognise the field name if it has spaces in it without the square brackets

:)

Hope that helps!

- Jordan

Thanks Jordan for all the help you've given me. We've managed to hand in our project with relative success. Thanks again!!

[JJCollins]

Not a problem, glad I could help :)

- Jordan

[lansky]

thanks for this thread, it has been useful to me, however I need to extend it. I need to navigate a query or table recordset througn a VB 2010 EXPRESS FORM after the connection to a database has been successfully established.

Can anyone provide me such code. Thanks in advance!