Store query result in variable

[paresh_thummar]

Hi I want to store query result in single variable like
if query is "select count(*) from tablename" , and it return suppose 21 records (integer value) then I want to save this value in my database during insertion of record
what should i write in place of question mark sign in below code...

string qry;
qry = "insert into General values(@Gno,@GRno,@Fname,@Mname,@Lname,@Sex,@Bdate,@Pass,@Mono,@OtherNo,@Photo,@Role,@GCID)";

                SqlCommand cmd = new SqlCommand(qry, cn);
                cmd.Parameters.AddWithValue("@Gno",??????????? );
                cmd.Parameters.AddWithValue("@GRno", TxtAddress.Text);
                cmd.Parameters.AddWithValue("@Fname", RadioButtonList1.SelectedItem.Text);
                cmd.Parameters.AddWithValue("@Mname", DrpDwnDD.SelectedItem.Text);
                cmd.Parameters.AddWithValue("@Lname", DrpDwnMM.SelectedItem.Text);
                cmd.Parameters.AddWithValue("@Sex", DrpDwnYY.SelectedItem.Text);
                cmd.Parameters.AddWithValue("@Bdate", TxtUserName.Text);
                cmd.Parameters.AddWithValue("@Pass", TxtPassword.Text);
                cmd.Parameters.AddWithValue("@Mono", TxtEmail.Text);
                cmd.Parameters.AddWithValue("@OtherNo", DrpDwnCountry.SelectedItem.Text);
                cmd.Parameters.AddWithValue("@Photo", "~/Photo/" + FileUpload1.FileName);
                cmd.Parameters.AddWithValue("@Role", DroDpwnPerque.SelectedItem.Text);
                cmd.Parameters.AddWithValue("@GCID", TxtAns.Text);
                FileUpload1.SaveAs(Server.MapPath("~/Photo/" + FileUpload1.FileName));
                cmd.ExecuteNonQuery();

Please Help Me..................

[urtrivedi]

Search on excutescalar function.

.
.
.
.

qry1 = "select count (*) from mytable where mycolumn='myvalue'";
                SqlCommand cmd2 = new SqlCommand(qry1, cn);
               value= cmd2.ExecuteScalar();

' now repalce your ?????? with value below

.
.
.
.
.

qry = "insert into General values(@Gno,@GRno,@Fname,@Mname,@Lname,@Sex,@Bdate,@Pass,@Mono,@OtherNo,@Photo,@Role,@GCID)";

SqlCommand cmd2 = new SqlCommand(qry, cn);
cmd.Parameters.AddWithValue("@Gno",value );
cmd.Parameters.AddWithValue("@GRno", TxtAddress.Text);
cmd.Parameters.AddWithValue("@Fname", RadioButtonList1.SelectedItem.Text);
cmd.Parameters.AddWithValue("@Mname", DrpDwnDD.SelectedItem.Text);
cmd.Parameters.AddWithValue("@Lname", DrpDwnMM.SelectedItem.Text);
cmd.Parameters.AddWithValue("@Sex", DrpDwnYY.SelectedItem.Text);
cmd.Parameters.AddWithValue("@Bdate", TxtUserName.Text);
cmd.Parameters.AddWithValue("@Pass", TxtPassword.Text);
cmd.Parameters.AddWithValue("@Mono", TxtEmail.Text);
cmd.Parameters.AddWithValue("@OtherNo", DrpDwnCountry.SelectedItem.Text);
cmd.Parameters.AddWithValue("@Photo", "~/Photo/" + FileUpload1.FileName);
cmd.Parameters.AddWithValue("@Role", DroDpwnPerque.SelectedItem.Text);
cmd.Parameters.AddWithValue("@GCID", TxtAns.Text);
FileUpload1.SaveAs(Server.MapPath("~/Photo/" + FileUpload1.FileName));
cmd.ExecuteNonQuery();

[Akash Saikia]

Add some code in it like this...

Just replace your ??? with numberOfRecords variable that contains that query result of (Select count(*) from tableName)

string qry,numberOfRecords;
  SqlCommand cmd = new SqlCommand();

