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.