ASP Drop down crashes page

Hello again,

I am trying to get a drop down menu/list to display product categories for the user to select
as part of a Add Record form. However, this code which I found after trolling the net for
code hints seems to stop the page working full stop. There's a record set on the same page
which displays the currents records. Is the following code conflicting, or is it totally incorrect?
If only I could use ColdFusion! Thanks for your help!



<%
Option Explicit
Dim SelectedCategoryID
SelectedCategoryID = 1

Call PopulateDropDown( SelectedCategoryID )

Sub PopulateDropDown(byVal SelectedCategoryID)
Dim objConn
Dim objRS
Dim strSQL
Dim CategoryID
Dim CategoryName

Const adOpenForwardOnly = 0
Const adLockReadOnly = 1

Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
Server.MapPath("inov8test.mdb")

strSQL = "SELECT categoryID, subCategoryName FROM subCategories"

objRS.Open strSQL, objConn, adOpenForwardOnly, adLockReadOnly

Response.Write "<SELECT Name=""categoryID"">"
Response.Write "<OPTION Value=""""></OPTION>"

' Loop through the recordset and populate the dropdown
Do Until objRS.EOF = True
UserID = objRS("categoryID").Value
UserName = objRS("subCategoryName").Value

If CategoryID = SelectedCategoryID Then
Response.Write "<OPTION Value=""" & categoryID & """ SELECTED>"
Response.Write subCategoryName
Response.Write "</OPTION>"
Else
Response.Write "<OPTION Value=""" & categoryID & """>"
Response.Write subCategoryName
Response.Write "</OPTION>"
End If

objRS.Movenext
Loop

Response.Write "</SELECT>"

objRS.Close
Set objRS = Nothing
Set objConn = Nothing
End Sub
%>


Regards,
Nicole

A couple of suggestions.

Take the code out of the sub and test it on a page on it's own.
Put your option explicit declaration in it's own code wrapper on the first line of the page:

Check you variables. You are trying to response.write non-existent variables in the sub (subCategoryName ).
What are these two values: UserID, UserName?
And finally - you are passing a value through to the sub ( SelectedCategoryID ), but don't use it anywhere??

wait, I fixed it... on a separate page anyway. When
I put the working code back into the original page, it
doesn't load. What is causing this? I'll prob just end
up making the add record form on a separate page.

Now I have to fix the Add Record form using this
dropdown to select a category to insert the value
into the Products table. You'll probably hear from
me again...


Regards,
Nicole

Have you fixed the variables that were wrong in the first code you posted? Can you post the revised code.

It's ok, it was the option explicit line which was causing the fault

The <%option explicit%> declaration won't cause faults, it highlights poor coding or errors in your code. It forces you to explicitly declare all variables, and picks up if you try to use a variable that you haven't declared, or have misspelt.

I can't seem to get this recordset to write eachg record to a new line, they just come right after each other across the screen.

<%
' Declaring variables
Dim rs1, conn, num
no = 0
conn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
Server.MapPath("inov8test.mdb")
' Creating Recordset Object and opening the database
Set rs1 = Server.CreateObject("ADODB.Recordset")
rs1.Open "SELECT Products.ProductID, Products.Name, Products.Model, Products.Price, Categories.CategoryID, Categories.CategoryName FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID", conn
' Looping through the records to show all of them
While Not rs1.EOF
%>
<td><%Response.Write ("<a href=""view_product.asp?ID=" & rs1("productId") & """>") & (rs1("Name")) & ("</a>")%></td>
<td>&nbsp;</td>
<td><%Response.Write (rs1("Model"))%></td>
<td>&nbsp;</td>
<td><%Response.Write (rs1("CategoryName"))%></td>
<td>&nbsp;</td>
<td><%Response.Write (FormatCurrency(rs1("Price"),2))%></td>
<td>&nbsp;</td>
<td><%Response.Write ("<form action='del_product_rec.asp' method='post'>") & ("<input type='submit' value='Delete'>")%></td>
<%
Response.Write "<input type='hidden' name='id' value='" & rs1("productId") & "'>"
Response.Write "</form>"
num = num + 1
rs1.MoveNext
Wend
' Done. Now close the Recordset
rs1.Close
Set rs1 = Nothing
%>

Any thoughts? Feel like I am getting absolutely nothing done *tears hair out*

