471,601 Members | 1,218 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,601 software developers and data experts.

User-defined function

I have user-defined function in MSSQL which returns Table (with 10 columns)

(sorry for Polish names)

CREATE FUNCTION PACZKI_Z_AKCJI
(@AKCJA_ID int)
RETURNS TABLE
RETURN
SELECT TOP 100 PERCENT dbo.PACZKI.ID_PACZKI, dbo.PACZKI.ID_AKCJI,
dbo.PACZKI.NR_PACZKI, dbo.PACZKI.ILOSC_KUPONOW, LOG_1.Login AS LOGIN1,
dbo.PACZKI.ID_PAKOWACZA, LOG_2.Login AS LOGIN2,
dbo.PACZKI.ID_S_PAKOWALNI, dbo.PACZKI.DATA_PAKOWANIA
FROM dbo.LOGOWANIE LOG_1 INNER JOIN
dbo.PACZKI ON LOG_1.Osoba_ID = dbo.PACZKI.ID_PAKOWACZA
INNER JOIN
dbo.LOGOWANIE LOG_2 ON dbo.PACZKI.ID_S_PAKOWALNI =
LOG_2.Osoba_ID
WHERE dbo.PACZKI.ID_AKCJI=@AKCJA_ID
ORDER BY dbo.PACZKI.NR_PACZKI

As you can see function has one parameter @AKCJA_ID so function doesn't
return always all rows but only these which I need.

---------------------

NOW - MY PROBLEM

How use that function in VB.DOTNET form?
I have combobox with ACTIONS - where I choose my paramter @AKCJA_ID

and I want then fill datagrid with table returned from user-defined function

QUESTIONS:
1) How connect User-defined function from MSSQL to form in VB.DONTET
2) How pass parameter from combobox to that function?

I found out that I can use dataadapter with command:

select * from paczki_z_akcji (1)

but how change parameter in brackets? (1) <---this should change while I am
changing items in combobox

In help of Microsoft VB.NET i can find that user defined functions are in
server explorer just like stored procedures :)
But there are no user defined functions! Only tables, views and procedures.
Maybe there is solution to return tables in stored procedures? :)
Nov 21 '05 #1
3 3983
Functions *usually* return scalar values... You might consider creating
this as a Stored Procedure. Then you just set up a Parameterized Query in
..NET and use the recordset returned like any other.

Of course if the Function method works, who am I to tell you differently?
To answer your second question, you might try something like this:

Dim i as Integer
i = 1
Dim sqlcmd as New SqlCommand ("select * from paczki_z_akcji (@AKCJA_ID)",
sqlcon)
sqlcmd.Parameters.Add("@AKCJA_ID", SqlDbType.Int).Value = i

One caveat: I've never actually done this with a UDF before, mostly just
with SP's. But hey, give it a shot and see if it works for you. Also
you'll have to change the code to match your variables, SqlCommand,
SqlConnection, etc.

"chreo" <ch***@gazeta.pl> wrote in message
news:d1**********@inews.gazeta.pl...
I have user-defined function in MSSQL which returns Table (with 10 columns)

(sorry for Polish names)

CREATE FUNCTION PACZKI_Z_AKCJI
(@AKCJA_ID int)
RETURNS TABLE
RETURN
SELECT TOP 100 PERCENT dbo.PACZKI.ID_PACZKI, dbo.PACZKI.ID_AKCJI,
dbo.PACZKI.NR_PACZKI, dbo.PACZKI.ILOSC_KUPONOW, LOG_1.Login AS LOGIN1,
dbo.PACZKI.ID_PAKOWACZA, LOG_2.Login AS LOGIN2,
dbo.PACZKI.ID_S_PAKOWALNI, dbo.PACZKI.DATA_PAKOWANIA
FROM dbo.LOGOWANIE LOG_1 INNER JOIN
dbo.PACZKI ON LOG_1.Osoba_ID =
dbo.PACZKI.ID_PAKOWACZA INNER JOIN
dbo.LOGOWANIE LOG_2 ON dbo.PACZKI.ID_S_PAKOWALNI =
LOG_2.Osoba_ID
WHERE dbo.PACZKI.ID_AKCJI=@AKCJA_ID
ORDER BY dbo.PACZKI.NR_PACZKI

