Error message in deleting recods from database using with checkboxes 1

Beng79 · Feb 18, 2006

Hi All,

I am trying to run an ASP script in deleting recods from database using with checkboxes and the following error message is seen:

-----------------------------------------------------------
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'FeatID=<FONT face=Arial><IMG onmouseup="FP_swapImg(0'.
/project/DeleteFeatures_verify.asp, line 88

Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1)

Page:
POST 1560 bytes to /project/DeleteFeatures_verify.asp

POST Data:
Delete=7&Delete=%3CFONT+face%3DArial%3E%3CIMG+onmouseup%3D%22FP_swapImg%280%2C0%2C%2F*id*%2F%27img1%27%2C%2F*url*%2F%27Delete_1A.jpg%27%29%22+onmousedown%3D%22FP_swapImg%281%2C0%2C%2F*id*%2F%27img1%27 . . .
-----------------------------------------------------------

The ASP script(DeleteFeatures_verify.asp) is as below:
<%
Dim connectDel, recstDel
Dim intSno, intDelSno, a, SQL

intSno=Request("Delete")

Set connectDel = Server.CreateObject("ADODB.Connection")
connectDel.Open("DRIVER={Microsoft Access Driver (*.mdb)};" _
& "DBQ=" & Server.MapPath("Estimation.mdb"))

a=split(intSno,",")

Dim iLoop
for iLoop=LBound(a) to UBound(a)
intDelSno=Trim(a(iLoop))
SQL = "Delete from FuncFeat where FeatID=" & intDelSno & ""
Set recstDel = connectDel.Execute(SQL)
'connectDel.Execute "Delete from FuncFeat where FeatID=" & intDelSno & ""
next
Response.Redirect "DeleteFeatures.asp"
connectDel.Close
Set recstDel = Nothing
Set connectDel=nothing
%>
-----------------------------------------------------------

The above ASP script(DeleteFeatures_verify.asp) is called when the DeleteFeatures.asp is executed.

The DeleteFeatures.asp is as below:
<form method="POST" action="DeleteFeatures_verify.asp" onSubmit="">
<p><![if !vml]><span style='mso-ignore:vglayout;
position:absolute;z-index:1;left:30px;top:189px;width:923px;height:2px'><img
width=923 height=2 src="DeleteFeatures59_files/image003.gif" v:shapes="_x0000_s1035"></span><![endif]></p>

<%
Dim connnectDel
Dim recstDel
Dim SQL
'Dim intCounter,intMod,rcolor
%>

<%
Set connectDel = Server.CreateObject("ADODB.Connection")
connectDel.Open("DRIVER={Microsoft Access Driver (*.mdb)};" _
& "DBQ=" & Server.MapPath("Estimation.mdb"))

Set recstDel = Server.CreateObject("ADODB.recordset")

recstDel.Open "SELECT FuncFeat.FeatID,FuncFeat.FeatName,FuncFeat.ProjID from FuncFeat order by FuncFeat.ProjID", connectDel

'SQL = "SELECT FuncFeat.FeatID, FuncFeat.FeatName, FuncFeat.ProjID from FuncFeat order by FuncFeat.ProjID"
'Set recstDel = connectDel.Execute(SQL)
%>

<p><![if !vml]><span style='mso-ignore:vglayout;
position:absolute;z-index:1;left:31px;top:512px;width:923px;height:2px'><img
width=923 height=2 src="DeleteFeatures59_files/image003.gif" v:shapes="_x0000_s1048"></span><![endif]></p>
<p><span style="vertical-align: middle"><b>
<font face="Arial" color="#333399">    </font></b></span></p>
<p><span style="vertical-align: middle"><b>
<font face="Arial" color="#333399">   </font></b></span></p>

<table width="674" border="1" cellpadding="0" cellspacing="0" height="43" style="border-collapse: collapse">
<tr>
<th align="left" bgcolor=#0099CC width="70" height="13" bordercolor="#000000" bordercolorlight="#006699" bordercolordark="#006699">
<b><font color="#000080" face="Arial">
<input type="checkbox" name="C1" value="ON" style="font-weight: 700"></font><font color="#000080" face="Arial">Check
</font> </b>
<th align="left" bgcolor=#0099CC width="290" height="13" bordercolor="#000000" bordercolorlight="#006699" bordercolordark="#006699">
<p align="center"><font face="Arial" size="2" color="#000080">
Functional </font><font color="#000080"><b>
<font SIZE="2" FACE="Arial">
Feature </font><font face="Arial"> </font></b></font><th align="left" bgcolor=#0099CC width="296" height="13" bordercolor="#000000" bordercolorlight="#006699" bordercolordark="#006699">
<p align="center"><font face="Arial" size="2" color="#000080">Project
ID</font></th>
</tr>

