PDA

Click to See Complete Forum and Search --> : help newbie stumped/perplexed

rigpig
Dec 11th, 2000, 10:46 AM
i cannot get the values of the database to populate based on the selection from the drop down. I have posted some of the code i am using to get the results i am looking for. <SELECT size=10 name=VehicleList onchange="document.frmUsedCars.submit()">
<OPTION>--------------------------------------------- New Listing ---------------------------------------------</OPTION>
<%If Not rsData.BOF Then rsData.MoveFirst
Do Until rsData.EOF%>
<option value="<%=rsData.Fields("CarID")%>"><%=rsData.Fields("Make")%></option>
<%rsData.MoveNext
Loop%>
</SELECT>
<font face=arial size=2>Make</font>&nbsp;<INPUT type="text" name="Make" value=<%if selected then Response.Write("value='" & rsData.Fields("Make") & "'") end if%>>>....this is the code i am using to get the value to populate. where am i making my mistake?
monte96
Dec 12th, 2000, 12:03 AM
Where are you submitting to? Where is the variable selected defined or set? Your code as I am reading it will cause an error because by the time your code gets to the line with the INPUT tag, the rsData.EOF=True.
rigpig
Dec 12th, 2000, 09:57 AM
This is the code that I am trying to execute..it should cycle through the database and load the information based on the first selection (truck,car,suv etc)..the only thing i can test right now is car (there is one car in the dB but it shows it ten times in the listbox when it should only show it once) and secondly when it is clicked the rest of the form should populate to be able to edit
<%
Option Explicit

Dim dbData
Dim rsData
Dim strSQL
Dim rsOptions
Dim CategoryID
Dim VehicleID
Dim numincol
Dim i

If Request.Form("VehicleID") = vbNullString Then
CategoryID = 0
Else
If Request.Form("VehicleID") = 1 Then
CategoryID = 2
ElseIf Request.Form("VehicleID") = 2 Then
CategoryID = 1
ElseIf Request.Form("VehicleID") = 3 Then
CategoryID = 36
ElseIf Request.Form("VehicleID") = 4 Then
CategoryID = 31
ElseIf Request.Form("VehicleID") = 5 Then
CategoryID = 47
End If
End If

If Request.Form("VehicleList") = vbNullString Then
VehicleID = 0
Else
VehicleID = Request.Form("VehicleList")
End If

'Connection to Database
Set dbData = Server.CreateObject("ADODB.Connection")
dbData.Open Application("DSN")

'Connection to Recordsets
strSQL = "SELECT ID, Description FROM used_options"
strSQL = strSQL & " Order by Description"
Set rsOptions = Server.CreateObject("ADODB.Recordset")
rsOptions.Open strSQL, dbData, 1, 3

strSQL = "SELECT * From used_cars"
strSQL = strSQL & " INNER JOIN used_category ON used_cars.CategoryID = used_category.ID"
strSQL = strSQL & " INNER JOIN used_car_options ON used_cars.CarID = used_car_options.CarID"
strSQL = strSQL & " INNER JOIN used_options ON used_car_options.OptionID = used_options.ID"
strSQL = strSQL & " WHERE CategoryID = " & CategoryID 'AND used_cars.CarID = " & VehicleID

Set rsData = Server.CreateObject("ADODB.Recordset")
rsData.Open strSQL, dbData, 1, 3
%>
<html>
<head>
<title></title>
</head>
<form name="frmUsed" action="newlayout.asp" method="post">
<table cellSpacing="0" cellPadding="0" width="750px" border="1" align="center"><tr><td colspan="2">
<table border="0" cellpadding="0" cellspacing="0">
<td colSpan="5"><center>Select the appropriate vehicle type to add to the inventory:</center>
</td><tr>
<table width="594" align="center">
<td><input type="image" onclick="this.form.VehicleID.value=1;" src="../images/used_images/truck1.jpg">Truck</td>
<td>
<input type="image" onclick="this.form.VehicleID.value=2;" src="../images/used_images/car1.jpg">Car
</td>
<td>
<input type="image"onclick="this.form.VehicleID.value=3;" src="../images/used_images/minivan1.jpg">Minivan
</td>
<td>
<input type="image" onclick="this.form.VehicleID.value=4;" src="../images/used_images/blazer1.jpg">Sport Utility
</td>
<td>
<input type="image" onclick="this.form.VehicleID.value=5;" src="../images/used_images/van1.jpg">
<input type="hidden" name="VehicleID">Van
</td>
</tr>
</table>
<br><br>
<center>
<font face="arial" size="5" color="#ff0000"><strong><em>
<%If Request.Form("VehicleID") = 1 Then%>
Trucks
<%ElseIf Request.Form("VehicleID") = 2 Then%>
Cars
<%ElseIf Request.Form("VehicleID") = 3 Then%>
Minivans
<%ElseIf Request.Form("VehicleID") = 4 Then%>
Suv's
<%ElseIf Request.Form("VehicleID") = 5 Then%>
Vans
<%End If%>
</em></strong></font>
</center>
<table border="1" cellpadding="10" cellspacing="0" bgcolor="c0c0c0" bordercolor="#000000" width="594" align="center">
<tr>
<td colspan="2" align="center">
<select size="10" name="VehicleList" onchange="document.frmUsedCars.submit()">
<option>--------------------------------------------- New Listing ---------------------------------------------</option>
<%If Not rsData.BOF Then rsData.MoveFirst
Do Until rsData.EOF%>
<option value="<%=rsData.Fields("CarID")%>"><%=rsData.Fields("Make")%></option>
<%rsData.MoveNext
Loop%>
</select>
</td>
</tr>
<tr>
<td>
<font face="arial" size="2">Make</font>&nbsp;<input type="text" name="Make" value="<%'if selected then Response.Write("value='" & rsData.Fields("Make") & "'") end if%>">&gt; *THIS IS WHERE I AM HAVING GRIEF -- IT WONT POPULATE =( *
</td>
monte96
Dec 12th, 2000, 01:59 PM
1) You need a <BODY> tag

2) You will need to put code in the beginning of your ASP page here that takes the selected item and puts it into a variable that can be displayed later in the page. (this variable would replace the rsdata reference you have in the input tag)

Now, since it looks like you need Make and not CarId later on, that means you have to either look up the make in the database using the CarId and then assign that make text to a variable OR you can store the make in the value as well and separate the two values with a '|' character. Then when you are using the form data at the top of yoru page on a submit, you can parse it with InStr and separate them into two values.

Another option would be (this depends on the amount of data you have here too) to use response.write to put all of your data in your page in javascript arrays that can be accessed on the client side. This eliminates the need for the refresh/submit but if you have a large amount of data, it is not such a good solution because the page will be a very heavy download.

Something else to consider, is that if you are intending to be able to edit this data, you need code that does that as well. You will NOT be able to use the query you have in your code to make a dynamic cursor. You can't do an update on a recordset based on an Inner Join. Only 1 table. The best solution is to use Insert querie(s) to update the data.
rigpig
Dec 13th, 2000, 12:45 PM
Well I appreciate your insight to my problems. The reason we were attempting to try it this way was because the salesmen over in the used car department wanted the page set up similar to the way we have it laid out now but if we are going to be faced with loading times then that really is not that feasible for us.
On a side note how do you create a multiple selection of checkboxes sorted into equal columns with the tags embedded into an existing table.

what i have right now is
<%If Not(rsOptions.EOF and rsOptions.BOF) then rsOptions.MoveFirst
numincol = rsOptions.RecordCount \3+1 (there are 60+options)%>

<%then i connect to the database table in this statement block%>

