Displaying value in dropdown thats been loaded from the database 1

[jakeyg]

I've got the following combo box on my form
<select size="1" name="Location">
<option value="UK"> UK </option>
<option value="Europe"> Europe </option>
<option value="Americas"> Americas </option>
</select>

I've loaded the recordset fine, but how do I get this dropdown to display the contents of the "Location" field? A fancy bit of Javascript or ASP or a stupidly simple line of HTML?

thanks

 

[lastdonuk]

jakeyg,

Well, this is maybe not the cleanest way to do it, but here's the code I'm using (the ASP is written in VBScript) to do what you want to do...I've called the field name with 'vchLocation', etc, so you'll need to replace the table name with your table name.
Also, you need the connection string, I've included all of it here without the SQL Server DB name, userid or password - not a good idea to show the ip address, server details, etc on your asp page, the strConnect line is stored in an #include file on our webserver.

<select name="location">
   <option value="<%Response.Write(location)%>" selected="selected">
<%Response.Write(location)%>
   </option>
<%
strConnect = "Provider=SQLOLEDB; Data Source=IPADRESS; Initial Catalog=DBNAME; User ID=USERID; Password=PWORD; Network Library=dbmssocn;"	
	
Set conn=Server.CreateObject("ADODB.Connection")
conn.Open strConnect
Set objrsocc = Server.CreateObject("ADODB.Recordset")
strConnect = "SELECT [vchLocation] FROM [TABLE]"
objrsocc.Open strConnect, conn					

Do While Not objrsocc.eof
Response.Write "<option value= '" &                  objrsocc("vchLocation") & "'>"					Response.Write objrsocc("vchLocation") & "</option>"
Response.Write location = objrsocc("vchLocation")	objrsocc.MoveNext 
loop%>

</option>
</select>

Hope this helps you out, let me know if my explanation is unreadable and I'll try to simplify...

cheers,
rob

 

[jakeyg]

Sorry, explained that wrong by the looks of it, wouldn't be the first time :-(

I've hardcoded the values into the list (UK, Europe, etc.) using HTML
<option value="Europe"> Europe </option>

Location is a field in my UserDetails table
I need the value in that Location field displaying/selecting in the combo box

 

[lastdonuk]

No worries, I misunderstood too...do you want to insert this hard-coded value into the DB? Or do you want to display the contents of the location field in the dropdown? Or something else?

Have you got a url so we can see the form?

cheers,
rob

 

[jakeyg]

Or do you want to display the contents of the location field in the dropdown

That one
I've got the save from combobox to database part working

The current URL is

http://www.auction.co.uk/asp/Register.asp

That's got the 2 combos and the option button I need to load from the database when users want to edit their details

Don't want to have to a case statement and repeat a load of HTML code if I can help it.

 

[Sheco]

If you have a hard coded list and one of the things in the list is the value of the database field... and you want it to be the selected value...


OK, suppose the field value is Europe, you want the HTML output of your ASP to look like this:

<select size="1" name="Location">
  <option value="UK">          UK          </option>
  <option value="Europe" [red]selected[/red] >      Europe      </option>
  <option value="Americas">    Americas    </option>
</select>

So, to get there with ASP you might have something like this:

<%
\ '... your other database code is here ...

MyLoc = MyRecordset("MyField")
%>

<select size="1" name="Location">
  <option value="UK" [highlight]<% IF (MyLoc = "UK") THEN Response.Write " selected "%>[/highlight]>UK</option>
  <option value="Europe" [highlight]<% IF (MyLoc = "Europe") THEN Response.Write " selected "%>[/highlight]>Europe</option>
  <option value="Americas" [highlight]<% IF (MyLoc = "Americas") THEN Response.Write " selected "%>[/highlight]>Americas</option>
</select>

 

[lastdonuk]

I agree with and like Sheco's explanation, far more clear and concise than I could explain it...

I don't know if it's any use to you - it's untested - but here's your page code with my additions, hopefully I'm not muddying the water:

<!--- #include file= "includes/connectionstring.asp" -->
<html>
<head>

<title>Allsop &amp; Co Registration</title>

  <link REL="stylesheet" TYPE="text/css" HREF="../css/styleR.css">

</head>
<body>
<script LANGUAGE="JavaScript">
<!--
function VerifyData(frmName)
{
  var sEmail,sFind,iGSpace,iGAmp,DataChecked;
  DataChecked = frmName.DataProtection.checked

  if (frmName.username.value == "")
  {
    alert("Please enter your \"Name\".");
    frmName.username.focus();
    return(false);
  }

  sEmail = frmName.email.value;
  if (sEmail == "")
  {
    alert("Please enter a value for the \"E-mail\" field.");
    frmName.email.focus();
    return (false);
  }
  else
  {
    sFind = " ";
    iGSpace = sEmail.search(sFind);
    sFind = "@";
    iGAmp = sEmail.search(sFind);
    if (iGSpace != -1 || iGAmp == -1)
    {  
      alert("Please check the E-mail address, it is invalid.");
      frmName.email.focus();
      return (false);
    }
  }


  if (DataChecked)
  {
  }
  else
  {
    //alert("Sorry Unless you agree to the Data Protection Act we can not register you.");
    //return(false);
  }


  return(true);
  
}
-->
</script>

