asked on

Data Reader Null Values

Hi! I am getting an error with reading null values. How do I allow null values to be acceptable in the following code?

public List<Parts> GetPartsDetailFromPTNum(string PartNum)
        {
            List<Parts> partsList = new List<Parts>();
            SqlConnection myConn = new SqlConnection("MyConnection");
            

            myConn.Open();
            string commandString = "StoredProcedure";

            SqlCommand myCommand = new SqlCommand(commandString, myConn);
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.Parameters.Add("@PartNum", System.Data.SqlDbType.VarChar);
            myCommand.Parameters["@PartNum"].Value =PartNum ;
            SqlDataReader myReader = myCommand.ExecuteReader();


            while (myReader.Read())
            {
                partsList.Add(new Parts()

            {
                ID = myReader.GetString(myReader.GetOrdinal("ID")),
                DESCRIPTION = myReader.GetString(myReader.GetOrdinal("DESCRIPTION")),
                ENGINEERING_MSTR = myReader.GetString(myReader.GetOrdinal("ENGINEERING_MSTR")),
                COMMODITY_CODE = myReader.GetString(myReader.GetOrdinal("COMMODITY_CODE")),
                FABRICATED = myReader.GetString(myReader.GetOrdinal("FABRICATED")),
                PURCHASED = myReader.GetString(myReader.GetOrdinal("PURCHASED")),
                DRAWING_ID = myReader.GetString(myReader.GetOrdinal("DRAWING_ID")),
                DRAWING_REV_NO = myReader.GetString(myReader.GetOrdinal("DRAWING_REV_NO")),
                QTY_AVAILABLE_ISS = myReader.GetString(myReader.GetOrdinal("QTY_AVAILABLE_ISS")),
                USER_1 = myReader.GetString(myReader.GetOrdinal("USER_1")),
                HTS_CODE = myReader.GetString(myReader.GetOrdinal("HTS_CODE")),
                REVISION_ID = myReader.GetString(myReader.GetOrdinal("REVISION_ID")),
                QTY_ON_HAND = myReader.GetString(myReader.GetOrdinal("QTY_ON_HAND")),
                USER_2 = myReader.GetString(myReader.GetOrdinal("USER_2")),
                USER_6 = myReader.GetString(myReader.GetOrdinal("USER_6"))


            });

                myConn.Close();

            }

Open in new window

kaufmed

Identify which columns in your database can potentially contain NULL. Then add logic to each assignment statement within the while loop that references one of these columns to check for a DBNull.Value before performing the assignment.

e.g.

ID = (myReader.IsDbNull(myReader.GetOrdinal("ID")) ? (string)null : myReader.GetString(myReader.GetOrdinal("ID"))),

Open in new window

Annette Wilson, MSIS

ASKER

 ID = (myReader.GetString(myReader.GetOrdinal("ID")) ? (string)null : myReader.GetString(myReader.GetOrdinal("ID"))) ,

Open in new window

I'm getting the following error:

Error 1 Cannot implicitly convert type 'string' to 'bool'

Annette Wilson, MSIS

ASKER

I get

invalid attempt to read when no data is present.

SOLUTION

kaufmed

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Annette Wilson, MSIS

ASKER

Thank you. I corrected this and the Null error is gone. What's the correct syntax for checking for nulls and correctly casting a decimal as a string?

QTY_ON_HAND = (myReader.IsDBNull(myReader.GetOrdinal("QTY_ON_HAND")) ? (string)null : myReader.GetString(myReader.GetOrdinal("QTY_ON_HAND"))),

Open in new window

Annette Wilson, MSIS

ASKER

I'm also getting an error when I comment out the decimal items.

Invalid attempt to read data when reader is closed.

I never had this much trouble using datareader.

using (IDataReader myReader = myCommand.ExecuteReader()) 
            
            
            while (myReader.Read())
            {
                
                partsList.Add(new Parts()

            {
                ID = (myReader.IsDBNull(myReader.GetOrdinal("ID")) ? (string)null : myReader.GetString(myReader.GetOrdinal("ID"))),
                DESCRIPTION = (myReader.IsDBNull(myReader.GetOrdinal("DESCRIPTION")) ? (string)null : myReader.GetString(myReader.GetOrdinal("DESCRIPTION"))),
                ENGINEERING_MSTR = (myReader.IsDBNull(myReader.GetOrdinal("ENGINEERING_MSTR")) ? (string)null : myReader.GetString(myReader.GetOrdinal("ENGINEERING_MSTR"))),
                COMMODITY_CODE = (myReader.IsDBNull(myReader.GetOrdinal("COMMODITY_CODE")) ? (string)null : myReader.GetString(myReader.GetOrdinal("COMMODITY_CODE"))),
                FABRICATED = (myReader.IsDBNull(myReader.GetOrdinal("FABRICATED")) ? (string)null : myReader.GetString(myReader.GetOrdinal("FABRICATED"))),
                PURCHASED = (myReader.IsDBNull(myReader.GetOrdinal("PURCHASED")) ? (string)null : myReader.GetString(myReader.GetOrdinal("PURCHASED"))),
                DRAWING_ID = (myReader.IsDBNull(myReader.GetOrdinal("DRAWING_ID")) ? (string)null : myReader.GetString(myReader.GetOrdinal("DRAWING_ID"))),
                DRAWING_REV_NO = (myReader.IsDBNull(myReader.GetOrdinal("DRAWING_REV_NO")) ? (string)null : myReader.GetString(myReader.GetOrdinal("DRAWING_REV_NO"))),
                //QTY_AVAILABLE_ISS = (myReader.IsDBNull(myReader.GetOrdinal("QTY_AVAILABLE_ISS")) ? (string)null : myReader.GetString(myReader.GetOrdinal("QTY_AVAILABLE_ISS"))),
                USER_1 = (myReader.IsDBNull(myReader.GetOrdinal("USER_1")) ? (string)null : myReader.GetString(myReader.GetOrdinal("USER_1"))),
                HTS_CODE = (myReader.IsDBNull(myReader.GetOrdinal("HTS_CODE")) ? (string)null : myReader.GetString(myReader.GetOrdinal("HTS_CODE"))),
                REVISION_ID = (myReader.IsDBNull(myReader.GetOrdinal("REVISION_ID")) ? (string)null : myReader.GetString(myReader.GetOrdinal("REVISION_ID"))),
                //QTY_ON_HAND = (myReader.IsDBNull(myReader.GetOrdinal("QTY_ON_HAND")) ? (string)null : myReader.GetString(myReader.GetOrdinal("QTY_ON_HAND"))),
                USER_2 = (myReader.IsDBNull(myReader.GetOrdinal("USER_2")) ? (string)null : myReader.GetString(myReader.GetOrdinal("USER_2"))),
                USER_6 = (myReader.IsDBNull(myReader.GetOrdinal("USER_6")) ? (string)null : myReader.GetString(myReader.GetOrdinal("USER_6"))),
                
            

            });
                
               
            myConn.Close();

            }
            
            return partsList;

            }

Open in new window

ASKER CERTIFIED SOLUTION

kaufmed

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial