vb.net data: Custom functions in sql -- vb.net data

You're in the

vb.net data

group:

Custom functions in sql

Flavio Miano
10/13/2005 12:00:00 AM

vb.net data:

Hi all
i'm new in vb.net
i'm coming from access
and now my first problem is :
using ado.net in sql statement can i use my vb functions
example:
select ID, PRICE, CALCULATESELLPRICE(PRICE) AS SELLPRICE FROM PRODUCTS

WHERE CALULATESELLPRICE IS MY VB FUNCTION

this is very simple in MS Access but how to do this in VB.NET

Thanks in advance
FLAVIO

Re: Custom functions in sql

CT
10/13/2005 10:19:59 AM

"select ID, PRICE, " & CALCULATESELLPRICE(PRICE) & " AS SELLPRICE FROM
PRODUCTS"

--
Carsten Thomsen
Communities - http://community.integratedsolutions.dk

[quoted text, click to view]

Re: Custom functions in sql

Flavio Miano
10/13/2005 11:15:31 AM

excuse me but i don't understand
in this case the function is executed when i release the command
or when the command will be executed ?

the query must calculate the price by every row !!!
thanks
FLAVIO

"CT" <carstent@spammersgoawayintegrasol.dk> ha scritto nel messaggio
news:%23IqFo78zFHA.2652@TK2MSFTNGP14.phx.gbl...
[quoted text, click to view]

Re: Custom functions in sql

Jay B. Harlow [MVP - Outlook]
10/13/2005 12:39:29 PM

Flavio,
| using ado.net in sql statement can i use my vb functions
You cannot use your VB functions in SQL Server 2000 & earlier, however you
can you can use the CREATE FUNCTION statement in SQL Server 2000 (& maybe
7.0) to define user defined functions in your database itself.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_460j.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_7r1l.asp

Something like (untested)

CREATE FUNCTION CalculateSellPrice
(@Price Money )
RETURNS Money
AS
BEGIN
RETURN ( @Price * 1 / 2 )
END

Starting with SQL Server 2005 (due out in less then a month), you can
register .NET assemblies in SQL Server to use any .NET function & even
types. However! I don't have a good reference for that.
http://www.microsoft.com/sql/default.mspx

NOTE: I would carefully weigh registering .NET assemblies and using SQL user
defined functions. As I understand dropping & reregistering a .NET assembly
has its own set of tribulations...

--
Hope this helps
Jay [MVP - Outlook]
T.S. Bradley - http://www.tsbradley.net

[quoted text, click to view]
| Hi all
| i'm new in vb.net
| i'm coming from access
| and now my first problem is :
| using ado.net in sql statement can i use my vb functions
| example:
| select ID, PRICE, CALCULATESELLPRICE(PRICE) AS SELLPRICE FROM PRODUCTS
|
| WHERE CALULATESELLPRICE IS MY VB FUNCTION
|
| this is very simple in MS Access but how to do this in VB.NET
|
| Thanks in advance
| FLAVIO
|
|

Re: Custom functions in sql

Paul Clement
10/13/2005 12:57:01 PM

[quoted text, click to view]

¤ Hi all
¤ i'm new in vb.net
¤ i'm coming from access
¤ and now my first problem is :
¤ using ado.net in sql statement can i use my vb functions
¤ example:
¤ select ID, PRICE, CALCULATESELLPRICE(PRICE) AS SELLPRICE FROM PRODUCTS
¤
¤ WHERE CALULATESELLPRICE IS MY VB FUNCTION
¤
¤ this is very simple in MS Access but how to do this in VB.NET

If you're working with an Access database this is not possible. Unfortunately the Jet database
engine does not support user defined functions in an SQL query. This feature is only supported in
Microsoft Access.

Paul
~~~~

Re: Custom functions in sql

CT
10/13/2005 2:25:29 PM

In that case you need your code to be a function in the database, such as a
stored procedure. I don't know the exact calculation you perform in the
CALCULATESELLPRICE function, but do let me know and which database and
version are you using?

--
Carsten Thomsen
Communities - http://community.integratedsolutions.dk

[quoted text, click to view]

Re: Custom functions in sql

Flavio Miano
10/13/2005 3:42:12 PM

the function does not exist itis a simple example
by example the function can calculate the double price
function calculatesellprice(price)
return price *2
end function

this is not a problem but i have other function by example
createmyspecialguid()

this create a special ID for distribuited database by me
is not a known guid

and this is a vb function

other than i have more other function that i use in a query

in msaccess i create this function in a vb module
and then i can use it inside my queries

the source database (by now)
is msaccess database (i'm migrating all my software in dotnet but this is
not possible all in one time, i release form by form migration)

can you help me

thanks CT
FLAVIO

"CT" <carstent@spammersgoawayintegrasol.dk> ha scritto nel messaggio
news:%23C$g0E$zFHA.908@tk2msftngp13.phx.gbl...
[quoted text, click to view]

Re: Custom functions in sql

Jim Underwood
11/3/2005 1:57:48 PM

Basically, you have three choices that I see.

1. Create a function in the database for this field and call it in your sql.
2. Create a view that performs the calculation and select against the view
instead
3. Select the value then use VB to perform the function after the fact (this
you already said you cant do)

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the vb.net data group.

home · blog · groups

Questions? Comments? Contact the dn