Coding Article #: 41 - Published On: August 05, 2012 @ 20:29:16 PM - Last Updated on: August 05, 2012

Supported Files
File Download:
carrzkiss_083106DDForm.zip
File Size: 17.67 KB
Unzip with
(OR)

Screenshot of Source Code

This ASP Classic example loads the information from a database into a select menu (Drop down List), then the user is able to insert the selected item into the database, then the page is Redirected back to the Main page, with a Query from the URL to gather the data that was inserted into the database, to display that chosen item in the Select Menu.

View Live Example«

Next, we need to get our DATABASE connection.
Below, you can choose to use either JET or Microsoft Access Driver.

<%
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath ("dd.mdb") & ";"
objConn.Open

' OR
objConn.ConnectionString = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" & _
Server.MapPath ("dd.mdb")
%>

This function will help to protect your Database from SQL and XSS Injections.
Use this to protect all entries that are being submitted to your database.

<%
Function ProtectSQL(SQLString)
SQLString = SQLString
SQLString = Replace(SQLString, ">", ">") ' replace > with >
SQLString = Replace(SQLString, "<", "<") ' replace < with <
SQLString = Replace(SQLString, "(","(") ' replace ( with (
SQLString = Replace(SQLString, ")",")") ' replace ) with )
SQLString = Replace(SQLString, "&", "&")
SQLString = Replace(SQLString, "", "")
SQLString = Replace(SQLString, "/", "/")
' replace vblf with <br /> (This is mainly used for Memo fields. However. Make sure that you do not use this if you have a script that will do this for you.)
SQLString = Replace(SQLString, vblf,"<br />") 
SQLString = Replace(SQLString, "%", "%")
SQLString = Trim(SQLString)
ProtectSQL = SQLString
End Function
%>

Create our Select Statement. We need to get the records from our database, to display to our page.

<%
' Create the Command to start your SQL and then your RecordSet (rsDD)
Set sqlDD = Server.CreateObject("ADODB.Command")
sqlDD.ActiveConnection=objConn
sqlDD.Prepared = true
sqlDD.commandtext="SELECT ddid, ddname from myTable"
set rsDD = sqlDD.execute
%>

Next, we need to check if there is a QueryString. If there is, we will get that record from our database, and do something with it.

<%
getID = ProtectSQL(Request.QueryString("id"))
if getID<>"" then
Set sqlDD = Server.CreateObject("ADODB.Command")
sqlDD.ActiveConnection=objConn
sqlDD.Prepared = true
sqlDD.commandtext="SELECT ddid, ddname from myTable where ddid=?"
sqlDD.Parameters.Append sqlDD.CreateParameter("@ddid", adInteger, adParamInput, , getID)
set rsDDs = sqlDD.execute
end if
%>

Next, we create our Form. We will then populate our options list with the records.
And when there is an existing record, it will be selected in the first box.

<form action="insert.asp" method="post">
<select name="MyNum">
<%if getID<>"" then
if not rsDDs.eof then
ids = rsDDs("ddid")
ddNames = rsDDs("ddName")%>
<option value="<%=ids%>"><%=ddnames%></option>
<%end if
end if%>
<option value="0">Choose</option>
<%if not rsDD.eof then
while not rsDD.eof
id = rsDD("ddid")
ddName = rsDD("ddName")%>
<option value="<%=id%>"><%=ddName%></option>
<%rsDD.movenext
wend
end if
rsDD.close
set rsDD = nothing
objConn.close
set objConn = Nothing%>
</select>
<input type="submit" name="Submit" value="Submit It" />
</form><hr style="width:250px;" />

Next, we will insert the values into the database, and then redirect the user to the page with the ID in the Querystring.

<%
getID = ProtectSQL(Request.Form("MyNum"))
Set sqlDD = Server.CreateObject("ADODB.Command")
sqlDD.ActiveConnection=objConn
sqlDD.commandtext="insert into myInsert (ddid)values(?)"
sqlDD.Parameters.Append sqlDD.CreateParameter("@ddid", adInteger, adParamInput, , getID)
sqlDD.execute
' Send the Visitor back to the page
response.Redirect "Main.asp?id="&getID
%>