syntax question

[MeLoDi]

hi there i have a table which i want to copy all the records from one table to another, (many different serialno. with the same product name) and at the same time delete those existing records in the table i did the following:

sql3 = "SELECT * FROM Serial WHERE PurchaseInvNo = '"&RS("PurchaseInvNo")&"'"
Set RS4 = objConn.Execute(sql3) 
 
While NOT RS4.EOF 
Dim insertQuery
insertQuery = "INSERT INTO PurgedSerial VALUES "&_
"('"&RS4("SerialNo")&"', '"&RS4("PartNo")&"', '"&RS4("SalesInvNo")&"', '"&RS4("PurchaseInvNo")&"');"
 
objConn.Execute(insertQuery) 	
 
Dim deleteSQL3
deleteSQL3 = "Delete From Serial Where PurchaseInvNo='"&RS4("PurchaseInvNo")&"'"
	 'Response.Write(deleteSQL)
	 objConn.Execute(deleteSQL3) 	 
 
RS4.MoveNext
Wend

but it seems that the insert query only inserts one record (it doesnt seem to move next)while all deleted query deletes all the records that are stated

[jimfraser]

Your insert query isn't specifying the field names.

[MeLoDi]

nope that isnt the reason.. its got something to do with my looping haha i think

[RhythmAddict]

If you think it has somethin to do with your looping why don't you try something like...
although, you should definitely fix your insert statement.

sql3 = "SELECT * FROM Serial WHERE PurchaseInvNo = '"&RS("PurchaseInvNo")&"'"
Set RS4 = objConn.Execute(sql3) 
 
While NOT RS4.EOF 
Dim insertQuery
insertQuery = "INSERT INTO PurgedSerial VALUES "&_
"('"&RS4("SerialNo")&"', '"&RS4("PartNo")&"', '"&RS4("SalesInvNo")&"', '"&RS4("PurchaseInvNo")&"');"
 
'objConn.Execute(insertQuery) 	
Response.write InsertQuery  & "<BR><BR>"
 
Dim deleteSQL3
deleteSQL3 = "Delete From Serial Where PurchaseInvNo='"&RS4("PurchaseInvNo")&"'"
	 'Response.Write(deleteSQL)
	 objConn.Execute(deleteSQL3) 	 
 
RS4.MoveNext
Wend

[MeLoDi]

i tried that but it doesnt work properly haha

only one record gets inserted and all the records in the other table get deleted. Its supposed to insert all the records and delete as well

[RhythmAddict]

My code above isn't support to insert anything, it's supposed to print your Insert statement to the screen...ie, if you only see one insert statement, there is NO WAY anything more than one row is gettin inserted.

[jimfraser]

Oh - it's pretty obvious.

If your SELECT query is returning multiple records, then the first time that you run your DELETE query, they are all being deleted (look at the queries the WHERE conditions are the same).

Change your delete query to:

deleteSQL3 = "DELETE FROM Serial WHERE SerialNo='" & RS4("SerialNo") & "' AND PartNo='" & RS4("PartNo") & "' AND SalesInvNo='" & RS4("SalesInvNo") & "' AND PurchaseInvNo='" & RS4("PurchaseInvNo") & "'"

[MeLoDi]

o ok thanks ! its working fine now