drop down list in ASP with DSN-less connection

SamirKumar
SamirKumar used Ask the Experts™
on
Hello ASP Experts,

I am trying to create an asp page where users can submit the type of access they are requesting.  I am having problems with the access type field cause I need to reach out to a mdb database to populate the drop down.  I did some research and tried it myself but the page does not load.  My asp page is pasted below and NEED SOME HELP!  Thanks and it has to be dsn-less.  If you could modify my code or explain how you did it and where my errors are that would be great!  Thanks

Samir


----------------------------------------------------------



<html>
<head><title>Add User Page</title>
</head>
<p align="center"><img border = "0" src="\\PAERSCX2101\PRUBC\LOADTEST\LongLogo.gif"width="555" height="88"></p>
<p align="center"><b><font face="Verdana" color="#3165CE" size="4">Business Continuation Event Recovery Status</font></b></p>
<p align="center"><b><font face="Times New Roman" color="Black" size="4">Add New User Screen</font></b></p>


     <form name="Search" method="Post" action="addtodatabase.asp">
     
     <TABLE Border = 0 Align = "Center" Width = 40% BGCOLOR = "White">
<tr>
     <td align = "right"><b>UserName:</b></td>
     <td><input type="text" name="txtusername" size="20"></td></Tr>


     <td align = "right"><b>Password:</b></td>
     <td><input type="text" name="txtPassword" size="20"></td></tr>

     <td align = "right"><b>Access Type:</b></td>
     <td><select name = "AccessType" size = "1">
          <option value=PS>Please Select</OPTION>

     <%
     strSQL = "SELECT accesslevel FROM tblLoginInfo " 
         

     Set cnnLogin = Server.CreateObject("ADODB.Connection")
     cnnLogin.Open("DRIVER={Microsoft Access Driver (*.mdb)};" _
          & "DBQ=" & Server.MapPath("login.mdb"))

     Set rstLogin = cnnLogin.Execute(strSQL)

     Do While Not rstLogin.EOF
            Response.Write "<Option value='" & rstLogin("accesslevel") & "'>" & _
            rstLogin("accesslevel") & "</option">
            rstLogin.MoveNext
     Loop

Response.write "</select>"
rstLogin.Close
Set rstLogin = Nothing
cnnLogin.Close
Set cnnLogin = Nothing
%>
td><Center><input type="submit" VALUE="Login" /></td></Center>
</form>
</table>
</BODY>
</HTML>
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
can you post your errors, please?

Commented:
Try this one....

I think how you write the variable coming from the database that causes tha error...

========================
<html>
<head><title>Add User Page</title>
</head>
<p align="center"><img border = "0" src="\\PAERSCX2101\PRUBC\LOADTEST\LongLogo.gif"width="555" height="88"></p>
<p align="center"><b><font face="Verdana" color="#3165CE" size="4">Business Continuation Event Recovery Status</font></b></p>
<p align="center"><b><font face="Times New Roman" color="Black" size="4">Add New User Screen</font></b></p>


    <form name="Search" method="Post" action="addtodatabase.asp">
   
    <TABLE Border = 0 Align = "Center" Width = 40% BGCOLOR = "White">
<tr>
    <td align = "right"><b>UserName:</b></td>
    <td><input type="text" name="txtusername" size="20"></td></Tr>


    <td align = "right"><b>Password:</b></td>
    <td><input type="text" name="txtPassword" size="20"></td></tr>

    <td align = "right"><b>Access Type:</b></td>
    <td><select name = "AccessType" size = "1">
         <option value=PS>Please Select</OPTION>

    <%
    strSQL = "SELECT accesslevel FROM tblLoginInfo " 
         

    Set cnnLogin = Server.CreateObject("ADODB.Connection")
    cnnLogin.Open("DRIVER={Microsoft Access Driver (*.mdb)};" _
         & "DBQ=" & Server.MapPath("login.mdb"))
 
    Set rstLogin = cnnLogin.Execute(strSQL)

    Do While Not rstLogin.EOF
     %>
           <Option value="<%=rstLogin("accesslevel")%>"><%=rstLogin("accesslevel") %></option>
     <%      rstLogin.MoveNext
    Loop
%>
</select>
      <%
rstLogin.Close
Set rstLogin = Nothing
cnnLogin.Close
Set cnnLogin = Nothing
%>
      <td><Center>
      <input type="submit" VALUE="Login" /></Center></td>

</table>
</form>
</BODY>
</HTML>

=============================

Hope this help...

Happy programming....
Commented:
So I found two problems with the code, when I tried to run it on my server. One was the connection string. When I changed to the OLEDB driver it worked fine, as below:

cnnLogin.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("login.mdb")

I couldn't really find anything wrong with your statement though, it just wouldn't work, so maybe our server setup is different. This connection string though is "Better" according to Microsoft.

The other thing was this statement
Response.Write "<Option value='" & rstLogin("accesslevel") & "'>" & _
           rstLogin("accesslevel") & "</option">

should be
Response.Write "<option value='" & rstLogin("accesslevel") & "'>" &_
                  rstLogin("accesslevel") & "</option>"

the end quotation was misplaced...



Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Author

Commented:
drain,

is the connection able to be used on dsn-less connections?

cause the connection i have worked on another page for me.  maybe it is the missing "".  i will try tomorrow. but let me know if that connection is dsn-less

the error message i get is that page can not be displayed.

samir

Commented:
SamirKumar,

Have you tried the comment that I have posted... I dont here any acknowledge from you... what is the result?

BTW it is better to write it this way... It is much easier to understand... and avoid confusion as well...

<Option value="<%=rstLogin("accesslevel")%>"><%=rstLogin("accesslevel") %></option>
   

Try this link regarding the DSN Less connection

http://www.4guysfromrolla.com/webtech/070399-1.shtml

http://www.able-consulting.com/ADO_Conn.htm

Happy programming...

Good night...

Commented:
yeah, the connection string is DSNless as well

Commented:
SamirKumar,

There is no problem with your connection as I have pointed out.

I think how you write the variable coming from the database on the option tag that causes the error that causes tha error...

Response.Write "<Option value='" & rstLogin("accesslevel") & "'>" & _
           rstLogin("accesslevel") & "</option">

especially the end tag

"</option">

should be

"</option>"

Writing this way is to confusing...

I replace it with this

<Option value="<%=rstLogin("accesslevel")%>"><%=rstLogin("accesslevel") %></option>


Please give some feedback...

HTH...

HAppy programming...



           

Author

Commented:
Good job Drain,

My script was perfect...I messed up the "" by option.

Thanks
Samir

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial