Help With Simple ASP Query 1

phzero · Feb 25, 2003

Hi all, below please find my source code for my ASP page. What I'm trying to do is to populate the dropdown box called "ProbCat" in the form. All the code works. If I comment out the document... line and put it in a MsgBox, it displays the names from the database. Could anybody please tell me what I'm doing wrong. Any comments would be appreciated.

<HTML>
<HEAD>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<meta http-equiv="Pragma" content="no-cache">
<meta http-equiv="cache-control" content="private, no-cache, must-revalidate">
<link rel="stylesheet" href="styles/global.css" type="text/css">

<script language="vbscript">
sub GetData()
dim db, rs
set db = CreateObject("adodb.connection&quot

set rs = CreateObject("adodb.recordset&quot

db.Provider = "sqloledb"
db.ConnectionString = "driver={SQL Server};server=127.0.0.1;uid=gino;pwd=global;database=MyWebDatabase;"
db.Open
set rs = db.Execute("select * from GuestInfo&quot

rs.MoveFirst
while not rs.EOF
document.LogIncident.ProbCat("mother&quot

.add rs("name&quot

rs.MoveNext
wend
rs.Close
db.Close
end sub
</script>

</HEAD>
<BODY>
<form name="LogIncident" method="post" action="LogIncident.asp">
<table>
<tbody>
<tr><td colspan=2><h1>Please Log Your Request</h1></td></tr>
<tr><td colSpan=2><h4>Incident Category:</h4></td></tr>
<tr>
<td class="label">Problem Category:</td>
<td>
<select name="ProbCat">
<option name="mother">
</select>
</td>
</tr>
<tr><td> </td></tr>
</tbody>
</table>
<input class="button" value="Click me" onclick="GetData()">
</form>
</BODY>
</HTML>

arst06d · Feb 25, 2003

you are trying to populate the data when the user clicks the button

populate it before you serve the page

<select name="ProbCat">
<% GetData %>
</select>

and in the procedure:

while not rs.EOF
response.write "<Option value=" & rs("name&quot

& ">" & rs("name&quot

& "</option>"
rs.MoveNext
wend

JamesLean · Feb 25, 2003

The first problem I notice with your script is that it is all being performed client-side. Is that really what you want (the user will be able to view all your connection information, including username/password)?!

Also, I don't quite understand the point of only filling in the select list when the user clicks the button?

So, firstly, put your ASP code between ASP delimiters <% %>. Then get rid of the sub and just output the select list when you load the page:

Code:

<%
'open connection

strSQL = &quot;SELECT col1 FROM table1&quot;

Set rs = objConn.Execute(strSQL)
%>

...
<select name=&quot;probcat&quot;>
<%
Do Until rs.EOF
  Response.Write &quot;<option value='&quot; & rs(&quot;col1&quot;) & &quot;'>&quot; & rs&quot;col1&quot;) & &quot;</option>&quot;

  rs.MoveNext
Loop
%>
</select>
...

<%
'close rs and conn
%>

--James

JamesLean · Feb 25, 2003

Just some more advice, whenever I have to build an option list like this, I use the getstring() method. It can perform better and also allows you to close you db connections as quickly as possible:

Code:

<%
'open conn

Set rs = objConn.Execute(strSQL)

strPrefix = &quot;<option value='&quot;
strMidfix = &quot;'>&quot;
strSuffix = &quot;</option>&quot;

strOptions = rs.GetString(,,strMidfix,strSuffix & strPrefix)
strOptions = strPrefix & Left(strOptions, Len(strOptions) - Len(strPrefix))

'we can now close the recordset and connection as we have the data in our string variable
%>

...
<select name=&quot;probcat&quot;><%= strOptions %></select>
...

You can view a more detailed breakdown of the getstring() method here:

http://www.devguru.com/Technologies/ado/quickref/recordset_getstring.html

--James

phzero · Feb 25, 2003

You guys are the best. Thanks for all the comments and advice. All is much appreciated. may you all have a great day. Cheers,

BG12424 · Feb 28, 2003

I use this class to optimize my string concatenations.

Code:

'********************************************************************************
' START STRING OPTIMIZER CLASS
'********************************************************************************

' PURPOSE: 	This is a string optimization class when working with large
'						string concatenations. This class will dramatically speed
'						the process of concatenating strings.
' 			
' ABOUT: 	
'			VB and VBScript have intrinsic array functions such as JOIN that are, 
'			MUCH FASTER at concatenating variant array elements. This class keeps our 
'			string information in an array (its always a Variant anyway, so what's the 
'			difference?) and then when we're finished with all of our concatenations we 
'			have it just do a JOIN on the array with NO DELIMITER so it all comes back 
'			as ONE BIG LONG STRING, in a SINGLE OPERATION.
'
' USAGE:	
'			Set objStrOpt = New StringOptimizer
'			objStrOpt.Reset
'			objStrOpt.Append &quot;String1&quot;
'			objStrOpt.Append &quot;String2&quot;
'			objStrOpt.Append &quot;Stringx&quot;
'			Response.write(objStrOpt.Concat)
Class StringOptimizer
	Dim stringArray,growthRate,numItems

	Private Sub Class_Initialize()
		growthRate = 50: numItems = 0
		ReDim stringArray(growthRate)
	End Sub

	Public Sub Append(ByVal strValue)
		' next line prevents type mismatch error if strValue is null. Performance hit is negligible.
		strValue=strValue & &quot;&quot;
	
		If numItems > UBound(stringArray) Then ReDim Preserve stringArray(UBound(stringArray) + growthRate)
		stringArray(numItems) = strValue: numItems = numItems + 1

	End Sub

	Public Sub Reset
		Erase stringArray
		Class_Initialize
	End Sub

	Public Function Concat() 
		Redim Preserve stringArray(numItems) 
		Concat = Join(stringArray, &quot;&quot;)
	End Function

End Class

_______________________________
regards,
Brian
AOL IM: FreelanceGaines

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Help With Simple ASP Query 1

phzero

Programmer

arst06d

Programmer

JamesLean

Programmer

JamesLean

Programmer

phzero

Programmer

BG12424

Programmer

Similar threads

Part and Inventory Search

Sponsor