Out tutorial focuses on how to insert ASP.NET Form values into a Microsoft Access database. It is worth noting the difference between our ASP form to database example where the form posts to a different page and our ASP.NET example below where our form posts to the same page.
Our Microsoft Access database is called 'Customers' and has a table called 'tblCustomers'. The table has a primary key autonumber field 'ID', and two textfields 'FirstName' and 'LastName'. The physical location of our database is 'C:\Inetpub\wwwroot\net\form-to-database\customers.mdb'.
Our page 'default.aspx' in figure 1 below displays 2 textbox server controls for the user to enter their firstname and lastname and a button 'Button1'.
Figure 1
As we'll be inserting our form details into an Access Database, the directive <%@ Import Namespace="System.Data.Oledb" %> allows us to import the classes that we need in order to do this.
When the page loads for the first time and the page property 'IsPostBack' is False, our label 'Label1' will not be shown.
If either textbox control is empty when the button is clicked the validation controls, in this case 'RequiredFieldValidator1' and 'RequiredFieldValidator2' will stop the form from processing and display their error message. You can read more on validation controls.
When the button 'button1' is clicked the click event of the button is raised. A postback occurs and the Button1_Click method inside the script block is executed. The Button1_Click method inserts the form details into the database.
This subroutine creates a connection, passes in our connection string with the physical file path to the database, creates a command object, then passes in name parameters and values. You can read more on inserting records inserting records. In this current example we use parameterized queries rather than a SQL Query like we did in our classic ASP example. Our parameter names are @FirstName and @LastName and the associated values come from the textboxes TextBox1.Text and TextBox2.Text.
We also use the Command object's ExecuteNonQuery method as our SQL Insert statement is a straight insert and we don't need to worry about retrieving any records.
Our label 'label1' will now be shown and display the message 'Record inserted'. We also clear the textboxes of the last entered details for a new entry.
'Default.aspx'
<%@ Import Namespace="System.Data.Oledb" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
Protected Sub Page_Load()
If Page.IsPostBack = False Then
Label1.Visible = False
End If
End Sub
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim Connection As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;" & _
"Data Source=C:\Inetpub\wwwroot\net\form-to-database\customers.mdb")
Connection.Open()
Dim Command As New OleDbCommand("INSERT INTO tblCustomers(FirstName," & _
"LastName)VALUES(@FirstName,@LastName)", Connection)
Command.Parameters.Add(New OleDbParameter("@FirstName", TextBox1.Text))
Command.Parameters.Add(New OleDbParameter("@LastName", TextBox2.Text))
Command.ExecuteNonQuery()
Connection.Close()
Label1.Text = "Record inserted."
Label1.Visible = True
'Clear the text boxes after a new insert
TextBox1.Text = ""
TextBox2.Text = ""
End Sub
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id=Head1 runat="server">
<title>ASP.NET Form to Database Sample</title>
</head>
<body>
<div>
<h1>ASP.NET Form to Database</h1>
<form id="form1" runat="server">
First name:<asp:TextBox ID="TextBox1" runat="server" Text=""></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" ControlToValidate="TextBox1" runat="server" ErrorMessage="Please enter a first name"></asp:RequiredFieldValidator><br /><br />
Last name:<asp:TextBox ID="TextBox2" runat="server" Text=""></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" ControlToValidate="TextBox2" runat="server" ErrorMessage="Please enter a last name"></asp:RequiredFieldValidator><br /><br />
<asp:Button ID=Button1 runat="server" Text="Insert Record" OnClick="Button1_Click" />
<br />
<asp:Label ID=Label1 runat="server" Text=""></asp:Label>
</form>
</div>
</body>
</html>
Plug and play ASP membership script that integrates with PayPal to let you charge recurring membership fees.
Get your best asp web hosting provider now and save 25%
