Samples - ASP VBScript

Code Samples -- VBScript
Example #6 - Insert
<%@ Language=VBScript %>
<%
Set oConn = Server.CreateObject("OpenX2.Connection")
Set oCommand = Server.CreateObject("OpenX2.Command")
Dim sResult, sResult1, sRetrieveSQL, sInsertSQL
Dim bError, bDelete
bInsert = (Request.Form("id").Count > 0 And Request.Form("id") = "Add New")
sRetrieveSQL = "SELECT * FROM authors"
sInsertSQL = "INSERT INTO authors ( au_id, au_lname, au_fname, phone, address, city, state, zip, contract ) VALUES ( :1, :2, :3, :4, :5, :6, :7, :8, :9 )"

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 processing the rest of errors
      sResult = "ASP Error: #" & CStr(Err.Number) & ". " & Err.description & "<br />"
    End If
  End If
End Sub

Sub ProcessQuery
  Dim i
  bError = false
  sResult = ""
  oConn.Connect("ms_ox1")
  oCommand.Connection = oConn
  oConn.autoCommit = true
  If bInsert Then
    Dim iRnd
    Randomize
    iRnd = CStr(Round(1000000000 * Rnd))
    oCommand.CommandText = sInsertSQL
    oCommand.ParamValueAsString(1) = Mid(iRnd, 1, 3) & "-" & Mid(iRnd, 4, 2) & "-" & Mid(iRnd, 5, 4)
    For i = 2 To 9
      oCommand.ParamValueAsString(i) = Request.Form("fld" + CStr(i))
    Next
    oCommand.Execute
  End If
  oCommand.CommandText = sRetrieveSQL
  oCommand.Execute
  sResult = sResult & "<form action='OX2testVB7.asp' method='post'>"
  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><tr><td><input type='submit' name='id' value='Add New'></td>"
  For i = 2 To oCommand.FieldCount
    If oCommand.FieldSize(i) > 20 Then
      iFS = 20
    Else
      iFS = oCommand.FieldSize(i)
    End If
    sResult = sResult & "<td><input type='text' name='fld" & CStr(i) & "' value='' size='" & CStr(iFS) & "'></td>"
  Next
  sResult = sResult & "</tr>"  

  While oCommand.MoveNext()
    sResult = sResult & "<tr>"
    For i = 1 To oCommand.FieldCount
      sResult = sResult & "<td> " & oCommand.FieldValueAsString(i) & "</td>"
    Next
    sResult = sResult & "</tr></form>"
  Wend
  sResult = sResult & "</table>"
End Sub

REM // Main Processing
On Error Resume Next
ProcessQuery
ProcessErr

%>
<html>
  <head><title>OpenX2 Test #5 - UPDATE Sample</title></head>
  <body>
    <%= sResult1 %>
    <div><%=sResult%></div>
  </body>
</html>