<%@ LANGUAGE="VBSCRIPT" %>
<!--#INCLUDE FILE="adovbs.inc"-->
<!--#INCLUDE FILE="params.inc"-->
<HTML>
<HEAD>
<TITLE>Test ASP</TITLE>
</HEAD>
<BODY BGCOLOR="orange">
<%
' ODBCName, UserName, Password, ssn, & pin are set in params.inc
'************************************************************************
'* *
'* Connection Object: *
'* *
'* Properties: *
'* Attributes ConnectionTimeout Mode *
'* CommandTimeout DefaultDatabase Provider *
'* ConnectionString IsolationLevel Version *
'* *
'* Methods: *
'* BeginTrans RollbackTrans Execute *
'* CommitTrans Close Open *
'* *
'* Collections *
'* Errors Properties *
'* *
'************************************************************************
' create a database connection
Set Db = Server.CreateObject("ADODB.Connection")
Db.CommandTimeout = 10 * 60 ' set the command timeout (10 minutes)
Db.Open ODBCName, UserName, Password ' log into the ODBC connection
' transaction processing
Db.BeginTrans ' begin transaction
Db.RollbackTrans ' rollback transaction
Db.BeginTrans
Db.CommitTrans ' commit transaction
' Create a recordset from the connection object
Set Rs = Db.Execute("SELECT * FROM statecd_tab") ' execute SQL command
'Call Dump(Rs)
Rs.Close: Rs = Null
' read/write properties for the connection
x = Db.Attributes ' transaction attributes
x = Db.CommandTimeout ' timeout for command to complete
x = Db.ConnectionString ' info used for connection to data source
x = Db.ConnectionTimeout ' time to wait while establishing a connection
x = Db.DefaultDatabase ' default database for connection
x = Db.IsolationLevel ' isolation level for transactions
x = Db.Mode ' available permissions for modifying data
x = Db.Provider ' name of the provider
x = Db.Version ' ADO version number
' Connection Properties collection
For Each item in Db.Properties
'Response.Write item.name & "=" & item.value & "<BR>"
Next
' Connection Errors Collection
For Each item in Db.Errors
'Response.Write item.description & "<BR>"
Next
'Db.Close ' close the connection
'************************************************************************
'* *
'* Command Object: *
'* *
'* Properties: *
'* *ActiveConnection *CommandTimeout *Prepared *
'* *CommandText *CommandType *
'* *
'* Methods: *
'* CreateParameter *Execute *
'* *
'* Collections *
'* Parameters Properties *
'* *
'************************************************************************
' calling a stored procedure via a command object - Prepared Command
Set Cmd = Server.CreateObject("ADODB.Command") ' create a command object
Set Cmd.ActiveConnection = Db ' attach it to the database connection
Cmd.CommandTimeout = 10*60 ' set the command timeout (10 minutes)
Cmd.Prepared = False ' flag to compile version on server (default)
Cmd.CommandType = adCmdStoredProc ' set to talk to database via stored procs
Cmd.CommandText = "spIALogin" ' point to the Login stored procedure
Cmd.Parameters.Append Cmd.CreateParameter("SSN", adChar, adParamInput, 9, ssn)
Cmd.Parameters.Append Cmd.CreateParameter("PIN", adChar, adParamInput, 4, pin)
Set Rs = Cmd.Execute ' execute the login
'Call Dump(Rs)
Rs.Close: Rs = Null: Call ClearCmd(Cmd)
' calling a stored procedure via a command object - Unprepared Command
Cmd.CommandType = adCmdText ' set to discover type from refresh
Cmd.CommandText = "{call spIALogin(?,?)}"
Cmd.Parameters.Refresh ' retrieve parameter list from server
Cmd(0) = ssn
Cmd(1) = pin
Set Rs = Cmd.Execute
'Call Dump(Rs)
Rs.Close: Rs = Null: Call ClearCmd(Cmd)
' Use command object to call a query
Cmd.CommandType = adCmdText
Cmd.CommandText = "SELECT * FROM statecd_tab WHERE statecd = ?"
Cmd.Parameters.Refresh
Cmd(0) = "TX"
Set Rs = Cmd.Execute
'Call Dump(Rs)
Rs.Close: Rs = Null: Call ClearCmd(Cmd)
' Command Properties collection
For Each item in Cmd.Properties
'Response.Write item.name & "=" & item.value & "<BR>"
Next
' clear the parameters from command object
Sub ClearCmd(Cmd)
Do While (Cmd.Parameters.Count > 0)
Cmd.Parameters.Delete 0
Loop
End Sub
'************************************************************************
'* *
'* Recordset Object: *
'* *
'* Properties: *
'* AbsolutePage CursorType PageCount *
'* AbsolutePosition EditMode PageSize *
'* ActiveConnection EOF RecordCount *
'* BOF Filter Source *
'* Bookmark LockType Status *
'* CacheSize MaxRecords *
'* *
'* Methods: *
'* AddNew Move Requery *
'* CancelBatch MoveFirst Resync *
'* CancelUpdate MoveLast Supports *
'* Clone MoveNext Update *
'* Close MovePrevious UpdateBatch *
'* Delete NextRecordset *
'* GetRows Open *
'* *
'* Collections *
'* Fields Properties *
'* *
'************************************************************************
Set Cmd = Server.CreateObject("ADODB.Command")
Set Cmd.ActiveConnection = Db
Cmd.CommandTimeout = 10*60
Cmd.CommandType = adCmdStoredProc
Cmd.CommandText = "spIALogin"
Cmd.Parameters.Append Cmd.CreateParameter("SSN", adChar, adParamInput, 9, ssn)
Cmd.Parameters.Append Cmd.CreateParameter("PIN", adChar, adParamInput, 4, pin)
Set Rs = Cmd.Execute
' access column value by name - store it in session var
Session("ADMINEMPID") = Rs("ADMINEMPID")
Session("ADMINSYSKEY") = Rs("SYSKEY")
Rs.Close: Rs = Null: Call ClearCmd(Cmd)
' dump out a resultset to drop down box
Cmd.CommandType = adCmdStoredProc
Cmd.CommandText = "spIACodesEmpType"
Set Rs = Cmd.Execute
Response.Write("<select name=EMPTYPECD>")
Do While (Not (Rs.EOF))
Response.Write("<option value=" & Rs("EMPTYPECD") & ">" & Rs("DESCRIPTION")& "</option>")
Rs.MoveNext
Loop
Response.Write("</select>")
Rs.Close: Rs = Null: Call ClearCmd(Cmd)
' dump out recordset in formatted html table
Sub Dump(Rs)
Response.Write("<TABLE BORDER=2><TR>")
For Each fld In Rs.Fields
Response.Write("<TD>" & fld.Name & "</TD>")
Next
Response.Write("</TR>")
Do While (Not (Rs.EOF))
Response.Write("<TR>")
FOR i = 0 To (Rs.Fields.Count - 1)
Response.Write("<TD>" & Rs(i) & "</TD>")
Next
Response.Write("</TR>")
Rs.MoveNext
Loop
Response.Write("</TABLE>")
End Sub
'************************************************************************
'* *
'* Field Object: *
'* *
'* Properties: *
'* ActualSize NumericScale UnderlyingValue *
'* Attributes OriginalValue Value *
'* DefinedSize Precision *
'* Name Type *
'* *
'* Methods: *
'* AppendChunk GetChunk *
'* *
'* Collections *
'* Properties *
'* *
'************************************************************************
'************************************************************************
'* *
'* Parameter Object: *
'* *
'* Properties: *
'* Attributes NumericScale Type *
'* Direction Precision Value *
'* Name Size *
'* *
'* Methods: *
'* AppendChunk *
'* *
'************************************************************************
'************************************************************************
'* *
'* Property Object: *
'* *
'* Properties: *
'* Attributes Type Value *
'* Name *
'* *
'************************************************************************
'************************************************************************
'* *
'* Error Object: *
'* *
'* Properties: *
'* Description NativeError SQLState *
'* HelpContext Number *
'* HelpFile Source *
'* *
'************************************************************************
Db.Close
%>
<form name="MyForm" action="hello.asp" method="post">
<input type="submit" name="MySubmit" value="Submit Form">
<input type="hidden" name="SubmitButton" Value="None">
</form>
</BODY>
</HTML>