ADODB Recordset Error

[carla]

Good Morning,

I am trying to pull Customer Names from a database into an drop down menu. The below code has worked fine on it's current server for the past 2 years. I am trying to move it to a new server and keep getting the following error:


ADODB.Recordset error '800a0bb9'

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

/customerservice/jtac/reports/cases.asp, line 146



I've tried to include the adovbs.inc file, but I still get the same error. Can anyone see anything in my code that would cause this error? Any help would be greatly appreciated!


<tr><td>
<form method=&quot;POST&quot; action=&quot;

http://reports/publish/jtac/escalation_customer.rpt?user0=xxxx&password0=xxxxxx&quot;>

<p>

<%
Dim objConn
Set objConn = Server.CreateObject(&quot;ADODB.Connection&quot

objConn.ConnectionString = &quot;UID=xxxx;pwd=xxxx;DRIVER={Microsoft ODBC for Oracle};SERVER=xxxx;&quot;
objConn.Open

Dim objRS
Set objRS = Server.CreateObject(&quot;ADODB.Recordset&quot

Dim strSQL
Set strSQL = objConn.execute(&quot;SELECT DISTINCT NAME FROM TABLE_LOCATION&quot

objRS.Open strSQL, objConn

Response.Write &quot;<select name=&quot;&quot;prompt0&quot;&quot;>&quot;
Response.Write &quot;<option>Choose Customer</option>&quot;
Response.Write &quot;<option value=&quot;&quot;&quot;&quot;></option>&quot;
Do Until objRS.EOF = True
Response.Write &quot;<option>&quot; & objRS(&quot;NAME&quot & &quot;</option>&quot;
objRS.MoveNext
Loop
Response.Write &quot;</select>&quot;

objRS.close
Set objRS = Nothing

objConn.close
Set objConn = Nothing
%>

<input type=&quot;SUBMIT&quot; value=&quot;Go&quot;>
</form>
<br>


</td></tr>


Best Regards,
Carla

 

[onpnt]

Hi carla,
which line is this erroring on. the sql statement or the connection Open?

have you checked for a updated oracle connection string for the new server?

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

haven't seen the one you are currently using but haven't wqorked with oracle to often prior

____________________________________________________
[sub]The most important part of your thread is the subject line.
Make it clear and about the topic so we can find it later for reference. Please!! faq333-2924[/sub]

http://www.onpntwebdesigns.com

 

[sodakotahusker]

Check the MDAC versions to see if they are the same on the two machines.

 

[bernlaw]

It appears that after you dimension your string varible you are setting it to a recordset when you execute the SQL statement with objConn.execute. You just want to create a string variable to pass to the open command.

So Change this:

Dim strSQL
Set strSQL = objConn.execute(&quot;SELECT DISTINCT NAME FROM TABLE_LOCATION&quot
objRS.Open strSQL, objConn

to this:


Dim strSQL
strSQL = &quot;SELECT DISTINCT NAME FROM TABLE_LOCATION&quot;
objRS.Open strSQL, objConn

 

[DreXor]

i agreee with bernlaw, also you can shorten the code down significantly without having to remember all the arguments etc by using the following:

Set CustomersCon = Server.CreateObject(&quot;ADODB.Connection&quot 'create connection object
CustomersCon.Open(&quot;UID=xxxx;pwd=xxxx;DRIVER={Microsoft ODBC for Oracle};SERVER=xxxx;&quot ' specify and execute connection
CustomersSQL = &quot;Select * from table where conditions&quot; 'define recordset query
Set CustomersRS = CustomersCon.Execute(CustomersSQL) ' this makes a recordset cause it's what you're requesting.


and later do standard cleanup:

Set CustomersRS = nothing
CustomersCon.Close
Set CustomersCon = nothing

 

[carla]

Sincere thanks to everyone for your help. I finally got it working. It was a problem with my code. Here is the update:

<%
Dim conn, objRS
set conn = server.createobject(&quot;ADODB.Connection&quot

conn.open &quot;UID=xxxxx;pwd=xxxxxx;DRIVER={Microsoft ODBC for Oracle};SERVER=xxxx;&quot;

set objRS = Server.CreateObject(&quot;ADODB.Recordset&quot
objRS.Open &quot;SELECT DISTINCT NAME FROM TABLE_LOCATION&quot;, conn, 0, 1

%>
<form method=&quot;POST&quot; action=&quot;

http://reports/publish/jtac/escalation.rpt?user0=xxxx&password0=xxxxx&quot;>

<p>
<select name=&quot;prompt0&quot;>
<option>Choose Customer</option>
<option value=&quot;&quot;></option>
<%
Do While Not objRS.EOF
%>

<option><%= objRS(&quot;NAME&quot %></option>

<%
objRS.MoveNext
Loop
%>
</select>
<input type=&quot;submit&quot; value=&quot;Go&quot;/>
</form>
<%

objRS.Close
set objRS = Nothing
conn.Close
Set conn = Nothing
%>