ASP Error 500 1

[hodgesp]

I'm trying to creat a dropdown box by connecting to a database and getting the information from a column in a table. The problem is, ASP doesn't seem to see my connection. I used the same connection setup that I use on a browse page where it seems to work. However, when I attempt to utilize it to populate a dropdown box object I get nothing. What am I missing? Here is my Code:

<% ' 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, cn, DBConn,CTSNum,TheCount,MyNumb,CoorNumb,ComCode

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

StrConn = sDriver & sServer & sDsn & sUid & sPass & sDB
Set dbConn = Server.CreateObject("ADODB.Connection")
dbConn.Open (StrConn)
'----------------------------------------------------------------------------------------------------------------
Set RS1 = Server.CreatObject("ADODB.Recordset")
dbConn.Open StrConn
RS1.Open SQLStr
Set objASPErr = Server.GetLastError()
'Set RS1 = Application("dbConn").execute(SQLStr)
'----------------------------------------------------------------------------------------------------------------
if Err.number <> 0 then
Response.Write "<br>"&"Connection Error:"
Response.Write "<b>ASP Code:</b> " & objASPErr.ASPCode & "<br><br>"
Response.Write Err.Number
else
Response.Write "<br>"&"Dept:" &"<SELECT NAME=""CogDept"" STYLE=""WIDTH: 100PX"">"
WHILE NOT RS1.EOF
RESPONSE.Write "<option value='"&RS1.FIELDS("CogDeptCode")&"'>"&RS1.FIELDS("CogDeptCode")
RS1.MoveNext
WEND
end if
Response.Write "</SELECT>"
%>

 

[williamsba]

Post your error code. Also look at this:

RESPONSE.Write "<option value='"&RS1.FIELDS("CogDeptCode")&"'>"&RS1.FIELDS("CogDeptCode")

Your not closing your <option> tag, so do this:

RESPONSE.Write "<option value='"&RS1.FIELDS("CogDeptCode")&"'>"&RS1.FIELDS("CogDeptCode")&"</option>"

Brad Williams
Webmaster

 

[hodgesp]

Connection Error:500

this is the error I'm getting. I added the </option> as you suggested , however it made no differnce in my page. For some reason, The DropDown box doesn't even show up, becuase of the If statement. But prior to placing the If error statement the page would just lock up. I was getting error 424 then it transfomed into 500. I am very new to ASP and am trying to learn it by the seat of my pants. Any suggestion or help is Greating Appreciated.

Thank You Very Many!

 

[DreXor]

error 500's are not fun to slueth out, and half the time most people dont even know what they did to fix it

try removing the on error resume and see if that will throw you a more "focused" error to work from

 

[ralphtrent]

1.why are you doing this:
Set RS1 = Server.CreatObject("ADODB.Recordset")
dbConn.Open StrConn

You already have this opened above this code.

2.What does the SQLStr equate to?

3.Also if CogDeptCode is the name of the field you are going after then just to do this: RS1("CogDeptCode")

 

[DreXor]

you can significantly shorten this block ...

Set RS1 = Server.CreatObject("ADODB.Recordset")
dbConn.Open StrConn
RS1.Open SQLStr
Set objASPErr = Server.GetLastError()
'Set RS1 = Application("dbConn").execute(SQLStr)



down to :


dbConn.Open StrConn
Set RS1 = dbConn.Execute(SQLStr)

 

[hodgesp]

I modified the Code and am no longer getting error 500, however now error 13. I made sure that there was a dns on the server that the page resides on but still the drop down box has no values listed in it. here is my code, I modified it based on previous suggestions.


<% ' 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=Roswell;"
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>"

%>

 

[ralphtrent]

Why are you Response.Write dbConn &"<br>".
Have you verified that you can connect to the database. Meaning taking out all code other then you connectivity code. Are you getting an error number 13 or line number 13? I never heard of an error code 13 before, thats why I ask.

 

[DreXor]

looks like you're trying to response out your results, this should make a nice little table for you

replace this out :

dbConn.Open StrConn
Set RS1 = dbConn.Execute(SQLStr)
response.Write RS1 &"<br>"
Set objASPErr = Server.GetLastError()

with :

response.write "<table>" & vbcrlf
dbConn.Open StrConn
Set RS1 = dbConn.Execute(SQLStr)
if not rs1.eof then
  Response.write "<tr>" & vbcrlf
  for each field in rs1.fields
    response.write "<td>" & Server.HTMLEncode(field.name) & "</td>" & vbcrlf
  next
  Response.write "</tr>" & vbcrlf
  do while not rs1.eof
    Response.write "<tr>" & vbcrlf
    for each field in rs1.fields
      response.write "<td>" & Server.HTMLEncode(rs1(field.name)) & "</td>" & vbcrlf
    next
    Response.write "</tr>" & vbcrlf
    rs1.movenext
  loop
Else
  response.write "<tr><td>no data</td></tr>" & vbcrlf
End If
response.write "</table>" & vbcrlf
Set objASPErr = Server.GetLastError()