BleepingComputer.com: SQL select statement errors

I am trying to grab data from a db and using a variable in my where clause and getting the following error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Unit WHERE Status='Approved' AND WHERE Unit=ALLRED UNIT'.

Here is my code:

<%
	dim rst1
	set rst1 = Server.CreateObject("ADODB.Recordset")
		rst1.Open "SELECT Unit FROM Prison Where Status='APPROVED' ORDER BY Unit", conn, 3, 3
	do while not rst1.EOF
		strUnit=rst1.Fields("Unit")
	
%>
	
	
	<THEAD>
		<TR class=textcol style="BACKGROUND-COLOR:rgb(216,233,236)">
			<TD nowrap width=120 colspan="3">
				<div align="center"><%=rst1.Fields("Unit")%>				</div>
				</TD>
		</TR>
	</THEAD>
	<TBODY>
		<TR>
		<%	set rst = Server.CreateObject("ADODB.Recordset")
		rst.LockType = 3
		rst.CursorType = 3
		strSql= "SELECT FirstName, LastName, Birthday, Unit FROM Prison ORDER BY Unit WHERE Status='Approved' AND WHERE Unit="& strUnit 
		rst.Open strSQL, conn
		'Response.Write strSql
	Do While Not rst.EOF
	%>
			<TD nowrap >
				<%=rst.Fields("FirstName")%>			</TD>
			<TD nowrap >
				<%=rst.Fields("LastName")%>			</TD>
			<TD nowrap >
				<%=rst.Fields("Birthday")%>			</TD>
		</TR>
		
	<%
	R.MoveNext
	loop
	R.Close
	set R=nothing
	
	rst1.MoveNext
	%>
	
	<%
	loop
	rst1.Close
	set rst1=nothing
	%>

Ty "Unit WHERE Status='Approved' AND Unit=ALLRED UNIT"

Hello SpaCeTraNce

I'm no asp expert, but it looks like:

This is the culprit:
strSql= "SELECT FirstName, LastName, Birthday, Unit FROM Prison ORDER BY Unit WHERE Status='Approved' AND WHERE Unit="& strUnit

you need the end quote here. Make that
strSql= "SELECT FirstName, LastName, Birthday, Unit FROM Prison ORDER BY Unit WHERE Status='Approved' AND WHERE Unit='"& strUnit & "'"

Billy3

This post has been edited by Billy O'Neal: 14 October 2008 - 02:04 PM

groovicus:

I don't want to hard code "allred unit" I am using a variable defined above.

billy:

Tried that and now I get:

Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/ccommission/icomadmin/prayerlist.asp, line 57, column 128
strSql= "SELECT FirstName, LastName, Birthday, Unit FROM Prison ORDER BY Unit WHERE Status='Approved' AND WHERE Unit="& strUnit "'"

Now in this error the strUnit is NOT pulling the data as before where the output was ALLRED UNIT, so I think I am closer to a grand slam when recieving the other error.

Quote

You forgot the & ;)

Or rather, you need to add those strings together.. not exactly sure how that's accomplised in that language....

Billy3

All I did was copy the statement that you used, and corrected the syntax error. You only need to use WHERE once. I assumed you could figure out how to change the code all by yourself.

Troubleshooting a SQL statement is easy. All you need to do is drop to the command line, and type in the query. If the query works, then you know that there is a problem with the PHP. IF it doesn't, then there is an error in the SQL syntax.

Even if I take out the second WHERE clause I still get the same error.

If I add the second & as BIlly suggest I am back to where I started.

"SELECT FirstName, LastName, Birthday, Unit FROM Prison ORDER BY Unit WHERE Status='Approved' AND Unit="& strUnit &"'"

=

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Unit WHERE Status='Approved' AND Unit=ALLRED UNIT''.

This post has been edited by SpaCeTraNce: 14 October 2008 - 05:07 PM

Now you're missing a quote ;)

remember quotes have to go before and after your inserted data.

Sorry.. missed that one.

"SELECT FirstName, LastName, Birthday, Unit FROM Prison ORDER BY Unit WHERE Status='Approved' AND Unit='"& strUnit &"'"

I got it to work!

"SELECT FirstName, LastName, Birthday, Unit FROM Prison  WHERE Status='Approved' AND Unit='" & strUnit & "' ORDER BY Unit"

Congrats!!

thanx