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

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

[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] wrote:

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

[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

adrian zaharia wrote:

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:

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

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

[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


Bob Barrows [MVP] wrote:

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:

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:

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]

"adrian zaharia" <ad****@hotmail.com> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...

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.