As you can see function has one parameter @AKCJA_ID so function doesn't
return always all rows but only these which I need.

---------------------

NOW - MY PROBLEM

How use that function in VB.DOTNET form?
I have combobox with ACTIONS - where I choose my paramter @AKCJA_ID

and I want then fill datagrid with table returned from user-defined
function

QUESTIONS:
1) How connect User-defined function from MSSQL to form in VB.DONTET
2) How pass parameter from combobox to that function?

I found out that I can use dataadapter with command:

select * from paczki_z_akcji (1)

but how change parameter in brackets? (1) <---this should change while I
am changing items in combobox

In help of Microsoft VB.NET i can find that user defined functions are in
server explorer just like stored procedures :)
But there are no user defined functions! Only tables, views and
procedures.
Maybe there is solution to return tables in stored procedures? :)

Nov 21 '05 #2
It still doesn't work... :(

Użytkownik "Michael C#" <xy*@abcdef.com> napisał w wiadomości
news:jE***************@fe08.lga...
Functions *usually* return scalar values... You might consider creating
this as a Stored Procedure. Then you just set up a Parameterized Query in
.NET and use the recordset returned like any other.

Of course if the Function method works, who am I to tell you differently?
To answer your second question, you might try something like this:

Dim i as Integer
i = 1
Dim sqlcmd as New SqlCommand ("select * from paczki_z_akcji (@AKCJA_ID)",
sqlcon)
sqlcmd.Parameters.Add("@AKCJA_ID", SqlDbType.Int).Value = i

One caveat: I've never actually done this with a UDF before, mostly just
with SP's. But hey, give it a shot and see if it works for you. Also
you'll have to change the code to match your variables, SqlCommand,
SqlConnection, etc.

"chreo" <ch***@gazeta.pl> wrote in message
news:d1**********@inews.gazeta.pl...
I have user-defined function in MSSQL which returns Table (with 10
columns)

(sorry for Polish names)

CREATE FUNCTION PACZKI_Z_AKCJI
(@AKCJA_ID int)
RETURNS TABLE
RETURN
SELECT TOP 100 PERCENT dbo.PACZKI.ID_PACZKI, dbo.PACZKI.ID_AKCJI,
dbo.PACZKI.NR_PACZKI, dbo.PACZKI.ILOSC_KUPONOW, LOG_1.Login AS LOGIN1,
dbo.PACZKI.ID_PAKOWACZA, LOG_2.Login AS LOGIN2,
dbo.PACZKI.ID_S_PAKOWALNI, dbo.PACZKI.DATA_PAKOWANIA
FROM dbo.LOGOWANIE LOG_1 INNER JOIN
dbo.PACZKI ON LOG_1.Osoba_ID =
dbo.PACZKI.ID_PAKOWACZA INNER JOIN
dbo.LOGOWANIE LOG_2 ON dbo.PACZKI.ID_S_PAKOWALNI =
LOG_2.Osoba_ID
WHERE dbo.PACZKI.ID_AKCJI=@AKCJA_ID
ORDER BY dbo.PACZKI.NR_PACZKI

As you can see function has one parameter @AKCJA_ID so function doesn't
return always all rows but only these which I need.

---------------------

NOW - MY PROBLEM

How use that function in VB.DOTNET form?
I have combobox with ACTIONS - where I choose my paramter @AKCJA_ID

and I want then fill datagrid with table returned from user-defined
function

QUESTIONS:
1) How connect User-defined function from MSSQL to form in VB.DONTET
2) How pass parameter from combobox to that function?

I found out that I can use dataadapter with command:

select * from paczki_z_akcji (1)

but how change parameter in brackets? (1) <---this should change while I
am changing items in combobox