<%do until recstDel.EOF%>

<tr>
<td align="left" width="70" height="20">
<font SIZE="2" FACE="arial" COLOR="BLACK">
<width=35%>

 <Input Type=CheckBox Name=Delete Value=<%=recstDel("FeatID")%>><td align="left" width="290" height="20">
<font SIZE="2" FACE="arial" COLOR="BLACK">
<%Response.Write (recstDel("FeatName"))%> </font>
<td align="center" width="296" height="20">
<font SIZE="2" FACE="arial" COLOR="BLACK">
<%Response.Write(recstDel("ProjID"))%> </font>
<%recstDel.MoveNext%>

</tr>
<%loop
Set recstDel=nothing
Set connectDel=nothing
%>
</table>

<div style="position: absolute; width: 128px; height: 26px; z-index: 2; left: 767px; top: 380px" id="Cancel">
 </div>
<p>
<button name="Delete" type="submit" class="UserBackground" style="border-style:solid; border-width:0px; width: 135px; height: 26px; padding: 0" tabindex="15" value="Delete">
<font face="Arial">
<img border="0" id="img1" src="Delete_1C.jpg" height="25" width="125" alt="DELETE" fp-style="fp-btn: Embossed Rectangle 1; fp-font: Arial; fp-font-size: 12; fp-font-color-normal: #333399; fp-font-color-hover: #0000FF; fp-font-color-press: #333399" fp-title="DELETE" align="left" onmouseover="FP_swapImg(1,0,/*id*/'img1',/*url*/'Delete_1A.jpg')" onmouseout="FP_swapImg(0,0,/*id*/'img1',/*url*/'Delete_1C.jpg')" onmousedown="FP_swapImg(1,0,/*id*/'img1',/*url*/'Delete_1B.jpg')" onmouseup="FP_swapImg(0,0,/*id*/'img1',/*url*/'Delete_1A.jpg')"></font>
</button>       
<button name="Clear" style="border-style:solid; border-width:0px; width: 135px; height: 26px" class="UserBackground" type="reset" tabindex="16">
<img border="0" id="img12" src="Clear_1C.jpg" height="25" width="125" alt="CLEAR" onmouseover="FP_swapImg(1,0,/*id*/'img12',/*url*/'Clear_1A.jpg')" onmouseout="FP_swapImg(0,0,/*id*/'img12',/*url*/'Clear_1C.jpg')" onmousedown="FP_swapImg(1,0,/*id*/'img12',/*url*/'Clear_1B.jpg')" onmouseup="FP_swapImg(0,0,/*id*/'img12',/*url*/'Clear_1A.jpg')" fp-style="fp-btn: Embossed Rectangle 1; fp-font: Arial; fp-font-size: 12; fp-font-color-normal: #333399; fp-font-color-hover: #0000FF; fp-font-color-press: #333399; fp-orig: 0" fp-title="CLEAR">
</button>
<font face="Arial">
               