Regards,
Nicole

You'll need to build some <tr></tr> tags into your code like:

Thanks mate, it's displaying right now. Just a few more questions, and I'll praise the ground
you walk on.... how do I go about passing a value from a drop down menu to be inserted
into a database table? The form is set up, the drop down displaying all categories. It's slightly
difficult to transfer a coldfusion model to an ASP one. Can you please point me in the right
direction with the following:

1) The Add Record form needs to insert the categoryID selected from the drop down (which is
populated by the Categories table) into the Products table where the rest of the form values
will be inserted

2) The Update Record form needs to have the product's category already selected from the
dropdown then update the new category etal like above.

I haven't even started on uploading & displaying images to an Access database yet but as long
as I can get this part fully working as soon as possible. Thanks so much for your help!!

Regards,
Nicole

OK it's getting late, I've had a few beers, so things might get a little wobbly here

On the processing page that collects the data from the forms for updating/deleting/adding, whatever it is you are doing, you reference the form field values like:


You can then build your sql statement to perform the action you require and reference the form field values as required, like:

Heh heh, thanks! I have actually got it mostly working except when it comes to Updating the CategoryID



The update_prodrec.asp page features this code to get the dropdown (which u prob have seen before). It needs to show
the category which corresponds to the productID the user selected:



<%
Dim SelectedCategoryID
SelectedCategoryID = 1



Call PopulateDropDown( SelectedCategoryID )

' *********************************
' Populate Drop Down Sub Routine
' *********************************
Sub PopulateDropDown(byVal SelectedCategoryID)
Dim objConn
Dim objRS
Dim strSQL
Dim CategoryID
Dim CategoryName

Const adOpenForwardOnly = 0
Const adLockReadOnly = 1

Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")

objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
Server.MapPath("inov8test.mdb")

strSQL = "SELECT categoryID, categoryName FROM Categories"

objRS.Open strSQL, objConn, adOpenForwardOnly, adLockReadOnly

Response.Write "<SELECT Name=""categoryID"">"
Response.Write "<OPTION Value=""""></OPTION>"

' Loop through the recordset and populate the dropdown
Do Until objRS.EOF = True
categoryID = objRS("categoryID").Value
categoryName = objRS("categoryName").Value

If CategoryID = SelectedCategoryID Then
Response.Write "<OPTION Value=""" & categoryID & """ SELECTED>"
Response.Write categoryName
Response.Write "</OPTION>"
Else
Response.Write "<OPTION Value=""" & categoryID & """>"
Response.Write categoryName
Response.Write "</OPTION>"
End If

objRS.Movenext
Loop

Response.Write "</SELECT>"

objRS.Close
Set objRS = Nothing
Set objConn = Nothing
End Sub
%>


This is the code of the update_entry.asp page which processes the form and crashes when I try to include the category drop down as it is:

<% 'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsUpdateEntry 'Holds the recordset for the record to be updated
Dim strSQL 'Holds the SQL query to query the database
Dim lngRecordNo 'Holds the record number to be updated
'Read in the record number to be updated
lngRecordNo = CLng(Request.Form("productId"))
'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("inov8test.mdb")
'Create an ADO recordset object
Set rsUpdateEntry = Server.CreateObject("ADODB.Recordset")
'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT products.productId, products.name, products.model, products.briefspecs, products.fullspecs, products.price, categories.categoryID, categories.categoryName FROM Categories INNER JOIN Products ON Categories.CategoryID=Products.CategoryID WHERE productId=" & lngRecordNo
'Set the cursor type we are using so we can navigate through the recordset
rsUpdateEntry.CursorType = 2

'Set the lock type so that the record is locked by ADO when it is updated
rsUpdateEntry.LockType = 3

'Open the recordset with the SQL query
rsUpdateEntry.Open strSQL, adoCon

'Update the record in the recordset
rsUpdateEntry.Fields("CategoryID") = Request.Form("CategoryID")
rsUpdateEntry.Fields("Name") = Request.Form("Name")
rsUpdateEntry.Fields("Model") = Request.Form("Model")
rsUpdateEntry.Fields("BriefSpecs") = Request.Form("BriefSpecs")
rsUpdateEntry.Fields("FullSpecs") = Request.Form("FullSpecs")
rsUpdateEntry.Fields("Price") = Request.Form("Price")

'Write the updated recordset to the database
rsUpdateEntry.Update
'Reset server objects
rsUpdateEntry.Close
Set rsUpdateEntry = Nothing
Set adoCon = Nothing
'Return to the update select page in case another record needs deleting
Response.Redirect "update_products_test.asp"
%>

It uses ADO and works fine without the categoryID. Might just drop that option anyway. I have to learn to say 'no' to certain jobs. Argh. Thanks so much for your help!!

Regards,
Nicole

Dim objRSm, strQm, strcatm, objRSm1, strQm1, objRSm2

'*** configure table connections here

strQm = "SELECT * FROM Categories ORDER by Category ASC"
strQm1 = "SELECT * FROM Subcategories ORDER by Category_ID ASC"
Set objRSm = objConn.Execute(strQm)
Set objRSm1 = objConn.Execute(strQm1)
Set objRSm2 = objConn.Execute(strQm)

Response.Write "<script type=""text/javascript"">" & Vbcrlf

'*** configure drop down menu here
Response.Write "var menuwidth='165px'" & Vbcrlf '*** default menu width
Response.Write "var menubgcolor='#cbcbcb'" & Vbcrlf '*** menu bgcolor
Response.Write "var disappeardelay=250" & Vbcrlf '*** menu disappear speed onMouseout (in miliseconds)
Response.Write "var hidemenu_onclick=""yes""" & Vbcrlf'*** hide menu when user clicks within menu?

