Help with empty datarow

[News]

Hi, I wrote a function that pulls a record, below:

++++++++++++++++++++++++++++++++++++++++
Public Function getServPlanProgActivity( serviceprogramenroll_id As Integer,
activity_id As Integer ) As DataRow
' Connect To db
Dim myConn As New SqlConnection(_DSN)
Dim dtrHasRows As DataRow

Try
Dim myAdapter As New SqlDataAdapter("Select " & _
"serviceactivity_id, activity_id, " & _
"serviceprogramenroll_id, additional_text " & _
"FROM ServicePlanProgramActivity " & _
"WHERE serviceprogramenroll_id = " &
ctype(serviceprogramenroll_id , integer) & "And activity_id =" &
ctype(activity_id , integer), myConn)
Dim myDataSet As New DataSet()
myAdapter.Fill(myDataSet, "ServicePlanProgramEnroll")

' Return datarow
If myDataSet.Tables(0).Rows.Count > 0 Then
Return myDataSet.Tables(0).Rows(0)
Else
dtrHasRows = Nothing
Return dtrHasRows
End If

Catch ex As Exception
Throw ex
Finally
myConn.Close()
End Try
End Function

+++++++++++++++++++++++++++++++++++++++

This is a snippet where I reference it:

dstServPlanProgProgActivity =
myServPlanProgProgEnroll.getServPlanProgActivity(v iewstate("speid"),
reader.Item("id"))
If Not IsDBNull(dstServPlanProgProgActivity("activity_id" )) Then
checktextbox.ctcChecked = true
If Not IsDBNull(dstServPlanProgProgActivity("additional_t ext")) Then
checktextbox.ctcText =
dstServPlanProgProgActivity("additional_text")
End If
End If

+++++++++++++++++++++++++++++++++++++++

Problem is when there is a record it works fine, but if no record found I
get this error:
System.NullReferenceException: Object reference not set to an instance of an
object.
Line 190: If Not IsDBNull(dstServPlanProgProgActivity("activity_id" )) Then
I understand that there is no record for this search criteria, how do I
validate that this function returned Nothing or may be there is a better
way, say assign something to a return value of the function? If I were to
assign some value, how do I assign something to a data type of DataRow?
Thanks,

Gene

[marinov]

"News" <ge**@mailcity.com> wrote in message
news:JL********************@magma.ca...

Hi, I wrote a function that pulls a record, below:

++++++++++++++++++++++++++++++++++++++++
Public Function getServPlanProgActivity( serviceprogramenroll_id As Integer, activity_id As Integer ) As DataRow
' Connect To db
Dim myConn As New SqlConnection(_DSN)
Dim dtrHasRows As DataRow

Try
Dim myAdapter As New SqlDataAdapter("Select " & _
"serviceactivity_id, activity_id, " & _
"serviceprogramenroll_id, additional_text " & _
"FROM ServicePlanProgramActivity " & _
"WHERE serviceprogramenroll_id = " &
ctype(serviceprogramenroll_id , integer) & "And activity_id =" &
ctype(activity_id , integer), myConn)
Dim myDataSet As New DataSet()
myAdapter.Fill(myDataSet, "ServicePlanProgramEnroll")

' Return datarow
If myDataSet.Tables(0).Rows.Count > 0 Then
Return myDataSet.Tables(0).Rows(0)
Else
dtrHasRows = Nothing
Return dtrHasRows
End If

Catch ex As Exception
Throw ex
Finally
myConn.Close()
End Try
End Function

+++++++++++++++++++++++++++++++++++++++

This is a snippet where I reference it:

dstServPlanProgProgActivity =
myServPlanProgProgEnroll.getServPlanProgActivity(v iewstate("speid"),
reader.Item("id"))
If Not IsDBNull(dstServPlanProgProgActivity("activity_id" )) Then
checktextbox.ctcChecked = true
If Not IsDBNull(dstServPlanProgProgActivity("additional_t ext")) Then
checktextbox.ctcText =
dstServPlanProgProgActivity("additional_text")
End If
End If

