No Option Appear in my <select> Box 1

[hodgesp]

I have this code that Im trying to use to populate a drop down box. The problem is that it doesn't list any of the values from the field Im Connecting too. I get an error code 13. What am I missing???


<% ' This select option object allows for cogdept choices in a drop down box

'On Error Resume Next
DIM sDriver, sServer, sDsn, sUid, sDB, StrConn, sPass, dbConn, SQLStr, RS1,objASPErr
dim CTSNum,TheCount,MyNumb,CoorNumb,ComCode


SQLStr="select CogDeptCode from tblCogDept order by CogDeptCode"
response.Write "<br>"&SQLStr&"<br>"

sDriver = "DRIVER={SQL Server};"
sServer = "SERVER=ServerName;"
sDsn = "DSN=CTS2000;"
sUid = "UID=sa;"
sPass = "PWD=password;"
sDB = "DATABASE=WIPPCTS2000;"

StrConn = sDriver & sServer & sDsn & sUid & sPass & sDB
response.Write "<br>"& StrConn &"<br>"

Set dbConn = Server.CreateObject("ADODB.Connection")
Response.Write dbConn &"<br>"

'----------------------------------------------------------------------------------------------------------------
dbConn.Open StrConn
Set RS1 = dbConn.Execute(SQLStr)
response.Write RS1 &"<br>"
Set objASPErr = Server.GetLastError()
'----------------------------------------------------------------------------------------------------------------
if Err.number <> 0 then
Response.Write "<b>ASP Error Number: "& Err.Number &"<br>"
Response.Write objASPErr
else

Response.Write "<br>"&"Dept:" &"<SELECT NAME=""CogDept"" STYLE=""WIDTH: 100PX"">"
WHILE NOT RS1.EOF
RESPONSE.Write"<option value='"&RS1("CogDeptCode")&"'>"&RS1("CogDeptCode")
RS1.MoveNext
WEND
end if
Response.Write "</SELECT>"

%>



Any help will be appreciated, Thank You!

PHH.

 

[BoulderBum]

If you don't get an answer here, you may want to try the plain old ASP forum. This isn't ASP.NET stuff.

 

[meckeard]

A few things come to mind:

1) I see that you are response.write'ing you sql string. Run it in access or sql and make sure you are actually getting records back.

2) Check the recordset BEFORE you loop thru it. Like this:

Response.Write "<br>"&"Dept:" &"<SELECT NAME=""CogDept"" STYLE=""WIDTH: 100PX"">"
If Not RS1.BOF and Not RS1.EOF Then
'We have some records. Display them
Do Until RS1.EOF
Response.Write "<option value=" & RS1("CogDeptCode")& ">" & RS1("CogDeptCode") "</option>"
RS1.MoveNext
Loop
Else
'No records. Write a default option entry.
Response.Write "<option value=""0"">No DB values to write</option>"
End If
Response.Write "</SELECT>"

This code is untested, but it should help.

Mark