<a href="Functional%20Features%20submenu.htm">
<img border="0" id="img4" src="Cancel_1C.jpg" height="25" width="125" alt="CANCEL" onmouseover="FP_swapImg(1,0,/*id*/'img4',/*url*/'Cancel_1A.jpg')" onmouseout="FP_swapImg(0,0,/*id*/'img4',/*url*/'Cancel_1C.jpg')" onmousedown="FP_swapImg(1,0,/*id*/'img4',/*url*/'Cancel_1B.jpg')" onmouseup="FP_swapImg(0,0,/*id*/'img4',/*url*/'Cancel_1A.jpg')" fp-style="fp-btn: Embossed Rectangle 1; fp-font: Arial; fp-font-size: 12; fp-font-color-normal: #333399; fp-font-color-hover: #0000FF; fp-font-color-press: #333399; fp-orig: 0" fp-title="CANCEL"></a></font></p>
<p> </p>
<div style="position: absolute; left: 426px; top: 565px; width: 157px; height: 247px; z-index: 13" id="Feat6_10">
<div style="position: absolute; width: 125px; height: 25px; z-index: 16; left: 24px; top: 195px" id="Feat10">
<span style="vertical-align: middle"><b>
<font face="Arial" color="#333399">        Feature 10</font></b></span><b><font face="Arial" color="#333399">:</font></b></div>
<div style="position: absolute; width: 125px; height: 25px; z-index: 17; left: 23px; top: 150px" id="Feat9">
<span style="vertical-align: middle"><b>
<font face="Arial" color="#333399">        Feature 9</font></b></span><b><font face="Arial" color="#333399">:</font></b></div>
<div style="position: absolute; width: 125px; height: 25px; z-index: 18; left: 23px; top: 107px" id="Feat8">
<span style="vertical-align: middle"><b>
<font face="Arial" color="#333399">        Feature 8</font></b></span><b><font face="Arial" color="#333399">:</font></b></div>
<div style="position: absolute; width: 125px; height: 25px; z-index: 19; left: 23px; top: 66px" id="Feat7">
<span style="vertical-align: middle"><b>
<font face="Arial" color="#333399">        Feature 7</font></b></span><b><font face="Arial" color="#333399">:</font></b></div>
<div style="position: absolute; width: 125px; height: 25px; z-index: 20; left: 23px; top: 21px" id="Feat6">
<span style="vertical-align: middle"><b>
<font face="Arial" color="#333399">        Feature 6</font></b></span><b><font face="Arial" color="#333399">:</font></b></div>
<p> </div>
<div style="position: absolute; left: -37px; top: 539px; width: 166px; height: 273px; z-index: 11" id="Feat1_5">
<div style="position: absolute; width: 125px; height: 25px; z-index: 13; left: 40px; top: 222px" id="Feat5">
<span style="vertical-align: middle"><b>
<font face="Arial" color="#333399">        Feature 5</font></b></span><b><font face="Arial" color="#333399">:</font></b></div>
<div style="position: absolute; width: 125px; height: 25px; z-index: 14; left: 41px; top: 176px" id="Feat4">
<span style="vertical-align: middle"><b>
<font face="Arial" color="#333399">        Feature 4</font></b></span><b><font face="Arial" color="#333399">:</font></b></div>
<div style="position: absolute; width: 125px; height: 25px; z-index: 15; left: 41px; top: 133px" id="Feat3">
<span style="vertical-align: middle"><b>
<font face="Arial" color="#333399">        Feature 3</font></b></span><b><font face="Arial" color="#333399">:</font></b></div>
<div style="position: absolute; width: 125px; height: 25px; z-index: 16; left: 41px; top: 91px" id="Feat2">
<span style="vertical-align: middle"><b>
<font face="Arial" color="#333399">        Feature 2</font></b></span><b><font face="Arial" color="#333399">:</font></b></div>
<div style="position: absolute; width: 125px; height: 25px; z-index: 17; left: 41px; top: 46px" id="Feat1">
<span style="vertical-align: middle"><b>
<font face="Arial" color="#333399">        Feature 1</font></b></span><b><font face="Arial" color="#333399">:</font></b></div>
<p> </div>

</form>
-----------------------------------------------------------
Thank you

Sorry for this long script.

johnwm · Feb 18, 2006

The clue is in the error message.

query expression 'FeatID=<FONT face=Arial><IMG onmouseup="FP_swapImg(0'.
/project/DeleteFeatures_verify.asp, line 88

Check the datatype of your FeatID field. If it's numeric then you need to pass it a number not a string. If it's a string then you need to pass it a properly delimited string. If your string contains quote marks then you will need to replace them with double quotes, otherwise they will be taken as the closing delimiter.

Check out faq222-2244 especially paragraph 14 to get guidelines on code posting, as it's difficult and tedious for us to wade through the MS generated tags to actually look at the problem

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

Beng79 · Feb 18, 2006

This is the main part of the script that has error when running:
<%
Dim connnectDel
Dim recstDel
Dim SQL
%>

<%
Set connectDel = Server.CreateObject("ADODB.Connection")
connectDel.Open("DRIVER={Microsoft Access Driver (*.mdb)};" _
& "DBQ=" & Server.MapPath("Estimation.mdb"))

Set recstDel = Server.CreateObject("ADODB.recordset")

recstDel.Open "SELECT FuncFeat.FeatID,FuncFeat.FeatName,FuncFeat.ProjID from FuncFeat order by FuncFeat.ProjID", connectDel

