0

I am having problem with SQLDataReader when it comes into situation that I have one row of data, and one or tho columns (fields) have null value.

Can anybody help with this? Null values are related to string, float and datetime datatype.

First method is:

public class SpajanjeCollection:ObservableCollection<Spajanje>
{
    public static SpajanjeCollection GetAllSpajanjes() 
    {
        SpajanjeCollection spajanjes = new SpajanjeCollection();  
        Spajanje spajanje = null;  

        using (SqlConnection conn = new SqlConnection())  
        {
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnString"].ToString();    
            conn.Open();    

            SqlCommand command = new SqlCommand("SELECT Ponude.IdPonude, Ponude.SifraProizvoda, Proizvodi.Naziv, Proizvodi.Opis, Proizvodi.PocetnaCijena, Ponude.PocetakAukcije, Users.UserName, Ponude.PonudjenaCijena, Ponude.ZavrsetakAukcije, Ponude.Status FROM  Ponude LEFT OUTER JOIN Users ON Ponude.Kupac = Users.UserName INNER JOIN Proizvodi ON Ponude.SifraProizvoda = Proizvodi.Id", conn);

            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())   
                {
                    spajanje = Spajanje.GetSpajanjeFromResultSet(reader);
                    spajanjes.Add(spajanje);    
                }
            }
        }
        return spajanjes;   
    }
}

Second method for data reading is:

    public static Spajanje GetSpajanjeFromResultSet(SqlDataReader reader) 
    {
        Spajanje spajanje = new Spajanje(Convert.ToInt16(reader["IdPonude"]), Convert.ToInt16(reader["SifraProizvoda"]), Convert.ToString(reader["Naziv"]), Convert.ToString(reader["Opis"]), float.Parse(reader["PocetnaCijena"].ToString()), (DateTime?)reader["PocetakAukcije"], Convert.ToString(reader["UserName"]), float.Parse(reader["PonudjenaCijena"].ToString()), (DateTime?)reader["ZavrsetakAukcije"], Convert.ToString(reader["Status"]));

        return spajanje;    
    }
Hannah Vernon
  • 3,367
  • 1
  • 26
  • 48
  • It doesn't help much. I don't need to test if some rows have empty value, but I need to force SqlDataReader to properly read those rows without stacking application –  Jun 19 '19 at 20:04
  • I can not read data from SQL Server table if it finds row in which some fields have null value –  Jun 19 '19 at 19:36
  • Still not good enough. What do you mean by "cannot read"? If you get an error, show the error. If you get unexpected results, explain what they are and what you expect instead. – mustaccio Jun 19 '19 at 19:43
  • When I start program, I am getting error message "System.FormatException: 'Input string was not in a correct format.'" on Second method. Is it more clear now. I have data in SQL table but some fields have null value and when I start program I get this message –  Jun 19 '19 at 19:48
  • Add a COALESCE around the column names in the DataReader query. – Hannah Vernon Jun 19 '19 at 20:08
  • Please, can you use my code and show me exactly how should it look like. Thank you in advance, I am beginner in C# and SQL and I really need help –  Jun 19 '19 at 20:12
  • coalesce is not solution. I want that SQLDataReader read all values from row and if it finds some column null, replace it with "" of 0. I don't want to change array structure –  Jun 19 '19 at 20:31
  • 2
    Possible duplicate of [how to check if a datareader is null or empty](https://stackoverflow.com/questions/762861/how-to-check-if-a-datareader-is-null-or-empty) – Josh Darnell Jun 19 '19 at 23:53

0 Answers0