Expertrating - ASP.Net Tutorial, ADO.NET

Home > Courses, Tutorials & eBooks > ASP.NET Tutorial > ADO.NET

ASP.NET Tutorial - ADO.NET

Page 6 of 14

The output of this example is as shown below:

The example above contains two TextBox controls named tbFirstname and tbLastname. When the values are entered into the two Textbox controls and Submit clicked, the Button Click subroutine is executed.

The Button Click subroutine creates an instance of the SqlCommand class with a parametric Select statement. Two instances of the SqlParameter class are created and added to the Parameters collection of the SqlCommand class. For example, the @firstname parameter is created and added with the following statement:

cmdSelect.Parameters.Add( "@firstname", tbFirstname.Text )

This statement creates a new SqlParameter named @firstname and further adds to the Parameters collection of the SqlCommand class. Passing its name and value creates the parameter. In this case, the value of the parameter is the value of the tbFirstname TextBox control.

Using Parameters with MS Access

When using the classes from the System.Data.OleDb namespace, you need to create the parameters for MS Access. For example, to retrieve a phone number for an employee from the Employees table of Northwind database, you have to write the SQL statement like: -

Select HomePhone From Employees Where FirstName =? And LastName=?

What is Character?

This represents the parameter instead of using a named parameter like @firstname or @lastname.

Important Note: - (i) Use @ character for SQL SERVER

(ii) Use? Or @ character with MS Access.

But is? Recommended with MS ACCESS

Example 24 illustrates how you can use parametric queries with a Microsoft Access database.

Example 24 OleDbParameterSelectDemo.aspx

<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="System.Data.OleDb" %>

Sub Button_Click (s as Object, e As EventArgs)

Dim conEmployee As OleDbConnection

Dim strSelect As String

Dim cmdSelect As OleDbCommand

conEmployee = New OleDbConnection( "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=c:\Employees.mdb" )

strSelect = "Select HomePhone From Employees Where FirstName=? And LastName=?"

cmdSelect = New OleDbCommand( strSelect, conEmployee )

cmdSelect.Parameters.Add( "@firstname", tbFirstname.Text )

cmdSelect.Parameters.Add( "@lastname", tbLastname.Text )

conEmployee. Open ()

lblHomePhone. Text = "<b>Home Phone Is</b> " & cmdSelect. ExecuteScalar ()

conEmployee. Close ()

End Sub

</Script>

<html>

<body>

<h2>Employee Phone Retrieval</h2>

<b>First Name:</b>

<br>

<asp:TextBox

ID="tbFirstname"

Runat="Server" />

<p>

<br>

<asp:TextBox

ID="tbLastname"

Runat="Server" />

<p>

<asp:Button

Text="Submit"

OnClick="Button_Click"

Runat="Server" />

<p>

<asp:Label

ID="lblHomePhone"

Runat="Server" />

</form>

</body>

</html>

The output of above example is shown below:

ExpertRating ASP.NET Certification - Get Certified Now!!