Me again,

I am trying to query to seperate tables in the same database. One for the 
top navigation (tblManu) and the other for the middle content (tblList). I 
can get one or the other to work but not both.

Here is the query that the access query wizard made up for me:

strSQL = SELECT DISTINCTROW tblList.fldName, tblList.fldDesc, 
tblList.fldPrice, tblList.fldImagePath, tblManu.fldManuName
FROM tblManu INNER JOIN tblList ON tblManu.ID = tblList.fldID;


Here is the error:

Syntax error
/template/index.asp, line 25, column 9
strSQL = SELECT DISTINCTROW tblList.fldName, tblList.fldDesc, 
tblList.fldPrice, tblList.fldImagePath, tblManu.fldManuName
--------^

Any suggestions?

Lori

<%
strSQL = "SELECT ...
%>

Your SQL string (in your ASP page) is just a string of literal text. You
need to enclose it in double-quote marks.

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Lori Bannon" <lori@s...>
Subject: [access_asp] querying two different tables


: Me again,
:
: I am trying to query to seperate tables in the same database. One for the
: top navigation (tblManu) and the other for the middle content (tblList). I
: can get one or the other to work but not both.
:
: Here is the query that the access query wizard made up for me:
:
: strSQL = SELECT DISTINCTROW tblList.fldName, tblList.fldDesc,
: tblList.fldPrice, tblList.fldImagePath, tblManu.fldManuName
: FROM tblManu INNER JOIN tblList ON tblManu.ID = tblList.fldID;
:
:
: Here is the error:
:
: Syntax error
: /template/index.asp, line 25, column 9
: strSQL = SELECT DISTINCTROW tblList.fldName, tblList.fldDesc,
: tblList.fldPrice, tblList.fldImagePath, tblManu.fldManuName
: --------^
:
: Any suggestions?
:
: Lori

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

ok..i'm getting frustrated. I put the quotes in the SELECT statement and 
it reads the records 3 times each and now I can't even get my other query 
to work.

I started seeing if I could see where the query stopped working and put a 
response.write throughout the code. When it gets to the top nav query the 
response.write stops working...no error..just nothing. Do I need to 
refresh the oRs and if so how do I do that??? 

To make things worse...I deleted the original top nav table from the 
database and just added to the original table so that I would only be 
querying one table. Still doesn't work.


Here's my connection and query:

strSQL = "SELECT fldName,fldDesc,fldPrice,fldManuName FROM tblList"

(fldManuName is the top nav)

'Set connection object 
set conn = server.createobject("adodb.connection")
conn.open strconn
'Use the execute method of the connection object the insert the record
SET oRs = conn.execute(strSQL)

(put in the response.writes to make sure the query works)
response.write(oRs.Fields("fldName"))
response.write(oRs.Fields("fldDesc"))
response.write(oRs.Fields("fldPrice"))
response.write(oRs.Fields("fldManuName"))

Here's my top nav (its in an include file topNav.asp):

<form method="POST" action="index.asp">
<table width="530" border="0" cellspacing="0" cellpadding="0">

<tr valign="top">
<td class="textMain">Select a Category: <Select Name="ManuDrop" size="1">

<% DO WHILE NOT oRs.EOF 
on error resume next
%>

<OPTION VALUE="<%= oRs.Fields("fldManuName") %>"><%= oRs.Fields
("fldManuName") %></option>

<% oRs.MoveNext %>
<% Loop %>
</Select>
<input type="submit" value="Submit></td>
</tr>
</table>
</form>

Here's the middle content:

<%
DO WHILE NOT oRs.EOF
on error resume next
%>

        <tr>
	<td class="colorHeader"><% = oRs.Fields("fldName") %></td>
	<td class="colorHeader"><% = oRs.Fields("fldDesc") %></td>
	<td class="colorHeader"><% = oRs.Fields("fldPrice") %></td>
	</tr>
	
<% oRs.MoveNext %>
<% Loop %>
	
<%
conn.close
set conn = nothing
%>



Thanks again






> <%
strSQL = "SELECT ...
%>

Your SQL string (in your ASP page) is just a string of literal text. You
need to enclose it in double-quote marks.

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Lori Bannon" <lori@s...>
Subject: [access_asp] querying two different tables


