Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

How to insert records into and display records from MS Access database in ASP.NET

Author: Mike Green
Download Source Code : 1374_InsertAndDisplayMSAccess.zip

I will explain in this article that how we can insert records into MS Access database and how we can retrieve and display MS Access database records.

 

 

Inserting records into MS Access database in ASP.NET is same as inserting records to SQL database but we need to use “System.Data.Oledb” namespace instead of “System.Data.SqlClient”. “System.Data.Oledb” namespace includes OledbConnection class to connect to MS Access database and OledbCommand class to represent statement to insert data into or to retrieve data from MS Access database. We can write query statement in the same syntax for MS Access database as we use for SQL Server database.
 
 
  1. Open MS Visual Studio 2010
  2. File > New > Website > Visual C# or Visual Basic > ASP.NET Empty Web Site
  3. Select Web Location as File System and Click OK
  4. From Menu, Website > Add New Item > Select Web Form and Click Add.
  5. Write below connection string in web.config file

    <connectionStrings>
           <add name="CustomersDatabase" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\Customers.accdb"/>
    </connectionStrings>
     
    We need to write "provider" and set data source for MS Access database.
     
  6. Write code below in default.aspx page

    <table>
        <tr>
            <td>CustomerID</td>
            <td>
                <asp:TextBox ID="txtID" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>CompanyName</td>
            <td>
                <asp:TextBox ID="txtCompany" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>ContactName</td>
            <td>
                <asp:TextBox ID="txtContact" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
           <td>City</td>
           <td>
               <asp:TextBox ID="txtCity" runat="server"></asp:TextBox>
           </td>
        </tr>
        <tr>
           <td>Country</td>
           <td>
               <asp:TextBox ID="txtCountry" runat="server"></asp:TextBox>
           </td>
           </tr>
    </table>
    <br />
    <asp:Button ID="btnInsert" runat="server" Text="Insert"
        onclick="btnInsert_Click" />
    <asp:Button ID="btnDisplay" runat="server" Text="Display"
        onclick="btnDisplay_Click" />
    <br />
    <asp:Label ID="lblMessage" runat="server"></asp:Label>
    <br />
    <asp:GridView ID="gvCustomers" runat="server">
    </asp:GridView>
     
  7. Include following namespaces in your code file

    C#
     
    using System.Configuration;
    using System.Data.OleDb;
    using System.Data;
     
    VB.NET

    Imports System.Configuration
    Imports System.Data.OleDb
    Imports System.Data
     
  8. Write below code in Insert Button click event to insert record into MS Access Database

    C#

    protected void btnInsert_Click(object sender, EventArgs e)
    {
        string CustID = txtID.Text;
        string CompName = txtCompany.Text;
        string contact = txtContact.Text;
        string city = txtCity.Text;
        string country = txtCountry.Text;
     
        string connString = ConfigurationManager.ConnectionStrings["CustomersDatabase"].ConnectionString;
        OleDbConnection connection = new OleDbConnection(connString);
        string insertQuery = "INSERT INTO Customers(CustomerID, CompanyName, ContactName, City, Country) VALUES(@CustomerID, @CompanyName, @ContactName, @City, @Country)";
        OleDbCommand command = new OleDbCommand();
        command.Connection = connection;
        command.CommandText = insertQuery;
        command.CommandType = CommandType.Text;
        command.Parameters.AddWithValue("@CustomerID", CustID);
        command.Parameters.AddWithValue("@CompanyName", CompName);
        command.Parameters.AddWithValue("@ContactName", contact);
        command.Parameters.AddWithValue("@City", city);
        command.Parameters.AddWithValue("@Country", country);
     
        try
        {
            connection.Open();
            command.ExecuteNonQuery();
            lblMessage.Text = "Record inserted successfully";
        }
        catch (Exception ex)
        {
            lblMessage.Text = "Unable to insert record";
        }
        finally
        {
            connection.Close();
        }
    }
     
    VB.NET
     
    Protected Sub btnInsert_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnInsert.Click
        Dim CustID As String = txtID.Text
        Dim CompName As String = txtCompany.Text
        Dim contact As String = txtContact.Text
        Dim city As String = txtCity.Text
        Dim country As String = txtCountry.Text
     
        Dim connString As String = ConfigurationManager.ConnectionStrings("CustomersDatabase").ConnectionString
        Dim connection As New OleDbConnection(connString)
        Dim insertQuery As String = "INSERT INTO Customers(CustomerID, CompanyName, ContactName, City, Country) VALUES(@CustomerID, @CompanyName, @ContactName, @City, @Country)"
        Dim command As New OleDbCommand()
        command.Connection = connection
        command.CommandText = insertQuery
        command.CommandType = CommandType.Text
        command.Parameters.AddWithValue("@CustomerID", CustID)
        command.Parameters.AddWithValue("@CompanyName", CompName)
        command.Parameters.AddWithValue("@ContactName", contact)
        command.Parameters.AddWithValue("@City", city)
        command.Parameters.AddWithValue("@Country", country)
     
        Try
            connection.Open()
            command.ExecuteNonQuery()
            lblMessage.Text = "Record inserted successfully"
        Catch ex As Exception
            lblMessage.Text = "Unable to insert record"
        Finally
            connection.Close()
        End Try
    End Sub
     
    First we need to get connection string from web.config file and create OledbConnection object. Then we have written a query here to insert record to MS Access database. We have created OledbCommand object and then we have provided connection, query text and all parameters to command object. We should have an open connection to MS Access database to call ExecuteNonQuery() method and insert data into MS Access database.
     
  9. Write below code in Display Button click event to display data from MS Access database

    C#
     
    protected void btnDisplay_Click(object sender, EventArgs e)
    {
        string connString = ConfigurationManager.ConnectionStrings["CustomersDatabase"].ConnectionString;
        OleDbConnection connection = new OleDbConnection(connString);
        string selectQuery = "SELECT * FROM Customers";
        OleDbCommand command = new OleDbCommand();
        command.Connection = connection;
        command.CommandText = selectQuery;
        command.CommandType = CommandType.Text;
     
        try
        {
            connection.Open();
            OleDbDataAdapter adapter = new OleDbDataAdapter(command);
            DataTable table = new DataTable();
            adapter.Fill(table);
            gvCustomers.DataSource = table;
            gvCustomers.DataBind();
        }
        catch (Exception ex)
        {
            lblMessage.Text = "Unable to get records";
        }
        finally
        {
            connection.Close();
        }
    }
     
    Vb.NET

    Protected Sub btnDisplay_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnDisplay.Click
        Dim connString As String = ConfigurationManager.ConnectionStrings("CustomersDatabase").ConnectionString
        Dim connection As New OleDbConnection(connString)
        Dim selectQuery As String = "SELECT * FROM Customers"
        Dim command As New OleDbCommand()
        command.Connection = connection
        command.CommandText = selectQuery
        command.CommandType = CommandType.Text
     
        Try
            connection.Open()
            Dim adapter As New OleDbDataAdapter(command)
            Dim table As New DataTable()
            adapter.Fill(table)
            gvCustomers.DataSource = table
            gvCustomers.DataBind()
        Catch ex As Exception
            lblMessage.Text = "Unable to get records"
        Finally
            connection.Close()
        End Try
    End Sub
     
    Same pattern is repeated here to establish connection to MS access database but the query statement is changed this time. We need to get data from database so we have written a select statement. OledbCommand object is created and connection and query text is provided in same manner. We have created OledbDataAdapter object to retrieve data from MS Access database and filled DataTable object to display data on a GridVeiw control.
     
  10. Now you can browse the website and insert record into MS Access database and can display records. 

 

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< How to use AJAX MaskedEditExtender in ASP.NET

Disclaimer - Privacy
© 2002-2017 DevASP.net