Solved

passing SQLDataReader to multiple functions

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

For most people, the WrapPanel seems like a magic when they switch from WinForms to WPF. Most of us will think that the code that is used to write a control like that would be difficult. However, most of the work is done by the WPF engine, and the W…
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…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

734 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