Undefined function 'InStrRev' in expression.

14 Jun 2006 12:09 PM

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

14 Jun 2006 12:15 PM

CT

Alan,

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

--
Carsten Thomsen
Communities - http://community.integratedsolutions.dk
---------
Voodoo Programming: Things programmers do that they know shouldn't work but
they try anyway, and which sometimes actually work, such as recompiling
everything. (Karl Lehenbauer)
---------
<ala***@gmail.com> wrote in message

Show quoteHide quote

news:1150286962.467405.302910@u72g2000cwu.googlegroups.com...
> 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
>

14 Jun 2006 3:17 PM

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

Show quoteHide quote

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

14 Jun 2006 4:11 PM

Paul Clement

On 14 Jun 2006 05:09:22 -0700, ala***@gmail.com 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)

14 Jun 2006 4:40 PM

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 wrote:
Show quoteHide quote

> On 14 Jun 2006 05:09:22 -0700, ala***@gmail.com 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)

14 Jun 2006 5:31 PM

Paul Clement

On 14 Jun 2006 09:40:28 -0700, ala***@gmail.com 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)

15 Jun 2006 4:43 AM

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

Paul Clement wrote:
Show quoteHide quote

> On 14 Jun 2006 09:40:28 -0700, ala***@gmail.com 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)

16 Jun 2006 4:31 PM

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
ala***@gmail.com wrote:
Show quoteHide quote

> 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
>
> Paul Clement wrote:
> > On 14 Jun 2006 09:40:28 -0700, ala***@gmail.com 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)