VB.Net function in OleDB select command

[Sorwen]

So I'm trying to move this query to vb.net to see if performance is any better and so I don't have to keep dual developing it. Right now the queries are handled in Access and the end result data is pulled into vb.net. I've done queries a million times in vb.net the problem with this one is that part of what it does in access is during the query it uses a custom function to check for the proper case as the default proper case is to limited for names. The problem is doing the same thing in vb.net.

An example of the Access select command.

SELECT RemoteTable_tbl.Field1, RemoteTable_tbl.Field2, RemoteTable_tbl.Field3, RemoteTable_tbl.Field4, RemoteTable_tbl.Field5, RemoteTable_tbl.Field6, RemoteTable_tbl.Field7, RemoteTable_tbl.Field8, RemoteTable_tbl.Field9, RemoteTable_tbl.Field10, RemoteTable_tbl.Field11, RemoteTable2_tbl.Field12, RemoteTable2_tbl.Field13, RemoteTable_tbl.Field14, [red]VerifyProperCase[/red](Trim([Field14])) AS CaseCheck, [red]CorrectCase[/red](Trim([Field14])) AS ProperCase INTO CheckProperCase_tbl
FROM RemoteTable_tbl INNER JOIN RemoteTable2_tbl ON (RemoteTable_tbl.Field1 = RemoteTable2_tbl.Field2) AND (RemoteTable_tbl.Field3 = RemoteTable2_tbl.Field3) AND (RemoteTable_tbl.Field2 = RemoteTable2_tbl.Field4) AND (RemoteTable_tbl.Field1 = RemoteTable2_tbl.Field5);

So [red]VerifyProperCase[/red] just does a string compare using [red]CorrectCase[/red] which of course is the function that changes names to the case we need. Easy for me in Access not so much in vb.net.

I assume what I need is a stored procedure? If that is the case the problem I had was finding good example of one that dealt with a function local in vb.net rather than a stored procedure in the remote database. Any help would be greatly appreciated.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[jakeir]

Hi,
As far as I know you cannot bring the Access query in the Vb app but you can call the Access query from within the vb application and then assign the results to whereever you want; like a grid view.
If you do a google search for running an Ms Access query from within a VB.Net application, you will get sight to shoow you hao to do this. One I found was -->

http://social.msdn.microsoft.com/Forums/en-US/vbide/thread/e4344a25-93e5-40d4-a97d-c5ee0b573577/

Hope this helps

 

[Sorwen]

Thanks. I was afraid that would be the answer. I'll just have to see what kind of performance difference I get pulling the data into vb.net then running my case on it. If it is worse then I'll give what is in that post a shot as it would give me a better way to run the Access query at least. Thanks again.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!