'SQL = "SELECT FuncFeat.FeatID, FuncFeat.FeatName, FuncFeat.ProjID from FuncFeat order by FuncFeat.ProjID"
'Set recstDel = connectDel.Execute(SQL)
%>

The error is seen in the inital posting of the thread.

My datatype for FeatID is Autonumber. By the way, how do I declare the variable as a number and not a string?

Thanks

johnwm · Feb 19, 2006

The error shown in your first post relates to a WHERE clause in a query expression, and you are passing the wrong type of parameter. <Q>Where FeatID=.....</Q> is expecting a numeric value and your construction is sending an undelimited string. Also you don't usually use a recordset for an Execute statement that doesn't return a recordset, as shown in my commented line below.
The standard method for resolving these problems with constructed SQL queries is to temporarily put in a Response.Write statement and don't execute the SQL string, like this:

Code:

        intDelSno=Trim(a(iLoop))
        SQL = "Delete from FuncFeat where FeatID=" & intDelSno & ""
        'connectDel.Execute(SQL)
        Response.Write SQL

When you see the results from this you can then see where the problem lies. If it isn't obvious to you then post the results of the Response.Write back here.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

Beng79 · Feb 19, 2006

Hi johnwm,

I got this result when I used Response.Write ->

Delete from FuncFeat where FeatID=34Delete from FuncFeat where FeatID=

The Delete sql statement is being executed but no delete action was done when I refer back to the script again.Why?

I am sorry that you need to explain further as I am still a novice in ASP scripts.

Tarwn · Feb 19, 2006

Two things:
1) Double check that you have quotes around your value in the checkboxes. If you don't have quotes it is possible that it could get confused.

2) By naming your submit button Delete (same name as checkboxes) whenthe user clicks the submit button you will get an additional value of "Delete" for Request.Form("Delete") because the submit button will pass it's value when itis clicked (this is useful when you have multiple submit buttons to figure out which one was clicked).

My main suggestion would be to go back and rewrite te HTML you have in that page. Frontpage generally makes a mess of things and by cleaning it up you would be able to troubleshoot a great deal easier. For instance:

Code:

    <p><span style="vertical-align: middle"><b>
    <font face="Arial" color="#333399">&nbsp;&nbsp;&nbsp; </font></b></span></p>
    <p><span style="vertical-align: middle"><b>
    <font face="Arial" color="#333399">&nbsp;&nbsp; </font></b></span></p>

Beng79 · Feb 20, 2006

Hi Tarwan,

For 1),the value is added to the checkbox -> <%=recstDel("FeatID")%>

For 2), I have name the submit button as "Delete" which is the same as the checkbox.

As for the HTML, I have clean it up.

But sad to say, the problem still exist -> the record is not deleted! = (

The code is now like this:

<%
Dim connectDel, recstDel
Dim intSno, intDelSno, a, SQL

intSno=Request("Delete")

Set connectDel = Server.CreateObject("ADODB.Connection")
connectDel.Open("DRIVER={Microsoft Access Driver (*.mdb)};" _
& "DBQ=" & Server.MapPath("Estimation.mdb"))

a=split(intSno,",")

Dim iLoop
for iLoop=LBound(a) to UBound(a)
intDelSno=Trim(a(iLoop))
SQL = "Delete from FuncFeat where FeatID=" & intDelSno & ""
Response.Write SQL
Next
Response.Redirect "Delete Features.asp"

connectDel.Close
Set recstDel = Nothing
Set connectDel=nothing
%>

Could it be the database problem?

To be honest, the script was modified from this url:

http://www.planet-source-code.com/v...654&lngWId=4&txtForceRefresh=2202006948733213

emozley · Feb 20, 2006

If you type

Response.Write("intSno value:") & intSno

immediately after

intSno=Request("Delete")

what does it get. It looks like you are trying to execute a query with no criteria for the WHERE part.

Tarwn · Feb 20, 2006

After reading through your error message I found the actual error in this case, and it wasn't as I originally suspected. It seems you have several things named "Delete" in your form. The key here is that one of them is posting part of your html instead of a normal value, usually indicating a quote out of place or something similar. Unfortunatly since the frontpage code is so hard to follow I didn't notice the error on my last time through the code.
You have two button tags in your code. Unfortunatly IE6 decides to send the content included between the <button></button> tags instead of sending the content in the value attribute. Net result is that you end up with a whole lot of HTML posted to the next page instead of whatever value is assigned to the value attribute of that button.

There are two or more bugs in IE6 related to the button tag, both of which were posted a couple years ago and it appears that at least one of them hasn't been resolved. I haven't bothered to test the other issue because I'm afraid that one probably wasn't resolved either. Just remember that IE isn't really compliant with any of the HTML standards, it's only nearly compliant.

My suggestion to resolve the issue would be to get rid of the buttons and use a little javascript on the images you currently are using inside the button tags. You should be able to use the onClick event to call a page reload (reset) or form submit. I would also suggest not calling everything bythe same name in the future, as that makes it harder to determine what is broken when something doesn't work correctly.

-T

Beng79 · Feb 21, 2006

Sorry, but I intend change to something simpler.

Say, delete one record at a time from the database instead of doing multiple delete with checkbox.

But, how can I change my scripts to what I wanted?

emozley · Feb 21, 2006

If list.asp was your list of all things that you may or may not want to delete you could list them all and have a delete link next to them. Each link would have a unique ID in which woul dbe passed to your delete script. Once the item was removed it would redirect back to your main list.

Hopefully this is the sort of thing you mean...

list.asp

rs.Open "SELECT * from FuncFeat", DB
Do While Not rs.EOF
Response.Write(rs("FeatName")) & "<a href='delete.asp?FeatID=" & rs("FeatID") & "'>Delete</a><br>")
rs.MoveNext
Loop

