How to work with null value

Hi
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))
            sqlCMD.Connection.Open()
            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)
            Loop
            dr.Close()
            sqlCMD.Dispose()
            sqlCMD.Connection.Dispose()
            sqlCMD.Connection.Close()

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
 
arifpervezAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

0
arifpervezAuthor Commented:
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.

thanks
0
David H.H.LeeCommented:
Hi arifpervez,
You can convert it to proper data type in this case.

Dim s as double

s=cdbl(iif(txtCentreScore.Text.ToString="",0,txtCentreScore.Text.ToString))

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

0
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

arifpervezAuthor Commented:
I want to pass NULL instead of 0. how can i do this.
0
David H.H.LeeCommented:
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:
http://msdn.microsoft.com/en-us/library/system.dbnull.value.aspx
0
arifpervezAuthor Commented:
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
0
arifpervezAuthor Commented:
Hi x_com

i changed CDbl to CStr. Now Its working.
Thank you for you help.
0
David H.H.LeeCommented:
Hi arifpervez,
Ok, i'm glad to help. Time to close this question?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.