return value from store procedure

[Hrvoje Voda]

I have this code:

SqlConnection conn = null;


string Table="";

int rCount;

conn = new

SqlConnection(sConn);

conn.Open();

SqlCommand cmd = new SqlCommand();

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

cmd.CommandType = CommandType.StoredProcedure;

cmd.Connection = conn;

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

cmd.Parameters.Add( new System.Data.SqlClient.SqlParameter("@tbl",
ParameterDirection.Output) );

cmd.ExecuteNonQuery();

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


gridSearch.Grid.DataMember = "";


SqlDataAdapter adap = new SqlDataAdapter();

adap.SelectCommand = 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]

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 ParameterDirection.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]

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

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 ParameterDirection.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

I have this code:

SqlConnection conn = null;


string Table="";

int rCount;

conn = new

SqlConnection(sConn);

conn.Open();

SqlCommand cmd = new SqlCommand();

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

cmd.CommandType = CommandType.StoredProcedure;

cmd.Connection = conn;

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

cmd.Parameters.Add( new System.Data.SqlClient.SqlParameter("@tbl",
ParameterDirection.Output) );

cmd.ExecuteNonQuery();

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


gridSearch.Grid.DataMember = "";


SqlDataAdapter adap = new SqlDataAdapter();

adap.SelectCommand = 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

 

[Bjorn Abelli]

...

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?

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.

Shouldn't this be 'Cakes'?

// Bjorn A