Link to home
Start Free TrialLog in
Avatar of here4u247
here4u247

asked on

Cannot display oracle date field in ASP: ODBC Drivers error '80020009'

Dear all,

I 'm getting an error whenever i want to display a date field in my ASP page, i read couple of solution this problem they all recommend to select the date field last in my sql command which i done but still i'm getting the same error which is:

Microsoft OLE DB Provider for ODBC Drivers error '80020009'

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

?

I can see all the field but when it comes to date field the error shown.

I'm using IIS, ASP with Oracle as DB, here is my sql command: Sqlquery = "Select ORDERID,SUPP_SUPPID,TOTAL,SORDER_DATE from supp_orders S,INVOICES N where S.ORDERID <> N.SORDER_ORDERID"


full code is as follows:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
 
set objConn = server.createObject ("ADODB.connection")
Set Rs=Server.CreateObject("ADODB.Recordset")
objConn.Open "dsn=design01;uid=****;pwd=****;;"
Sqlquery = "Select ORDERID,SUPP_SUPPID,TOTAL,SORDER_DATE from supp_orders S,INVOICES N where S.ORDERID <> N.SORDER_ORDERID"

Response.Write(Sqlquery)

Rs.open Sqlquery, objConn

%>

{HTML}

<table border="0">
  <tr>
    <th height="57" colspan="12">Orders </th>
  </tr>

<tr>
  <th>Order ID </th>
  <th>Order Date</th>
  <th>Supplier ID</th>
  <th>Total</th>
  <th>Action</th>
</tr>
<%
while not Rs.eof
%>
<tr>
<td><% response.write(Rs("ORDERID"))%> </td>
<td><% response.write(Rs("SORDER_DATE"))%> </td>
<td><% response.write(Rs("SUPP_SUPPID"))%> </td>
<td><% response.write(Rs("TOTAL"))%> </td>

<td>View Item | Edit | Delete</td>
</tr>
<%

Rs.MoveNext
Wend
Rs.close
objConn.close

set Rs=nothing
Set objConn=nothing
%>
</table>

{HTML}
Avatar of ajaikumarr
ajaikumarr
Hai,

Try this.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
set objConn = server.createObject ("ADODB.connection")
Set Rs=Server.CreateObject("ADODB.Recordset")
objConn.Open "dsn=design01;uid=****;pwd=****;;"
Sqlquery = "Select ORDERID,SUPP_SUPPID,TOTAL,SORDER_DATE from supp_orders S,INVOICES N where S.ORDERID <> N.SORDER_ORDERID"
'Response.Write(Sqlquery)
Rs.open Sqlquery, objConn, 3, 3
%>

<HTML>
<table border="0">
  <tr>
    <th height="57" colspan="12">Orders </th>
  </tr>

<tr>
  <th>Order ID </th>
  <th>Order Date</th>
  <th>Supplier ID</th>
  <th>Total</th>
  <th>Action</th>
</tr>

<%
If not Rs.EOF Then
  while not Rs.eof
    %>
    <tr>
      <td><%=Rs("ORDERID") & ""%></td>
      <td><%=Rs("SORDER_DATE") & ""%></td>
      <td><%=Rs("SUPP_SUPPID") & ""%></td>
      <td><%=Rs("TOTAL") & ""%></td>
      <td>View Item | Edit | Delete</td>
    </tr>
    <%
    Rs.MoveNext
  Wend
End If
Rs.close
objConn.close
set Rs=nothing
Set objConn=nothing
%>
</table>
</HTML>

Bye
Ajai
Avatar of here4u247
here4u247

ASKER

hi Ajai,

i tried your solution by changing into:

Rs.open Sqlquery, objConn, 3, 3

but i got the same error:

Microsoft OLE DB Provider for ODBC Drivers error '80020009'

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

?

Avatar of jitganguly
jitganguly
Use alisas on the column names

I do not which one's coming from where, but basically somethign like that
Sqlquery = "Select N.ORDERID,S.SUPP_SUPPID,TOTAL,SORDER_DATE from supp_orders S,INVOICES N where S.ORDERID <> N.SORDER_ORDERID"

Make sure your use correct aliases in column names, otherwise you will get 'Item not found error'
Avatar of here4u247
here4u247

ASKER

hi jitganguly,

i tried what you suggested but i still have the same error when i want to display the date field:

Microsoft OLE DB Provider for ODBC Drivers error '80020009'

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

?

the modified code is now like this:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
set objConn = server.createObject ("ADODB.connection")
Set Rs=Server.CreateObject("ADODB.Recordset")
objConn.Open "dsn=design01;uid=****;pwd=****;"
'-----------jitganguly  suggesting---------'
Sqlquery = "Select N.SORDER_ORDERID, S.ORDERID,S.SUPP_SUPPID,S.TOTAL,S.SORDER_DATE from supp_orders S,INVOICES N where S.ORDERID <> N.SORDER_ORDERID"
'--------------------'

