In this tutorial we will learn how to connect with Microsoft Access database and insert records into a table from ASP.NET web form application.

Create Table in Microsoft Access

First create a table in database. Here we have created a table with name UserRegistration having following structure.

userRegsistration table structure

Design the ASP.NET webform

Open a new ASP.NET Visualbasic application and create a new Web Form as described in this tutorial (https://csveda.com/mini-projects/create-first-asp-net-application/).

Now place the WebControls from toolbox to design the UI of your web form. You must open the design view of web form (Click on the “Design” Tab below the form)

Place the labels and other controls on the webform as visible in the form design image. The names used in the design are also displayed in this image

form design

Create a list of professions in the dropdown list. Click on the small arrow on the dropdownlist control and click “Edit Items”. You will get the ListItem Collection Editor. Enter the values you wish to add in the Programmer Properties panel one by one and click “Add” button. When you finish adding the items click “OK”.

dropdoenlist1 add items to dropdownlist

Add code to the Button to insert records into a table from ASP.NET web form

Once you finish the design of the web form you have to type the code that will save data into the table. All you need to do is

  1. Establish connection with database using OLEDB connection object. Set the connection string property and opening the connection.
  2. Create a command object and attach the insert query with the command object. This is done by concatenating the values filled in the form web controls.
  3. Execute the query defined with the command object by calling the ExecuteNonQuery

You will add this code in the Click Event of the btnSave. So double click the btnSave button in the design view to open btnSave_Click event. The complete code of the aspx.vb file is specified below. Declare the conn and cmd objects in your web form code file and write the click event code.

 
 
Public Class UserRegistration
    Inherits System.Web.UI.Page
    Dim conn As New OleDb.OleDbConnection
    Dim cmd As New OleDb.OleDbCommand
    Protected Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
        Dim strGender As String
        conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=F:\DotNet\userdb.accdb"
        conn.Open()
        ' Define the value of gender field depending on the radiobutton checked by the user
        If rdoMale.Checked = True Then
            strGender = rdoMale.Text
        End If
        If rdoFemale.Checked = True Then
            strGender = rdoFemale.Text
        End If
        If rdoTransgender.Checked = True Then
            strGender = rdoTransgender.Text
        End If
        'create the insert query
        cmd.CommandText = "insert into UserRegistration Values ('" _
            & txtUName.Text & "','" _
            & strGender & "','" _
            & ddlProfession.Text & "','" _
            & txtEmail.Text & "','" _
            & txtAddress.Text & "',#" _
            & calDOB.SelectedDate & "#)"
        cmd.Connection = conn
        cmd.ExecuteNonQuery()
        MsgBox("One record is saved in the table ")
    End Sub
End Class

Now you are ready to execute the project by clicking “Google Chrome” on the tool bar. You will be able to see the webform in browser. Enter the data and click the “Save” Button. A message will be displayed to inform that data is saved in the table. Open database and check the table. A record will be inserted at the end of the table.