Solved

passing SQLDataReader to multiple functions

Posted on 2013-06-18
1
300 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

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…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now