ASP, Excel and SQL Replace function

[adrian zaharia]

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

 

[Bob Barrows [MVP]]

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

 

[adrian zaharia]

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]]

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

Bob Barrows

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

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

 

[adrian zaharia]

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

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

Bob Barrows

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

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

 

[Chris Hohmann]

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...

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