Classic ASP and databases

Connect and get data from a database using Classic ASP

<%@ Language = VBScript%>
<%Option Explicit%>
<!-- Place any includefile here, example: <!--#INCLUDE FILE="filename.asp"--> -->
<%
With Response
.Buffer=true
.Expires=0
.Clear
End With

'**********************************************************************
' *** Sample of how to connect to a database in MS SQL server ***
' *** with classic ASP ***
'**********************************************************************


'**********************************************************************
'*** Declare ADO constants ********************************************
'**********************************************************************
Const adOpenStatic = 3
Const adLockReadOnly = 1
Const adCmdText = &H0001
Const adUseClient = 3
Const adOpenKeyset = 1
Const adLockPessimistic = 2



'**********************************************************************
'*** Declare Variables ************************************************
'**********************************************************************
Dim strServer ' Database server
Dim strDatabase ' Database
Dim strUserId ' UserId in database
Dim strPassword ' Password in database
Dim strConn ' Connection string
Dim oRs ' ADODB Recordset
Dim sSql ' Sql select string
Dim Conn ' Connection Object

'**********************************************************************
'*** Fill Variables ***************************************************
'**********************************************************************
strServer = "DBServer" ' Database server
strDatabase = "DBName" ' The database
strUserId = "TheUserName" ' Username in database
strPassword = "ThePassWord" ' Password in database


'**********************************************************************
'*** Create connection object and connect to database *****************
'**********************************************************************
Set Conn = Server.CreateObject("ADODB.Connection")
strConn = "Provider=SQLOLEDB; Data Source = " & strServer & "; Initial Catalog = " & strDatabase & "; User Id= " & strUserId & "; Password = " & strPassword & ";"
' Access
'strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source = " & Server.MapPath(acc.mdb)
' mysql
'strConn = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=" & strServer & ";DATABASE=" & strDatabase & ";UID=" & strUserId & ";PWD=" & strPassword & ";OPTION=3;"
'--- Open ---
Conn.Open strConn


'**********************************************************************
'*** Write the SQL select string **************************************
'**********************************************************************
sSql = "SELECT LastName,FirstName "_
& "FROM Employees "_
& "ORDER BY LastName"
' ---Debug---
'Response.Write(sSql)


'**********************************************************************
'*** Create and open the RecordSet ************************************
'**********************************************************************
Set oRs = Server.CreateObject("ADODB.Recordset")
oRs.CursorLocation = adUseClient
oRs.Open sSql, Conn, adOpenStatic, adLockReadOnly, adCmdText ' Read
'oRs.Open sSql, Conn, adOpenKeyset, adLockPessimistic, adCmdText ' Add, Delete, Update



'**********************************************************************
'*** Do something with the data we have in the RecordSet **************
'**********************************************************************
Response.Write("RecordCount: " & oRs.RecordCount & "<br>")
Response.Write("Number of Col: " & oRs.Fields.Count & "<br><br>")
Do While NOT oRs.EOF
Response.Write(oRs(0) & " - " & oRs(1) & "<br>")
oRs.MoveNext
Loop
' --- Some tests ---------
oRs.MoveFirst
oRs.Filter="Lastname='King'"
Response.Write("RecordCount: " & oRs.RecordCount & "<br>")


'**********************************************************************
'*** Close and cleanup ************************************
'**********************************************************************
oRs.Close
Set oRs = Nothing
Conn.Close
Set Conn = Nothing
%>

Post a comment

Comments closed