'-----------Ajai suggesting---------'
Rs.open Sqlquery, objConn, 3, 3
'--------------------'
%>

<table border="0">
  <tr>
    <th height="57" colspan="12">Products Details</th>
  </tr>

<tr>
  <th>Order ID </th>
  <th>Order Date</th>
  <th>Supplier ID</th>
  <th>Total</th>
  <th>Action</th>
</tr>
<%
while not Rs.eof
%>
<tr>
<td><% response.write(Rs("ORDERID"))%> </td>
<td><% response.write(Rs("SORDER_DATE"))%> </td>
<td><% response.write(Rs("SUPP_SUPPID"))%> </td>
<td><% response.write(Rs("TOTAL"))%> </td>

<td>View Item | Edit | Delete</td>
</tr>
<%

Rs.MoveNext
Wend
Rs.close
objConn.close

set Rs=nothing
Set objConn=nothing
%>
Avatar of jitganguly
jitganguly
OK try this SQL

Sqlquery = "Select N.SORDER_ORDERID, S.ORDERID,S.SUPP_SUPPID,S.TOTAL,S.SORDER_DATE from supp_orders S,INVOICES N where S.ORDERID != N.SORDER_ORDERID"

Note teh not equal signed changed from <> to !=
Avatar of jitganguly
jitganguly
Also put a if not eof, complete code

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
set objConn = server.createObject ("ADODB.connection")
Set Rs=Server.CreateObject("ADODB.Recordset")
objConn.Open "dsn=design01;uid=****;pwd=****;"
'-----------jitganguly  suggesting---------'
Sqlquery = "Select N.SORDER_ORDERID, S.ORDERID,S.SUPP_SUPPID,S.TOTAL,S.SORDER_DATE from supp_orders S,INVOICES N where S.ORDERID != N.SORDER_ORDERID"
'--------------------'

'-----------Ajai suggesting---------'
Rs.open Sqlquery, objConn, 3, 3
'--------------------'
if not Rs.eof
%>

<table border="0">
  <tr>
    <th height="57" colspan="12">Products Details</th>
  </tr>

<tr>
  <th>Order ID </th>
  <th>Order Date</th>
  <th>Supplier ID</th>
  <th>Total</th>
  <th>Action</th>
</tr>
<%
while not Rs.eof
%>
<tr>
<td><% response.write(Rs("ORDERID"))%> </td>
<td><% response.write(Rs("SORDER_DATE"))%> </td>
<td><% response.write(Rs("SUPP_SUPPID"))%> </td>
<td><% response.write(Rs("TOTAL"))%> </td>

<td>View Item | Edit | Delete</td>
</tr>
<%

Rs.MoveNext
Wend
else
Response.Write "No Data Found !!!"
end if
Rs.close
objConn.close

set Rs=nothing
Set objConn=nothing
%>
Avatar of here4u247
here4u247

ASKER

thanks jitganguly, i did excatly what you suggested but still i have the same problem!

modified code:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%

set objConn = server.createObject ("ADODB.connection")
Set Rs=Server.CreateObject("ADODB.Recordset")
objConn.Open "dsn=design01;uid=im259113;pwd=im259113zz;"
Sqlquery = "Select N.SORDER_ORDERID, S.ORDERID,S.SUPP_SUPPID,S.TOTAL,S.SORDER_DATE from supp_orders S,INVOICES N where S.ORDERID != N.SORDER_ORDERID"
Response.Write(Sqlquery)

Rs.open Sqlquery, objConn, 3, 3

%>

<%if not Rs.eof then
%>
<table border="0">
  <tr>
    <th height="57" colspan="12">Products Details</th>
  </tr>

<tr>
  <th>Order ID </th>
  <th>Order Date</th>
  <th>Supplier ID</th>
  <th>Total</th>
  <th>Action</th>
</tr>
<%
while not Rs.eof
%>
<tr>
<td><% response.write(Rs("ORDERID"))%> </td>
<td><% response.write(Rs("SORDER_DATE"))%> </td>
<td><% response.write(Rs("SUPP_SUPPID"))%> </td>
<td><% response.write(Rs("TOTAL"))%> </td>

<td>View Item | Edit | Delete</td>
</tr>
<%

Rs.MoveNext
Wend
Rs.close
objConn.close

