Problem Reading Data

Question

HI Im getting an arroe when reaching :
dr = cmd.ExecuteReader();
I can't now wht is the problem
this my code in the btnSAVE:
cmd = new SqlCommand ( "sp_Main_Information" , con);               cmd.CommandType = CommandType .StoredProcedure;               cmd.Parameters.AddWithValue( "@REG_Date" , dt);             cmd.Parameters.AddWithValue( "@NAME" , name);             cmd.Parameters.AddWithValue( "@ADDRESS" , Address);             cmd.Parameters.AddWithValue( "@YEAR" , Year);             cmd.Parameters.AddWithValue( "@CURRENT_GPA " , gpa);             cmd.Parameters.AddWithValue( "@ATTCH_ID1" , img1);             cmd.Parameters.AddWithValue( "@ATTCH_ID2 , img2);             cmd.Parameters.AddWithValue( "@REMARKS" , remarks.Text);               dr = cmd.ExecuteReader();             dr.Read();                   int xx = int .Parse(dr[0].ToString());                 Response.Write(xx);                       for (i = 0; i                     {                         if (chktype.Items[i].Selected == true )                         {                             cmd = new SqlCommand ();                             cmd.CommandText = "insert into tblItemstb values ('" + chktype.Items[i].Text + "'," + xx + ")" ;                             cmd.Connection = con;                             cmd.ExecuteNonQuery();                         }                       }               dr.Close();             cmd.ExecuteNonQuery();               dr = cmd.ExecuteReader();             dr.Read();                   int yy = int .Parse(dr[0].ToString());                 Response.Write(yy);                       for (i = 0; i                     {                         if (chkoption.Items[i].Selected == true )                         {                             cmd = new SqlCommand ();                             cmd.CommandText = "insert into tbloptions values ('" + chkoption.Items[i].Text + "'," + yy + ")" ;                             cmd.Connection = con;                             cmd.ExecuteNonQuery();                         }                       }               dr.Close();             cmd.ExecuteNonQuery();               con.Close();               lbldone.Text = "Record Added Successfully!" ;           }       }

Vulpes · Accepted Answer

That's right.
You can just call ExecuteNonQuery and then read the return value of the stored procedure from the Value property of the return parameter.
However, just noticed I've made a couple of silly mistakes - corrected them now :)

ToBe · Answer

thanks Vulpes when I pass the parameter using the stored procedure all of the data goes fine to the main table but when I want to insert the check list values in to the other tables i started to i get this problem . from your code I guess I don't need to use sqlreader ?

Vulpes · Answer

If you're trying to execute a stored procedure with a return value, then you need to declare a parameter to hold the return value.
Try this:
cmd = new SqlCommand("sp_Main_Information", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@REG_Date", dt);
cmd.Parameters.AddWithValue("@NAME", name);
cmd.Parameters.AddWithValue("@ADDRESS", Address);
cmd.Parameters.AddWithValue("@YEAR", Year);
cmd.Parameters.AddWithValue("@CURRENT_GPA ", gpa);
cmd.Parameters.AddWithValue("@ATTCH_ID1", img1);
cmd.Parameters.AddWithValue("@ATTCH_ID2 , img2);
cmd.Parameters.AddWithValue("@REMARKS", remarks.Text);
SqlParameter returnParameter = cmd.Parameters.Add("@RETURNVAL", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();
int xx = (int)returnParameter.Value;
Response.Write(xx);
SqlCommand cmd2 = null; // use a different variable for the inserts
for (i = 0; i cmd.ExecuteNonQuery(); // call stored procedure again
int yy = (int)returnParameter.Value;
Response.Write(yy);
for (i = 0; i < chkoption.Items.Count; i++)
{
if (chkoption.Items[i].Selected == true)
{
cmd2 = new SqlCommand();
cmd2.CommandText = "insert into tbloptions values ('" + chkoption.Items[i].Text + "'," + yy + ")";
cmd2.Connection = con;
cmd2.ExecuteNonQuery();
cmd2.Dispose();
}
}
cmd.Dispose();
con.Close();
lbldone.Text = "Record Added Successfully!";