Calling a stored function

[Bmxpert]

Hi,

Because the VBA function replace isn't accessible with the ole db Jet Provider in vb.net, I've made a module in my (Acces)database wich contains a function that returns the same value as replace does:

Function gReplace(strIn As String, strFind As String, strReplace As String) As String
gReplace = Replace(strIn, strFind, strReplace)
End Function

Now I would like to call this function in an updatequery from vb.net looking like this:

adapter.UpdateCommand.CommandText="UPDATE Crawford_nl
SET Phonenr= gReplace(Phonenr, '/', '')"

But when I execute the command I get the error: "Undefined function gReplace in expression".

And when I add this code:

adapter.UpdateCommand.CommandType=CommandType.StoredProcedure

I get the error: "Expected query name after EXECUTE"

Can anyone help me with this problem?

[Pirate]

Is gReplace Function declared as Public ??

[Bmxpert]

It wasn't declared public, but after trying that, I still get the same error.

[Pirate]

Try to use VisualBasic namespace at the very top of the module file .

[Bmxpert]

Could you give me some advice on how to do that, I have not worked much with namespaces yet.

[Pirate]

Simply , just use this in the module :

Imports Microsoft.VisualBasic

[Bmxpert]

The intellisence engine does not regognize the keyword,
Imports ...

When I put that line in my code, I can't even use the function anymore in Acces.

[Pirate]

Originally posted by Bmxpert
The intellisence engine does not regognize the keyword,
Imports ...

When I put that line in my code, I can't even use the function anymore in Acces.

How come it doesn't recognize it ? Are you sure ,you're using VB.NET and put that line at very top of the module?

[Bmxpert]

I'm using vb.net to open the database and to update it. But the function gReplace is a user defined function I wrote in a module in the database in Acces2003 and there the line: "Imports Microsoft.VisualBasic" is not recognized.

Or did you mean that I had to put that line a the top of my vb.net code? When I do that it is obviously recognized but there is still not much happening (read: "I get the same errors")

[Pirate]

Yeah , I meant to put that import statement in vb.net IDE code . Anyways , if you are getting the error in this line , try this .

adapter.UpdateCommand.CommandText="UPDATE Crawford_nl
SET Phonenr=" & gReplace(Phonenr, '/', '')

[Bmxpert]

Don't take this the wrong way, but I think we're not on the same frequency. I'll try to clarify what my problem is. In Acces you can make updatequeries such as:

UPDATE Crawford_nl
SET Phonenr= Replace(Phonenr, '/', '')"

The Replace function is unfortunately not available when trying to execute this query in VB.NET because the Provider (Microsoft Jet 4.0 OLE DB Provider) does not support this. After searching on the net, I've discovered that some people have encountered the same problem. A solution someone suggested was to write a wrapper function in the Accesdatabase wich returns the same result as the replace function, and to call this function in the query. Wich means that VB.NET has no knowledge of the existance of this function. So the commandtext of the updatecommand has to be a string containing the full query(see above). I hope this makes things more clear and btw, thanx for your help.

[Pirate]

I was a little confused because the Replace Function which exists in two different namespaces (or classes): 1- in the VisualBasic 2-in the String . So I was paying attention to this abviously . Sorry dude I've never worked on this thing . I mean , I've worked with ADO.NET and updating stuff but not like what you're doing . It's so easy . But , can't you use one of these to solve the problem ?

[Edneeis]

If your query in Access uses the Access function you made then you should be able to call it by name some what like you would a stored procedure in SQL. Then it should use any native Access functions of Access user functions. (I think this is true anyway) If you are trying to write out the query text in vb.net then you don't need the native Access functions you can use the VB.NET functions to perform the same task.