delete.asp

FeatID=Request.QueryString("FeatID")
rs.Open "DELETE * FROM FuncFeat WHERE FeatID=" & FeatID, DB
Set rs=Nothing
Set DB=Nothing
Response.Redirec("list.asp")

Beng79 · Feb 21, 2006

The delete link meaning the user can only delete one record at a time from the list that is displayed?

emozley · Feb 21, 2006

That is correct - I hope I did not misunderstand:

"Say, delete one record at a time from the database..."

cheers

Beng79 · Feb 21, 2006

Yup, my intention is that. I will try it out and feedback whether it works.

Thank you.

Beng79 · Feb 21, 2006

Just wanted to know, can I used it for modifying of records one at a time from the database too?

emozley · Feb 22, 2006

Indeed, just make it point to a different script:

rs.Open "SELECT * from FuncFeat", DB
Do While Not rs.EOF
Response.Write(rs("FeatName")) & "<a href='editform.asp?FeatID=" & rs("FeatID") & "'>Edit</a><br>")
rs.MoveNext
Loop

Then on your editform.asp execute a SQL command based on the value you pass across in FeatID

<%

rs.Open "SELECT FieldA, FieldB, FieldC FROM FeatName WHERE FeatID=" & Request.QueryString("FeatID")

<form method="post" action="edit.asp">
<input type="hidden" name="FeatID" value="<% = rs("FeatID") %>">

Field A <input type="text" name="FieldA" value="<% = rs("FieldA") %>"><br>

Field B <input type="text" name="FieldB" value="<% = rs("FieldB") %>"><br>

Field C <input type="text" name="FieldC" value="<% = rs("FieldC") %>"><br>

<input type="submit" value="Update Record">
</form>

rs.Close
Set rs=Nothing
Set DB=Nothing

%>

Beng79 · Feb 22, 2006

I try to run the delete script, but have this error:

Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/project/Delete Features.asp, line 119, column 110
Response.Write(recstDel("FeatName")) & "<a href='delete.asp?FeatID=" & recstDel("FeatID") & "'>Delete</a><br>")

What should I do to create a link next to the records listed in html code or asp script?

emozley · Feb 22, 2006

Sorry my mistake take off the bracket at the end eg:

Response.Write(recstDel("FeatName")) & "<a href='delete.asp?FeatID=" & recstDel("FeatID") & "'>Delete</a><br>

Beng79 · Feb 23, 2006

Got this error when try to run the below script you gave:
______________________________________________________
FeatID=Request.QueryString("FeatID")
rs.Open "DELETE * FROM FuncFeat WHERE FeatID=" & FeatID, DB
Set rs=Nothing
Set DB=Nothing
Response.Redirec("list.asp")
_________________________________________________________
Error Type:
Microsoft VBScript runtime (0x800A01A8)
Object required: "

emozley · Feb 23, 2006

Response.Redirec("list.asp")

should read

Response.Redirect("list.asp")

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Error message in deleting recods from database using with checkboxes 1

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Technical User

Programmer

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Similar threads

Log in

Part and Inventory Search

Sponsor