|
Home > Archive > Visual FoxPro SQL Queries > September 2005 > Help with a query
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Eric Beasley 2005-09-13, 11:25 am |
| Why does this work:
SQLEXEC(1,'select ticket.serial, ticket.ticketnum, main.builder, ;
from main;
inner join ticket;
on main.serial=ticket. serial','tempcursor'
)
But when I try to add a 3rd table it doesn't work:
SQLEXEC(1,'select ticket.serial, ticket.ticketnum, main.builder,
codes.subcode ;
from main;
inner join ticket on main.serial=ticket.serial;
inner join codes on ticket.subcode=main. subcode','tempcursor
')
I'm connecting from VFP 9 to SQL Server. When I run the first query it gives
me my "tempcursor". When I run the second query I get nothing. Control
returns to the command window but no cursor is created and no error comes up.
Thanks!
| |
| Fred Taylor 2005-09-13, 11:25 am |
| An error message won't "come up". You have to check the return status from
the SQLEXEC call and if it's not 1, then you can use AERROR() to see what
the error is.
--
Fred
Microsoft Visual FoxPro MVP
"Eric Beasley" < EricBeasley@discussi
ons.microsoft.com> wrote in message
news:BE7B5D56-2902-4486-B2C1- ACF4A627F910@microso
ft.com...
> Why does this work:
> SQLEXEC(1,'select ticket.serial, ticket.ticketnum, main.builder, ;
> from main;
> inner join ticket;
> on main.serial=ticket. serial','tempcursor'
)
>
> But when I try to add a 3rd table it doesn't work:
> SQLEXEC(1,'select ticket.serial, ticket.ticketnum, main.builder,
> codes.subcode ;
> from main;
> inner join ticket on main.serial=ticket.serial;
> inner join codes on ticket.subcode=main. subcode','tempcursor
')
>
> I'm connecting from VFP 9 to SQL Server. When I run the first query it
> gives
> me my "tempcursor". When I run the second query I get nothing. Control
> returns to the command window but no cursor is created and no error comes
> up.
> Thanks!
| |
| Eric Beasley 2005-09-13, 11:25 am |
| Fred, the SQLEXEC call is giving me a -1. I looked that up and it's a
connection level error. For some reason, however, it isn't appearing as a Fox
error because my "ON ERROR" error routine isn't firing. (that's where I put
the aerror() code to trap the error)
Strange that when joining only 2 tables there is no connection error, but
when I add the 3rd it causes such an error.
"Fred Taylor" wrote:
> An error message won't "come up". You have to check the return status from
> the SQLEXEC call and if it's not 1, then you can use AERROR() to see what
> the error is.
>
> --
> Fred
> Microsoft Visual FoxPro MVP
>
>
> "Eric Beasley" < EricBeasley@discussi
ons.microsoft.com> wrote in message
> news:BE7B5D56-2902-4486-B2C1- ACF4A627F910@microso
ft.com...
>
>
>
| |
| Anders 2005-09-13, 8:25 pm |
| The second ON clause is screwed up; it's got to be
INNER JOIN Codes ON Codes.some_column=Main.subcode
or possibly
JOIN Codes ON Codes.some_column = Ticket.subcode
-Anders
"Eric Beasley" < EricBeasley@discussi
ons.microsoft.com> skrev i meddelandet
news:BE7B5D56-2902-4486-B2C1- ACF4A627F910@microso
ft.com...
> Why does this work:
> SQLEXEC(1,'select ticket.serial, ticket.ticketnum, main.builder, ;
> from main;
> inner join ticket;
> on main.serial=ticket. serial','tempcursor'
)
>
> But when I try to add a 3rd table it doesn't work:
> SQLEXEC(1,'select ticket.serial, ticket.ticketnum, main.builder,
> codes.subcode ;
> from main;
> inner join ticket on main.serial=ticket.serial;
> inner join codes on ticket.subcode=main. subcode','tempcursor
')
>
> I'm connecting from VFP 9 to SQL Server. When I run the first query it
> gives
> me my "tempcursor". When I run the second query I get nothing. Control
> returns to the command window but no cursor is created and no error comes
> up.
> Thanks!
| |
| Eric Beasley 2005-09-13, 8:25 pm |
| Eureka! Thanks for the 2nd set of eyes Anders! I completely missed that!
Problem solved.
"Anders" wrote:
> The second ON clause is screwed up; it's got to be
> INNER JOIN Codes ON Codes.some_column=Main.subcode
> or possibly
> JOIN Codes ON Codes.some_column = Ticket.subcode
>
> -Anders
>
> "Eric Beasley" < EricBeasley@discussi
ons.microsoft.com> skrev i meddelandet
> news:BE7B5D56-2902-4486-B2C1- ACF4A627F910@microso
ft.com...
>
>
>
| |
| Fred Taylor 2005-09-14, 3:24 am |
| That's because it isn't a VFP error at all, it's a SQL error. You must
check the return status of the SQLEXEC call and then use AERROR() there to
see what the problem is.
--
Fred
Microsoft Visual FoxPro MVP
"Eric Beasley" < EricBeasley@discussi
ons.microsoft.com> wrote in message
news:52B06EE4-0E8D-4CB4-89F9- 1A524D679A2A@microso
ft.com...[color=darkred]
> Fred, the SQLEXEC call is giving me a -1. I looked that up and it's a
> connection level error. For some reason, however, it isn't appearing as a
> Fox
> error because my "ON ERROR" error routine isn't firing. (that's where I
> put
> the aerror() code to trap the error)
>
> Strange that when joining only 2 tables there is no connection error, but
> when I add the 3rd it causes such an error.
>
> "Fred Taylor" wrote:
>
|
|
|
|
|