Running a complex query in VB

[Malhoosh]

Hello,

I'm trying to find some way to run a complex query (not just a simple select statement) against a database and the output is a table.
More details:
I have a sql database that contains two tables Tickets and TicketDetails joined on by the Key TicketNumber. Now, the ticket numbers are not consecutive which means that the Ids in the Tickets table can be "1-2-4-16-20-50" and so on. I need to create a windows form application that can display the missing ticket numbers as well as the existing ticket numbers. So in some way I will display graphically the existing ticket details and next to the missing numbers I will display "Does not exist" for example:
1 - "Person1 - Age1 "
2 - "Person2 - Age2 "
3 - "Does not exist"
4 - "Person4 - Age3"
5 - "Does not exist"
....etc.
To do this, I thought of this query:

declare @from int
declare @to int

SET @from=5000
SET @to=5100

CREATE TABLE #mytable
(
	TicketNo INT
)

While (@from<=@to)
BEGIN
	INSERT INTO #mytable (TicketNo)
	VALUES (@from)

	set @from=@from+1
END

SELECT  #mytable.TicketNo, TicketDetails.Person, TicketDetails.Age 
FROM #mytable
LEFT JOIN Tickets on #mytable.TicketNo=Tickets.TicketNo
LEFT JOIN TicketDetails ON Tickets.TicketNo=TicketDetails.TicketNo

DROP TABLE #mytable

I can't find a way to run this query on my windows form application. I can only run simple select statements to query the database, I also can't create a stored procedure in my database to do this, because this is not a local database, it's a shared database and I can't alter it.

Is there a way I can run this query on my windows form application and return the result in a grid ?

Thanks in advance,
M

[Radjesh Klauke]

Well, since you "DROP TABLE" on the end of the query it will never show any result. But have a look at this: http://www.sourcecodester.com/tutori...basic-net.html

[jmcilhinney]

I can't find a way to run this query on my windows form application. I can only run simple select statements to query the database

Really? What happens if you create a data adapter with that SQL code in the SelectCommand and then call Fill? Are you saying that an exception is thrown?

[techgnome]

Well, since you "DROP TABLE" on the end of the query it will never show any result. But have a look at this: http://www.sourcecodester.com/tutori...basic-net.html

Not true... there's a select just before the drop... common practice... I do that all the time... select from the temp table, then promptly drop it... that's how you should do it.


wait... hold on... why is there a temp table in the first place? It doesn't seem to serve any purpose at all. Ah... never mind... I see it's a left join... I thought I saw an inner join... OK....

what DBMS are you using? And what version? There might be a better way to skin this cat.

-tg

[Shaggy Hiker]

I'd be inclined to suck in the data you have, then add in the missing rows on the app side. I don't know which would prove to be more efficient.

[Radjesh Klauke]

there's a select just before the drop

Aaaaah... overlooked that part.