In help of Microsoft VB.NET i can find that user defined functions are in
server explorer just like stored procedures :)
But there are no user defined functions! Only tables, views and
procedures.
Maybe there is solution to return tables in stored procedures? :)


Nov 21 '05 #3
Did you try it as a stored procedure, and can you post some of your code?
Specifically the part that sets up your SqlCommand?

"chreo" <ch***@gazeta.pl> wrote in message
news:d1**********@inews.gazeta.pl...
It still doesn't work... :(

Użytkownik "Michael C#" <xy*@abcdef.com> napisał w wiadomości
news:jE***************@fe08.lga...
Functions *usually* return scalar values... You might consider creating
this as a Stored Procedure. Then you just set up a Parameterized Query
in .NET and use the recordset returned like any other.

Of course if the Function method works, who am I to tell you differently?
To answer your second question, you might try something like this:

Dim i as Integer
i = 1
Dim sqlcmd as New SqlCommand ("select * from paczki_z_akcji (@AKCJA_ID)",
sqlcon)
sqlcmd.Parameters.Add("@AKCJA_ID", SqlDbType.Int).Value = i

One caveat: I've never actually done this with a UDF before, mostly just
with SP's. But hey, give it a shot and see if it works for you. Also
you'll have to change the code to match your variables, SqlCommand,
SqlConnection, etc.

"chreo" <ch***@gazeta.pl> wrote in message
news:d1**********@inews.gazeta.pl...
I have user-defined function in MSSQL which returns Table (with 10
columns)

(sorry for Polish names)

CREATE FUNCTION PACZKI_Z_AKCJI
(@AKCJA_ID int)
RETURNS TABLE
RETURN
SELECT TOP 100 PERCENT dbo.PACZKI.ID_PACZKI, dbo.PACZKI.ID_AKCJI,
dbo.PACZKI.NR_PACZKI, dbo.PACZKI.ILOSC_KUPONOW, LOG_1.Login AS LOGIN1,
dbo.PACZKI.ID_PAKOWACZA, LOG_2.Login AS LOGIN2,
dbo.PACZKI.ID_S_PAKOWALNI, dbo.PACZKI.DATA_PAKOWANIA
FROM dbo.LOGOWANIE LOG_1 INNER JOIN
dbo.PACZKI ON LOG_1.Osoba_ID =
dbo.PACZKI.ID_PAKOWACZA INNER JOIN
dbo.LOGOWANIE LOG_2 ON dbo.PACZKI.ID_S_PAKOWALNI =
LOG_2.Osoba_ID
WHERE dbo.PACZKI.ID_AKCJI=@AKCJA_ID
ORDER BY dbo.PACZKI.NR_PACZKI

As you can see function has one parameter @AKCJA_ID so function doesn't
return always all rows but only these which I need.

---------------------

NOW - MY PROBLEM

How use that function in VB.DOTNET form?
I have combobox with ACTIONS - where I choose my paramter @AKCJA_ID

and I want then fill datagrid with table returned from user-defined
function

QUESTIONS:
1) How connect User-defined function from MSSQL to form in VB.DONTET
2) How pass parameter from combobox to that function?

I found out that I can use dataadapter with command:

select * from paczki_z_akcji (1)

but how change parameter in brackets? (1) <---this should change while I
am changing items in combobox

In help of Microsoft VB.NET i can find that user defined functions are
in server explorer just like stored procedures :)
But there are no user defined functions! Only tables, views and
procedures.
Maybe there is solution to return tables in stored procedures? :)



Nov 21 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by zlst | last post: by
2 posts views Thread by Jesper Stocholm | last post: by
5 posts views Thread by Michelle Stone | last post: by
6 posts views Thread by martin | last post: by
2 posts views Thread by rn5a | last post: by
1 post views Thread by Carlettus | last post: by
3 posts views Thread by shapper | last post: by
reply views Thread by leo001 | last post: by
reply views Thread by MichaelMortimer | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.