It looks like it is producing an SQL statement that would look like:(I'm
making up the 123 and the HOUSTON)
SELECT HOUSE_KEY, STREET, LOT FROM VADDRESS_BASE WHERE TRIM(BOTH ' ' FROM
ADDR1) = '123'
AND
CITY= 'HOUSTON' ORDER BY STREET,
LOT;
If you try it in SQLPlus that will fail because TRIM(BOTH ' ' FROM ADDR1) is
not an Oracle function.(assuming the tables and columns exist.) I suspect
it is a SQLServer function and just because it works in SQLServer does not
mean it will work in Oracle. Two entirely different beasts.
Try it without..
Jim
--
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"Jay" <jerry280_at_yahoo.com> wrote in message
news:tUXka.14$Hg5.19794_at_news.uswest.net...
> TRIM(BOTH ' ' FROM ADDR1) removes the spaces (or whatever character you
have
> within the single quotes) from BOTH sides of the value.
>
> This is not the cause of the problem.
>
> J
>
>
> "Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.com> wrote in message
> news:tUWka.416610$S_4.484608_at_rwcrnsc53...
> > what the heck is: TRIM(BOTH ' ' FROM ADDR1) in a sql statement? Never
> seen
> > that in Oracle
> > Jim
> > "Jay" <jerry280_at_yahoo.com> wrote in message
> > news:xGWka.8$Hg5.13910_at_news.uswest.net...
> > > Hello everyone! I've created an ASP application to connect to an
Oracle
> > > database. I used a file DSN but am now getting the following error
> > message:
> > >
> > > Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
> > >
> > > ODBC driver does not support the requested properties.
> > >
> > > This error message is for the following line of code:
> > > RS.open SQL, conn, 1, 2
> > >
> > > Following is my ASP code. Can anyone help me figure out why I am
getting
> > the
> > > message? Also, is there a better way to connect to the Oracle database
> > > instead of a file DSN? I've heard arguments for DSN connections and
> > DSN-Less
> > > connections with Oracle and am finding that the DSN connection isn't
> > working
> > > so well.
> > >
> > > <body>
> > > <%
> > > Dim FormKey, ConStr, RS, SQL, conn
> > > For Each FormKey in Request.Form
> > > Session(FormKey) = Replace(Request.Form.Item (FormKey), "'", "''")
> > > Next
> > >
> > > set conn=Server.CreateObject("ADODB.Connection")
> > > conn.ConnectionTimeout=15
> > > conn.open "FILEDSN=C:\Program Files\Common Files\ODBC\Data
> > > Sources\Detective.dsn;" & _
> > > "uid=USERID;" & _
> > > "pwd=PASSWORD;" & _
> > > "server=ORACLESERVERNAME;"
> > >
> > > Set RS = Server.CreateObject("ADODB.RecordSet")
> > > SQL = "SELECT HOUSE_KEY, STREET, LOT FROM VADDRESS_BASE WHERE" & _
> > > "TRIM(BOTH ' ' FROM ADDR1) = '" & session.value("HouseNumber") & "'
> AND
> > > CITY" & _
> > > "= '" & UCASE(session.value("AddressCity")) & "' ORDER BY STREET,
> LOT;"
> > > RS.open SQL, conn, 1, 2
> > > if not RS.eof then
> > > %>
> > > <form action="dish2.asp" method="post">
> > > <input type="hidden" name="inc" value="2">
> > > <table border="0" summary="layout" align="center">
> > > <tr>
> > > <td class="label">House Number: </td>
> > > <td class="value"><%=session.value("HouseNumber")%></td>
> > > </tr>
> > > <tr>
> > > <td class="label">City:
> > > </td>
> > > <td
> > > class="value"><%=session.value("AddressCity")%></td>
> > > </tr>
> > > <tr>
> > > <td class="label">Street:
> > > </td>
> > > <td class="value">
> > > <select name="HouseID">
> > > <%
> > > do until RS.eof
> > > %>
> > > <option value="<%=RS("HOUSE_KEY")%>"><%=RS("STREET")%>.
> > > <%=RS("LOT")%></option>
> > > <%
> > > RS.movenext
> > > loop
> > > %>
> > > </select>
> > > <%
> > > else
> > > msg = session.value("HouseNumber") & " in " &
> > > session.value("AddressCity") & " is not a recognized address."
> > > session.abandon
> > > Response.Write("<" & "script>alert('" & msg & "');")
> > > Response.Write("location.href='dish.asp'")
> > > Response.Write("<" & "/script>")
> > > end if
> > > RS.close
> > > set RS = nothing
> > > conn.close
> > > set conn = nothing
> > > %>
> > > </td>
> > > </tr>
> > > <tr>
> > > <td colspan="2" align="center"><input type="submit"
name="submitBTN"
> > > value="Next -->"></td>
> > > </tr>
> > > </table>
> > > </form>
> > > </body>
> > >
> > > Thank you!
> > >
> > > J
> > >
> > >
> >
> >
>
>
Received on Wed Apr 09 2003 - 20:08:32 CDT
