Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

passing SQLDataReader to multiple functions

Posted on 2013-06-18
1
Medium Priority
?
308 Views
Last Modified: 2013-06-18
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
Comment
Question by:AlexF777
1 Comment
 

Accepted Solution

by:
AlexF777 earned 0 total points
ID: 39257609
ok, i found an answer: SQLDataReader needs SQLConnection until it is completely done ...
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to make a Windows 7 gadget that accepts files dropped from the Windows Explorer.  It also illustrates how to give your gadget a non-rectangular shape and how to add some nifty visual effects to text displayed in a your gadget.…
This article shows how to make a Windows 7 gadget that extends its U/I with a flyout panel -- a window that pops out next to the gadget.  The example gadget shows several additional techniques:  How to automatically resize a gadget or flyout panel t…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question