Undefined function 'InStrRev' in expression.

[alanwo]

Hi Experts,

Connected to Access MDB file using ADO.net OLEDB, I have a SQL to get
file extension from file name which is stored as field.
SELECT Right(FileName, Len(FileName) - InStrRev(FileName, '.')) FROM
Files

This query work in MS ACCESS enivronment but not VB.net program
connecting to the database.

StrReverse / Reverse don't work neither

Your help is highly appreciated.

Alan Wo
http://noclone.net

 

[CT]

Alan,

Have a look at the Substring and the LastIndexOf methods of the String
class.

 

[Cor Ligthert [MVP]]

Hi,

Do you have more code around this.

I don't know if this is meant as SQL Select string, in that case it could be
something as

"SELECT " & Right(FileName, Len(FileName) - InStrRev(FileName, '.')) &
"FROM
Files "

However I am not sure if you are after this.

Cor

 

[Paul Clement]

On 14 Jun 2006 05:09:22 -0700, (e-mail address removed) wrote:

¤ Hi Experts,
¤
¤ Connected to Access MDB file using ADO.net OLEDB, I have a SQL to get
¤ file extension from file name which is stored as field.
¤ SELECT Right(FileName, Len(FileName) - InStrRev(FileName, '.')) FROM
¤ Files
¤
¤ This query work in MS ACCESS enivronment but not VB.net program
¤ connecting to the database.
¤
¤ StrReverse / Reverse don't work neither
¤
¤ Your help is highly appreciated.

Jet supports a number of VBA type functions in sandbox mode. Unfortunately InStrRev is not supported
when running a SQL query outside of Access. Only the InStr string function is supported.

I would recommend using the .NET functions that Carsten suggested *after* retrieving the full
filename from the database.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[alanwo]

Paul

Great, you understood my problem. Sure, we can do some post-processing
after retrieving the full file name, but if it can be done in SQL
statement, it is faster. In an attempt to do so, I tried to use
StrReverse function plus InStr to do the same result, but StrReverse is
not supported through ado.net but only in MS Access environment or
"sandbox". Do you know the function to reverse string or could you give
me the web page that list all built-in functions that can be used
through ado.net/oledb using mdb database? Do you know the equivalence
of InStrRev using combination of supported functions?
Thanks again.

Alan Wo
http://noclone.net

 

[Paul Clement]

On 14 Jun 2006 09:40:28 -0700, (e-mail address removed) wrote:

¤ Paul
¤
¤ Great, you understood my problem. Sure, we can do some post-processing
¤ after retrieving the full file name, but if it can be done in SQL
¤ statement, it is faster. In an attempt to do so, I tried to use
¤ StrReverse function plus InStr to do the same result, but StrReverse is
¤ not supported through ado.net but only in MS Access environment or
¤ "sandbox". Do you know the function to reverse string or could you give
¤ me the web page that list all built-in functions that can be used
¤ through ado.net/oledb using mdb database? Do you know the equivalence
¤ of InStrRev using combination of supported functions?

The string manipulation functions are rather limited under Jet. I took a quick look but I didn't see
anything that triggered an idea. If you want to see what is available check the following MS KB
article:

How to configure Jet 4.0 to prevent unsafe functions from running in Access 2003
http://support.microsoft.com/default.aspx?scid=kb;ja;294698


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[alanwo]

Hi Paul,

Thank you very much for your valuable information. I know more about
the issue. I found that InStrRev is disabled in sandbox mode but
setting
\\HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\engines\SandboxMode to
0 does not work.
http://office.microsoft.com/en-us/assistance/HP010504321033.aspx?mode=print


Alan

 

[alanwo]

Hi All,

We finally work around a solution, store the dot position returned by
..net string function in database during insertion. This avoid the time
to process after retrieval while keeping the lowest storage.

Alan
http://noclone.net