'*** no further configuring neccessary

Response.Write "var ie4=document.all" & Vbcrlf
Response.Write "var ns6=document.getElementById&&!document.all" & Vbcrlf

Response.Write "if (ie4||ns6)" & Vbcrlf
Response.Write "document.write('<div id=""dropmenudiv"" style=""visibility:hidden;width:'+menuwidth+';background-color:'+menubgcolor+'"" onMouseover=""clearhidemenu()"" onMouseout=""dynamichide(event)""></div>')" & Vbcrlf & Vbcrlf

Response.Write "function getposOffset(what, offsettype){" & Vbcrlf
Response.Write "var totaloffset=(offsettype==""left"")? what.offsetLeft : what.offsetTop;" & Vbcrlf
Response.Write "var parentEl=what.offsetParent;" & Vbcrlf
Response.Write "while (parentEl!=null){" & Vbcrlf
Response.Write "totaloffset=(offsettype==""left"")? totaloffset+parentEl.offsetLeft :" & Vbcrlf
Response.Write "totaloffset+parentEl.offsetTop;" & Vbcrlf
Response.Write "parentEl=parentEl.offsetParent;" & Vbcrlf
Response.Write "}" & Vbcrlf
Response.Write "return totaloffset;" & Vbcrlf
Response.Write "}" & Vbcrlf

Response.Write "function showhide(obj, e, visible, hidden, menuwidth){" & Vbcrlf
Response.Write "if (ie4||ns6)" & Vbcrlf
Response.Write "dropmenuobj.style.left=dropmenuobj.style.top=-500" & Vbcrlf
Response.Write "if (menuwidth!=""""){" & Vbcrlf
Response.Write "dropmenuobj.widthobj=dropmenuobj.style" & Vbcrlf
Response.Write "dropmenuobj.widthobj.width=menuwidth" & Vbcrlf
Response.Write "}" & Vbcrlf
Response.Write "if (e.type==""click"" && obj.visibility==hidden || e.type==""mouseover"")" & Vbcrlf
Response.Write "obj.visibility=visible" & Vbcrlf
Response.Write "else if (e.type==""click"")" & Vbcrlf
Response.Write "obj.visibility=hidden" & Vbcrlf
Response.Write "}" & Vbcrlf

Response.Write "function iecompattest(){" & Vbcrlf
Response.Write "return (document.compatMode && document.compatMode!=""BackCompat"")?" & Vbcrlf
Response.Write "document.documentElement : document.body" & Vbcrlf
Response.Write "}" & Vbcrlf

Response.Write "function clearbrowseredge(obj, whichedge){" & Vbcrlf
Response.Write "var edgeoffset=0" & Vbcrlf
Response.Write "if (whichedge==""rightedge""){" & Vbcrlf
Response.Write "var windowedge=ie4 && !window.o