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!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

CheckBox Deletion From Access DB

Status
Not open for further replies.
marcus1

marcus1

MIS
Feb 9, 2002
6
US
Having some problems getting the CheckBox Deletion from Access Database examples that I have seen in the Tek Tips Forum to work. I am getting a couple of different error messages depening on how I set up my variable types. I got this example from Ovatvvon from thread 333-73372. The cust_id key field in my CUSTHITS database was originally an autogenerating integer value, but during my troubleshooting, I have since changed it to a text field in the database (Thought that example was using a text based key field versus my integer based field). The error that I am getting is within the second ASP page when I'm attempting to do the deletion of the records that I have checked to be deleted. I get an:

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

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'cust_id ='.

/pages/delblock.asp, line 21

error in my second page when the deleteRecord variable is not enclosed in quotes and when I change line 21 to look like the Forum example:

strSQL = "Delete * from custhits where ((cust_id) =" & deleteRecord & ");"

I get an error that says:

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

[Microsoft][ODBC Microsoft Access Driver] Extra ) in query expression '((cust_id) =)'.

/pages/delblock.asp, line 21


Here's my code:

First ASP page*******************************************

<HTML>
<BODY>
<form name=&quot;FormName&quot; method=&quot;post&quot; action=&quot;delblock.asp&quot;>
<input type=&quot;button&quot;><input type=&quot;submit&quot; name=&quot;submitButtonName&quot; value=&quot;Delete&quot;>
</form>
<p>
<%
Dim strSQL
Dim begDate
Dim endDate
Dim reccount
Dim passchecks

'Open a connection to our database
Dim objConn
Set objConn = Server.CreateObject(&quot;ADODB.Connection&quot;)
objConn.ConnectionString=&quot;DSN=webhits&quot;
objConn.open

' Create our SQL statement variable

strSQL = &quot;SELECT * FROM CUSTHITS Order by ins_date,ins_time&quot;
reccount = 0
' Create a recordset to hold the records as they are read in
Dim objRS
Set objRS=Server.CreateObject(&quot;ADODB.Recordset&quot;)
objRS.Open strSQL,objConn

' Display the Contents of the Custhits Database

Do While Not objRS.EOF
Response.Write &quot;<A HREF='Preview.asp'>&quot;
Response.Write &quot;<input type='checkbox' name='deleteRecord' value=&quot; & objRS(&quot;cust_id&quot;) & &quot;>Customer Record: &quot; & objRS(&quot;cust_id&quot;) & &quot; &quot; & objRS(&quot;first_name&quot;) & &quot; &quot; & objRS (&quot;last_name&quot;) & &quot; &quot; & objRS (&quot;address&quot;) & objRS (&quot;address&quot;) & &quot;<BR>&quot;
Response.Write &quot;</A>&quot;
reccount = reccount + 1
' Move to the next row in the CustHists table
objRS.MoveNext
Loop
Response.Write&quot;End of Search Results.... &quot; & reccount & &quot; Records Retrieved&quot; & &quot;<BR>&quot; & &quot;<BR>&quot;
Response.Write &quot;<A HREF='dtsrch.asp' >.&quot;
Response.Write &quot;Return to Web Customer Search Page.&quot;
Response.Write &quot;</A>&quot;
'Clean up the ADO objects
objRS.Close
Set ObjRS = Nothing

objConn.Close
Set objConn = Nothing

%>
</p>
</BODY>
</HTML>

Second ASP Page *******************************************
<%@ Language=VBScript %>
<% Option Explicit %>
<HTML>
<meta name=&quot;site-config-URL&quot; content=&quot;&quot;>

<BODY>
<%
Dim deleteRecord
deleteRecord = request.form(&quot;deleteRecord&quot;)
Dim strSQL
Dim objRS
Dim objConn
'Open a connection to our database
Set objConn = Server.CreateObject(&quot;ADODB.Connection&quot;)
objConn.ConnectionString=&quot;DSN=webhits&quot;
objConn.open
' Create a recordset to hold the records as they are read in
strSQL = &quot;Delete * from custhits where cust_id =&quot; & deleteRecord & &quot; &quot;
Set objRS=Server.CreateObject(&quot;ADODB.Recordset&quot;)
objRS.Open strSQL,objConn
'Clean up the ADO objects and exit
Set ObjRS = Nothing
Set objConn = Nothing
Response.Write &quot;<A HREF = 'delsrch.asp'>&quot;
Response.Write &quot;Return to Web Deletion Page to Enter a New Customer ID Number.&quot;
Response.Write &quot;</A>&quot;
%>
</BODY>
<HTML>

