[thelist] ASP Tip

[Mohammad Burhan Khalid]

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;"
		.Open
		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;"
		.Open
	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 />"
		adoRs.MoveNext
	Loop

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 :

	adoRs.close
	adoConnection.close
	Set adoRs = Nothing
	Set adoConnection = Nothing
	Set adoCommand = Nothing

</tip>

hth,
Burhan Khalid