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. :)