Solved

Data Reader Null Values

Posted on 2013-10-29
7
411 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Understanding Linux Permissions

Linux for beginners: How to view the permissions associated with files and directories and also how you can change them.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

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…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
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…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

617 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