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.

Recommended Answers

All 9 Replies

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

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.

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.

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.

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".

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

Thank you for the Startup Help !

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.