[thelist] ASP Tip

Mohammad Burhan Khalid mohammad_b at alph.swosu.edu
Mon Apr 1 09:22:00 CST 2002

Hello folks :

	It's been a while since I submitted a tip, so here's a good one :

<tip type="ASP ADO with SQL Server" author="Burhan Khalid">

	This is the common code snippet most developers use when using ADO to connect
to a database in ASP :

	Dim adoConn, adoRS
	Set adoConn = Server.CreateObject("ADODB.Connection")
	Set adoRS   = Server.CreateObject("ADODB.Recordset")

	With adoConn

		.ConnectionString = "Provider=SQLOLEDB;Data Source = " & _
		"MyServer;Initial Catalog=Northwind; User ID=sa;"
		Set adoRS = .Execute( "EXEC CustOrdersOrders 'ALFKI' )

	End With

	A better way (and often overlooked way) of doing the above program segment
using the Command object :

	Dim adoConn, adoCommand, adoRs

	Const adCmdStoredProc = 4

	Set adoConn = Server.CreateObject("ADODB.Connection")
	Set adoCommand = Server.CreateObject("ADODB.Command")
	Set adoRs = Server.CreateObject("ADODB.Recordset")

	With adoConn

		.ConnectionString = "Provider=SQLOLEDB;Data Source = " & _
		"MyServer;Initial Catalog=Northwind; User ID=sa;"
	End With
	With adoCommand
		Set .ActiveConnection = adoConn
		.CommandText = "CustOrdersOrders"
		.CommandType = adCmdStoredProc
		.Parameters("@CustomerID") = "ALFKI"
		Set adoRs = .Execute
	End With

	Do While Not adoRs.EOF
		Response.Write adoRs("OrderID") & _
		" ordered on " & adoRs("OrderDate") & "<br />"

Why would you want to use the command object? For one thing, its more
powerful that ADO, in that it can return either the results of a SQL
statement, a stored procedure, and even an entire table. In addition, with
the command object, there is the prepared property, which will compile the
SQL before sending it to the server (can anyone say optimization)? These
are some of the benefits of using the command object over ADO. I am still
working with it, so look out for more tips in the same category.

One last thing, don't forget :

	Set adoRs = Nothing
	Set adoConnection = Nothing
	Set adoCommand = Nothing


Burhan Khalid

More information about the thelist mailing list