ASP, Excel and SQL Replace function

Hello,

Is it possible to use Replace function in a query against an
excel file?

I am trying this test code and gives me an error:

Set cnnExcel = Server.CreateObject("ADODB.Connection")
cnnExcel.Open "DBQ=" & Server.MapPath('file.xls') & ";" & "DRIVER={Microsoft
Excel Driver (*.xls)};"
Set rstExcel = Server.CreateObject("ADODB.Recordset")
rstCmd = "SELECT REPLACE('abc', 'a', 'x') AS X"
rstExcel.Open rstCmd, cnnExcel, adOpenStatic, adLockPessimistic
Response.Write rstExcel("X")
Response.End

Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
ODBC driver does not support the requested properties.
/search/default.asp, line 26

Is there a driver limitation or is something i do wrong?

Thanks

Adrian Zaharia

re: ASP, Excel and SQL Replace function

adrian zaharia wrote:[color=blue]
> Hello,
>
> Is it possible to use Replace function in a query against an
> excel file?
>
> I am trying this test code and gives me an error:
>
> Set cnnExcel = Server.CreateObject("ADODB.Connection")
> cnnExcel.Open "DBQ=" & Server.MapPath('file.xls') & ";" &
> "DRIVER={Microsoft Excel Driver (*.xls)};"
> Set rstExcel = Server.CreateObject("ADODB.Recordset")
> rstCmd = "SELECT REPLACE('abc', 'a', 'x') AS X"
> rstExcel.Open rstCmd, cnnExcel, adOpenStatic, adLockPessimistic
> Response.Write rstExcel("X")
> Response.End
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
> ODBC driver does not support the requested properties.
> /search/default.asp, line 26
>
> Is there a driver limitation or is something i do wrong?
>
> Thanks
>
> Adrian Zaharia[/color]

This error message is referring to the cursor and locktypes you chose. It
obviously cannot support a static cursor with pessimistic locking. I've
never used this so I don't know what cursor and lock types are supported.
You should try opening a default cursor to see if that works.

If there was a problem with the sql statement, you would have gotten a
syntax error, or something like that.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

re: ASP, Excel and SQL Replace function

Hi,

Thanks, indeed i leave it to a default cursor and now i get this:

Microsoft][ODBC Excel Driver] Undefined function 'REPLACE' in expression.

Adrian




Bob Barrows [MVP] wrote:
[color=blue]
> adrian zaharia wrote:[color=green]
>> Hello,
>>
>> Is it possible to use Replace function in a query against an
>> excel file?
>>
>> I am trying this test code and gives me an error:
>>
>> Set cnnExcel = Server.CreateObject("ADODB.Connection")
>> cnnExcel.Open "DBQ=" & Server.MapPath('file.xls') & ";" &
>> "DRIVER={Microsoft Excel Driver (*.xls)};"
>> Set rstExcel = Server.CreateObject("ADODB.Recordset")
>> rstCmd = "SELECT REPLACE('abc', 'a', 'x') AS X"
>> rstExcel.Open rstCmd, cnnExcel, adOpenStatic, adLockPessimistic
>> Response.Write rstExcel("X")
>> Response.End
>>
>> Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
>> ODBC driver does not support the requested properties.
>> /search/default.asp, line 26
>>
>> Is there a driver limitation or is something i do wrong?
>>
>> Thanks
>>
>> Adrian Zaharia[/color]
>
> This error message is referring to the cursor and locktypes you chose. It
> obviously cannot support a static cursor with pessimistic locking. I've
> never used this so I don't know what cursor and lock types are supported.
> You should try opening a default cursor to see if that works.
>
> If there was a problem with the sql statement, you would have gotten a
> syntax error, or something like that.
>
> Bob Barrows[/color]

re: ASP, Excel and SQL Replace function

Which means exactly what it says. You'll have to do the replacement in your
vbscript code before displaying the data.

Bob Barrows