+++++++++++++++++++++++++++++++++++++++

Problem is when there is a record it works fine, but if no record found I
get this error:
System.NullReferenceException: Object reference not set to an instance of an object.
Line 190: If Not IsDBNull(dstServPlanProgProgActivity("activity_id" )) Then

I understand that there is no record for this search criteria, how do I
validate that this function returned Nothing or may be there is a better
way, say assign something to a return value of the function? If I were to
assign some value, how do I assign something to a data type of DataRow?
Thanks,

Gene

to avoid the error you can do this

dstServPlanProgProgActivity =
myServPlanProgProgEnroll.getServPlanProgActivity(v iewstate("speid"),
reader.Item("id"))
If Not dstServPlanProgProgActivity Then
If Not IsDBNull(dstServPlanProgProgActivity("activity_id" )) Then
checktextbox.ctcChecked = true
If Not IsDBNull(dstServPlanProgProgActivity("additional_t ext")) Then
checktextbox.ctcText =
dstServPlanProgProgActivity("additional_text")
End If
End If
End If

[News]

thanks, but this line "If Not dstServPlanProgProgActivity Then" now gives me
this error:
Operator 'Not' is not defined for type 'System.Data.DataRow'.
"marinov" <tr*@me.com> wrote in message
news:eL*************@TK2MSFTNGP12.phx.gbl...

"News" <ge**@mailcity.com> wrote in message
news:JL********************@magma.ca...

Hi, I wrote a function that pulls a record, below:

++++++++++++++++++++++++++++++++++++++++
Public Function getServPlanProgActivity( serviceprogramenroll_id As Integer,

activity_id As Integer ) As DataRow
' Connect To db
Dim myConn As New SqlConnection(_DSN)
Dim dtrHasRows As DataRow

Try
Dim myAdapter As New SqlDataAdapter("Select " & _
"serviceactivity_id, activity_id, " & _
"serviceprogramenroll_id, additional_text " & _
"FROM ServicePlanProgramActivity " & _
"WHERE serviceprogramenroll_id = " &
ctype(serviceprogramenroll_id , integer) & "And activity_id =" &
ctype(activity_id , integer), myConn)
Dim myDataSet As New DataSet()
myAdapter.Fill(myDataSet, "ServicePlanProgramEnroll")

' Return datarow
If myDataSet.Tables(0).Rows.Count > 0 Then
Return myDataSet.Tables(0).Rows(0)
Else
dtrHasRows = Nothing
Return dtrHasRows
End If

Catch ex As Exception
Throw ex
Finally
myConn.Close()
End Try
End Function

+++++++++++++++++++++++++++++++++++++++

This is a snippet where I reference it:

dstServPlanProgProgActivity =
myServPlanProgProgEnroll.getServPlanProgActivity(v iewstate("speid"),
reader.Item("id"))
If Not IsDBNull(dstServPlanProgProgActivity("activity_id" )) Then
checktextbox.ctcChecked = true
If Not IsDBNull(dstServPlanProgProgActivity("additional_t ext")) Then
checktextbox.ctcText =
dstServPlanProgProgActivity("additional_text")
End If
End If

+++++++++++++++++++++++++++++++++++++++

Problem is when there is a record it works fine, but if no record found I get this error:
System.NullReferenceException: Object reference not set to an instance of an

object.
Line 190: If Not IsDBNull(dstServPlanProgProgActivity("activity_id" ))

Then

I understand that there is no record for this search criteria, how do I
validate that this function returned Nothing or may be there is a better
way, say assign something to a return value of the function? If I were

to assign some value, how do I assign something to a data type of DataRow?
Thanks,

Gene

to avoid the error you can do this

dstServPlanProgProgActivity =
myServPlanProgProgEnroll.getServPlanProgActivity(v iewstate("speid"),
reader.Item("id"))
If Not dstServPlanProgProgActivity Then
If Not IsDBNull(dstServPlanProgProgActivity("activity_id" )) Then
checktextbox.ctcChecked = true
If Not IsDBNull(dstServPlanProgProgActivity("additional_t ext")) Then
checktextbox.ctcText =
dstServPlanProgProgActivity("additional_text")
End If
End If
End If

