PDA

Click to See Complete Forum and Search --> : Trouble with SQL query in ASP page

spiderbaby
04-29-2003, 07:55 AM
I'm trying to set up an ASP script to delete entries from an Access database, but I can't get the Query itself to work. Here's the code for the delete script:


<%
If Request("submit")="Yes" Then
'delete record
Query = "DELETE FROM emps WHERE last_name =" & Request("last_name") & " AND first_name =" & Request("first_name")
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open "phonedir"
Set RSlist = Server.CreateObject("ADODB.recordset")
RSlist.Open Query,DataConn,1,2
Response.Redirect "delemps.asp"
ElseIf Request("submit")="No" Then
'do not delete record
Response.Redirect "delemps.asp"
Else
'display option to delete%>
<html>
<body bgcolor="lightblue">
Do you wish to delete: <B><%=Request("first_name")%>&nbsp;<%=Request("last_name")%></B>?
<form action="deleteemps.asp">
<input type="submit" name="submit" value="No">&nbsp;
<input type="submit" name="submit" value="Yes">
</form>
</body>
</html>
<%End If%>


And whenever I run the script, I get the following error message:


Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access 97 Driver] Syntax error (missing operator) in query expression 'last_name = AND first_name ='.

/intranet/aspdb/deleteemps.asp, line 8



I'm guessing I'm just writing the Query syntax incorrectly? Can anyone help?
khaki
04-29-2003, 08:10 AM
that is the biggest bugaboo when trying to slide variables into sql statements.

i've spent many long days (and nites) trying to get that right....

so i simply had it tattooed into my brain...
and now i never forget anymore! :)

(just thought that i'd share :rolleyes: )
;) k
spiderbaby
04-29-2003, 08:15 AM
Thank you for the response dave. That code did work to eliminate the syntax error.

However, now it appears the code is skipping the first conditional (even when "submit" = "Yes") and only executing the ElseIf code, meaning I'm redirected to delemps.asp but the record I'm trying to delete was not deleted.

Any ideas?
spiderbaby
04-29-2003, 11:08 AM
Update:

I forced the first conditional to execute and commented out the ElseIf code. What I realized is that the SQL Query conditions aren't being met. When I hard-coded a specific first_name and last_name value, the script worked just fine, so it has something to do with the variables not holding their values within the SQL query.

The file delemps.asp lists the records in the database and provides a 'delete' hyperlink for each record. That hyperlink calls deleteemps.asp (the code I posted above). Here's the code for delemps.asp:


<%
Query = "SELECT last_name,first_name FROM emps ORDER BY last_name"
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open "phonedir"
Set RSlist = Server.CreateObject("ADODB.recordset")
RSlist.Open Query,DataConn,1,2
%>
<html>
<body bgcolor="lightblue" text="black" link="blue" vlink="blue">
<table border=1>
<tr><td><b>Last Name</b></td><td>First Name</td><td>&nbsp;</td></tr>
<%Do While Not RSlist.EOF
%>
<tr>
<td><%=RSlist("last_name")%></td>
<td><%=RSlist("first_name")%></td>
<td>
<a href="deleteemps.asp?first_name=<%=RSlist("first_name")%>&last_name=<%=RSlist("last_name")%>">delete</a>
</td>
</tr>
<%RSlist.Movenext
Loop%>
</table>
</body>
</html>


Any ideas why the Query variables are blank in deleteemps.asp?