SQLDataReader to read query issue

[Atove]

Hi all; I'd be really grateful if someone could assist me with this problem.

What i am trying to do is create a query that pulls information from two different tables: tblBooking and tblConcessions.
tblBooking holds the ID number of tblConcessions as a ForeignKey.
What i need within my statement is to pull some information from tblBooking and then replace the concessionID number with the concessionName from the concession table.
My program then reads this data and places it into a ListView box.

I've been able to get it to read just from one table; but i'm having trouble with pulling data from the other table.

My code so far is as follows.

Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click

        lvBookings.Items.Clear()

        Dim itm As ListViewItem

        Dim varCommand As String

        varCommand = "SELECT BookingID, C_ID, StartDate, EndDate FROM tblBooking WHERE "

        If Not txtBSBookingID.Text = Nothing Then
            varCommand = varCommand & "BookingID LIKE '" & txtBSBookingID.Text & "'"
        ElseIf cboInitials.SelectedIndex > -1 Or Not cboInitials.SelectedItem = "None" Then
            varCommand = varCommand & "FFInitiatedBy LIKE '" & cboInitials.SelectedItem & "'"
        End If

        objCommand = New SqlCommand(varCommand, objConnection)

        Try
            objConnection.Open()
            Dim reader As SqlDataReader = objCommand.ExecuteReader

            While reader.Read()
                str(0) = reader("BookingID")
                str(1) = reader("C_ID")
                str(2) = reader("StartDate")
                str(3) = reader("EndDate")
                itm = New ListViewItem(str)
                lvBookings.Items.Add(itm)

            End While
        Finally
            objConnection.Close()
        End Try

I have tried using a subquery; i have not used subqueries before so i'm sure i'm doing something wrong or missing some critical point.

This is what i tried:

"SELECT BookingID, (SELECT ConcessionName " & _
			"FROM tblConcessions " & _
			"INNER JOIN tblBooking " & _
			"ON tblConcessions.ConcessionID=tblBooking.C_ID), "& _
"StartDate, EndDate FROM tblBooking WHERE "

I got the error:
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

Is a subquery the best way to go about achieving this or is there a better way?

Thanks for your time. :)

[babbu]

subquery is not the only way.
u can simply create another query and fetch the data from the other table.
alternatively u can also use data tables...

[Atove]

Awsome! Solved it! I've been stuck trying to figure this out for days.
I used 'INNER JOIN' to solve the problem:

varCommand = "SELECT tblBooking.BookingID, tblConcessions.ConcessionName, " & _
                "tblGardenCentre.GardenCentreName, tblBooking.StartDate," & _
                "tblBooking.EndDate " & _
                "FROM tblBooking " & _
                "INNER JOIN tblConcessions " & _
                "ON tblBooking.C_ID = tblConcessions.ConcessionID " & _
                "INNER JOIN tblGardenCentre " & _
                "ON tblBooking.GC_ID = tblGardenCentre.GardenCentreID " & _
                "WHERE "

I just looked at the join statement in more detail and didn't use a subquery. I tried creating additional queries and then calling them and for some reason i couldn't get it to work in the i needed it to. Thanks for your time babbu!

[TomW]

Dont forget to mark the thread as solved and rep babbu for helping ya.