: Me again,
:
: I am trying to query to seperate tables in the same database. One for the
: top navigation (tblManu) and the other for the middle content (tblList). 
I
: can get one or the other to work but not both.
:
: Here is the query that the access query wizard made up for me:
:
: strSQL = SELECT DISTINCTROW tblList.fldName, tblList.fldDesc,
: tblList.fldPrice, tblList.fldImagePath, tblManu.fldManuName
: FROM tblManu INNER JOIN tblList ON tblManu.ID = tblList.fldID;
:
:
: Here is the error:
:
: Syntax error
: /template/index.asp, line 25, column 9
: strSQL = SELECT DISTINCTROW tblList.fldName, tblList.fldDesc,
: tblList.fldPrice, tblList.fldImagePath, tblManu.fldManuName
: --------^
:
: Any suggestions?
:
: Lori

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Nevermind...I figured it out.

thanks





> ok..i'm getting frustrated. I put the quotes in the SELECT statement and 
i> t reads the records 3 times each and now I can't even get my other 
query 
t> o work.

> I started seeing if I could see where the query stopped working and put 
a 
r> esponse.write throughout the code. When it gets to the top nav query 
the 
r> esponse.write stops working...no error..just nothing. Do I need to 
r> efresh the oRs and if so how do I do that??? 

> To make things worse...I deleted the original top nav table from the 
d> atabase and just added to the original table so that I would only be 
q> uerying one table. Still doesn't work.

> 
H> ere's my connection and query:

> strSQL = "SELECT fldName,fldDesc,fldPrice,fldManuName FROM tblList"

> (fldManuName is the top nav)

> 'Set connection object 
s> et conn = server.createobject("adodb.connection")
c> onn.open strconn
'> Use the execute method of the connection object the insert the record
S> ET oRs = conn.execute(strSQL)

> (put in the response.writes to make sure the query works)
r> esponse.write(oRs.Fields("fldName"))
r> esponse.write(oRs.Fields("fldDesc"))
r> esponse.write(oRs.Fields("fldPrice"))
r> esponse.write(oRs.Fields("fldManuName"))

> Here's my top nav (its in an include file topNav.asp):

> <form method="POST" action="index.asp">
<> table width="530" border="0" cellspacing="0" cellpadding="0">

> <tr valign="top">
<> td class="textMain">Select a Category: <Select Name="ManuDrop" size="1">

> <% DO WHILE NOT oRs.EOF 
o> n error resume next
%> >

> <OPTION VALUE="<%= oRs.Fields("fldManuName") %>"><%= oRs.Fields
(> "fldManuName") %></option>

> <% oRs.MoveNext %>
<> % Loop %>
<> /Select>
<> input type="submit" value="Submit></td>
<> /tr>
<> /table>
<> /form>

> Here's the middle content:

> <%
D> O WHILE NOT oRs.EOF
o> n error resume next
%> >

>         <tr>
	> <td class="colorHeader"><% = oRs.Fields("fldName") %></td>
	> <td class="colorHeader"><% = oRs.Fields("fldDesc") %></td>
	> <td class="colorHeader"><% = oRs.Fields("fldPrice") %></td>
	> </tr>
	> 
<> % oRs.MoveNext %>
<> % Loop %>
	> 
<> %
c> onn.close
s> et conn = nothing
%> >

> 

> Thanks again

> 

> 

> 
>>  <%
s> trSQL = "SELECT ...
%> >

> Your SQL string (in your ASP page) is just a string of literal text. You
n> eed to enclose it in double-quote marks.

> Cheers
K> en

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
F> rom: "Lori Bannon" <lori@s...>
S> ubject: [access_asp] querying two different tables

> 
:>  Me again,
:> 
:>  I am trying to query to seperate tables in the same database. One for 
the
:>  top navigation (tblManu) and the other for the middle content 
(tblList). 
I> 
:>  can get one or the other to work but not both.
:> 
:>  Here is the query that the access query wizard made up for me:
:> 
:>  strSQL = SELECT DISTINCTROW tblList.fldName, tblList.fldDesc,
:>  tblList.fldPrice, tblList.fldImagePath, tblManu.fldManuName
:>  FROM tblManu INNER JOIN tblList ON tblManu.ID = tblList.fldID;
:> 
:> 
:>  Here is the error:
:> 
:>  Syntax error
:>  /template/index.asp, line 25, column 9
:>  strSQL = SELECT DISTINCTROW tblList.fldName, tblList.fldDesc,
:>  tblList.fldPrice, tblList.fldImagePath, tblManu.fldManuName
:>  --------^
:> 
:>  Any suggestions?
:> 
:>  Lori

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~