[Martin Marinov]

I'm really sorry
Because i didn't test the code but write directly in the reply i forgot to
put the = Nothing
so the code should be

dstServPlanProgProgActivity =
myServPlanProgProgEnroll.getServPlanProgActivity(v iewstate("speid"),
reader.Item("id"))
If Not dstServPlanProgProgActivity = Nothing Then
If Not IsDBNull(dstServPlanProgProgActivity("activity_id" )) Then
checktextbox.ctcChecked = true
If Not IsDBNull(dstServPlanProgProgActivity("additional_t ext")) Then
checktextbox.ctcText =
dstServPlanProgProgActivity("additional_text")
End If
End If
End If
Regards
Martin
"News" <ge**@mailcity.com> wrote in message
news:Xt********************@magma.ca...

thanks, but this line "If Not dstServPlanProgProgActivity Then" now gives me this error:
Operator 'Not' is not defined for type 'System.Data.DataRow'.
"marinov" <tr*@me.com> wrote in message
news:eL*************@TK2MSFTNGP12.phx.gbl...

"News" <ge**@mailcity.com> wrote in message
news:JL********************@magma.ca...

Hi, I wrote a function that pulls a record, below:

++++++++++++++++++++++++++++++++++++++++
Public Function getServPlanProgActivity( serviceprogramenroll_id As Integer,

activity_id As Integer ) As DataRow
' Connect To db
Dim myConn As New SqlConnection(_DSN)
Dim dtrHasRows As DataRow

Try
Dim myAdapter As New SqlDataAdapter("Select " & _
"serviceactivity_id, activity_id, " & _
"serviceprogramenroll_id, additional_text " & _
"FROM ServicePlanProgramActivity " & _
"WHERE serviceprogramenroll_id = " &
ctype(serviceprogramenroll_id , integer) & "And activity_id =" &
ctype(activity_id , integer), myConn)
Dim myDataSet As New DataSet()
myAdapter.Fill(myDataSet, "ServicePlanProgramEnroll")

' Return datarow
If myDataSet.Tables(0).Rows.Count > 0 Then
Return myDataSet.Tables(0).Rows(0)
Else
dtrHasRows = Nothing
Return dtrHasRows
End If

Catch ex As Exception
Throw ex
Finally
myConn.Close()
End Try
End Function

+++++++++++++++++++++++++++++++++++++++

This is a snippet where I reference it:

dstServPlanProgProgActivity =
myServPlanProgProgEnroll.getServPlanProgActivity(v iewstate("speid"),
reader.Item("id"))
If Not IsDBNull(dstServPlanProgProgActivity("activity_id" )) Then
checktextbox.ctcChecked = true
If Not IsDBNull(dstServPlanProgProgActivity("additional_t ext")) Then checktextbox.ctcText =
dstServPlanProgProgActivity("additional_text")
End If
End If

+++++++++++++++++++++++++++++++++++++++

Problem is when there is a record it works fine, but if no record found I

get this error:
System.NullReferenceException: Object reference not set to an instance of

an

object.
Line 190: If Not IsDBNull(dstServPlanProgProgActivity("activity_id" ))

Then

I understand that there is no record for this search criteria, how do

I validate that this function returned Nothing or may be there is a better way, say assign something to a return value of the function? If I were

to assign some value, how do I assign something to a data type of DataRow?

Thanks,

Gene

to avoid the error you can do this

dstServPlanProgProgActivity =
myServPlanProgProgEnroll.getServPlanProgActivity(v iewstate("speid"),
reader.Item("id"))
If Not dstServPlanProgProgActivity Then
If Not IsDBNull(dstServPlanProgProgActivity("activity_id" )) Then
checktextbox.ctcChecked = true
If Not IsDBNull(dstServPlanProgProgActivity("additional_t ext"))

Then checktextbox.ctcText =
dstServPlanProgProgActivity("additional_text")
End If
End If
End If