CODEWITHSUNDEEP
asp-classic
Rahul Desai
Rahul Desai

Reputation: 15501

unable to execute query in ASP

Following is the ASP code that I am working on:

Dim sConnString, connection, sSQL, backgroundColor
sSQL = "SELECT firstName, lastName, email, zip, country, company, industry, revenue, timestamp FROM users"
sConnString=//a connection string
Set connection = Server.CreateObject("ADODB.Connection")
connection.Mode=adModeRead
connection.Open(sConnString)
connection.execute(sSQL)

Response.Write"<table>"
do while not connection.EOF
    if(backgroundColor="#f1f1f1")then
        backgroundColor="#ffffff"
    else
        backgroundColor="#f1f1f1"
    end if

    response.write"<tr backgroundColor="& backgroundColor & "></td>" & connection("firstName") & "</td><td>" & connection("lastName") & "</td><td>" & connection("email") & "</td><td>" & connection("zip") & "</td><td>" & connection("country") & "</td><td>" & connection("company") & "</td><td>" & connection("industry") & "</td><td>" & connection("revenue") & "</td><td>" & connection("timestamp") & "</td></tr>"

    connection.MoveNext
Loop
Response.Write"</table>"

connection.Close
Set connection = Nothing

I am getting the following error:

ADODB.Connection error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/wsu.asp, line 13

Line 13:

do while not connection.EOF

Could you please help me with this?

Updated code:

<html>
<body>
<%
Dim sConnString, connection, sSQL, backgroundColor
sSQL = "SELECT firstName, lastName, email, zip, country, company, industry, revenue, timestamp FROM users"
sConnString="a connection string for MS SQL database;"
Set connection = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")
connection.Mode=adModeRead
rs.open sSQL,connection

Response.Write"<table>"
do while not connection.EOF
if(backgroundColor="#f1f1f1")then
backgroundColor="#ffffff"
else
backgroundColor="#f1f1f1"
end if

response.write "<tr backgroundColor="& backgroundColor & "></td>" & connection("firstName") & "</td><td>" & connection("lastName") & "</td><td>" & connection("email") & "</td><td>" & connection("zip") & "</td><td>" & connection("country") & "</td><td>" & connection("company") & "</td><td>" & connection("industry") & "</td><td>" & connection("revenue") & "</td><td>" & connection("timestamp") & "</td></tr>"

connection.MoveNext
Loop
Response.Write"</table>"

connection.Close
Set connection = Nothing
%>
</body>
</html>

Upvotes: 0

Views: 3054

Answers (2)

Jatin
Jatin

Reputation: 1708

See the following edit in your code - you create a recordset and then open it and execute it

<html>
<body>
<%
Dim sConnString, connection, sSQL, backgroundColor,objrs
sSQL = "SELECT firstName, lastName, email, zip, country, company, industry, revenue, timestamp FROM users"    
Set connection = Server.CreateObject("ADODB.Connection")
connection.connectionstring = "put your connection string in here"
' replace put your connection string in here with your connection string
Set objrs= Server.CreateObject("ADODB.Recordset")   
connection.Open()
objrs.open sSQL,connection

Response.Write"<table>"
do while not objrs.EOF
if(backgroundColor="#f1f1f1")then
backgroundColor="#ffffff"
else
backgroundColor="#f1f1f1"
end if

response.write"<tr backgroundColor="& backgroundColor & "></td>" & objrs("firstName") & "</td><td>" & objrs("lastName") & "</td><td>" & objrs("email") & "</td><td>" & objrs("zip") & "</td><td>" & objrs("country") & "</td><td>" & objrs("company") & "</td><td>" & objrs("industry") & "</td><td>" & objrs("revenue") & "</td><td>" & objrs("timestamp") & "</td></tr>"

objrs.MoveNext
Loop
Response.Write"</table>"
%>
</body>
</html>

a note:--

this needs to be something like - not sure what database you are using - 

 sConnString= "Provider=sqloledb;Data Source=ServerName;Initial Catalog=DatebaseName "

or if you have a dsn connection then simply

sConnString = "DSN=xxxxx;uid=xxx;pwd=xxx"

Upvotes: 1

Murtaza
Murtaza

Reputation: 3065

You will need to create a Recordset to read the data from your SQL query. You have used connection itself.

try below example.

HERE

Upvotes: 0

Related Questions