I've spent a good deal of time troubleshooting and I just can't figure this one out. Any help that you can give me will be greatly appreciated.
 
Sort by date Sort by votes
Form element deleteRecord will have multiple values on checking multiple checkboxes(It may have one value if we check one box only.)

so when we retrive the values,
Code: 
Dim strSQL
strSQL=strSQL&&quot;Delete * from custhits where 1=1&quot;
if Request(&quot;deleteRecord&quot;).length <> 0 then
for i=1 to Request(&quot;deleteRecord&quot;).length

strSQL=strSQL&&quot; and cust_id=&quot;& Request(&quot;deleteRecord&quot;)(i) &&quot;&quot;

next
end if
Execute it here......

is the way to generate string and then execute it at one go.
Rushi Shroff Rushi@emqube.com
&quot;Life is beautiful.&quot;
 
Upvote 0 Downvote
Hey Rushi,

Thanks for the help, but still having problems. Put the code in that you suggested but the line:

if Request(&quot;deleteRecord&quot;).length <> 0 then

causes problems and it says that the object does not support this property or method. What exactly is this statement doing and why can't I find it in any of my ASP books? Is there a suitable substitute function?

Thanks Again,

Mark
 
Upvote 0 Downvote
marcus1,
copy this code and replace this line on your code.
All I did was add two single quotes. see if it solves your problem

strSQL = &quot;Delete * from custhits where cust_id ='&quot; & deleteRecord & &quot;' &quot;


 
Upvote 0 Downvote
Extremely sorry Marcus,
It is count

Request(&quot;deleteRecord&quot;).length <> 0 then

for i=1 to Request(&quot;deleteRecord&quot;).count

Sorry again..



Rushi Shroff Rushi@emqube.com
&quot;Life is beautiful.&quot;
 
Upvote 0 Downvote
Extremely sorry Marcus,
It is count

Request(&quot;deleteRecord&quot;).count <> 0 then

for i=1 to Request(&quot;deleteRecord&quot;).count

Sorry again..



Rushi Shroff Rushi@emqube.com
&quot;Life is beautiful.&quot;
 
Upvote 0 Downvote
Hey Guys,

Still not working even with both suggestions that each of you offered. I think the problem resting in the setting of the deleteRecord value in the first ASP script. As soon as I get into the second form I dimension the deleteRecord value and then set it equal to the Requestform value of deleteRecord from the first script. I put a response.write statement to display the value of deleteRecord that gets past to the second script and the value is always zero no matter how many checkboxes that I denote for deletion on the first ASP script. I think something is wrong with how the value is getting passed. Your thoughts????

Marcus
 
Upvote 0 Downvote
sorry because I tried both scripts and they seemed to work with minor changes.
 
Upvote 0 Downvote
Yes works like a champ now,

I was having a more fundamental problem in the first ASP script. It was a really rookie mistake (I'm a little red faced). I didn't include the ASP logic within the actual form on the first page, so when I passed the value of the checkboxes, it was always zero and thus the second page script would bomb because the deleteRecord didn't have any values in it (even though I had checked a bunch of boxes).

I would like to thank you and Rushi for all your help!!!

Cheers,

Mark
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
Start loop from a specified point
Replies
0
Views
340
penguinspeaks
penguinspeaks
JScannell
  • Locked
  • Question
SMTP emailing from localhost
Replies
1
Views
183
JScannell
JScannell
leifoet
  • Locked
  • Question
Classic ASP multiple record update with Access
Replies
7
Views
348
dhookom
dhookom
sdagger2
  • Locked
  • Question
Classic asp: Insert new record to access database from successful PayPal payment
Replies
2
Views
164
ChrisHirst
ChrisHirst
PSchubert
  • Locked
  • Question
Data in DB table cell not displaying in asp page table cell
Replies
4
Views
163
Ed Mozley
Ed Mozley

Part and Inventory Search

Sponsor

Back
Top