how to use inner join in vb .net to fill textbox of vb windows form application

Question

i am new in vb .net i am making a project airline reservation system so i have to fetch data from two tables and show in my vb form textboxs i am using inner join to fetching data from two different table but i am always getting error pls guys see my code and suggest me.here TICKET_NO,CUSTOMER_ID and FLIGHT_ID here in TICKET_RESERVATION Table and FLIGHT_CHARGES here in in FLIGHT_DETAILS Table and FLIGHT_ID column here in both TICKET_RESERVATION and FLIGHT_DETAILS Tables pls help me

I am getting error no value given for one or more required parameter

Try
    If Not con.State = ConnectionState.Open Then
        con.Open()
    End If
    Dim da As New OleDb.OleDbDataAdapter("SELECT TICKET.TICKET_NO,CUSTOMER.CUSTOMER_ID,FLIGHT.FLIGHT_ID,FLIGHT.FLIGHT_CHARGES FROM TICKET_RESERVATION INNER JOIN FLIGHT_DETAILS ON TICKET_RESERVATION.FLIGHT_ID = FLIGHT_DETAILS.FLIGHT_ID WHERE [TICKET_NO]= '" & txtTicketNo.Text & "'", con)
    Dim ds As New DataSet
    da.Fill(ds, "TICKET_RESERVATION")
    If ds.Tables("TICKET_RESERVATION").Rows.Count > 0 Then
        txtTicketNo.Text = ds.Tables("TICKET_RESERVATION").Rows(0).Item(0).ToString()
        txtCustomerId.Text = ds.Tables("TICKET_RESERVATION").Rows(0).Item(1).ToString()
        txtFlightId.Text = ds.Tables("TICKET_RESERVATION").Rows(0).Item(2).ToString()
        txtAmount.Text = ds.Tables("FLIGHT_DETAILS").Rows(0).Item(3).ToString()

    End If
    con.Close()
Catch ex As Exception
    MsgBox(ex.Message.ToString)
End Try

now my prblem is solve but Now i am getting error Object reference not set to an instance of object i think now prblem in my this code

 Dim ds As New DataSet
        da.Fill(ds)
        If ds.Tables("TICKET_RESERVATION").Rows.Count > 0 Then
            txtTicketNo.Text = ds.Tables("TICKET_RESERVATION").Rows(0).Item(0).ToString()
            txtCustomerId.Text = ds.Tables("TICKET_RESERVATION").Rows(0).Item(1).ToString()
            txtFlightId.Text = ds.Tables("TICKET_RESERVATION").Rows(0).Item(2).ToString()
            txtAmount.Text = ds.Tables("FLIGHT_DETAILS").Rows(0).Item(3).ToString()

        End If

Why `INNER JOIN` and not `Left Outer Join`? In case your query returns a NULL value, it will cause an error when trying to cast `.ToString()`. — Nadeem_MK, Oct 14 '14 at 10:45
The first thing you need to read about, and this is general practice rather than just VB, is avoiding SQL injection attacks. Read about Command objects. Ask yourself what happens if I come along and type "A'; Drop table Ticket-Reservation;" into your txtTicketNo textbox... — Martin Milan, Oct 14 '14 at 10:47

Martin Milan · Accepted Answer · 2014-10-14T14:07:09.637

1

The projection part of your Select query refers to table aliases that don't exist elsewhere in the query - such as Ticket,Customer and Flight...

Try this query instead (you can fix the SQL Injection yourself...):

SELECT TICKET.TICKET_NO,CUSTOMER.CUSTOMER_ID,FLIGHT.FLIGHT_ID,FLIGHT.FLIGHT_CHARGES FROM TICKET_RESERVATION AS TICKET INNER JOIN FLIGHT_DETAILS AS FLIGHT ON TICKET_RESERVATION.FLIGHT_ID = FLIGHT_DETAILS.FLIGHT_ID WHERE [TICKET_NO]= '" & txtTicketNo.Text & "'", con)

It'll still crash because of the CUSTOMER alias that I can't see anything for at all in your query, but I've fixed the other two... Perhaps you could take the Customer_If from the Ticket_Reservation table - I suspect it will be there...

in fact, I'll deal with that for you... Try this:

 SELECT TICKET.TICKET_NO,TICKET.CUSTOMER_ID,FLIGHT.FLIGHT_ID,FLIGHT.FLIGHT_CHARGES FROM TICKET_RESERVATION AS TICKET INNER JOIN FLIGHT_DETAILS AS FLIGHT ON TICKET.FLIGHT_ID = FLIGHT.FLIGHT_ID WHERE [TICKET.TICKET_NO]= '" & txtTicketNo.Text & "'", con)

edited Oct 14 '14 at 14:07

answered Oct 14 '14 at 10:51

Martin Milan

6,346
2
32
44

when i am using ur code i am getting (syntax error in join operation) SELECT TICKET.TICKET_NO,CUSTOMER.CUSTOMER_ID,FLIGHT.FLIGHT_ID,FLIGHT.FLIGHT_CHARGES FROM TICKET_RESERVATION AS TICKET INNER JOIN FLIGHT_DETAILS AS FLIGHT ON TICKET_RESERVATION.FLIGHT_ID = FLIGHT_DETAILS.FLIGHT_ID WHERE [TICKET_NO]= '" & txtTicketNo.Text & "'", con) – Ashu Renu Oct 14 '14 at 10:59
You're still refering to the Customer table in the select part of the query, but not specifiying it in the From part of the query... – Martin Milan Oct 14 '14 at 14:05
I've added more to myanswer – Martin Milan Oct 14 '14 at 14:07
my prblem is solve now its fetching data from table but now its showing error 'object reference not set to an instance of object' – Ashu Renu Oct 14 '14 at 15:36
Scratch my last comment... Your problem is that you don't really understand Datasets - that's where you need to concentrate now. If you're only uerying (not updating) information, you might find it easier to work with a DataReader instead... – Martin Milan Oct 15 '14 at 16:24

how to use inner join in vb .net to fill textbox of vb windows form application

1 Answers1

Linked