hasrow and execute reader loop gets error in the value exist check DB

[soundararajanom]

hi iam using this code for datagrid row storage while check data is exist or not in DB sql server 2008

cmd.Parameters["@pname"].Value = row.Cells["gpname"].Value;
cmd.Parameters["@cstock"].Value = row.Cells["gcstock"].Value;
cmd.Parameters["@ondate"].Value = DateTime.Now;
check();
 

after to debug in comes in check function first time hasrow function is true value check and get display as value exist then loop 2nd time comes sqldatareader comesdirectly out and has row function is false

 using (Conn = new SqlConnection(constr))
{
  var name = cmd.Parameters["@pname"].Value;
  var date = cmd.Parameters["@ondate"].Value;
  string dd = String.Format("{0:yyyy/MM/dd}", date);
  string query = "select pname from stock1 where pname='"+name+"' and ondate='"+dd+"'";
  cmd1 = new SqlCommand(query, Conn);
  cmd1.CommandType = CommandType.Text;
  try
  {
    Conn.Open();
    SqlDataReader sdr = cmd1.ExecuteReader();
 
    if(sdr.HasRows)
    {
      while (sdr.Read())
      {
        string s1 = sdr.GetString(0);
        if (s1 == name.ToString())
        {
          MessageBox.Show("value already exist");
        }
        else
        {
          cmd.ExecuteNonQuery();
        }
 
      }
    }
  }

how to set hasrow get always execute reader in the program

[Frinavale]

I don't fully understand your question but I do see some strange stuff in your code.

You are setting values for parameters for your SQL command but you actually don't have any parameters in the command text itself


Based on your posted code, this:

string query = "select pname from stock1 where pname='"+name+"' and ondate='"+dd+"'"; 

Should probably be this:

string query = "select pname from stock1 where pname=@pname and ondate=@ondate"; 

Fixing the SQL command isn't the only problem I see though: the fact that your ondate is being set to Now will very likely result in nothing being returned because it is highly unlikely that the date in the table will match the current date time since the date in the table was saved sometime in the past.

Also, you do not need to execute a query in the middle of reading the data of a query.... you should probably check out this MSDN document about the SqlCommand.ExecuteNonQuery Method for more information on how to use this function.

Also, check out this MSDN document on the SqlCommand.Parameters Property for more information on how to use parameters.