Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

passing SQLDataReader to multiple functions

Posted on 2013-06-18
1
Medium Priority
?
307 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

What my article will show is if you ever had to do processing to a listbox without being able to just select all the items in it. My software Visual Studio 2008 crystal report v11 My issue was I wanted to add crystal report to a form and show…
For a while now I'v been searching for a circular progress control, much like the one you get when first starting your Silverlight application. I found a couple that were written in WPF and there were a few written in Silverlight, but all appeared o…
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…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

670 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