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(viewstate("speid"),
reader.Item("id"))
If Not IsDBNull(dstServPlanProgProgActivity("activity_id")) Then
checktextbox.ctcChecked = true
If Not IsDBNull(dstServPlanProgProgActivity("additional_text")) 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]

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(viewstate("speid"),
reader.Item("id"))
If Not IsDBNull(dstServPlanProgProgActivity("activity_id")) Then
checktextbox.ctcChecked = true
If Not IsDBNull(dstServPlanProgProgActivity("additional_text")) 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(viewstate("speid"),
reader.Item("id"))
If Not dstServPlanProgProgActivity Then
If Not IsDBNull(dstServPlanProgProgActivity("activity_id")) Then
checktextbox.ctcChecked = true
If Not IsDBNull(dstServPlanProgProgActivity("additional_text")) 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'.

 

[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(viewstate("speid"),
reader.Item("id"))
If Not dstServPlanProgProgActivity = Nothing Then
If Not IsDBNull(dstServPlanProgProgActivity("activity_id")) Then
checktextbox.ctcChecked = true
If Not IsDBNull(dstServPlanProgProgActivity("additional_text")) Then
checktextbox.ctcText =
dstServPlanProgProgActivity("additional_text")
End If
End If
End If


Regards
Martin