Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

SQL select statement errors


  • Please log in to reply
10 replies to this topic

#1 SpaCeTraNce

SpaCeTraNce

  • Members
  • 97 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Earth
  • Local time:02:39 PM

Posted 14 October 2008 - 01:45 PM

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
	%>



If I find in myself a desire which no experience in this world can satisfy, the most probable explanation is that I was made for another world. -- C.S. Lewis

The more I study science, the more I believe in God. -- Albert Einstein

Mathematics is the language with which God has written the universe. -- Galileo Galilei

I tremble for my country when I reflect that God is just, that His justice cannot sleep forever. -- Thomas Jefferson

BC AdBot (Login to Remove)

 


m

#2 groovicus

groovicus

  • Security Colleague
  • 9,963 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Centerville, SD
  • Local time:01:39 PM

Posted 14 October 2008 - 02:01 PM

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

#3 Billy O'Neal

Billy O'Neal

    Visual C++ STL Maintainer


  • Malware Response Team
  • 12,301 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Redmond, Washington
  • Local time:12:39 PM

Posted 14 October 2008 - 02:02 PM

Hello SpaCeTraNce :thumbsup:

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

Edited by Billy O'Neal, 14 October 2008 - 02:04 PM.

Twitter - My statements do not establish the official position of Microsoft Corporation, and are my own personal opinion. (But you already knew that, right?)
Posted Image

#4 SpaCeTraNce

SpaCeTraNce
  • Topic Starter

  • Members
  • 97 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Earth
  • Local time:02:39 PM

Posted 14 October 2008 - 03:37 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.


If I find in myself a desire which no experience in this world can satisfy, the most probable explanation is that I was made for another world. -- C.S. Lewis

The more I study science, the more I believe in God. -- Albert Einstein

Mathematics is the language with which God has written the universe. -- Galileo Galilei

I tremble for my country when I reflect that God is just, that His justice cannot sleep forever. -- Thomas Jefferson

#5 Billy O'Neal

Billy O'Neal

    Visual C++ STL Maintainer


  • Malware Response Team
  • 12,301 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Redmond, Washington
  • Local time:12:39 PM

Posted 14 October 2008 - 03:58 PM

strUnit "'"

You forgot the & ;)

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

Billy3
Twitter - My statements do not establish the official position of Microsoft Corporation, and are my own personal opinion. (But you already knew that, right?)
Posted Image

#6 groovicus

groovicus

  • Security Colleague
  • 9,963 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Centerville, SD
  • Local time:01:39 PM

Posted 14 October 2008 - 04:15 PM

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. :thumbsup:

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.

#7 SpaCeTraNce

SpaCeTraNce
  • Topic Starter

  • Members
  • 97 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Earth
  • Local time:02:39 PM

Posted 14 October 2008 - 05:06 PM

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''.

Edited by SpaCeTraNce, 14 October 2008 - 05:07 PM.



If I find in myself a desire which no experience in this world can satisfy, the most probable explanation is that I was made for another world. -- C.S. Lewis

The more I study science, the more I believe in God. -- Albert Einstein

Mathematics is the language with which God has written the universe. -- Galileo Galilei

I tremble for my country when I reflect that God is just, that His justice cannot sleep forever. -- Thomas Jefferson

#8 Billy O'Neal

Billy O'Neal

    Visual C++ STL Maintainer


  • Malware Response Team
  • 12,301 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Redmond, Washington
  • Local time:12:39 PM

Posted 14 October 2008 - 05:16 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 &"'"

Twitter - My statements do not establish the official position of Microsoft Corporation, and are my own personal opinion. (But you already knew that, right?)
Posted Image

#9 SpaCeTraNce

SpaCeTraNce
  • Topic Starter

  • Members
  • 97 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Earth
  • Local time:02:39 PM

Posted 15 October 2008 - 02:55 PM

I got it to work!

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



If I find in myself a desire which no experience in this world can satisfy, the most probable explanation is that I was made for another world. -- C.S. Lewis

The more I study science, the more I believe in God. -- Albert Einstein

Mathematics is the language with which God has written the universe. -- Galileo Galilei

I tremble for my country when I reflect that God is just, that His justice cannot sleep forever. -- Thomas Jefferson

#10 groovicus

groovicus

  • Security Colleague
  • 9,963 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Centerville, SD
  • Local time:01:39 PM

Posted 15 October 2008 - 03:17 PM

Congrats!! :flowers: :thumbsup:

#11 SpaCeTraNce

SpaCeTraNce
  • Topic Starter

  • Members
  • 97 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Earth
  • Local time:02:39 PM

Posted 15 October 2008 - 04:00 PM

thanx :thumbsup:


If I find in myself a desire which no experience in this world can satisfy, the most probable explanation is that I was made for another world. -- C.S. Lewis

The more I study science, the more I believe in God. -- Albert Einstein

Mathematics is the language with which God has written the universe. -- Galileo Galilei

I tremble for my country when I reflect that God is just, that His justice cannot sleep forever. -- Thomas Jefferson




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users