adrian zaharia wrote:[color=blue]
> Hi,
>
> Thanks, indeed i leave it to a default cursor and now i get this:
>
> Microsoft][ODBC Excel Driver] Undefined function 'REPLACE' in
> expression.
>
> Adrian
>
>
>
>
> Bob Barrows [MVP] wrote:
>[color=green]
>> adrian zaharia wrote:[color=darkred]
>>> Hello,
>>>
>>> Is it possible to use Replace function in a query against an
>>> excel file?
>>>
>>> I am trying this test code and gives me an error:
>>>
>>> Set cnnExcel = Server.CreateObject("ADODB.Connection")
>>> cnnExcel.Open "DBQ=" & Server.MapPath('file.xls') & ";" &
>>> "DRIVER={Microsoft Excel Driver (*.xls)};"
>>> Set rstExcel = Server.CreateObject("ADODB.Recordset")
>>> rstCmd = "SELECT REPLACE('abc', 'a', 'x') AS X"
>>> rstExcel.Open rstCmd, cnnExcel, adOpenStatic, adLockPessimistic
>>> Response.Write rstExcel("X")
>>> Response.End
>>>
>>> Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
>>> ODBC driver does not support the requested properties.
>>> /search/default.asp, line 26
>>>
>>> Is there a driver limitation or is something i do wrong?
>>>
>>> Thanks
>>>
>>> Adrian Zaharia[/color]
>>
>> This error message is referring to the cursor and locktypes you
>> chose. It obviously cannot support a static cursor with pessimistic
>> locking. I've never used this so I don't know what cursor and lock
>> types are supported. You should try opening a default cursor to see
>> if that works.
>>
>> If there was a problem with the sql statement, you would have
>> gotten a syntax error, or something like that.
>>
>> Bob Barrows[/color][/color]

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

re: ASP, Excel and SQL Replace function

Great,

the only *tiny* problem is that finally i need to do a

select ... where replace(...) = 'value'

so vb will not help me here :(

thanks anyway even if i did not found if indeed i cannot use replace or
there is a workaround...

Adrian




Bob Barrows [MVP] wrote:
[color=blue]
> Which means exactly what it says. You'll have to do the replacement in
> your vbscript code before displaying the data.
>
> Bob Barrows
>
> adrian zaharia wrote:[color=green]
>> Hi,
>>
>> Thanks, indeed i leave it to a default cursor and now i get this:
>>
>> Microsoft][ODBC Excel Driver] Undefined function 'REPLACE' in
>> expression.
>>
>> Adrian
>>
>>
>>
>>
>> Bob Barrows [MVP] wrote:
>>[color=darkred]
>>> adrian zaharia wrote:
>>>> Hello,
>>>>
>>>> Is it possible to use Replace function in a query against an
>>>> excel file?
>>>>
>>>> I am trying this test code and gives me an error:
>>>>
>>>> Set cnnExcel = Server.CreateObject("ADODB.Connection")
>>>> cnnExcel.Open "DBQ=" & Server.MapPath('file.xls') & ";" &
>>>> "DRIVER={Microsoft Excel Driver (*.xls)};"
>>>> Set rstExcel = Server.CreateObject("ADODB.Recordset")
>>>> rstCmd = "SELECT REPLACE('abc', 'a', 'x') AS X"
>>>> rstExcel.Open rstCmd, cnnExcel, adOpenStatic, adLockPessimistic
>>>> Response.Write rstExcel("X")
>>>> Response.End
>>>>
>>>> Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
>>>> ODBC driver does not support the requested properties.
>>>> /search/default.asp, line 26
>>>>
>>>> Is there a driver limitation or is something i do wrong?
>>>>
>>>> Thanks
>>>>
>>>> Adrian Zaharia
>>>
>>> This error message is referring to the cursor and locktypes you
>>> chose. It obviously cannot support a static cursor with pessimistic
>>> locking. I've never used this so I don't know what cursor and lock
>>> types are supported. You should try opening a default cursor to see
>>> if that works.
>>>
>>> If there was a problem with the sql statement, you would have
>>> gotten a syntax error, or something like that.
>>>
>>> Bob Barrows[/color][/color]
>[/color]

re: ASP, Excel and SQL Replace function

"adrian zaharia" <adyzah@hotmail.com> wrote in message
news:%23y6OJHEyEHA.2572@tk2msftngp13.phx.gbl...[color=blue]
> Great,
>
> the only *tiny* problem is that finally i need to do a
>
> select ... where replace(...) = 'value'
>
> so vb will not help me here :(
>
> thanks anyway even if i did not found if indeed i cannot use replace or
> there is a workaround...[/color]

Can you describe the replace you need to perform? Perhaps there's a LIKE
expression that would suit your needs.