Creating a Classic ASP program to get data from Notes

Now we are ready to create the ASP page that will interact with our Notes View that we have just created. As previously stated, the purpose of this ASP is to query the Notes database and see if a user matching the username - case insensitive - and password match what is in the database. For the sake of this tutorial we will just output the result to the screen.

<%
Dim DSNName
Dim DBUserName
Dim DBPassword
Dim dbConn
Dim results
Dim SQLStatement

' Database connection sub
sub db_connect
    ' Create a connection object
    set dbConn = server.createobject("ADODB.connection")

    ' Open a connection using the following criteria - DSN Name, UserName, Password
    dbConn.open DSNName, DBUserName, DBPassword
end sub

' Database disconnect sub
sub db_disconnect
    dbConn.close
end sub

' DSN Connection info
DSNName="LN" ' DSN Name
DBUserName="Greg Griffiths" ' Notes User Name
DBPassword="password" ' Notes Password

' Create the SQL to return any matches if the uppercased version of the username
' and the actual password are found in the View called myNewView
SQLStatement="SELECT * FROM myNewView WHERE ucuserid='" & UCase(Request.Form("userid")) & "
SQLStatement=SQLStatement & "' AND password='" & Request.Form("password") & "'"

' connect to the DB
db_connect

' run the Query
set results=dbConn.execute(SQLStatement)

' If the results set is empty
if (results.EOF) then
    response.write("No Match")
else
    response.write("Match")
end if

' Close the results set
results.close

' Disconnect from the Datasource
db_disconnect
%>

We use standard ASP Subroutines to manage the connect and the disconnect from the database. Within the main code itself, we start by declaring the value of our connection information and then create the SQL Statement that we will use to query the database using the value from the user submitted form. We then connect to the database and run that query and populate a results set with the information, we then check if there is anything in that results set, and display a message accordingly. Finally we close both the results set and the database connection.

In the final section of this article we review what we have achieved and provide links and books for futher study.