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

re: User-defined function

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" <chreo@gazeta.pl> wrote in message
news:d1jn6i$2kl$1@inews.gazeta.pl...[color=blue]
>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? :)
>[/color]

re: User-defined function

It still doesn't work... :(

U¿ytkownik "Michael C#" <xyz@abcdef.com> napisa³ w wiadomo¶ci
news:jEj%d.1516$JZ.576@fe08.lga...[color=blue]
> 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" <chreo@gazeta.pl> wrote in message
> news:d1jn6i$2kl$1@inews.gazeta.pl...[color=green]
>>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? :)
>>[/color]
>
>[/color]

re: User-defined function

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" <chreo@gazeta.pl> wrote in message
news:d1mvn1$jv1$1@inews.gazeta.pl...[color=blue]
> It still doesn't work... :(
>
> U¿ytkownik "Michael C#" <xyz@abcdef.com> napisa³ w wiadomo¶ci
> news:jEj%d.1516$JZ.576@fe08.lga...[color=green]
>> 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" <chreo@gazeta.pl> wrote in message
>> news:d1jn6i$2kl$1@inews.gazeta.pl...[color=darkred]
>>>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? :)
>>>[/color]
>>
>>[/color]
>
>[/color]