VB/ASP.NET Selecting Null Values

[hollyquinn]

Hi I am working with a web application where I am selecting values from a SQL Server 2005 database and then loading the values into different controls on my page. Most of the values load with no problem, except for those columns where the value of the selected row in the column could be a null. I get the following error message:

The value for column 'COLUMN NAME' in table 'TABLE NAME' is DBNull.

I've tried in my Sql select statement to use the following: IsNull(Column_N ame, ' '), but I still get the error message. Can anyone tell me how to get around this error message?

Here is my web service with my ADO.NET code:

 <WebMethod()> _
    Public Function WebService2(ByVal SessionID As Integer, ByVal SessionMonth As String, ByVal SessionType As String, ByVal Program As String, ByVal PilotEvaltrID As Integer, ByVal SOEvaltrID As Integer, ByVal MinLevel As Integer, ByVal IssuedCA As Boolean, ByVal IssuedFO As Boolean, ByVal IssuedSO As Boolean, ByVal FltNbr As Integer, ByVal FltZDate As String, ByVal Path As String) As DataSet1

        ' Creates a new DataSet, a connection to data source named sqlConn, and a data adapter named sqlDA
        Dim DS2 As DataSet1
        Dim sqlConn As SqlConnection
        Dim sqlDA As SqlDataAdapter
        Dim value As Boolean


        ' creates a new connection to DB
        sqlConn = New SqlConnection("Data Source=S11694;Initial Catalog=svtppdbSQL;Integrated Security=True")

        sqlConn.Open()

        ' New instance of sqlDA used to fill the DataSet
        sqlDA = New SqlDataAdapter("select SessionMonth, SessionType, Program, PilotEvaltrID, SOEvaltrID, MinLevel, IssuedCA, IssuedFO, IssuedSO, FltNbr, FltZDate from tblSessionID where SessionID = '" & SessionID & "'", sqlConn)

        ' New instance of DataSet
        DS2 = New DataSet1

        'Disables EnforceConstraints
        value = DS2.EnforceConstraints
        DS2.EnforceConstraints = False


        ' fills the DataAdapter object with information from tblSessionID
        sqlDA.Fill(DS2.tblSessionID)

        ' Returns the DataSet for the function
        Return DS2

        sqlConn.Close()

    End Function

And then this is the code where I call the web service and populate the controls:

Try

            ' Declares variable TempPath and creates new instance of clsWebServices serviceObj

            Dim TempPath As String

            Dim serviceObj As New clsWebServices

            ' creates new instance of DataSet 

            Dim dsFind2 As New DataSet1


            TempPath = Server.MapPath("svtppdbSQL.mdf") 'Creates specifies MapPath for variable TempPath to svtppdbSQL

            ' Specifies search criteria 

            dsFind2 = serviceObj.WebService2(Me.ddlSessionID.Text, Me.ddlSessionID.Text, Me.ddlSessionID.Text, Me.ddlSessionID.Text, Me.ddlSessionID.Text, Me.ddlSessionID.Text, Me.ddlSessionID.Text, Me.ddlSessionID.Text, Me.ddlSessionID.Text, Me.ddlSessionID.Text, Me.ddlSessionID.Text, Me.ddlSessionID.Text, TempPath)


            'Returns rows > than 0 in tblSessionID 

            If dsFind2.tblSessionID.Rows.Count > 0 Then

                ' Displays information in specified text boxes

                Me.txtSessionMonth.Text = dsFind2.tblSessionID(0).SessionMonth.ToShortDateString()

                Me.txtSessionType.Text = dsFind2.tblSessionID(0).SessionType.ToString

                Me.txtSessionType.Text = dsFind2.tblSessionID(0).SessionType.ToString

                Me.txtProgram.Text = dsFind2.tblSessionID(0).Program.ToString

                Me.lblPilotEvaltrID.Text = dsFind2.tblSessionID(0).PilotEvaltrID.ToString
                
                Me.lblSOEvaltrID.Text = dsFind2.tblSessionID(0).SOEvaltrID.ToString

                Me.MinLevel.Text = dsFind2.tblSessionID(0).MinLevel.ToString

                Me.IssCA.Checked = dsFind2.tblSessionID(0).IssuedCA.ToString

                Me.IssFO.Checked = dsFind2.tblSessionID(0).IssuedFO.ToString

                Me.IssSO.Checked = dsFind2.tblSessionID(0).IssuedSO.ToString

                Me.txtFltNbr.Text = dsFind2.tblSessionID(0).FLTNbr.ToString()

                Me.txtFltZDate.Text = dsFind2.tblSessionID(0).FltZDate.ToString

                lblMessage.Text = ""

            Else

                lblMessage.Text = "No records were found!!"

            End If


        Catch ex As Exception

            ' Displays any error messages that may occur at runtime

            lblMessage.Text = ex.Message

        End Try

