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.
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
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”.
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
- Establish connection with database using OLEDB connection object. Set the connection string property and opening the connection.
- 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.
- 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.