ASP:Error:ADODB.Recordset (0x800A0CC1 [Archive]

BeginnerASP

04-07-2007, 01:41 AM

i am a beginner at ASP and am gettingfollowing error message when i am running the code:
<html>
<body>
<%
Set Conn=Server.CreateObject("ADODB.Connection")
Conn.Provider="SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=k****iz;Data Source=indpc007"
Conn.open
Set rs=Server.CreateObject("ADODB.Recordset")
sql="SELECT DISTINCT country FROM kshtemp"
Sql="SELECT DISTINCT age FROM kshtemp"
Sql="SELECT DISTINCT height FROM kshtemp"
rs.Open sql,Conn
country=request.form("country")
age=request.form("age")
height=request.form("height")
%>

<form method="post">
choose country <select name="country">
<% do until rs.EOF
response.write("<option")
if rs.fields("country")=country then response.write("selected")
response.write(">")
response.write(rs.Fields("country"))
rs.MoveNext
loop
rs.close
set rs=Nothing %>
</select>
choose age <select name="age">
<% do until rs.EOF
response.write("<option")
if rs.fields("age").age then response.write("selected")
resp.write(">")
response.write(rs.Fields("age"))
rs.MoveNext
loop
rs.close
set rs=Nothing %>
</select>
choose height <select name="height">
<%
do until rs.EOF
response.write("<option")
if rs.fields("height").height then response.write("selected")
resp.write(">")
response.write(rs.Fields("height"))
rs.MoveNext
loop
rs.close
set rs=Nothing %>
</select>
<input type="submit" value="show persons"
</form>
<%
if country<>"" then
sql="SELECT * FROM kshtemp WHERE country='"&country&"'"
Set rs=Server.CreateObject("ADODB.Recordset")
rs.Open sql,conn %>
<h3>Kshtemp Table</h3>
<table border="1" width="100%">
<tr>
<%
for each x in rs.Fields
response.write("<th>"&ucase(x.name)&"</th>")
next
%>
</tr>
<%
do until rs.EOF %>
<tr>
<form method="post" action="assign4.asp">
<%
for each x in rs.Fields
if lcase(x.name)="companyid" then %>
<td>
<input type="submit" name="companyid" value="<%x.value%>">
</td>
<%else%>
<td>
<%Response.Write(x.value)%></td>
</form>

<%end if%>
<% rs.MoveNext %>
</tr>
<%
next
loop
end if
rs.close
conn.close
%>
</table>
</body>
</html>

Can Anyone please help

buntine

04-07-2007, 03:02 AM

Just looking at the first few lines I can see your code is illogical. You are replacing the value of your variable three times in a row.

What you need to do is create one SQL query and execute it. See below:

Set Conn=Server.CreateObject("ADODB.Connection")
Conn.Provider="SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=k****iz;Data Source=indpc007"
Conn.open
Set rs=Server.CreateObject("ADODB.Recordset")
sql="SELECT DISTINCT country, age, height FROM kshtemp"
rs.Open sql,Conn
country=request.form("country")
age=request.form("age")
height=request.form("height")

It is always good practise to dimension all variables and keep your code indented, etc.

Andrew Buntine.