The multi-part identifier "ticket.ticketid" could not be bound.

[dieselxeon]

hi everyone ..
I am a student and i am learning how to create form using vb but i am stuck here since this is my school project, I need someone to help me to figgure out what is wrong with my code.also i try to create store procedure in sql 2005 and it give me This error Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value '@Itinerary.ticketid' to data type int(but my ticketid value already integer not varchar). Please help me ! And Thank you for taking time to read my massage.

this is in form VB

Private Sub orderidtxbx_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles orderidtxbx.Click
        sqlconnection = New SqlConnection("Data Source= xxxx;Initial Catalog = xxxx; user ID = sa; Password = xxxxx;")
        sqlcommand = New SqlCommand("select  Customer.LastName, Customer.FirstName from customer where ticket.ticketid ='" & orderidtxbx.Text & "'", sqlconnection)
 
        sqlconnection.Open()
        sqldatareader = sqlcommand.ExecuteReader()
        ListBox1.Items.Add(sqldatareader.Item("LastName") & " " & sqldatareader.Item("FirstName")

query in sql 2005,I need to make store procedure.

SELECT     Itinerary.FlightID, Itinerary.Class, Itinerary.Quantity, Ticket.Date, Flight.FlightNo, Flight.AirLine, Flight.DepartureDate, Flight.ArrivalDate, 
                      Flight.DepartureTime, Flight.ArrivalTime, Flight.Price, Departure.DepartureLocation, Arrival.ArrivalLocation,GST=convert(int,Itinerary.Quantity*0.06*Flight.Price),PST=convert(int,Itinerary.Quantity*0.07*Flight.Price),TotalPrice=convert(int,(Itinerary.Quantity*0.06*Flight.Price)+(Itinerary.Quantity*0.07*Flight.Price)+Flight.Price)
FROM         Flight INNER JOIN
                      Itinerary ON Flight.FlightID = Itinerary.FlightID INNER JOIN
                      Ticket ON Itinerary.TicketID = Ticket.TicketID INNER JOIN
                      Departure ON Flight.DepartureLocationID = Departure.DepartureID INNER JOIN
                      Arrival ON Flight.ArrivalLocationID = Arrival.ArrivalID
WHERE     (Itinerary.ticketid = '@Itinerary.ticketid')

if i change WHERE (Itinerary.ticketid = 1)
it will read the data in the table.

I need to refer that itinerary.ticketid into textbox in form that's why i make like this WHERE (Itinerary.ticketid = '@Itinerary.ticketid') but i don't know what is worng with this.

This error Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value '@Itinerary.ticketid' to data type int(but my ticketid value already integer not varchar).

[Shashi Sadasivan]

hi,
Please remove the single quotes at the end of your sql Statement for the parameter.
Putting single quotes defines that as a string value (and not as a parameter anymore), so your parameter will be ignored
And hence the error

the end of the SQL should look like this

Arrival ON Flight.ArrivalLocationID = Arrival.ArrivalID
WHERE     (Itinerary.ticketid = @Itinerary.ticketid)

[dieselxeon]

Thank you for replying.
I remove the quotes but it give me other error

SELECT     Itinerary.FlightID, Itinerary.Class, Itinerary.Quantity, Ticket.Date, Flight.FlightNo, Flight.AirLine, Flight.DepartureDate, Flight.ArrivalDate, 
                      Flight.DepartureTime, Flight.ArrivalTime, Flight.Price, Departure.DepartureLocation, Arrival.ArrivalLocation,GST=convert(int,Itinerary.Quantity*0.06*Flight.Price),PST=convert(int,Itinerary.Quantity*0.07*Flight.Price),TotalPrice=convert(int,(Itinerary.Quantity*0.06*Flight.Price)+(Itinerary.Quantity*0.07*Flight.Price)+Flight.Price)
FROM         Flight INNER JOIN
                      Itinerary ON Flight.FlightID = Itinerary.FlightID INNER JOIN
                      Ticket ON Itinerary.TicketID = Ticket.TicketID INNER JOIN
                      Departure ON Flight.DepartureLocationID = Departure.DepartureID INNER JOIN
                      Arrival ON Flight.ArrivalLocationID = Arrival.ArrivalID
WHERE     (Itinerary.ticketid = @Itinerary.ticketid)

this error
Msg 137, Level 15, State 2, Line 9
Must declare the scalar variable "@Itinerary".

Forgive me if remove not the right quotes

[Shashi Sadasivan]

why is your parameter named "@Itinerary.ticketid"?

I dont know if a . (dot) in the parameter name will be the issue.
Have you tried giving the parameter a name without the dot in it?
maybe soemthing like @ItineraryTicketid ?

[dieselxeon]

hi
i think i need to tell you what is in my table

i have
customer table, (what is side the table customer info and customerid)
employee table (what is side employee info and employeeid)
ticket table ,(ticketid (PK) ,Cusomerid(FK), employeeid(FK),and date)
itinerary table (TicketID(FK) from ticket table, Flightid(FK) from flight table,class,quantity)
also other table

I think ticket table like order table and Itinerary table like order detail.

right now there is some data in the table ticket and itinerary

Ticket table
ticketid = 1
customerid = 1
employeeid = 1
date= date of ordering

itinerary table
ticketid = 1(samecustomer)
flightid=1(flight to destination A)
class = business
quantity = 1

also it has other which is
ticketid =1 (same customer)
flightid = 2(flight to destination B)
class = business
quantity = 1

mean that the customer buy 2 ticket. so i need to get information from ticket and itinerary table. what i want to display is the value of integer for example:
is say customer id=1 so i need to find who is customerid no from customer table. i can't just put 1 right? becuase i don't know who is customer id no1. Other example it say flight id = 1 i need to know what is in flightid no 1 from flight table there are arrival time , departure time ,date and so on...

Thank you for helping me...

[Shashi Sadasivan]

Hi,
Welsome to TSDN

This seems to be more of a SQL related question than a .Net issue.
I will move the thread to the SQL server forum where Experts there will be able to help you better, and you should get a better response there.

Please do have a look at the Posting Guidelilnes on how to post questions, and how to name your post title so as to recieve a better response and keeping the thread titles crystal clear.

- Shashi

[dieselxeon]

I am sorry ... and Thank you for telling me because i am new in this forum. Would you mind to tell me the link where i can post my question. Thank you again.

[Shashi Sadasivan]

I have already moved it to the appropriate forum.
Please take a look at the navigation bar on the top (in blue) which indicates where you are.

with your SQL query, placing a '@' in @Itinerary.ticketid it seems that you requre this value to be passed in by the user / the application.
in which case it is right, In that case, the paramenter name could be anything, but i do not know if the current name violates the way in twhich parameters should be named (something like in C, you cannot have a variable which starts with number or contains dots)

Please try removing the dot in the variable.

[dieselxeon]

I am sorry about late reply because i just finish working. I just try to remove but it didn't work too.I am not quit undestand which one i have to remove. I am sorry because i am just beginneer.

[Shashi Sadasivan]

what i mean by removing the dot was to do something like renaming @Itinerary.ticketid to @ItenaryTicketID

[dieselxeon]

i did that one and still error
Msg 137, Level 15, State 2, Line 9
Must declare the scalar variable "@Itinerary".

[Shashi Sadasivan]

I did not realise that you do not know how to delclare variables with stored procedure as you had not provided the entire script for it.
Please have a look at this link on how to add paramters to your stored procedure

[dieselxeon]

You are right about that. i did that one and it works. however, when i try to bind the to listbox in vb form it come again the error travelagencydataset2.itineraryflightreport.fill,ge tdata()couldn't be previewed.
conversion failed when converting the varchar value '@itinerary.ticketid' to data type int.



this one is my store procedure name itineraryflightreport. so that's why i thought something is wrong with my storeprocedure.
so in my vb form i have one text box which is customeridtxbx and listbox. i bind the storeprocedure to listbox so that when i insert my customerid, my list box is going to display the information. My english is not that good please forgive me. let say like this if i put 1 in my textbox in form it will display the info based on my procedure like arrivallocation from table flight and so on. I don't know this is the right link to ask about vb.

[Shashi Sadasivan]

Hi
could you please repost the new SQL storedd procedure script (it should include declarations).
I cannot say whats going on until i see what you have done.

[dieselxeon]

USE [travelagency]
GO
/****** Object: StoredProcedure [dbo].[itineryfinal] Script Date: 11/25/2007 22:57:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[itineryfinal]
(@ticketid int)
as
SELECT Itinerary.FlightID, Itinerary.Class, Itinerary.Quantity, Ticket.Date, Flight.FlightNo, Flight.AirLine, Flight.DepartureDate, Flight.ArrivalDate,
Flight.DepartureTime, Flight.ArrivalTime, Flight.Price, Departure.DepartureLocation, Arrival.ArrivalLocation,GST=convert(int,Itinerary. Quantity*0.06*Flight.Price),PST=convert(int,Itiner ary.Quantity*0.07*Flight.Price),TotalPrice=convert (int,(Itinerary.Quantity*0.06*Flight.Price)+(Itine rary.Quantity*0.07*Flight.Price)+Flight.Price)
FROM Flight INNER JOIN
Itinerary ON Flight.FlightID = Itinerary.FlightID INNER JOIN
Ticket ON Itinerary.TicketID = Ticket.TicketID INNER JOIN
Departure ON Flight.DepartureLocationID = Departure.DepartureID INNER JOIN
Arrival ON Flight.ArrivalLocationID = Arrival.ArrivalID
WHERE (Itinerary.ticketid = '@Itinerary.ticketid')


is already create in my sql 2005

now i tried to bind to vb form this one "WHERE (Itinerary.ticketid = '@Itinerary.ticketid')" i will refer to my ticketidtxbx in form and the info will display in listbox.


i don't really know how to write the code in vb to execute the storeprocedure to listbox but i know to execute to gridview. first i wrote this code but every time i input 1 in textbox, it didn't display any info ... i think i wrote wrong code please correct me if i am wrong.



Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

If orderidtxbx.Text = "" Then
sqlconnection = New SqlConnection("Data Source= xxxxx;Initial Catalog = travelagency; user ID = xxxx; Password = xxxxxxxxx;")
sqlcommand = New SqlCommand("itineraryflightreport", sqlconnection)
sqlcommand.CommandType = CommandType.StoredProcedure

sqldatatable = New Data.DataTable("itineraryflightreport")
Try
sqlconnection.Open()

sqldatareader = sqlcommand.ExecuteReader()
While sqldatareader.Read
ListBox1.Items.Add(sqldatareader.Item("Flight.Flig htNo") & " " & sqldatareader.Item("Flight.AirLine") & " " & sqldatareader.Item("Departure.DepartureLocation") & " " & sqldatareader.Item("Arrival.ArrivalLocation") & " " & sqldatareader.Item("Flight.DepartureDate") & " " & sqldatareader.Item("Flight.ArrivalDate") & " " & sqldatareader.Item("Flight.DepartureTime") & " " & sqldatareader.Item("Flight.ArrivalTime") & " " & sqldatareader.Item("Flight.Price") & " " & sqldatareader.Item("Ticket.Date") & " " & sqldatareader.Item("Itinerary.TicketID") & " " & sqldatareader.Item("Itinerary.FlightID") & " " & sqldatareader.Item("Itinerary.Class") & " " & sqldatareader.Item("Itinerary.Quantity") & " " & sqldatareader.Item("GST = Convert(Int, Itinerary.Quantity * 0.06 * Flight.Price)") & " " & sqldatareader.Item("PST = Convert(Int, Itinerary.Quantity * 0.07 * Flight.Price)") & " " & sqldatareader.Item("TotalPrice = Convert(Int, (Itinerary.Quantity * 0.06 * Flight.Price) + (Itinerary.Quantity * 0.07 * Flight.Price) + Flight.Price)"))
End While

Catch ex As Exception
Debug.WriteLine(ex.Message)

End Try
End If

End Sub




so wrote the second code whic is

Private Sub orderidtxbx_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles orderidtxbx.Click
sqlconnection = New SqlConnection("Data Source= xxxxxx;Initial Catalog = travelagency; user ID = xxxxxxx; Password = xxxxxx;")
sqlcommand = New SqlCommand("select Customer.LastName, Customer.FirstName from customer where ticket.ticketid ='" & orderidtxbx.Text & "'", sqlconnection)

sqlconnection.Open()
sqldatareader = sqlcommand.ExecuteReader()
ListBox1.Items.Add(sqldatareader.Item("LastName") & " " & sqldatareader.Item("FirstName"))


End Sub

i just wrote sort code because i wanted to know is work or not so i select customer latname from customer table and firstname from customer table base on ticket id.

this is the detail again
ticket table in sql
ticket id = 1
customer id = 1 (i need to know who is one so based on ticket id no 1 )so i use join "customer.lastname"
employeid=1
everytime i debug it is say
The multi-part identifier "ticket.ticketid" could not be bound.
i don't know what to do please help me...