User-defined function

[chreo]

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? :)

[Michael C#]

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? :)

[chreo]

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? :)

[Michael C#]

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? :)