CODEWITHSUNDEEP
c#
Roman
Roman

Reputation: 37

Invalid attempt to read when no data is present

This is my code:

 db.Open();

 string updateString = "SELECT TOP 1 RTRIM(kol) kol, RTRIM(adres) adres, RTRIM(numwave) numwave FROM sorters WHERE kodprod=@kodprod AND sorter_kod=@sorter AND moved_ok IS NULL ORDER BY CAST(kol as int)";
 try
 {
     SqlCommand command = new SqlCommand(updateString, db);
     //command.Parameters.AddWithValue("@numdoc", NumDoc);
     command.Parameters.AddWithValue("@kodprod", KodProd.Id);
     command.Parameters.AddWithValue("@sorter", SorterKod);
     SqlDataReader reader = command.ExecuteReader();
     reader.Read();//here error
     Kol = reader["kol"].ToString();
     Adres = reader["adres"].ToString();
     NumWave = reader["numwave"].ToString();
     NumDoc = reader["numdoc"].ToString();
     reader.Close();
  }
  catch (Exception ex)
  { }

Why do I get this error when I run my code?:

Invalid attempt to read when no data is present

Upvotes: 0

Views: 342

Answers (4)

Mihai Caracostea
Mihai Caracostea

Reputation: 8466

Use reader.HasRows

string updateString = "SELECT TOP 1 RTRIM(kol) kol, RTRIM(adres) adres, RTRIM(numwave) numwave FROM sorters WHERE kodprod=@kodprod AND sorter_kod=@sorter AND moved_ok IS NULL ORDER BY CAST(kol as int)"; 
try 
{ 
SqlCommand command = new SqlCommand(updateString, db); 
//command.Parameters.AddWithValue("@numdoc", NumDoc); 
command.Parameters.AddWithValue("@kodprod", KodProd.Id); 
command.Parameters.AddWithValue("@sorter", SorterKod); SqlDataReader 
reader = command.ExecuteReader();
if(reader.HasRows)
 while(reader.Read())//here error 
{ 
Kol = reader["kol"].ToString(); 
Adres = reader["adres"].ToString(); 
NumWave = reader["numwave"].ToString(); 
NumDoc = reader["numdoc"].ToString(); 
} 
reader.Close(); 
}
catch{}

EDIT: sorry for the bad formatting, posting code from the Android app is a mess.

EDIT: See Microsoft example here

Upvotes: 0

Sachu
Sachu

Reputation: 7766

you should do a while loop to check reader contain data. You can also use IF if you are sure the query return only one row. If more than one row you should use While. In your case IF also do the job because you are only taking TOP1

string updateString = "SELECT TOP 1 RTRIM(kol) kol, RTRIM(adres) adres,
                       RTRIM(numwave) numwave FROM sorters WHERE 
                       kodprod=@kodprod AND sorter_kod=@sorter
                      AND moved_ok IS NULL ORDER BY CAST(kol as int)";
                try
                {
                    SqlCommand command = new SqlCommand(updateString, db);
                    //command.Parameters.AddWithValue("@numdoc", NumDoc);
                    command.Parameters.AddWithValue("@kodprod", KodProd.Id);
                    command.Parameters.AddWithValue("@sorter", SorterKod);
                    SqlDataReader reader = command.ExecuteReader();
                    while(reader.Read())
                     {
                    Kol = reader["kol"].ToString();
                    Adres = reader["adres"].ToString();
                    NumWave = reader["numwave"].ToString();
                    NumDoc = reader["numdoc"].ToString();
                     } 
                    reader.Close();
                }

Upvotes: 0

StuartLC
StuartLC

Reputation: 107277

I believe the error will in fact occur on the next line, viz when you access the reader via the index [] operator. What you need to do is check the result of reader.Read() before accessing it:

if (reader.Read())
{
    Kol = reader["kol"].ToString();
    Adres = reader["adres"].ToString();
    NumWave = reader["numwave"].ToString();
    NumDoc = reader["numdoc"].ToString();
}

Since you are only returning a maximum of one row (TOP 1) there will either be zero or one rows.

Upvotes: 1

Sai Kalyan Kumar Akshinthala
Sai Kalyan Kumar Akshinthala

Reputation: 11844

You can check whether the DataReader is ready to fetch the rows

if(reader.HasRows)
{
   //do the coding here
}

Upvotes: 5

Related Questions