Solved

Data Reader Null Values

Posted on 2013-10-29
7
408 Views
Last Modified: 2014-01-27
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

0
Comment
Question by:Annette Wilson, MSIS
  • 4
  • 3
7 Comments
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 39610184
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

0
 

Author Comment

by:Annette Wilson, MSIS
ID: 39610205
 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'
0
 

Author Comment

by:Annette Wilson, MSIS
ID: 39610252
I get

invalid attempt to read when no data is present.
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 75

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 500 total points
ID: 39610623
I'm getting the following error:
You forgot the IsDbNull that I demonstrated in my previous post.
0
 

Author Comment

by:Annette Wilson, MSIS
ID: 39611937
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

0
 

Author Comment

by:Annette Wilson, MSIS
ID: 39612743
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

0
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 500 total points
ID: 39613644
You've got the call to Close inside of the while loop; it needs to be outside.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question