Okay, you just downloaded Personal Web Server or IIS and you want to get that database connected and on the web. Here's a short tutorial to get you started and up quick.
Inserting a Record Into a Database
In the this section we will be inserting data from an html form. In order to do the insert you must have a page that will take values from the user and pass them to the ASP page for processing and inserting to your database.
First you have to create the form in your html:
<form action="InsertData.asp" method=get>
<input type=text name="txtMyForm">
<input type=submit>
</form>
You can see I created a form and pointed it to a ASP file to process the results. I named a text field called txtMyForm keep this in mind for the next part.
Then I create a file called my InsertData.asp. In this file I need to open a database connection object and insert the data into a database.
All ASP code is delimited by "<%" and "%>" This lets IIS know when the server side script begins and ends.
First create the database connection:
<%
Set DatabaseConnection = Server.CreateObject("ADODB.Connection")
'Then open the connection. This is a ODBC datasource which you set up in the control panel and then select ODBC32. Once here you point the actual physical location of the database and then name the DSN. In this example I named it MyDatabaseDSN. You can also create the DSN dynamically in the ASP script file itself without having to setup a DSN on the machine.
DatabaseConnection.Open "DSN=MyDatabaseDSN"
'Now create your SQL statement by passing the values from the form and building a string to execute the SQL statement:
sSQL = "INSERT INTO MYTABLE (MyField)VALUES('" & request.form("txtMyForm") & "')"
'As you can see from this I created a string and then with the Request object I added the value from my form. Remember above I called the text box txtMyForm. This is how we pass it from the form to the handler script. Also you need to learn how SQL handles variables. For numbers you don't use the
single quotes, for text you do. In addition it is always good practice to use the replace method of VBScript to double up and single quotes a user may enter into the text field. This can cause your SQL to crash. Do this function in the following way:
'Replace(request.form("txtMyForm", "'", "''")
'Now that you have the SQL statement built you just need to execute it:
Set rs = DatabaseConnection.Execute(sSQL)
'Then give the user some html to let them know it was inserted.
%><!--remember to close your ASP script.-->
Record was inserted! <a href="index.html">Back to home page.</a>
Retrieving Records from a Database
Ok now that we have data in our database, this section will go over retrieving that data and displaying the results to the user via a web page. This example will take the form we created in the pervious section and use the text value to query a database for matching values and then display the results to the user in an html table.
First create a form page similar to the one before, but this time you set the action of the form to a new script that we will create.
<form action="ViewData.asp" method=get>
<input type=text name="txtMyForm">
<input type=submit>
</form>
Now give this form page a name and save it. Now that this form page is created and ready to pass a value to be searched, we must create the ViewData.asp document to handle the criteria. Create a new file and name it ViewData.asp.
In the ViewData.asp document you'll have to add a reference to the previously mentioned connection object and open your data source.
First create the database connection:
<%
Set DatabaseConnection = Server.CreateObject("ADODB.Connection")
'Then open the connection. This is a ODBC datasource which you set up in the control panel and then select ODBC32. Once here you point the actual physical location of the database and then name the DSN. In this example I named it MyDatabaseDSN. You can also create the DSN dynamically in the ASP script file itself without having to setup a DSN on the machine.
DatabaseConnection.Open "DSN=MyDatabaseDSN"
'Everything was the same as inserting the data up until this point. Now we have to change our SQL statement to select all matching records of our criteria.
sSQL = "SELECT * FROM MyTable WHERE MyField ='" & request.form("txtMyForm") & "'"
'Depending on the data type you have in the field you this SQL statement may be different. If you checking a string then you surround the value with apostrophes " ' " if it's an integer then you omit the apostrophes.
'Now Execute the SQL statement:
Set rs = DatabaseConnection.Execute(sSQL)
'Now that we executed our SQL statement we need to check to see if there are any records that matched and if there are display the results.
If Not rs.EOF Then
response.write "<table>"
Do Until rs.EOF
response.write "<tr>"
For i = 0 to (rs.Fields - 1)
response.write "<td>" & rs(i) & "</td>"
Next
response.write "</tr>"
rs.MoveNext
Loop
response.write "</table>"
Else
response.write "No Matching Records"
End If
Now what this did was it first checked to see if there were any matching records, if there were then it created a table header, after this for each recordset it creates a row, then it loops through all the fields in the database and outputs the data contained in them in their own table cell. It then exits the looping through the fields and ends the row and then one record and continues this process until all matching records are displayed. If it didn't find any matches then it displays this status to the user.
That's all there is to it. You can add and modify as you like in order to customize it to your needs. You need to get in there and play around with this in order to make it work for you.
Keep on Coding!
By: Patrick Santry, Microsoft MVP (ASP/ASP.NET), developer of this site, author of books on Web technologies, and member of the DotNetNuke core development team. If you're interested in the services provided by Patrick, visit his company Website at Santry.com.
