return value from store procedure

[Hrvoje Voda]

I have this code:

SqlConnection conn = null;


string Table="";

int rCount;

conn = new

SqlConnection(s Conn);

conn.Open();

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "dbo.[RecepiesSearch]";

cmd.CommandType = CommandType.Sto redProcedure;

cmd.Connection = conn;

cmd.Parameters. Add (new SqlParameter ("@Name", tbName.Text ));

cmd.Parameters. Add( new System.Data.Sql Client.SqlParam eter("@tbl",
ParameterDirect ion.Output) );

cmd.ExecuteNonQ uery();

imeTablice = cmd.Parameters["@tbl"].Value.ToString ();


gridSearch.Grid .DataMember = "";


SqlDataAdapter adap = new SqlDataAdapter( );

adap.SelectComm and = cmd;

DataSet ds = new DataSet ();

adap.Fill (ds, Table);

gridSearch.Grid .DataSource = ds;

gridSearch.Grid .DataMember = Table;



this is store procedure:



CREATE PROCEDURE ReceptiSearch
@Name nvarchar(50),
@tbl nvarchar(50) = NULL OUTPUT

AS

if exists
(
select name, ingridients from Cakes where Name = @Name
)
set @tbl='Case'


if exists
(
select name, ingridients from Meat where Name = @Name
)
set @tbl='Meat'



Why doesn't it work?



as the result for Table I get 'OUTPUT'



Hrcko

[Marc Gravell]

Re: return value from store procedure

Its because there is no constructor for SqlParameter that accepts the
direction as the second parameter; there *is*, however, one that accepts the
*value* (as an object), which ParameterDirect ion.Output satisfies - hence
"Output" (the enum) is being used as the parameter value.

Simply use a different constructor, or use the default-constructor and set
the name and direction manually before adding it to the parameters
collection.

Marc

"Hrvoje Voda" <hrvoje.voda@lu atech.com> wrote in message
news:dnbvoe$c4c $1@ss405.t-com.hr...[color=blue]
>I have this code:
>
> SqlConnection conn = null;
>
>
> string Table="";
>
> int rCount;
>
> conn = new
>
> SqlConnection(s Conn);
>
> conn.Open();
>
> SqlCommand cmd = new SqlCommand();
>
> cmd.CommandText = "dbo.[RecepiesSearch]";
>
> cmd.CommandType = CommandType.Sto redProcedure;
>
> cmd.Connection = conn;
>
> cmd.Parameters. Add (new SqlParameter ("@Name", tbName.Text ));
>
> cmd.Parameters. Add( new System.Data.Sql Client.SqlParam eter("@tbl",
> ParameterDirect ion.Output) );
>
> cmd.ExecuteNonQ uery();
>
> imeTablice = cmd.Parameters["@tbl"].Value.ToString ();
>
>
> gridSearch.Grid .DataMember = "";
>
>
> SqlDataAdapter adap = new SqlDataAdapter( );
>
> adap.SelectComm and = cmd;
>
> DataSet ds = new DataSet ();
>
> adap.Fill (ds, Table);
>
> gridSearch.Grid .DataSource = ds;
>
> gridSearch.Grid .DataMember = Table;
>
>
>
> this is store procedure:
>
>
>
> CREATE PROCEDURE ReceptiSearch
> @Name nvarchar(50),
> @tbl nvarchar(50) = NULL OUTPUT
>
> AS
>
> if exists
> (
> select name, ingridients from Cakes where Name = @Name
> )
> set @tbl='Case'
>
>
> if exists
> (
> select name, ingridients from Meat where Name = @Name
> )
> set @tbl='Meat'
>
>
>
> Why doesn't it work?
>
>
>
> as the result for Table I get 'OUTPUT'
>
>
>
> Hrcko
>
>[/color]

[Hrvoje Voda]

Re: return value from store procedure

I manage to solve a problem with parameter, but now I get an error:

Additional information: Cannot create a child list for field Case.

Why?

Hrcko


"Marc Gravell" <mgravell@rm.co m> wrote in message
news:u45nlXM$FH A.4028@tk2msftn gp13.phx.gbl...[color=blue]
> Its because there is no constructor for SqlParameter that accepts the
> direction as the second parameter; there *is*, however, one that accepts
> the *value* (as an object), which ParameterDirect ion.Output satisfies -
> hence "Output" (the enum) is being used as the parameter value.
>
> Simply use a different constructor, or use the default-constructor and set
> the name and direction manually before adding it to the parameters
> collection.
>
> Marc
>
> "Hrvoje Voda" <hrvoje.voda@lu atech.com> wrote in message
> news:dnbvoe$c4c $1@ss405.t-com.hr...[color=green]
>>I have this code:
>>
>> SqlConnection conn = null;
>>
>>
>> string Table="";
>>
>> int rCount;
>>
>> conn = new
>>
>> SqlConnection(s Conn);
>>
>> conn.Open();
>>
>> SqlCommand cmd = new SqlCommand();
>>
>> cmd.CommandText = "dbo.[RecepiesSearch]";
>>
>> cmd.CommandType = CommandType.Sto redProcedure;
>>
>> cmd.Connection = conn;
>>
>> cmd.Parameters. Add (new SqlParameter ("@Name", tbName.Text ));
>>
>> cmd.Parameters. Add( new System.Data.Sql Client.SqlParam eter("@tbl",
>> ParameterDirect ion.Output) );
>>
>> cmd.ExecuteNonQ uery();
>>
>> imeTablice = cmd.Parameters["@tbl"].Value.ToString ();
>>
>>
>> gridSearch.Grid .DataMember = "";
>>
>>
>> SqlDataAdapter adap = new SqlDataAdapter( );
>>
>> adap.SelectComm and = cmd;
>>
>> DataSet ds = new DataSet ();
>>
>> adap.Fill (ds, Table);
>>
>> gridSearch.Grid .DataSource = ds;
>>
>> gridSearch.Grid .DataMember = Table;
>>
>>
>>
>> this is store procedure:
>>
>>
>>
>> CREATE PROCEDURE ReceptiSearch
>> @Name nvarchar(50),
>> @tbl nvarchar(50) = NULL OUTPUT
>>
>> AS
>>
>> if exists
>> (
>> select name, ingridients from Cakes where Name = @Name
>> )
>> set @tbl='Case'
>>
>>
>> if exists
>> (
>> select name, ingridients from Meat where Name = @Name
>> )
>> set @tbl='Meat'
>>
>>
>>
>> Why doesn't it work?
>>
>>
>>
>> as the result for Table I get 'OUTPUT'
>>
>>
>>
>> Hrcko
>>
>>[/color]
>
>[/color]

[Bjorn Abelli]

Re: return value from store procedure


"Hrvoje Voda" wrote...
[color=blue]
> I manage to solve a problem with parameter, but now I get an error:
>
> Additional information: Cannot create a child list for field Case.
>
> Why?[/color]

I don't think the code you have provided is the *exact* code that that you
have running, so it's not possible to give a definite answer, but I would
guess that you have misspelled something in your procedure.
[color=blue][color=green][color=darkred]
>>> if exists
>>> (
>>> select name, ingridients from Cakes where Name = @Name
>>> )
>>> set @tbl='Case'[/color][/color][/color]

Shouldn't this be 'Cakes'?

// Bjorn A