469,292 Members | 1,349 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,292 developers. It's quick & easy.

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
Jul 22 '05 #1
5 8488
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"
Jul 22 '05 #2
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


Jul 22 '05 #3
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"
Jul 22 '05 #4
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


Jul 22 '05 #5
"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.
Jul 22 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

15 posts views Thread by daniroy | last post: by
5 posts views Thread by Simon | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Geralt96 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.