There are 4 columns where the value can be null: PilotEvaltrID, SOEvaltrID, FltNbr, FltZDate.

Can anyone offer me a solution?

[balabaster]

Try looking up System.DBNull.V alue...

If Not MyValue Is System.DBNull.V alue Then do something...

[hollyquinn]

Could you be a little more specific? Like maybe show me an example? When I try the following in my click event I still get the error message:

If Not dsFind2.tblSess ionID(0).FLTNbr .ToString Is System.DBNull.V alue Then


Me.txtFltNbr.Te xt = dsFind2.tblSess ionID(0).FLTNbr .ToString


End If

[balabaster]

Could you be a little more specific? Like maybe show me an example? When I try the following in my click event I still get the error message:

If Not dsFind2.tblSess ionID(0).FLTNbr .ToString Is System.DBNull.V alue Then


Me.txtFltNbr.Te xt = dsFind2.tblSess ionID(0).FLTNbr .ToString


End If

Are you trying to run the ToString() method on a value that doesn't exist?

Try removing the ToString in your IF...

If Not dsFind2.tblSess ionID(0).FLTNbr Is System.DBNull.V alue Then...

and seeing if your code still crashes...

[MrMancunian]

How about

[CODE]
If Not IsDBNull(dsFind 2.tblSessionID( 0).FLTNbr) Then
...
End If
[CODE]

Steven

[hollyquinn]

I have tried both of those, and neither work. But, I did figure out the answer for anyone else who might read this and is having the same problem. I needed to use a DataReader like this:

Dim cn As SqlConnection

        Dim cmd As SqlCommand

        Dim rdr As SqlDataReader


        Try

            cn = New SqlConnection("Data Source=(local);Initial Catalog=db1;Integrated Security=True")

            cmd = New SqlCommand("select SessionMonth, SessionType, Program, PilotEvaltrID, SOEvaltrID, MinLevel, IssuedCA, IssuedFO, IssuedSO, FltNbr, FltZDate from tblSessionID where SessionID = '" & ddlSessionID.SelectedValue & "'", cn)

            cn.Open()

            rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

            rdr.Read()

            txtSessionMonth.Text = rdr("SessionMonth").ToString()

            txtSessionType.Text = rdr("SessionType").ToString()

            txtProgram.Text = rdr("Program").ToString()

            lblPilotEvaltrID.Text = rdr("PilotEvaltrID").ToString()

            lblSOEvaltrID.Text = rdr("SOEvaltrID").ToString()

            MinLevel.Text = rdr("MinLevel").ToString()

            IssCA.Checked = rdr("IssuedCA").ToString()

            IssFO.Checked = rdr("IssuedFO").ToString()

            IssSO.Checked = rdr("IssuedSO").ToString()

            txtFltNbr.Text = rdr("FltNbr").ToString()

            txtFltZDate.Text = rdr("FltZDate").ToString()

            rdr.Close()

            cn.Close()

        Catch ex As Exception

            lblMessage.Text = ex.Message


        End Try

Once I did this I no longer the error message. I think it was a very sloppy way I was trying to select my data. Thanks for everyones help.