How to work with null value

Posted on 2009-05-22
Medium Priority
Last Modified: 2012-05-07
I am trying to show data in page load. but every time i am getting error. MY CODE is -
            Dim SID As Integer = 0
            SID = Request("SID")
            Dim strSQL As String = ""
            strSQL = "SELECT LID, SurveyTypeID, CentreID, CentreScore, EnquiryDate, QCat, CommentsAfterQuestion " & _
                     "FROM dbo.QuestionDetails WHERE LID = " & SID
            Dim strCon As String = ""
            strCon = "Data Source=Test; Initial Catalog=Test; Integrated Security=True"

            Dim sqlCMD As SqlCommand = New SqlCommand(strSQL, New SqlConnection(strCon))
            Dim dr As SqlDataReader = sqlCMD.ExecuteReader()

            Do While (dr.Read())
                txtSurveyTypeID.Text = dr.GetInt32(1)
                txtCentreID.Text = dr.GetInt32(2)
                txtCentreScore.Text = dr.GetDecimal(3)              
                txtEnquiryDate.Text = dr.GetDateTime(4)
                txtQCat.Text = dr.GetInt32(5)
                txtComments.Text = dr.GetString(6)

THE Error is -

Data is Null. This method or property cannot be called on Null values.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.

Source Error:
Line 37:                 txtSurveyTypeID.Text = dr.GetInt32(1)
Line 38:                 txtCentreID.Text = dr.GetInt32(2)
Line 39:                 txtCentreScore.Text = dr.GetDecimal(3)
Line 40:                
Line 41:                 txtEnquiryDate.Text = dr.GetDateTime(4)

Please can anyone help me how to solve this probelm.

thanks in advance
Question by:arifpervez
  • 4
  • 4
LVL 29

Expert Comment

by:David H.H.Lee
ID: 24448992
Hi arifpervez,
It seem like NULL value returned from database. You can cast all the return value into string mode instead using .ToString() method.
txtCentreScore.Text = dr(3).ToString()              


Author Comment

ID: 24449021
Hi x_com
Yes its working. But If i want to update then showing error.
cmd.Parameters.Add("@CentreScore", SqlDbType.Decimal).Value = txtCentreScore.Text
error is -
System.FormatException: Failed to convert parameter value from a String to a Decimal.

what i will do with this.

LVL 29

Expert Comment

by:David H.H.Lee
ID: 24449100
Hi arifpervez,
You can convert it to proper data type in this case.

Dim s as double


cmd.Parameters.Add("@CentreScore", SqlDbType.Decimal).Value = s

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.


Author Comment

ID: 24449131
I want to pass NULL instead of 0. how can i do this.
LVL 29

Expert Comment

by:David H.H.Lee
ID: 24449153
Hi arifpervez,
First make sure your column is allow null value. Then, change the existing proposed code:

cmd.Parameters.Add("@CentreScore", SqlDbType.Decimal).Value=iif(txtCentreScore.Text.ToString="", DBNULL.value,cdbl(txtCentreScore.Text.ToString))

Check DBNULL.value property here:

Author Comment

ID: 24449215
Hi x_com
My column is allow null value. still i am getting error -

System.InvalidCastException: Conversion from string "" to type 'Double' is not valid. ---> System.FormatException: Input string was not in a correct format

Author Comment

ID: 24449234
Hi x_com

i changed CDbl to CStr. Now Its working.
Thank you for you help.
LVL 29

Accepted Solution

David H.H.Lee earned 375 total points
ID: 24449269
Hi arifpervez,
Ok, i'm glad to help. Time to close this question?

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
The video provides a quick and easy steps to migrate MBOX file to well known Outlook PST and Office 365. Besides this, it also supports and migrates more than 20 email clients of MBOX which include AppleMail, Opera, Thunderbird and SeaMonkey effortl…
The video will let you know the exact process to import OST/PST files to the cloud based Office 365 mailboxes. Using Kernel Import PST to Office 365 tool, one can quickly import numerous OST/PST files to Office 365. Besides this, the tool also comes…

619 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