Insert Records before Delete

[pvsmith5]

I'm trying to insert a record having only one field, VOTER_ID, into a Holding Table before the record is actually deleted from a main table. I just can't get this code to work. (I'm getting a Syntax error from ASP)Can anybody eyeball it and see where the error is?
------
set dbConnection = server.CreateObject ("ADODB.Connection")
dbConnection.ConnectionString = strConnection
dbConnection.Open
Call ReportError

for i=1 to request.form("mdelete").count
ind = CInt(request.form("mdelete")(i))
strSQL = "Insert INTO HoldingTable(VOTER_ID) from " & strTableName & " where " & AddWrappers(strKeyField) & "=" & gstrQuote & Replace(request.form("mdelete1")(ind), "'", "''") & gstrQuote
dbConnection.Execute strSQL


strSQL="delete from " & strTableName & _
" where " & AddWrappers(strKeyField) & "=" & gstrQuote & Replace(request.form("mdelete1")(ind), "'", "''") & gstrQuote
if strKeyField2<>"" then _
strSQL = strSQL & " and " & AddWrappers(strKeyField2) & "=" & gstrQuote2 & Replace(request.form("mdelete2")(ind), "'", "''") & gstrQuote2
if strKeyField3<>"" then _
strSQL = strSQL & " and " & AddWrappers(strKeyField3) & "=" & gstrQuote3 & Replace(request.form("mdelete3")(ind), "'", "''") & gstrQuote3

 

[Sheco]

Which line does your error appear on and is it possible that it is happening in one of the functions called but not listed in the code. ie: ReportError() or AddWrappers()

The problem with making such compact code is that it is a b*tch to read. Maybe exploding it out some will help....

Oh, and you've got a For without a Next that I just noticed when I exploded it up some.

Here is what I did to it. I didn't fix anything just made it easier to read.

set dbConnection = Server.CreateObject("ADODB.Connection")
dbConnection.ConnectionString = strConnection
dbConnection.Open

Call ReportError

Dim iMax, md1, md2, md3
iMax = request.form("mdelete").count

for i = 1 to iMax
		md1 = request.form("mdelete1")(ind)
		md2 = request.form("mdelete2")(ind)
		md3 = request.form("mdelete3")(ind)
		
    ind = CInt(request.form("mdelete")(i))
        
    strSQL = "Insert INTO HoldingTable(VOTER_ID) from " _
					 & strTableName _
					 & " where " _
					 & AddWrappers(strKeyField) _
					 & "=" _
					 & gstrQuote _
					 & Replace(md1, "'", "''") _
					 & gstrQuote 
					 
    dbConnection.Execute strSQL
        

		strSQL= "delete from " _
					& strTableName _ 
					& " where " _
					& AddWrappers(strKeyField) _
					& "=" _
					& gstrQuote _
					& Replace(md1, "'", "''") _
					& gstrQuote 
        
    if (strKeyField2 <> "") then 
			strSQL = strSQL _
						 & " and " _
						 & AddWrappers(strKeyField2) _
						 & "=" _
						 & gstrQuote2 _
						 & Replace(md2, "'", "''") _
						 & gstrQuote2 
		end if
				
    if (strKeyField3 <> "") then 
			strSQL = strSQL _
						 & " and " _
						 & AddWrappers(strKeyField3) _
						 & "=" _
						 & gstrQuote3 _
						 & Replace(md3, "'", "''") _
						 & gstrQuote3 
		end if
Next

 

[pvsmith5]

Thanks for cleaning up the code to the average person can read it.
I'm sure the error comes with the INSERT INTO statement. I didn't get the syntax error until I started trying to tweak around with the Insert into.

 

[Sheco]

Alright well syntax error is usually something like misplaced quotes or a line continuation underscore character that is bumped up against the last char in the line.

Also your INSERT statement has a call to AddWrapper() so it might be inside that function.

 

[pvsmith5]

I've worked another three hours or so, and I'm closer. The Delete code works. The Insert code still doesn't work. I want to add just one record with one field in it (VOTER_ID). So what do I need to do?

strSQL = "INSERT INTO HoldingTable (VOTER_ID) "& _
"VALUES ('') " _
& " where " _
& AddWrappers(strKeyField) _
& "=" & gstrQuote _
& Replace(request.form("mdelete1") _
& (ind), "'", "''") _
& gstrQuote

dbConnection.Execute strSQL


strSQL="delete from " & strTableName & _
" where " _
& AddWrappers(strKeyField) _
& "=" & gstrQuote _
& Replace(request.form("mdelete1") _
& (ind), "'", "''") _
& gstrQuote

 

[Sheco]

My guess is that the quote character is acting like a string delimitter is some way.

Try using this and see if it doesn't help.

strSQL = "INSERT INTO HoldingTable (VOTER_ID) " _
       & "VALUES ('') " _ 
       & "WHERE " _ 
       & AddWrappers(strKeyField) _
       & "=" _
       & gstrQuote  _ 
       & Replace(request.form("mdelete1") & (ind), "'", "`") _
       & gstrQuote 

Response.Write "<BR>My SQL looks like this: " & strSQL & "<BR>"        
dbConnection.Execute strSQL

 

[Sheco]

Well, there is also the problem that the value that you want to insert is an empty string. ie: VALUES ('')

 

[teamBadMonkey]

If I ever have a bad SQL statement (errors on dbConnection.Execute strSQL line), I always add the debug line that Sheco showed - that will usually show you what's wrong right away.

If you still don't see it, copy the query, go into Query Analyzer, and paste it.

 

[pvsmith5]

Good suggestion. I went back to Access-land and decided to trash everything and start over. This is close but still throws a syntax error: Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/Bartle4/ALL_REGISTERED_VOTERS_MAIN_TABLE_list.asp, line 66, column 78
& "WHERE (((ALL_REGISTERED_VOTERS_MAIN_TABLE.VOTER_ID)=Replace(request.form ("VOTER_ID"))
------------------------------------


strSQL="INSERT INTO HoldingTable ( VOTER_ID ) " _
& "SELECT ALL_REGISTERED_VOTERS_MAIN_TABLE.VOTER_ID " _
& "FROM ALL_REGISTERED_VOTERS_MAIN_TABLE " _
& "WHERE (((ALL_REGISTERED_VOTERS_MAIN_TABLE.VOTER_ID)=Replace(request.form ("VOTER_ID"))


I think this is real close. What is the little detail I'm missing?

 

[Sheco]

Your quotation marks are messed up on that line that made the error... the one with the WHERE clause.