out parameter question with datareader function

[Guest]

i have this function which call an sp that has a return code
public SqlDataReader fn_SpecData(Int32 iDate, Int32 iRange, out Int32
iRowCount)
{
string constr =
ConfigurationManager.ConnectionStrings["arlcapConnectionString"].ConnectionString;
SqlConnection sqlcon1 = new SqlConnection(constr);
SqlCommand sqlcmd1 = sqlcon1.CreateCommand();
sqlcmd1.CommandType = CommandType.StoredProcedure;
sqlcmd1.CommandText = "sp_getRatingSummaryRange";
sqlcmd1.Parameters.AddWithValue("@iDate", iDate);
sqlcmd1.Parameters.AddWithValue("@iRange", iRange);
SqlParameter RowCount = sqlcmd1.Parameters.Add("@isOK", null);
RowCount.SqlDbType = SqlDbType.Int;
RowCount.Direction = ParameterDirection.ReturnValue;
sqlcon1.Open();
iRowCount = Convert.ToInt32(RowCount.Value);
return sqlcmd1.ExecuteReader(CommandBehavior.CloseConnection);
}
but the RowCount.Value does not return a value
i've tried a few variations but still no value
if i use the non query function i get a value, but was hoping to combine
*** this one will return a value:
public Int32 fn_getSectorCount()
{
// returns 0 invalid or 1 valid for a userid and password
string constr =
ConfigurationManager.ConnectionStrings["arlcapConnectionString"].ConnectionString;
SqlConnection sqlcon1 = new SqlConnection(constr);
SqlCommand sqlcmd1 = sqlcon1.CreateCommand();
sqlcmd1.CommandType = CommandType.StoredProcedure;
sqlcmd1.CommandText = "sp_getSectorCount";
SqlParameter rowcount = sqlcmd1.Parameters.Add("@rowcount", null);
rowcount.SqlDbType = SqlDbType.Int;
rowcount.Direction = ParameterDirection.ReturnValue;
sqlcon1.Open();
sqlcmd1.ExecuteNonQuery();
sqlcon1.Close();
sqlcmd1.Dispose();
sqlcon1 = null;
sqlcmd1 = null;
return Convert.ToInt32(rowcount.Value);
}
--
(i''ll be asking a lot of these, but I find C# totally way cooler than vb
and there''s no go''n back!!!)
thanks (as always)

kes

 

[DeveloperX]

i have this function which call an sp that has a return code
public SqlDataReader fn_SpecData(Int32 iDate, Int32 iRange, out Int32
iRowCount)
{
string constr =
ConfigurationManager.ConnectionStrings["arlcapConnectionString"].Connection­String;
SqlConnection sqlcon1 = new SqlConnection(constr);
SqlCommand sqlcmd1 = sqlcon1.CreateCommand();
sqlcmd1.CommandType = CommandType.StoredProcedure;
sqlcmd1.CommandText = "sp_getRatingSummaryRange";
sqlcmd1.Parameters.AddWithValue("@iDate", iDate);
sqlcmd1.Parameters.AddWithValue("@iRange", iRange);
SqlParameter RowCount = sqlcmd1.Parameters.Add("@isOK", null);
RowCount.SqlDbType = SqlDbType.Int;
RowCount.Direction = ParameterDirection.ReturnValue;
sqlcon1.Open();
iRowCount = Convert.ToInt32(RowCount.Value);
return sqlcmd1.ExecuteReader(CommandBehavior.CloseConnection);
}
but the RowCount.Value does not return a value
i've tried a few variations but still no value
if i use the non query function i get a value, but was hoping to combine
*** this one will return a value:
public Int32 fn_getSectorCount()
{
// returns 0 invalid or 1 valid for a userid and password
string constr =
ConfigurationManager.ConnectionStrings["arlcapConnectionString"].Connection­String;
SqlConnection sqlcon1 = new SqlConnection(constr);
SqlCommand sqlcmd1 = sqlcon1.CreateCommand();
sqlcmd1.CommandType = CommandType.StoredProcedure;
sqlcmd1.CommandText = "sp_getSectorCount";
SqlParameter rowcount = sqlcmd1.Parameters.Add("@rowcount", null);
rowcount.SqlDbType = SqlDbType.Int;
rowcount.Direction = ParameterDirection.ReturnValue;
sqlcon1.Open();
sqlcmd1.ExecuteNonQuery();
sqlcon1.Close();
sqlcmd1.Dispose();
sqlcon1 = null;
sqlcmd1 = null;
return Convert.ToInt32(rowcount.Value);
}
--
(i''ll be asking a lot of these, but I find C# totally way cooler than vb
and there''s no go''n back!!!)
thanks (as always)

kes

I would assume it's because

sqlcon1.Open();
iRowCount = Convert.ToInt32(RowCount.Value);
return
sqlcmd1.ExecuteReader(CommandBehavior.CloseConnection);

opens the connection then reads the parameter and then executes the
stored proc?

Try assigning the return value from sqlcmd1.ExecuteReader to a
temporary SqlDataReader variable and then do the iRowCount line before
returning your tmp variable.

sqlcon1.Open();
SqlDataReader tmp =
sqlcmd1.ExecuteReader(CommandBehavior.CloseConnection);
iRowCount = Convert.ToInt32(RowCount.Value);
return tmp;

?

 

[Guest]

thanks for replying,
tried that, same thing.

--
(i''ll be asking a lot of these, but I find C# totally way cooler than vb
and there''s no go''n back!!!)
thanks (as always)

kes

i have this function which call an sp that has a return code
public SqlDataReader fn_SpecData(Int32 iDate, Int32 iRange, out Int32
iRowCount)
{
string constr =
ConfigurationManager.ConnectionStrings["arlcapConnectionString"].Connection-String;
SqlConnection sqlcon1 = new SqlConnection(constr);
SqlCommand sqlcmd1 = sqlcon1.CreateCommand();
sqlcmd1.CommandType = CommandType.StoredProcedure;
sqlcmd1.CommandText = "sp_getRatingSummaryRange";
sqlcmd1.Parameters.AddWithValue("@iDate", iDate);
sqlcmd1.Parameters.AddWithValue("@iRange", iRange);
SqlParameter RowCount = sqlcmd1.Parameters.Add("@isOK", null);
RowCount.SqlDbType = SqlDbType.Int;
RowCount.Direction = ParameterDirection.ReturnValue;
sqlcon1.Open();
iRowCount = Convert.ToInt32(RowCount.Value);
return sqlcmd1.ExecuteReader(CommandBehavior.CloseConnection);
}
but the RowCount.Value does not return a value
i've tried a few variations but still no value
if i use the non query function i get a value, but was hoping to combine
*** this one will return a value:
public Int32 fn_getSectorCount()
{
// returns 0 invalid or 1 valid for a userid and password
string constr =
ConfigurationManager.ConnectionStrings["arlcapConnectionString"].Connection-String;
SqlConnection sqlcon1 = new SqlConnection(constr);
SqlCommand sqlcmd1 = sqlcon1.CreateCommand();
sqlcmd1.CommandType = CommandType.StoredProcedure;
sqlcmd1.CommandText = "sp_getSectorCount";
SqlParameter rowcount = sqlcmd1.Parameters.Add("@rowcount", null);
rowcount.SqlDbType = SqlDbType.Int;
rowcount.Direction = ParameterDirection.ReturnValue;
sqlcon1.Open();
sqlcmd1.ExecuteNonQuery();
sqlcon1.Close();
sqlcmd1.Dispose();
sqlcon1 = null;
sqlcmd1 = null;
return Convert.ToInt32(rowcount.Value);
}
--
(i''ll be asking a lot of these, but I find C# totally way cooler than vb
and there''s no go''n back!!!)
thanks (as always)

kes

I would assume it's because

sqlcon1.Open();
iRowCount = Convert.ToInt32(RowCount.Value);
return
sqlcmd1.ExecuteReader(CommandBehavior.CloseConnection);

opens the connection then reads the parameter and then executes the
stored proc?

Try assigning the return value from sqlcmd1.ExecuteReader to a
temporary SqlDataReader variable and then do the iRowCount line before
returning your tmp variable.

sqlcon1.Open();
SqlDataReader tmp =
sqlcmd1.ExecuteReader(CommandBehavior.CloseConnection);
iRowCount = Convert.ToInt32(RowCount.Value);
return tmp;

?

 

[Guest]

i have this function which call an sp that has a return code 8< snip
but the RowCount.Value does not return a value
i've tried a few variations but still no value
if i use the non query function i get a value, but was hoping to combine

You have to read everything in the data reader (or close it) before you
can read the output parameters.

 

[Guest]

thank you , that explains a lot.
Is this an issue with ado.net or sql server?
i've found an artical that says it an be done, but so far it has not worked
for me.
see: http://aspnet.4guysfromrolla.com/articles/062905-1.aspx
--
(i''ll be asking a lot of these, but I find C# totally way cooler than vb
and there''s no go''n back!!!)
thanks (as always)

kes

 

[Guest]

thank you , that explains a lot.
Is this an issue with ado.net or sql server?

This is documented for the base classes in ADO.NET, so it applies to all
database solutions.

"Output, InputOut, and ReturnValue parameters returned by calling
ExecuteReader cannot be accessed until you call Close or Dispose on the
DbDataReader."

http://msdn2.microsoft.com/en-us/library/system.data.common.dbparameter.direction.aspx

i've found an artical that says it an be done, but so far it has not worked
for me.
see: http://aspnet.4guysfromrolla.com/articles/062905-1.aspx

Yes, it can be done, I've done it myself. Unfortunately that code is way
to involved to post here, but if you just do it in the correct order,
it's not that complicated at all.