I cannot get rows from reader = cmd.ExecuteReader() using SP

Niyazi Toros

10y1.1kVisual Basic .NET

Hi,

I have an SP like this;

/*Parameters*/
ALTER Procedure [dbo].[VerificationControl](
@Value1 nvarchar(16),
@Value2 nvarchar(4),
@Value3 nvarchar(6),
@Value4 nvarchar(10),
@Value5 nvarchar(20),
@Value6 nvarchar(20) output,
@Value7 nvarchar(20) output,
@Value8 nvarchar(50) output
)
AS
BEGIN
....
....

I am using reader = cmd.ExecuteReader();

But I cannot get any data from while (reader.Read()).

When I debug I cannot enter while (reader.Read()) loop.

// SET THE CONNECTIN VARIABLES
SqlConnection cnn;
cnn = new SqlConnection(connetionString);
// SET CONNECTION AND OPEN
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;
DataTable dt = new DataTable();
cmd.CommandText = "VerificationControl";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Value1", "AAA"
cmd.Parameters.AddWithValue("@Value2", "BBB"
cmd.Parameters.AddWithValue("@Value3", "CCC"
cmd.Parameters.AddWithValue("@Value4", "DDD"
cmd.Parameters.AddWithValue("@Value5", "EEE"
cmd.Parameters.AddWithValue("@Value6", "");
cmd.Parameters.AddWithValue("@Value7", "");
cmd.Parameters.AddWithValue("@Value8", "");
cmd.Connection = cnn;
cnn.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
string mCusName = "";
mCusName = reader[0].ToString();
string mSurName = "";
mSurName = reader[1].ToString();
string mStatusCode = "";
mStatusCode = reader[2].ToString();
}
reader.Close();
cnn.Close();

When I look at the my SQL table that my SP works perfectly.

Any help plase?

Kind Regards

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ranjit Powar10y ago
Accepted answer
Add output parameters like this
SqlParameter outPutParameter = new SqlParameter();
outPutParameter.ParameterName = “@Value6”;
outPutParameter.SqlDbType = System.Data.SqlDbType.NVarChar;
outPutParameter.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(outPutParameter);
Niyazi Toros10y ago
Hi Ranjit,
I am getting an error as;
"String[5]: the Size property has an invalid size of 0." string;

Then I find the soultion as:
SqlParameter outPutParameter1 = new SqlParameter();
outPutParameter1.ParameterName = "@CusName";
outPutParameter1.SqlDbType = System.Data.SqlDbType.NVarChar;
outPutParameter1.Direction = System.Data.ParameterDirection.Output;
outPutParameter1.Size = 20;
cmd.Parameters.Add(outPutParameter1);

SqlParameter outPutParameter2 = new SqlParameter();
outPutParameter2.ParameterName = "@CusSurName";
outPutParameter2.SqlDbType = System.Data.SqlDbType.NVarChar;
outPutParameter2.Direction = System.Data.ParameterDirection.Output;
outPutParameter2.Size = 20;
cmd.Parameters.Add(outPutParameter2);

SqlParameter outPutParameter3 = new SqlParameter();
outPutParameter3.ParameterName = "@StatusCode";
outPutParameter3.SqlDbType = System.Data.SqlDbType.Bit;
outPutParameter3.Direction = System.Data.ParameterDirection.Output;
outPutParameter3.Size = 50;
cmd.Parameters.Add(outPutParameter3);
Parameter Size is required for variable size Output parameters. I need to provide the size required for the parameter.... So I set the Parameter size to size of the output variable from the SQL.
Kind Regards