BUG? SELECT statment w/ Return Value doesn't work together

  • Thread starter Thread starter Jordan
  • Start date Start date
J

Jordan

What I'm trying to do is exectue a stored procedure that returns some
records along with an additional RETURN value. The code is below:

***
SqlConnection Conn = new SqlConnection
(System.Configuration.ConfigurationSettings.AppSettings["MyConn"]);
SqlCommand Cmd = new SqlCommand("spMySP", Conn);
Cmd.CommandType = CommandType.StoredProcedure; Cmd.Parameters.Add (new
SqlParameter("@RowCount", SqlDbType.Int));
Cmd.Parameters["@RowCount"].Direction = ParameterDirection.ReturnValue;
Cmd.Parameters.Add (new SqlParameter("@Parm1", SqlDbType.VarChar, 5));
Cmd.Parameters["@Parm1"].Value = Parm1;
Conn.Open(); SqlDataReader DR = Cmd.ExecuteReader();
Message.Text = Cmd.Parameters["@RowCount"].Value.ToString(); !!!!
***

The problem (i.e. bug) is that I can do either get the results, or get the
return value, but not both. When I have the SELECT statement in the Stored
Proc, it runs and I can bind the DataReader results to a Repeater, but when
then I get a compile error on the line marked above with "!!!!." If I
comment out the line, it works. On the otherhand, if I comment out the
SELECT statement in my Stored Proc, everything compiles and I can see my
return value in the <asp:label id="Message"> field. WHAT THE ...?

Any ideas?

j
 
How will that work with the method I'm currently working on. I'd like to use
WebControls where possible and not a custom loop if I don't have to.

Jordan


William Ryan eMVP said:
Try calling While dr.Read --- check out Retrieving the Gozoutas by Bill
Vaugh at www.betav.com -> Articles -> MSDN

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
Jordan said:
What I'm trying to do is exectue a stored procedure that returns some
records along with an additional RETURN value. The code is below:

***
SqlConnection Conn = new SqlConnection
(System.Configuration.ConfigurationSettings.AppSettings["MyConn"]);
SqlCommand Cmd = new SqlCommand("spMySP", Conn);
Cmd.CommandType = CommandType.StoredProcedure; Cmd.Parameters.Add (new
SqlParameter("@RowCount", SqlDbType.Int));
Cmd.Parameters["@RowCount"].Direction = ParameterDirection.ReturnValue;
Cmd.Parameters.Add (new SqlParameter("@Parm1", SqlDbType.VarChar, 5));
Cmd.Parameters["@Parm1"].Value = Parm1;
Conn.Open(); SqlDataReader DR = Cmd.ExecuteReader();
Message.Text = Cmd.Parameters["@RowCount"].Value.ToString(); !!!!
***

The problem (i.e. bug) is that I can do either get the results, or get the
return value, but not both. When I have the SELECT statement in the Stored
Proc, it runs and I can bind the DataReader results to a Repeater, but when
then I get a compile error on the line marked above with "!!!!." If I
comment out the line, it works. On the otherhand, if I comment out the
SELECT statement in my Stored Proc, everything compiles and I can see my
return value in the <asp:label id="Message"> field. WHAT THE ...?

Any ideas?

j
Click to expand...
Click to expand...
 
Oops...didn't close the DataReader, that was the problem. Thanks for the
article, great stuff.

Jordan

William Ryan eMVP said:
Try calling While dr.Read --- check out Retrieving the Gozoutas by Bill
Vaugh at www.betav.com -> Articles -> MSDN

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
Jordan said:
What I'm trying to do is exectue a stored procedure that returns some
records along with an additional RETURN value. The code is below:

***
SqlConnection Conn = new SqlConnection
(System.Configuration.ConfigurationSettings.AppSettings["MyConn"]);
SqlCommand Cmd = new SqlCommand("spMySP", Conn);
Cmd.CommandType = CommandType.StoredProcedure; Cmd.Parameters.Add (new
SqlParameter("@RowCount", SqlDbType.Int));
Cmd.Parameters["@RowCount"].Direction = ParameterDirection.ReturnValue;
Cmd.Parameters.Add (new SqlParameter("@Parm1", SqlDbType.VarChar, 5));
Cmd.Parameters["@Parm1"].Value = Parm1;
Conn.Open(); SqlDataReader DR = Cmd.ExecuteReader();
Message.Text = Cmd.Parameters["@RowCount"].Value.ToString(); !!!!
***

The problem (i.e. bug) is that I can do either get the results, or get the
return value, but not both. When I have the SELECT statement in the Stored
Proc, it runs and I can bind the DataReader results to a Repeater, but when
then I get a compile error on the line marked above with "!!!!." If I
comment out the line, it works. On the otherhand, if I comment out the
SELECT statement in my Stored Proc, everything compiles and I can see my
return value in the <asp:label id="Message"> field. WHAT THE ...?

Any ideas?

j
Click to expand...
Click to expand...
 
"Vaughn"... ;)

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

William Ryan eMVP said:
Try calling While dr.Read --- check out Retrieving the Gozoutas by Bill
Vaugh at www.betav.com -> Articles -> MSDN

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
Jordan said:
What I'm trying to do is exectue a stored procedure that returns some
records along with an additional RETURN value. The code is below:

***
SqlConnection Conn = new SqlConnection
(System.Configuration.ConfigurationSettings.AppSettings["MyConn"]);
SqlCommand Cmd = new SqlCommand("spMySP", Conn);
Cmd.CommandType = CommandType.StoredProcedure; Cmd.Parameters.Add (new
SqlParameter("@RowCount", SqlDbType.Int));
Cmd.Parameters["@RowCount"].Direction = ParameterDirection.ReturnValue;
Cmd.Parameters.Add (new SqlParameter("@Parm1", SqlDbType.VarChar, 5));
Cmd.Parameters["@Parm1"].Value = Parm1;
Conn.Open(); SqlDataReader DR = Cmd.ExecuteReader();
Message.Text = Cmd.Parameters["@RowCount"].Value.ToString(); !!!!
***

The problem (i.e. bug) is that I can do either get the results, or get the
return value, but not both. When I have the SELECT statement in the Stored
Proc, it runs and I can bind the DataReader results to a Repeater, but when
then I get a compile error on the line marked above with "!!!!." If I
comment out the line, it works. On the otherhand, if I comment out the
SELECT statement in my Stored Proc, everything compiles and I can see my
return value in the <asp:label id="Message"> field. WHAT THE ...?

Any ideas?

j
Click to expand...
Click to expand...
 
Sorry about that - I type it enough I ought to know it by now ;-)

--

W.G. Ryan, eMVP

Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/community/newsgroups
William (Bill) Vaughn said:
"Vaughn"... ;)

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

William Ryan eMVP said:
Try calling While dr.Read --- check out Retrieving the Gozoutas by Bill
Vaugh at www.betav.com -> Articles -> MSDN

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
Jordan said:
What I'm trying to do is exectue a stored procedure that returns some
records along with an additional RETURN value. The code is below:

***
SqlConnection Conn = new SqlConnection
(System.Configuration.ConfigurationSettings.AppSettings["MyConn"]);
SqlCommand Cmd = new SqlCommand("spMySP", Conn);
Cmd.CommandType = CommandType.StoredProcedure; Cmd.Parameters.Add (new
SqlParameter("@RowCount", SqlDbType.Int));
Cmd.Parameters["@RowCount"].Direction = ParameterDirection.ReturnValue;
Cmd.Parameters.Add (new SqlParameter("@Parm1", SqlDbType.VarChar, 5));
Cmd.Parameters["@Parm1"].Value = Parm1;
Conn.Open(); SqlDataReader DR = Cmd.ExecuteReader();
Message.Text = Cmd.Parameters["@RowCount"].Value.ToString(); !!!!
***

The problem (i.e. bug) is that I can do either get the results, or get the
return value, but not both. When I have the SELECT statement in the Stored
Proc, it runs and I can bind the DataReader results to a Repeater, but when
then I get a compile error on the line marked above with "!!!!." If I
comment out the line, it works. On the otherhand, if I comment out the
SELECT statement in my Stored Proc, everything compiles and I can see my
return value in the <asp:label id="Message"> field. WHAT THE ...?

Any ideas?

j
Click to expand...
Click to expand...
Click to expand...
 
Back
Top