set Rs=nothing
Set objConn=nothing
else
Response.Write "No Data Found !!!"
end if%>
</table>
Avatar of jitganguly
jitganguly
Take off 3,3 from open i.e Rs.open Sqlquery, objConn
Avatar of jitganguly
jitganguly
Does the SQL runs from SQLPlus ? Try that first, I have a felling  SQL is not right
Avatar of jitganguly
jitganguly
Also in which line your are getting this ? in Open ? or somewhere else ?
Avatar of jitganguly
jitganguly
You are missing equal to sign here
<% response.write(Rs("ORDERID"))%>
It should be
<% =response.write(Rs("ORDERID"))%>
change it everywhere

Complete code

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%

set objConn = server.createObject ("ADODB.connection")
Set Rs=Server.CreateObject("ADODB.Recordset")
objConn.Open "dsn=design01;uid=im259113;pwd=im259113zz;"
Sqlquery = "Select N.SORDER_ORDERID, S.ORDERID,S.SUPP_SUPPID,S.TOTAL,S.SORDER_DATE from supp_orders S,INVOICES N where S.ORDERID != N.SORDER_ORDERID"
Response.Write(Sqlquery)

Rs.open Sqlquery, objConn

%>

<%if not Rs.eof then
%>
<table border="0">
  <tr>
    <th height="57" colspan="12">Products Details</th>
  </tr>

<tr>
  <th>Order ID </th>
  <th>Order Date</th>
  <th>Supplier ID</th>
  <th>Total</th>
  <th>Action</th>
</tr>
<%
while not Rs.eof
%>
<tr>
<td><%= Rs("ORDERID")%> </td>
<td><% =Rs("SORDER_DATE")%> </td>
<td><%=Rs("SUPP_SUPPID")%> </td>
<td><%=Rs("TOTAL")%> </td>

<td>View Item | Edit | Delete</td>
</tr>
<%

Rs.MoveNext
Wend
Rs.close
objConn.close

set Rs=nothing
Set objConn=nothing
else
Response.Write "No Data Found !!!"
end if%>
</table>
Avatar of here4u247
here4u247

ASKER

hi jitganguly, the sql is right because it does display the all the required record it just only when it want to print the date field on to the screen, for example if i don't request the date field(value) it will show 4 records which is correct.

i tried what you suggested but i still have the same error

CODE:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
 
set objConn = server.createObject ("ADODB.connection")
Set Rs=Server.CreateObject("ADODB.Recordset")
objConn.Open "dsn=design01;uid=im259113;pwd=im259113zz;"
Sqlquery = "Select N.SORDER_ORDERID, S.ORDERID,S.SUPP_SUPPID,S.TOTAL,S.SORDER_DATE from supp_orders S,INVOICES N where S.ORDERID != N.SORDER_ORDERID order by S.TOTAL"


Rs.open Sqlquery, objConn

%>

<%if not Rs.eof then
%>
<table border="0">
  <tr>
    <th height="57" colspan="12">Products Details</th>
  </tr>

<tr>
  <th>Order ID </th>
  <th>Order Date</th>
  <th>Supplier ID</th>
  <th>Total</th>
  <th>Action</th>
</tr>
<%
while not Rs.eof
%>
<tr>
<td><%= Rs("ORDERID")%> </td>
<td><% =Rs("SORDER_DATE")%> </td>
<td><%=Rs("SUPP_SUPPID")%> </td>
<td><%=Rs("TOTAL")%> </td>

<td>View Item | Edit | Delete</td>
</tr>
<%

Rs.MoveNext
Wend
Rs.close
objConn.close

set Rs=nothing
Set objConn=nothing
else
Response.Write "No Data Found !!!"
end if%>
</table>
Avatar of ajaikumarr
ajaikumarr
Hai,

I feel something is wrong with your connection string...

Just try this and let me know.
"Driver={Microsoft ODBC for Oracle};Server=OracleServer.world;Uid=Username;Pwd=Password;"

Change the server, username, password and check.

Bye
Ajai
Avatar of ajaikumarr
ajaikumarr
Hai,

Also check this article... There are some examples to check the connctivity problem.

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q255084

Bye
Ajai
Avatar of here4u247
here4u247

ASKER

thanks all i figure out why i was getting that error, and as you jitganguly the problem was with SQL command, apparently Oracle must first convert that value from the special internal format to a printable string. so i used TO_CHAR command and it did worked!!

my sql now looks like this:

Sqlquery = "Select N.SORDER_ORDERID, S.ORDERID,S.SUPP_SUPPID,S.TOTAL,to_char(S.SORDER_DATE, 'DD MM YY') as d from supp_orders S,INVOICES N where S.ORDERID != N.SORDER_ORDERID order by S.TOTAL"


thank you all again.
ASKER CERTIFIED SOLUTION
Avatar of CetusMOD
CetusMOD
Flag of Netherlands image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial