Samples - ASP VBScript - Example #7

Code Samples -- VBScript
Example #7 - Field Type
<%@ Language=VBScript %>
<!--#include file="OpenX2VB.inc"-->
<%
Set oConn = Server.CreateObject("OpenX2.Connection")
Set oCommand = Server.CreateObject("OpenX2.Command")
Dim sResult, sSQL
Dim bError
Dim i

sRetrieveSQL = "SELECT * FROM titles"

On Error Goto 0

Sub ProcessErr
  If Err.number <> 0 Then
    bError = true
    sResult = ""
    If oConn.ErrorCode <> 0 Then
      sResult = "OpenX2 Connection Error: " & oConn.ErrorInfo & ". Error #" & oConn.ErrorCode & " (" & oConn.ErrorCodeEx & ")<br />"
    Else
      If oCommand.ErrorCode <> 0 Then
        sResult = "OpenX2 Command Error: " & oCommand.ErrorInfo & ". Error #" & oCommand.ErrorCode & " (" & oCommand.ErrorCodeEx & ")<br />"
      End If
    End If
    If sResult = "" Then
      Rem you may reRaise the Error here if you want to allow IIS process rest of errors
      sResult = "ASP Error: #" & CStr(Err.Number) & ". " & Err.description & "<br />"
    End If
  End If
End Sub

Sub ProcessQuery
  sResult = ""
  bError = false
  oConn.Connect("ms_ox1")
  oCommand.Connection = oConn
  oConn.autoCommit = true
  oCommand.CommandText = sRetrieveSQL
  oCommand.Execute
  sResult = sResult & "<table border=1><tr>"
  For i = 1 To oCommand.FieldCount
    sResult = sResult & "<td><b>" & oCommand.FieldName(i) & "</b></td>"
  Next
  sResult = sResult & "</tr>"
  
  Dim aNum
  Dim aDate
  While oCommand.MoveNext
    sResult = sResult & "<tr>"
    For i = 1 To oCommand.FieldCount
      Select Case oCommand.FieldType(i)
        Case dtShort
        Case dtLong
          sResult = sResult & "<td align='right'style='font-size: 10pt;'>" & oCommand.FieldValueAsString(i) & " </td>"
        Case dtDouble
          aNum = oCommand.FieldValueAsDouble(i)
          sResult = sResult & "<td align='right'style='font-size: 10pt;'>$" & FormatNumber(aNum, 2) & " </td>"
        Case dtDateTime
          aDate = oCommand.FieldValueAsDate(i)
          sResult = sResult & "<td align='center'style='font-size: 10pt;'>" + FormatDateTime(aDate, 2) + " </td>"
        Case Else
          sResult = sResult & "<td style='font-size: 10pt;'>" + oCommand.FieldValueAsString(i) + " </td>"
      End Select
    Next
    sResult = sResult & "</tr>"
  Wend
  sResult = sResult & "</table>"

End Sub

REM // Main Processing
On Error Resume Next
ProcessQuery
ProcessErr

%>
<html>
  <head><title>OpenX2 Test #8 - 'Field Type'</title></head>
  <body>
    <div><%=sResult%></div>
  </body>
</html>


<% ' OpenX2VB.inc

' Transaction Isolation Level
Const tilUnknown = -1	' default
Const tilReadUncommitted = 0
Const tilReadCommitted = 1
Const tilRepeatableRead = 2
Const tilSerializable = 3

' AutoCommit
Const acAutoCommitUnknown = -1
Const acAutoCommitOff = 0
Const acAutoCommitOn = 1

' Data types
Const dtUnknown = 0
Const dtShort = 1
Const dtLong = 2
Const dtDouble = 3
Const dtDateTime = 4
Const dtString = 5
Const dtBytes = 6
Const dtLongBinary = 7
Const dtLongChar = 8
Const dtBLob = 9
Const dtCLob = 10
Const dtCursor = 11

' Command types
Const cmdTypeUnknown = 0  ' default
Const cmdTypeSQLStmt = 1
Const cmdTypeStoredProc = 2

' Parameter types
Const ptParamInput = 0
Const ptParamInputOutput = 1
Const ptParamOutput = 2
Const ptParamReturn = 3

%>