Expertrating - ASP.Net Tutorial, ADO.NET

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

ASP.NET Tutorial - ADO.NET

Page 5 of 14

Example 22 OleDbExecuteScalarCount.aspx

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

Sub Page_Load

Dim conNorthwind As OleDbConnection

Dim cmdSelectCount As OleDbCommand

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

conNorthwind. Open ()

cmdSelectCount = New OleDbCommand( "Select Count(*) From Authors", conNorthwind )

lblEmp. Text = cmdSelectCount. ExecuteScalar ()

conNorthwind. Close ()

End Sub

</Script>

<html>

<body>

There are

<asp:Label

ID="lblEmp"

Runat="Server" />

Employees in this Table

</body>

</html>

The output is shown below:

Queries Using Parameters

Queries using parameters are performed to make ADO.NET more efficient. A parameter is a value that’s either passed in or out of a query. Using parameters helps you to keep your information straight and makes your queries comprehensible. For example you need to retrieve all employees from a table who works in department no. 10.In this case the SQL query will be: -

strSql=”Select * from employees where deptno=10”

This will give a fixed result and the scope will be limited to department number 10. If we want that each record is changed by passing outside parameters the query will be as: -

strSql=Select * from employees where deptno= ”& tbDeptno.text

This will serve the purpose of building a dynamic query, but an unrecognized one. What if you have three different textboxes?

Having them would become difficult for a developer, who is trying to read the code to know as to which box contains which information.

How ever, a more efficient method is to use parameters.

Let us replace previous query with a parametric one: -

strSql= “Select * from employees where deptno= @deptNo”

here @deptNo represents the parameter.

Using Parameters with Microsoft SQL Server

When you execute a SQL statement with the SqlCommand class, parameters are represented in the statement like:

Select HomePhone From Employees Where FirstName=@firstname And LastName=@lastname

In this statement, @firstname and @lastname represent parameters.

Example23 SqlParameterSelectDemo.aspx

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

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

Sub Button_Click (s as Object, e As EventArgs)

Dim conNorthwind As SqlConnection

Dim strSelect As String

Dim cmdSelect As SqlCommand

conNorthwind = New SqlConnection( "server=localhost;UID=sa;PWD=secret;Database=Northwind" )

strSelect = "Select HomePhone From Employees Where FirstName=@firstname And LastName=@lastname"

cmdSelect = New SqlCommand( strSelect, conNorthwind )

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

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

conNorthwind. Open ()

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

conNorthwind. Close ()

End Sub

</Script>

<html>

<body>

<h3>Employee Phone Retrieval</h3>

<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>

ExpertRating ASP.NET Certification - Get Certified Now!!