471,337 Members | 1,129 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,337 software developers and data experts.

return value from store procedure

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
Dec 9 '05 #1
3 1722
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" <hr*********@luatech.com> wrote in message
news:dn**********@ss405.t-com.hr...
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

Dec 9 '05 #2
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" <mg******@rm.com> wrote in message
news:u4**************@tk2msftngp13.phx.gbl...
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" <hr*********@luatech.com> wrote in message
news:dn**********@ss405.t-com.hr...
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


Dec 9 '05 #3

"Hrvoje Voda" wrote...
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.
if exists
(
select name, ingridients from Cakes where Name = @Name
)
set @tbl='Case'


Shouldn't this be 'Cakes'?

// Bjorn A
Dec 9 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Daniel Caetano | last post: by
1 post views Thread by Hugo Lefevre | last post: by
1 post views Thread by ken | last post: by
2 posts views Thread by philip | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.