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]

It still doesn't work...

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.

 

[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?