Multiple SQL Connections 1

[Halliarse]

Hi

I'm using VS2012, vb.net, connecting to a SQL database.

Apologies if this is a stupid question but I think I may have just been staring at this one for far too long!! I am trying to run up to 4 seperate SQL quesries dependent on certain variables and for some unknown reason to me, the 'reader3' line of code is failing saying that there is no open connection! I'm sure I've done this before....what am I missing please?

Dim constr As String = ConfigurationManager.ConnectionStrings("SnipITConnectionString").ConnectionString

If Session("UserType") = 1 Then

Dim cmd As New SqlCommand("Select * from EventDates where EventID = " & Request.QueryString("EventID"), New SqlConnection(constr))
cmd.Connection.Open()

Dim reader As SqlDataReader = cmd.ExecuteReader

If reader.HasRows Then
While reader.Read
Session("CustomerID") = reader.Item("CustomerID").ToString()
Session("StylistID") = reader.Item("StylistID").ToString()
Session("VisitType") = reader.Item("VisitType").ToString()
Session("SalonID") = reader.Item("SalonID").ToString()
txtStartDate.Text = Mid(reader.Item("EventStartDate"), 1, 10)
txtStartTime.Text = Mid(reader.Item("EventStartDate"), 12, 5)
txtEndDate.Text = Mid(reader.Item("EventEndDate"), 1, 10)
txtEndTime.Text = Mid(reader.Item("EventEndDate"), 12, 5)
End While
End If

reader.Close()
cmd.Connection.Close()
cmd.Connection.Dispose()

If Session("CustomerID") <> "" Then

Dim cmd1 As New SqlCommand("Select * from Customer where CustomerID = " & Session("CustomerID"), New SqlConnection(constr))
Dim reader1 As SqlDataReader = cmd1.ExecuteReader
cmd1.Connection.Open()

If reader1.HasRows Then
While reader1.Read

txtCustomer.Text = reader1.Item("CustomerName").ToString()
txtAddressLine1.Text = reader1.Item("AddressLine1").ToString()
txtAddressLine2.Text = reader1.Item("AddressLine2").ToString()
txtAddressLine3.Text = reader1.Item("AddressLine3").ToString()
txtCity.Text = reader1.Item("City").ToString()
txtCounty.Text = reader1.Item("County").ToString()
txtPostCode.Text = reader1.Item("Postcode").ToString()
txtHomePhone.Text = reader1.Item("Telephone").ToString()
txtMobilePhone.Text = reader1.Item("Mobile").ToString()
txtEmailAddress.Text = reader1.Item("EmailAddress").ToString()

End While
End If

reader1.Close()
cmd1.Connection.Close()
cmd1.Connection.Dispose()
End If

If Session("VisitType") <> "" Then

Dim cmd2 As New SqlCommand("Select * from VisitTypes where VisitType = " & Session("VisitType"), New SqlConnection(constr))
Dim reader2 As SqlDataReader = cmd2.ExecuteReader
cmd2.Connection.Open()

If reader2.HasRows Then
While reader2.Read
txtAppType.Text = reader2.Item("Description").ToString()
End While
End If

reader2.Close()
cmd2.Connection.Close()
cmd2.Connection.Dispose()
End If

Dim cmd3 As New SqlCommand("Select * from Stylist where StylistID = " & Session("StylistID"), New SqlConnection(constr))
[highlight #CC0000]Dim reader3 As SqlDataReader = cmd3.ExecuteReader[/highlight]
cmd3.Connection.Open()

If reader3.HasRows Then
While reader3.Read
txtStylist.Text = reader3.Item("StylistName").ToString()
End While
End If

reader3.Close()
cmd3.Connection.Close()
cmd3.Connection.Dispose()
Else







End If

 

[SgtJarrow]

Not sure why you aren't seeing the error during the SECOND call...Only your first set is correct.

Let's look at the logic:

If condition Then
    Create Command Object
    Open Connection
        Execute Query
        If Query Has Rows Then
            Do Work
        End If
    Close Connection
End If

If you review EACH of the four possible actions, you will see that:

The first is well defined
The second and third has the open connection after the execute query
The fourth is missing the if condition

Ensure that each of the four possible actions follows the logic above and you should be working.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
C#.NET Programmer

 

[Halliarse]

So basically a schoolboy error!! Thanks Robert