qry="Select count(*)  from tableName";
 cmd.Connection =cn;
            cmd.CommandText =qry2;
numberOfRecords=cmd.ExecuteScalar ();
qry = "insert into General values(@Gno,@GRno,@Fname,@Mname,@Lname,@Sex,@Bdate,@Pass,@Mono,@OtherNo,@Photo,@Role,@GCID)";

              
                cmd.Parameters.AddWithValue("@Gno",numberOfRecords );
                cmd.Parameters.AddWithValue("@GRno", TxtAddress.Text);
                cmd.Parameters.AddWithValue("@Fname", RadioButtonList1.SelectedItem.Text);
                cmd.Parameters.AddWithValue("@Mname", DrpDwnDD.SelectedItem.Text);
                cmd.Parameters.AddWithValue("@Lname", DrpDwnMM.SelectedItem.Text);
                cmd.Parameters.AddWithValue("@Sex", DrpDwnYY.SelectedItem.Text);
                cmd.Parameters.AddWithValue("@Bdate", TxtUserName.Text);
                cmd.Parameters.AddWithValue("@Pass", TxtPassword.Text);
                cmd.Parameters.AddWithValue("@Mono", TxtEmail.Text);
                cmd.Parameters.AddWithValue("@OtherNo", DrpDwnCountry.SelectedItem.Text);
                cmd.Parameters.AddWithValue("@Photo", "~/Photo/" + FileUpload1.FileName);
                cmd.Parameters.AddWithValue("@Role", DroDpwnPerque.SelectedItem.Text);
                cmd.Parameters.AddWithValue("@GCID", TxtAns.Text);
                FileUpload1.SaveAs(Server.MapPath("~/Photo/" + FileUpload1.FileName));
                cmd.ExecuteNonQuery();

[urtrivedi]

whats new in your code akash

[Akash Saikia]

The executescalar part,that gives the count of records from the table name.
That is what required here.

:)

[Akash Saikia]

Add some code in it like this...

Just replace your ??? with numberOfRecords variable that contains that query result of (Select count(*) from tableName)

string qry,numberOfRecords;
  SqlCommand cmd = new SqlCommand();

qry="Select count(*)  from tableName";
 cmd.Connection =cn;

            cmd.CommandText =qry;
con.Open();
numberOfRecords=cmd.ExecuteScalar ();
con.Close();
qry = "insert into General values(@Gno,@GRno,@Fname,@Mname,@Lname,@Sex,@Bdate,@Pass,@Mono,@OtherNo,@Photo,@Role,@GCID)";

              cmd.CommandText=qry;
con.Open();
                cmd.Parameters.AddWithValue("@Gno",numberOfRecords );
                cmd.Parameters.AddWithValue("@GRno", TxtAddress.Text);
                cmd.Parameters.AddWithValue("@Fname", RadioButtonList1.SelectedItem.Text);
                cmd.Parameters.AddWithValue("@Mname", DrpDwnDD.SelectedItem.Text);
                cmd.Parameters.AddWithValue("@Lname", DrpDwnMM.SelectedItem.Text);
                cmd.Parameters.AddWithValue("@Sex", DrpDwnYY.SelectedItem.Text);
                cmd.Parameters.AddWithValue("@Bdate", TxtUserName.Text);
                cmd.Parameters.AddWithValue("@Pass", TxtPassword.Text);
                cmd.Parameters.AddWithValue("@Mono", TxtEmail.Text);
                cmd.Parameters.AddWithValue("@OtherNo", DrpDwnCountry.SelectedItem.Text);
                cmd.Parameters.AddWithValue("@Photo", "~/Photo/" + FileUpload1.FileName);
                cmd.Parameters.AddWithValue("@Role", DroDpwnPerque.SelectedItem.Text);
                cmd.Parameters.AddWithValue("@GCID", TxtAns.Text);
                FileUpload1.SaveAs(Server.MapPath("~/Photo/" + FileUpload1.FileName));
                cmd.ExecuteNonQuery();

con.Close();

sorry for last Post,where I missed few important points,here is the changed solution for the same...
Thanks urtrivedi :)