ASP code to connect to a SQL Server Database

If you need to connect to a sql server and run a select statement:

<%

Dim adoCon223

Dim rs223

Dim strSQL223

Set adoCon223 = Server.CreateObject("ADODB.Connection")

adoCon223.Open "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=userid;Password=password;Initial Catalog=Database;Data Source=ServerName"

Set rs223 = Server.CreateObject("ADODB.Recordset")

strSQL223 = "SELECT * from table"

rs223.Open strSQL223, adoCon223

Do While not rs223.EOF

rs223.MoveNext

Loop

rs223.Close

Set rs223 = Nothing

Set adoCon223 = Nothing

%>

If you need to insert records into a table:

<%

Dim adoCon

Set adoCon = Server.CreateObject("ADODB.Connection")

adoCon.Open "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sam;Password=jones;Initial Catalog=Database;Data Source=servername"

Dim objExec

Dim strsql2

strSQL2 = "Insert into crdtTransactions (Plate, Locationnumber, startdate, enddate, chargeamount) "

strSQL2 = strSQL2 & "values ('" & Plate & "'," & Location

strSQL2 = strSQL2 & " ,'" & Startdate & "','" & Enddate & "'," & Chargeamount & ")"

Set objExec = adoCon.Execute(strSQL2)

%>

If you want to connect to the database using an ODBC connection:

Dim adoCon

Set adoCon = Server.CreateObject("ADODB.Connection")

adoCon.Open "ODBCName", "userid"

Dim rs

Set rs = CreateObject("ADODB.Recordset")

Dim strsql

strsql = "Select top 1 * from dm.clt"

rs.open strsql, adoCon

Do while not rs.eof

rs.movenext

Loop

rs.close

Set rs=Nothing

If you want to execute a stored procedure with parameters populate a recordset:

Dim adoCon

Set adoCon = Server.CreateObject("ADODB.Connection")

adoCon.Open "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=userid;Password=password;Initial Catalog=Database;Data Source=ServerName"

Dim rs

Set rs = Server.CreateObject("ADODB.Recordset")

set rs = adocon.execute("exec storeprocedurename '" & begindate & "', '" & enddate & "'")

Do while not rs.eof

rs.movenext

Loop

rs.close

Set rs=Nothing

If you want to prevent sql injection attacks in accordance with Microsoft policy (see

http://msdn.microsoft.com/en-us/library/cc676512.aspx ) when you are running sql using a form post, use this sql syntax:

Dim adoCon

Set adoCon = Server.CreateObject("ADODB.Connection")

adoCon.Open "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Password=yourpassword;Initial Catalog=YourDatabase;Data Source=YourSQLServer"

Dim cmd

Dim param

Dim strsql67

Set cmd = Server.CreateObject("ADODB.Command")

Set cmd.ActiveConnection = adoCon

Dim rs67

Set rs67 = Server.CreateObject("ADODB.Recordset")

strsql67 = "Select * from table where userid=?"

cmd.CommandText = strsql67

Set param = cmd.CreateParameter("@userid", 200,1 ,255, request.form("userid"))

cmd.Parameters.Append param

Set rs67 = cmd.Execute

Do while not rs67.eof

rs67.movenext

Loop

rs67.close

Set rs67=Nothing

NOTE: the values for the createparameter command of 200, 1, 255 come from:

http://www.w3schools.com/ado/met_comm_createparameter.asp

In our example, 200 means a string, the 1 means an input parameter, and the 255 is the maximum length.