Solved

Data Reader Null Values

Posted on 2013-10-29
7
407 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 74

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 74

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 74

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

What my article will show is if you ever had to do processing to a listbox without being able to just select all the items in it. My software Visual Studio 2008 crystal report v11 My issue was I wanted to add crystal report to a form and show…
A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
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…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

743 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now