Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Data Reader Null Values

Posted on 2013-10-29
7
Medium Priority
?
412 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 75

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 2000 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 2000 total points
ID: 39613644
You've got the call to Close inside of the while loop; it needs to be outside.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
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…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…

705 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