<%
Dim conn 
Dim strConnect
Dim location

'use this OR preferably include it in the #includes file - see line 1 above
strConnect = "Provider=SQLOLEDB; Data Source=yourIPaddress; Initial Catalog=yourDBname; User ID=yourSQLusername; Password=yourSQLpassword; Network Library=dbmssocn;"

Set conn=Server.CreateObject ("ADODB.Connection")
conn.Open strConnect

'try this if the previous ASP got here by: memberregister.asp?id=123
id = Request.QueryString("userid")
'or this if you passed it thru as a hidden input field on the previous submission. 
id = Request.Form("userid")

Set objrsfin = Server.CreateObject ("ADODB.recordset")  
	objrsfin.Open "UserDetails", strConnect,2,3,2
	objrsfin.find  "userid ="&id
	
	'pass the value you want to show into a variable
	objrsfin("location") = location
%>

<table border="0" cellPadding="1" cellSpacing="1" style="WIDTH: 550px" width="550">

  <tr>
   <td colspan=2>
     <H1> Welcome to the Allsop &amp; Co online auction catalogue </H1>
     <H5> Complete the form below to register and receive the following benefits :-</H5>
     <ol>
      <li>

                    Notification of availability of online catalogue by email.
      </li>
      <li>
                    Access to the <font color="#2D5E86"><b>online catalogue</b></font> before the paper catalogue is released.
      </li>
      <li>
                    Receive <font color="#2D5E86"><b>guide prices</b></font> and <font color="#2D5E86"><b>results</b></font> automatically by e-mail
      </li>

      <li>
                    Perform and save search criteria on an Auction by Auction Basis.  
      </li>
     </ol>
    </td>
   </tr>

<form method="post" action="memberregister.asp" onSubmit="return VerifyData(this)">
   <tr>
    <td> Name </td>

    <td> <input name="username" size="40" > </td>
   </tr>

   <tr>
    <td> E-mail </td>
    <td> <input name="email" size="40" > </td>

   </tr>

   <tr>
    <td> I am based in </td>

    <td>
    	<select size="1" name="Location">
			<!--this will show the saved data first (selected), it will be duplicated if this value exists in the hard coded list, eg. if it's 'UK'-->
			<option value="<%Response.Write(location)%>" selected="selected"><%Response.Write(location)%></option>				      
			<!--comment ends-->  
			<option selected value="UK">UK</option>
			<option value="Europe">Europe</option>
			<option value="Middle East">Middle East</option>
			<option value="Africa">Africa</option>
			<option value="Asia">Asia</option>
			<option value="Far East">Far East</option>
			<option value="Americas">Americas</option>
			<option value="Australasia">Australasia</option>
		 </select>
    </td>
   </tr>

   <tr>
    <td> I am a </td>
    <td>

     <select size="1" name="buyertype">            
      <option selected value="Private">Private Investor</option>
      <option value="Corporate">Corporate Investor</option>
      <option value="Agent">Agent / Intermediary</option>
     </select>
    </td>
   </tr>

   <tr>
    <td> I am Interested in </td>
    <td>
     <input type="radio" name="propertytype" Value="C"> &nbsp;Commercial&nbsp;
     <input type="radio" name="propertytype" Value="R"> &nbsp;Residential&nbsp;
     <input type="radio" name="propertytype" Value="B" checked> &nbsp;Both&nbsp;

    </TD>
   </tr>

   <tr>
    <td colspan = 2>
       If you <u>do not</u> wish Allsop & Co to pass your contact details to Allsop Group Companies and <u>do not</u> wish those group companies to provide you with more information about investing in property tick the box  
       <input type="checkbox" name="DataProtection" value="ON">

    </td>
     </tr>
    </table>
    <input type="image" src="../imagesx/Buttons/Register.gif" value="Register Now" id="submit1" name="submit"> 
   </FORM>
  </td>
 </tr>
</table>


<script LANGUAGE="JAVASCRIPT" TYPE="TEXT/JAVASCRIPT">
//<!-- Cloak
//{
//  InfoWindow = window.open('regist.htm','InfoWin','scrollbars=yes,width=400,height=300');
//}
// un-cloak -->

</script>
</body>
</html>

cheers,
rob

 

[jakeyg]

Total genius I can use that method in about a dozen places to solve a load of other problems too

Cheerz guys