• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • Last Modified:

passing SQLDataReader to multiple functions

hello experts,
i am trying to centralize logic for loading SQLDataReader like shown here:

      Private Sub GetVisit(ByVal SearchVal As String)

            Dim WhereClause As String = "Where "
            Select Case SearchVal
                  Case "Account Number"
                        WhereClause += "v.hospacct = '" & Me.txtSearchText.Text & "'"
                  Case "MRN"
                        WhereClause += "v.mrn = '" & Me.txtSearchText.Text & "'"
                  Case "Last Name"
                        WhereClause += "v.PatLName like '" & Me.txtSearchText.Text & "%'"
            End Select

            Try
                  Using dr As SqlDataReader = LoadDataReader("Select * from visit v " & WhereClause)
                        If Not dr.HasRows Then
                              MessageBox.Show("no visit rows for this search criteria", My.Application.Info.AssemblyName)
                              dr.Close()
                              Exit Sub
                        End If

                        dgOrders.ColumnCount = 4
                        dgOrders.Columns(0).Name = "Name"
                        dgOrders.Columns(1).Name = "Account #"
                        dgOrders.Columns(2).Name = "MRN"
                        dgOrders.Columns(3).Name = "Visit ID"
                        dgOrders.Columns(3).Visible = False

                        While dr.Read
                              Dim row As String() = New String() {dr.Item("PatFname").ToString & " " & dr.Item("PatLname").ToString, dr.Item("hospacct").ToString, dr.Item("MRN").ToString, dr.Item("visitid").ToString}
                              'Me.dgOrders.Rows.Add(dr.Item("firstname").ToString & " " & dr.Item("Lastname").ToString)
                              dgOrders.Rows.Add(row)
                        End While
                  End Using

                  dgOrders.Focus()

            Catch ex As Exception
                  MessageBox.Show(ex.Message & vbCr & vbCr & ex.StackTrace, My.Application.Info.AssemblyName)

            End Try

      End Sub

      Private Function LoadDataReader(strSQL As String) As SqlDataReader

            Try
                  Using objConnection As New SqlConnection(My.Settings.SQLConn), objCmd As New SqlCommand(strSQL, objConnection)
                        objConnection.Open()
                        Return objCmd.ExecuteReader()
                  End Using

            Catch ex As Exception
                  MessageBox.Show(ex.Message & vbCr & vbCr & ex.StackTrace, My.Application.Info.AssemblyName)
                  Return Nothing

            End Try

      End Function

but when i run this, calling function still thinks that DataReader is closed,
not sure what is going on here

thanks in advance,
Alex
0
AlexF777
Asked:
AlexF777
1 Solution
 
AlexF777Author Commented:
ok, i found an answer: SQLDataReader needs SQLConnection until it is completely done ...
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now