objReader.GetString(x) NULL values are a problem with this

[DougP]

If I have null in the tabel the objReader has issues I can't seem to do anything to passify it.
Can't look at it for a NULL cause it can't conatin a NULL and errors no matter what I do.
how do I get around this I just want to put data in text boxes. can some one give me a code snippet , Please. It's having trouble with a Date being Null. Is there something I can put in the DAte field to make it happy. we are trying sto come up with a date that is eitehr 1900 or 2100, so far out then have to check for it. But I have to update all the records. I would prefer to handle the Nulls with this reader. Or can I put something into the SQL string so it returns something other than Null?
Also note I left out some of the = objReader.GetString(x) lines below to save space.

Error >>>>> Data is Null. this property or method cannot be called on Null values.

                  Try
            cnn.Open()
            Dim queryTrans As String
            queryTrans = "SELECT ResourceLastname, ResourceFirstname, Manager, " & _
                        "ResourceType, [Offshore-OnShore], Vendor, SOWTracker, StartDate, " & _
                        " EndDate, email,intUserId  From SOWResources " & _
                        "WHERE ResourceLastname = '" & Lname & "' " & _
                "AND ResourceFirstname = '" & Fname & "' 

Dim strConn As New SqlConnection(connStr)
            Dim objComm As SqlCommand
            Dim objReader As SqlDataReader
            strConn.Open()
            objComm = New SqlCommand(queryTrans, strConn)
            objReader = objComm.ExecuteReader()

            If objReader.HasRows Then
                Do While objReader.Read()
                    Me.txtResourceLastname.Text = objReader.GetString(0)
                    Me.txtResourceFirstname.Text = objReader.GetString(1)
                    Me.ddlManagerName.Text = objReader.GetString(2)
                    Me.txtResourceType.Text = objReader.GetString(3)
                    Me.ddlShore.Text = objReader.GetString(4)
                    ...
                    Me.txtEndDate.Text = objReader.GetString(8) ' ERROR here the End date is null
                    ' Me.ddlVendor.Text = TheVendor.ToString

                Loop '
            End If

TIA

DougP

 

[jbenson001]

See if this works for you:

IIf(objReader.IsDBNull(objReader.GetString(8)), String.Empty, objReader.GetString(8))

 

[DougP]

No jbenson001 , that does not work. I remeber something about needing to test it prior to it geting a NULL cause onec it has a NULL it won't do anything.

Is there something else I can use beside the Reader?
On thing I did as a workaround was put a value in the Date column that's not Null but has no significance. I added another column and this problem came up again. I put a "-" in every row for it but the reader has problems with NULLs. I want to get rid of it or fix perfer geet rid of it.

I need to fill a combo box without using a SQLDataSource, need to do it manually.

DougP

 

[jbenson001]

Instead of using an inline if IIF, use a regular IF statement, something like:

if objReader.IsDBNull(objReader.GetString(8))
    Me.txtEndDate.Text = string.empty
else
     Me.txtEndDate.Text = objReader.GetString(8)
end if

 

[Qik3Coder]

I give to you the gift of the MSDN!

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.isdbnull.aspx

IF objReader.IsDBNull(8) Then
    Me.txtEndDate.Text = string.empty
Else
     Me.txtEndDate.Text = objReader.GetString(8)
End If

Lodlaiden

You've got questions and source code. We want both!
There's a whole lot of Irish in that one.

 

[MarkSweetland]

You can use isNull() (SQL Server) to transform NULL entries to an empty string:

queryTrans = "SELECT ResourceLastname, ResourceFirstname, Manager, " & _
                   "ResourceType, [Offshore-OnShore], Vendor, SOWTracker, StartDate, " & _
                   "[red]EndDate=isNull([/red]EndDate[red],'')[/red], email,intUserId  From SOWResources " & _
                   "WHERE ResourceLastname = '" & Lname & "' " & _
                   "AND ResourceFirstname = '" & Fname & "'

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach