Can anybody help with a sql syntax question????

[1712]

I have the following code that gives me an error that something is wrong with the from clause.

sqlstr = "SELECT * FROM nanny, references left join references on nanny.id = references.id"
sqlstr = sqlstr & " WHERE nanny.id = " & request("id&quot

I have tried to cut and paste a query from access sql builder and also tried a number of ways manually but cannot seem to fix this. It works find if I just do a select * from file where field = variable.

 

[Veep]

Try:
Select nanny.*,references.* from nanny LEFT JOIN references on nanny.ID = references.ID

Also try to get away from SELECT *

 

[1712]

I tried your suggestion and the error now is
page cannot be displayed

 

[codestorm]

sqlstr = "SELECT * FROM nanny left join references on nanny.id = references.id"
sqlstr = sqlstr & " WHERE nanny.id = " & request("id&quot

You had nanny, references.
If you are using JOINs, you don't list all tables at the start - you only do it that way if the sql is like

sqlstr = "select * from nanny, references where nanny.id = references.id and nanny.id = " & request("id&quot

On a side note, I assume that request("id&quot is only ever an integer?

codestorm
Newbie Life Member.
Fire bad. Tree pretty. - Buffy
<insert witticism here>

 

[mmcgon]

I don't think you want the references table both before and after the join. Try:

sqlstr = &quot;SELECT * FROM nanny LEFT JOIN references on nanny.id = references.id&quot;

sqlstr = sqlstr & &quot; WHERE nanny.id = &quot; & request(&quot;id&quot

 

[Veep]

First: Go to Tools>Internet Options>Advanced> and uncheck &quot;Show Friendly HTTP Error Messages&quot;

Second: Response.Write your SQL Statement before you execute it ie. This is an unwritten rule. Don't even ask a think about asking about what's wrong until you do this.

response.write(sqlstr)
response.end

This way you can see exactly what you're sending to the database.

Third: Build the query in MSACCESS (I'm assuming that's what you're using). Go to SQL view and see what MSACCESS likes. It's important that the database that you talk to &quot;likes&quot; what you say to it.

Fourth: You can create a parameterized query in MSACCESS and just send it the &quot;ID&quot; and let Access do the rest ie:
sqlstr=&quot;EXEC myQuery @ID=&quot; & ID
Trust me, wayyyyy less headaches.

Fifth: Skip number 5. I was going somewhere I shouldn't go.

Sixth: I hope you didn't take my suggestion literaly. You still need to add the &quot; WHERE &quot; part.

 

[1712]

thanks for all the replies but I did have the sql statement first like most of you suggested and it still says I have an error with the from clause:

sqlstr = &quot;SELECT * FROM Nanny LEFT JOIN References ON Nanny.id = References.ID&quot;
sqlstr = sqlstr & &quot; WHERE nanny.id = &quot; & request(&quot;id&quot

I have tried also to create a query in msaccess and copy & paste the same phrase over and still it does not like it.
I did like the tip on unchecking the box for the unfriendly http errors. It confirmed the same error with the sql statement. I have another problem unrelated to this that this may also help out.

I am going to see if I can try the parameterized query as you suggested. How does the asp code know of the query name?

 

[1712]

I just tried doing a sql statement without the where statement and it still failed with the from clause error. I then put the where statement back in and took out the left join statement out and it worked again. So it simply does not like the left join section.

Is the parameterized query in access easy to setup? any helpfiles on this since I never did this before?

 

[dmhirsch]

Just a left-field thought... Is &quot;references&quot; a reserved word in SQL or Access? You could try putting square brackets around it, like:

[references].id