How to Connect Access Database with Visual Basic 2008 Project

[singhSP]

1. Form created in VB2008

3 Textbox [Name(string), Company(string), Contact(number)]
3 Buttons [New Record, Update Record, Delete Record]

2. Database created in MS Access 2003

File Name: Contact.mbd

Fields:
ID [Primary key]
Name [Text]
Company [Text]
Contact [Number]

How to Create Connection?
How to get data on form load from database in textboxes?
How to insert, update, delete data?
Please provide information as I am not able to get any simplest way to understand these questions.

[Oxiegen]

This is a very simple example of how to fetch, insert, update and delete data from a MS Access database.
By default you can also see how to connect to it.

However, consider that a contact database will eventually contain several records, you should also be able to use this and figure out a way to browse between them.

Imports System
Imports System.Data.OleDb

Public Class Form1
    Private connString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Application.StartupPath & "\Contact.mbd; User Id=admin; Password=;"
    Private intID As Integer

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

    Private Sub cmdNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdNew.Click
        InsertData()
    End Sub

    Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdate.Click
        UpdateData()
    End Sub

    Private Sub cmdDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDelete.Click
        DeleteData()
    End Sub

    Private Sub FetchData()
        Dim con As New OleDbConnection(connString)
        Dim com As OleDbCommand = Nothing
        Dim reader As OleDbDataReader = Nothing

        Try
            con.Open()
            com = New OleDbCommand("SELECT * FROM contact", con)
            reader = com.ExecuteReader(CommandBehavior.CloseConnection)
            If reader.HasRows Then
                intID = reader("ID")
                If Not IsDBNull(reader("Name")) Then txtName.Text = reader("Name")
                If Not IsDBNull(reader("Company")) Then txtName.Text = reader("Company")
                If Not IsDBNull(reader("Contact")) Then txtName.Text = reader("Contact")
            End If
            reader.Close()
        Catch ex As Exception
            If con.State = ConnectionState.Open Then
                con.Close()
            End If
        End Try
    End Sub

    Private Sub InsertData()
        Dim con As New OleDbConnection(connString)
        Dim com As OleDbCommand = Nothing

        Try
            con.Open()
            com = New OleDbCommand("INSERT INTO contact (Name, Company, Contact) VALUES ('" & txtName.Text & "','" & txtCompany.Text & "'," & CInt(txtContact.Text) & ")", con)
            com.ExecuteNonQuery()
            con.Close()
        Catch ex As Exception
            If con.State = ConnectionState.Open Then
                con.Close()
            End If
        End Try
    End Sub

    Private Sub UpdateData()
        Dim con As New OleDbConnection(connString)
        Dim com As OleDbCommand = Nothing

        Try
            con.Open()
            com = New OleDbCommand("UPDATE contact SET Name = '" & txtName.Text & "', Company = '" & txtCompany.Text & "', Contact = " & CInt(txtContact.Text) & " WHERE ID = " & intID, con)
            com.ExecuteNonQuery()
            con.Close()
        Catch ex As Exception
            If con.State = ConnectionState.Open Then
                con.Close()
            End If
        End Try
    End Sub

    Private Sub DeleteData()
        Dim con As New OleDbConnection(connString)
        Dim com As OleDbCommand = Nothing

        Try
            con.Open()
            com = New OleDbCommand("DELETE FROM contact WHERE ID = " & intID, con)
            com.ExecuteNonQuery()
            con.Close()
        Catch ex As Exception
            If con.State = ConnectionState.Open Then
                con.Close()
            End If
        End Try
    End Sub
End Class

[singhSP]

Using the Code

Private Sub FetchData()
        Dim con As New OleDbConnection(connString)
        Dim com As OleDbCommand = Nothing
        Dim reader As OleDbDataReader = Nothing

        Try
            con.Open()
            com = New OleDbCommand("SELECT * FROM contact where ID = 1", con)
            reader = com.ExecuteReader(CommandBehavior.CloseConnection)
            If reader.HasRows Then
                intID = reader("ID")
                If Not IsDBNull(reader("Name")) Then txtName.Text = reader("Name")
                If Not IsDBNull(reader("Company")) Then txtCompany.Text = reader("Company")
                If Not IsDBNull(reader("Contact")) Then txtContact.Text = reader("Contact")
            End If
            reader.Close()
        Catch ex As Exception
            If con.State = ConnectionState.Open Then
                con.Close()
            End If
        End Try
    End Sub

It jumps from line 7 to line 17

I might not be getting proper understanding of Connections.

[Oxiegen]

So. What exactly does the exception say?

If the connectionstring is at fault, then the most probable culprit is the path to your database.
I inserted Application.StartupPath (the path from where your programs EXE file is executed), because I figured that's where your MDB file is located.

[singhSP]

While running the program no error is coming, even connString showing same database location.
Do not know where the problem is coming.

Attaching my Project.
Please have a look.

[Oxiegen]

Take another look at the connectionstring and pay close attention to the filename itself.
In a hurry I mistakenly switched some letters around in the extension.
So change "Contact.mbd" to "Contact.mdb".

[singhSP]

After renaming the file extension from ".mbd" to ".mdb"...
NEW
UPDATE
DELETE

worked, but SELECT was not working. So I google once again for some references.

After some reading I made this specific change.

Changed:

If reader.HasRows Then

To

If reader.Read = True Then

After that getting data from table in textbox, now working on various way to make database work on runtime.

Hope I am getting things right, what changed is not wrong.

Regards

[singhSP]

Thank you for the Startup Help !

[9743992654]

<p> can anyone explain me how connect vb express edtion 2010 to oracle database or ms-access with coding please </p>
Regards from
Syed

[Santanu.Das]

You can get here all references of ConnectionStrings of every RDBMS