Discover New Possibilities!

ASP Database Code

This code demonstrates some database basics using ADO and ASP. In order to test the ADO connection string being used, the easiest thing to do is create a UDL file. Once the settings are configured and returns successfully after a "Test", the UDL file can be simply be opened in a text-editor and the connection string extracted.

  Dim addStr, adCmdText, oConn, oCmd

  ' Executing command that contains a SQL statement.
  adCmdText = 1

  addStr = "INSERT INTO tblappissue ( projectname, issuedescription, "
  addStr = addStr & "issuepriority, issuetype, assignto ) VALUES ( '"
  addStr = addStr & txtProjectName & "', '" & txtErrDesc & "', '" & optPriority & "', '" 
  addStr = addStr & rdoType & "', '" & lstdev & "' )"

  ' Use / create UDL file to build ADO connection string.
  Set oConn = Server.CreateObject( "ADODB.Connection" )
  oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _ 
                        & Server.MapPath( "testing.mdb" ) & ";Persist Security Info=False"

  ' Check oConn initialization -- SUCCESS!
  If oConn.Errors.Count <> 0 Then
    Set oCmd = Server.CreateObject( "ADODB.Command" )
    Set oCmd.ActiveConnection = oConn

    oCmd.CommandText = addStr
    oCmdType         = adCmdText

    ' Execute SQL statement.
    oCmd.Execute

    Response.Redirect( "index.asp?lastaction=insert&errors=" _
                                      & oConn.Errors.Count )

    ' Free oCmd resources.
    Set oCmd  = Nothing
  End If

  ' Free objConn resources.
  Set oConn = Nothing

  Dim qryStr, adCmdText, oConn, rs
  qryStr = "SELECT TOP 1 issueID, projectName, issueDescription, " 
  qryStr = qryStr & "issuePriority, issueType, assignTo FROM tblAppIssue "
  qryStr = qryStr & "WHERE issueID = " & Request( "id" )

  ' Use / create UDL file to build ADO connection string.
  Set oConn = Server.CreateObject( "ADODB.Connection" )
  oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _ 
                        & Server.MapPath( "testing.mdb" ) & ";Persist Security Info=False"

  ' Check oConn initialization -- SUCCESS!
  If oConn.Errors.Count <> 0 Then
    Set rs = Server.CreateObject( "ADODB.recordSet" )
    rs.CursorLocation = 3 ' client-side cursor

    rs.Open qryStr, oConn

    '' Empty recordset returned.
    'If rs.EOF Then
    '    contactID = 1
    '' Increment contactID by 1.
    'Else
    '    contactID = rs( "contactID" ) + 1
    'End If

    ' Loop through recordset until reaching the end.
    Do Until rs.EOF

      rs.MoveNext
    Loop

    ' Close recordset as soon as possible.
    rs.Close

    ' Free rs resources.
    Set rs = Nothing
  End If

  ' Free oConn resources.
  Set oConn = Nothing