Reading ID value always is 0

Asked by At

I have a database from which I want to read a particular value.

When I am reading ID is always showing up as 0.

public void ClustersStatus(Clusters cl)
{
    using (SqlConnection con = new SqlConnection("Data Source=DESKTOP-JMGPJ6J;Initial Catalog=Infinity;Integrated Security=True"))
    {
        using (SqlCommand cmd = new SqlCommand("Select Approved from Master_Cluster where ClusterID = @id", con))
        {
            con.Open();

            cmd.Parameters.AddWithValue("@id", cl.ClusterID);

            cmd.Connection = con;

            SqlDataReader rdr = cmd.ExecuteReader();

            if (rdr.HasRows)
            {
                rdr.Read();

                if (Convert.ToInt32(rdr["Approved"]) == 1)
                {
                }
                else
                {
                }
            }
        }

        // return cl;
    }
}

2 Answers

0
Bogdan Doicin On

Supposed your query offers you what you want, to be able to use the data read with SqlDataReader, you need to use the IDataRecord interface. You cannot use directly the rdr variable.

For ease of use, the I wrote the reading engine into a function. I am writing here the idea for you to adapt it to your needs:

        private static int ReadSingleRow(IDataRecord record)
        {
//below is an example of processing using `record`. You adapt it to your needs
            int.TryParse(record[0].ToString(), out int temp);
            MessageBox.Show (temp.ToString());
//...do the processing you need
            return temp;
        }

After you wrote the function, the main becomes:

              if (rdr.HasRows)
                {
                  while (rdr.Read());
                   int x=ReadSingleRow((IDataRecord) rdr); 
//or use, somehow, the above function
// other processing

                }
1
Soner Gönül On

It is really hard to answer your question without even more information about your specific problem, but I think the real issue in your

rdr.Read();

line. SqlDataReader.Read method advances your data reader to the next record which means that you are reading your rdr["Approved"] column of your next line with your

if (Convert.ToInt32(rdr["Approved"]) == 1)

code which seems weird and I think this is not what you want. You might wanna consider to delete it.

Also I have a few suggestions;

  • Do not put your connection string directly in your code. These settings can be change over time to time. Put it some configuration file or read it from some settings file.
  • Open your connection object just before you use it. Not a big deal, but it is a good practice.
  • Do not use AddWithValue method. It may generate unexpected results sometimes. Use Add method instead (preferably Add(String, SqlDbType, Int32) overload)