SqlDataReader read() working unconscionability

Question

im getting data from the server as expected as you can see here (the data is not null)

but it won't enter to while (reader.Read())

or sometimes after one or two iteration im getting the exception

"Data is Null. This method or property cannot be called on Null values."

again, the data is not null

EDIT: added some code snippet

 System.Data.SqlClient.SqlConnection sqlConnection = null;
            try
            {
                sqlConnection = connectToDB();

                SqlCommand cmd = new SqlCommand(@"SELECT TOP 50 R.[RoundID] ,[DailyOrder] ,min(R.[RoundName]) as RoundName, min([EquipCode]) as TruckCode,
                                                sum(RD.[Weight]) as [Weight], RD.BlilCode, min(Blil.BlilName) as BlilName
                                                FROM [CVfeedDB].[dbo].[Planning.Rounds] as R 
                                                left join [CVfeedDB].[dbo].[Planning.RoundsDetail] as RD on R.RoundID = RD.RoundID 
                                                left join [CVfeedDB].[dbo].[constants.Blil] as Blil on RD.BlilCode = Blil.BlilCode  
                                                WHERE R.[ActionDate] = @ActionDate
                                                Group by R.[RoundID] ,[DailyOrder], RD.BlilCode  
                                                order by [DailyOrder]  ", sqlConnection);
                cmd.CommandType = System.Data.CommandType.Text;
                //string date = Convert.ToString();
                var dt = DateTime.ParseExact(Variables().Item("Date Select").get_Value(0).ToString(), "dd/MM/yyyy", null);
                var dt1 = dt.Date.ToString("yyyy-MM-dd");
                cmd.Parameters.AddWithValue("@ActionDate",dt1 );
                string prefix = "";
                int i = 1;
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    prefix = ordersName + i.ToString() + "]";
                    List<object> list = new List<object>();
                    List<object> IDList = new List<object>();
                    int id = Convert.ToInt32(Variables().Item(prefix + ".LoopID").get_Value(0));
                    int k = reader.GetInt32(0);

                    if (id == 0 || id == k)//if no id or we are writing the same id                        
                    {
                        Variables().Item(prefix + ".LoopID").set_Value(0, reader.GetInt32(0));
                        //Variables().Item(prefix + ".").set_Value(0, reader.GetInt32(1));      //Order sequence
                        Variables().Item(prefix + ".LoopName").set_Value(0, reader.GetString(2));
                        Variables().Item(prefix + ".Truck").set_Value(0, Convert.ToInt32(reader.GetString(3)));
                        Variables().Item(prefix + ".Weight").set_Value(0, Convert.ToInt32(reader.GetDecimal(4)));
                        Variables().Item(prefix + ".MixID").set_Value(0, Convert.ToInt32(reader.GetString(5)));
                        Variables().Item(prefix + ".MixName").set_Value(0, reader.GetString(6));
                    }

please not that Variables().Item(...) it how I communicate with 3rd party software

You're performing LEFT JOINs and then returning some columns directly from those tables without even considering aggregates. You have to anticipate that, in some rows, some values will be NULL but your code doesn't currently protect from that situation. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented May 23, 2017 at 7:19
@Damien_The_Unbeliever as i said i checked the result in the DB and there are no null(s) what so ever in the results — styx
– styx, Commented May 23, 2017 at 7:26
Plainly, ADO.NET disagrees with you. It doesn't randomly assign error message strings to exceptions. It is reading a NULL. There's insufficient context here for anyone else to debug this situation. You're the only one with a complete set of runnable code and your actual data. We can't help as this currently stands. If you're able to, please create a minimal reproducible example. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented May 23, 2017 at 7:29

Ravi · Accepted Answer · 2017-05-23 07:39:37Z

First problem is that your SqlConnection is null. It will always return null value.

Secondally You should check the reader has rows like i m using reader.Hasrows

System.Data.SqlClient.SqlConnection sqlConnection = null;
                try
                {
                    sqlConnection = connectToDB();

                    SqlCommand cmd = new SqlCommand(@"SELECT TOP 50 R.[RoundID] ,[DailyOrder] ,min(R.[RoundName]) as RoundName, min([EquipCode]) as TruckCode,
                                                    sum(RD.[Weight]) as [Weight], RD.BlilCode, min(Blil.BlilName) as BlilName
                                                    FROM [CVfeedDB].[dbo].[Planning.Rounds] as R 
                                                    left join [CVfeedDB].[dbo].[Planning.RoundsDetail] as RD on R.RoundID = RD.RoundID 
                                                    left join [CVfeedDB].[dbo].[constants.Blil] as Blil on RD.BlilCode = Blil.BlilCode  
                                                    WHERE R.[ActionDate] = @ActionDate
                                                    Group by R.[RoundID] ,[DailyOrder], RD.BlilCode  
                                                    order by [DailyOrder]  ", sqlConnection);
                    cmd.CommandType = System.Data.CommandType.Text;
                    //string date = Convert.ToString();
                    var dt = DateTime.ParseExact(Variables().Item("Date Select").get_Value(0).ToString(), "dd/MM/yyyy", null);
                    var dt1 = dt.Date.ToString("yyyy-MM-dd");
                    cmd.Parameters.AddWithValue("@ActionDate",dt1 );
                    string prefix = "";
                    int i = 1;
                    SqlDataReader reader = cmd.ExecuteReader();

                if(reader.HasRows)
                {
                    while (reader.Read())
                    {
                        prefix = ordersName + i.ToString() + "]";
                        List<object> list = new List<object>();
                        List<object> IDList = new List<object>();
                        int id = Convert.ToInt32(Variables().Item(prefix + ".LoopID").get_Value(0));
                        int k = reader.GetInt32(0);

                        if (id == 0 || id == k)//if no id or we are writing the same id                        
                        {
                            Variables().Item(prefix + ".LoopID").set_Value(0, reader.GetInt32(0));
                            //Variables().Item(prefix + ".").set_Value(0, reader.GetInt32(1));      //Order sequence
                            Variables().Item(prefix + ".LoopName").set_Value(0, reader.GetString(2));
                            Variables().Item(prefix + ".Truck").set_Value(0, Convert.ToInt32(reader.GetString(3)));
                            Variables().Item(prefix + ".Weight").set_Value(0, Convert.ToInt32(reader.GetDecimal(4)));
                            Variables().Item(prefix + ".MixID").set_Value(0, Convert.ToInt32(reader.GetString(5)));
                            Variables().Item(prefix + ".MixName").set_Value(0, reader.GetString(6));
                        }
                      }
              }

First. in the connectToDB(); function I'm connecting to the DB (and I checked that the status is open). seconds. reader.HasRows always passes. Third: as I said I checked the reader and it has values

SynozeN Technologies · Accepted Answer · 2017-05-23 07:44:57Z

1

try this.

string namethestore = myReader.IsDBNull(namePos) 
                                          ? string.Empty 
                                          : reader.GetString(reader.GetString(2));

 Variables().Item(prefix + ".LoopName").set_Value(0, namethestore);

answered May 23, 2017 at 7:44

SynozeN Technologies

1,3431 gold badge14 silver badges19 bronze badges

Comments

styx · Accepted Answer · 2017-05-23 08:04:38Z

0

I have added isNull(rowname, 0 ) to the problematic rows and it solved my problem

answered May 23, 2017 at 8:04

styx

1,9751 gold badge11 silver badges24 bronze badges

Collectives™ on Stack Overflow

SqlDataReader read() working unconscionability

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related