<%for a = 1 to numincol
If rsOptions.EOF Then Exit For%>
<input type="checkbox" name="<%=rsOptions.Fields("Description")%>" value="<%=rsOptions.Fields("ID")%>"<%if selected then
if not rsOption.EOF then Response.Write(" selected")
end if%>><%=rsOptions.Fields("Description")%>
<%=rsOptions.Fields("Description")%><br>
<%rsOptions.MoveNext
Next%>
<%loop%>
it is displaying all my options but in a very disorganized manner -- if i can somehow get them ordered then I am alright and I am sure the guys can work with the "new design"
monte96
Dec 13th, 2000, 02:32 PM
Put them in a table. Each checkbox has it's own td tag and every 3 iterations add a /tr and new tr tag.
rigpig
Dec 13th, 2000, 02:39 PM
Thanx Monte

After I sent that last messsage the light bulb in my head just went off.. but heres my problem that i should have addressed instead. how to get the values of those checkboxes to be editable (user errors) ie car has power windows and you check that one save it but then realize that it should have been power trunk or whatever else so how do you call the info back to the page so that power windows can be unchecked and the correct option can be checked?
monte96
Dec 15th, 2000, 09:14 AM
Ok.. you can do this by having a separate page for editing records (or you can incorporate it into the same page and have different modes of operation (i.e. Add, Edit/Delete, etc.)

Your submit button for the edit functions would be trapped by an if/then statement in the code at the top that would perform the update on the database with the new data. (Give your edit submit and your add submit different values so you can differentiate which one was clicked on the server side when the page is reloaded and the data is saved)
rigpig
Dec 15th, 2000, 01:45 PM
<!-- #include file = "../include/functions.asp" -->
<%

Dim dbData 'connection to database (ADODB.Connection)
Dim rsData 'recordset to store cars in the dB
Dim strSQL
Dim rsOptions 'car options
Dim CategoryID
Dim VehicleID 'gets proper information related to selection
Dim numincol '
Dim i 'counter
Dim rsCars
Dim selected
<form name="frmUsed" action="newlayout.asp" method="post">
<table cellSpacing="0" cellPadding="0" width=400 border="1" align="center">
<tr>
<td colSpan="5">
<center>Select the appropriate vehicle type to add to the inventory:</center>
</td>
</tr>
<tr>
<td>
<input type="image" onclick="this.form.CategoryID.value=2;" src="../images/masterimages/truck.gif">
<p>Trucks</p>
</td>
<td>
<input type="image" onclick="this.form.CategoryID.value=1;" src="../images/masterimages/corvette.gif">
<p>Cars</p>
</td>
<td>
<input type="image" onclick="this.form.CategoryID.value=36;" src="../images/masterimages/mvan.gif">
<p>Minivans</p>
</td>
<td>
<input type="image" onclick="this.form.CategoryID.value=31;" src="../images/masterimages/blazer.gif">
<p>SUV's</p>
</td>
<td>
<input type="image" onclick="this.form.CategoryID.value=47;" src="../images/masterimages/van.gif">
<input type="hidden" name="CategoryID" value="<%=CategoryID%>">
<p>Vans</p>
</td>
</tr>
</table>
<br><br>
<center>
<font face="arial" size="5" color="#ff0000"><strong><em>
<%If CategoryID = 2 Then%>
Trucks
<%ElseIf CategoryID = 1 Then%>
Cars
<%ElseIf CategoryID = 36 Then%>
Minivans
<%ElseIf CategoryID = 31 Then%>
Suv's
<%ElseIf CategoryID = 47 Then%>
Vans
<%End If%>
</em></strong></font>
</center>
<table border="1" cellpadding="10" cellspacing="0" bgcolor="c0c0c0" bordercolor="#000000" width="400" align="center">
<tr>
<td colspan="2" align="center">
<select size="10" name="VehicleList" onchange="LoadMakes();">
<option value="0" <%If VehicleID = 0 Then Response.Write "selected" End If%>>--------------------------------------------- New Listing ---------------------------------------------</option>
<%
Dim rsListing

strSQL = "SELECT Make, CarID, Year, Model, CategoryID From used_cars"
strSQL = strSQL & " WHERE CategoryID = " & CategoryID
strSQL = strSQL & " ORDER BY Make"
set rsListing=server.CreateObject("ADODB.Recordset")
rsListing.Open strSQL, dbdata, 1, 3
If NOT (rsListing.BOF AND rsListing.EOF) Then rsListing.MoveFirst
do until rsListing.EOF
%>
<option value="<%=rsListing.Fields("CarID")%>" <%If VehicleID = rsListing.Fields("CarID") Then Response.Write "selected" End If%>><%=rsListing.Fields("Year")& " " & rsListing.Fields("Make")& " " & rsListing.Fields("Model") %></option>
<%rsListing.MoveNext
loop
rsListing.Close
set rsListing=nothing
%>
</select>
</td></tr>
<tr><td>
<font face="arial" size="2">Make</font>&nbsp;<input type="text" name="Make" value="<%if selected then Response.Write("value='" & rsListing.Fields("Make") & "'") end if%>"></td>

This command Line should get the value of chevrolet should it not?


<td>
<center>
<font face="arial" size="5" color="#ff0000"><strong><em>Features and Equipment</em></strong></font>
</center>
<table border="1" cellpadding="10" cellspacing="0" bordercolor="#000000" bgcolor="#c0c0c0" WIDTH=400 align="center">
<tr>
<%If Not(rsOptions.EOF AND rsOptions.BOF) Then rsOptions.MoveFirst
numincol = rsOptions.recordcount \3+1
Do Until rsOptions.EOF%>
<td valign="top">
<%for i = 1 to numincol
If rsOptions.EOF Then Exit For%>
<input type="checkbox" name="<%=rsOptions.Fields("Description")%>" value="<%=rsOptions.Fields("ID")%>">
<%=rsOptions.Fields("Description")%><br>
<%rsOptions.MoveNext
Next%>
</td>
<%Loop%>
<input type="submit" value="Submit" name="submit1">
<input type="submit" value="Delete" name="submit2">
<br><br>
</center>
</form>
</td></table>
</body>
</html>
<%
rsOptions.Close
Set rsOptions = Nothing
rsData.Close
Set rsData = Nothing
dbData.Close
Set dbData = Nothing
%>
monte96
Dec 18th, 2000, 09:35 AM
<input type="text" name="Make" value="<%if selected then Response.Write("value='" & rsListing.Fields("Make") & "'") end if%>">
This command Line should get the value of chevrolet should it not?

No.

The problem is that response.write is server side. The 'selected' thing is a problem as well as testing VehicleID because I don't see anywhere that those values are set. If your trying to see if something is selected in a dropdown list, you have to do:

VehicleList.selectedIndex
'which will return the index of the selected item in the dropdown or:
VehicleList.options(VehicleList.selectedIndex).value
'returns the value of the selected item


You need to have code to handle the change in selection to load the new lists. You can't do it on the client side unless you preload ALL data from the database into client side arrays
rigpig
Dec 18th, 2000, 09:50 AM
Where am I to put that code that you have given me? With the way that we have it now, what/where will the problems arise?

Thank you once again for all your help
Clint
monte96
Dec 18th, 2000, 10:04 AM
Can you post the code in your LoadMakes() function?

That seems to be the key as to how to do this. Are you reloading the page when someone changes the selection? You have to remember that any server side code (inside <% %> ) executes only once and executes before anything is sent to the browser. This means that you can't check server side variables that won't get set to anything and alter the display.

You would want to put code above your <HTML> tag that receives the values selected from the form when it is submited (or the page is refreshed) and puts them into variables you can use later in the page.
rigpig
Dec 18th, 2000, 10:11 AM
So in other words you mean the loadmakes function would look something like this
//Load Models
function LoadMakes()
{
document.frmUsed.submit();
VehicleList.selectedIndex
'which will return the index of the selected item in the dropdown or:
VehicleList.options(VehicleList.selectedIndex).value
'returns the value of the selected item
return;
}

And to answer the question if the page is being refreshed yes it is. As the user selects the car they want to edit the page refreshes and the values are populated into the form.

if you wish to view the page www2.shaganappi.com/pages/newlayout.asp and that will give you a clearer idea of what I am trying to accomplish (knock on wood).

Take Care
Clint