ASP‎ > ‎

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.


 
Comments