ADODB.Recordset keeping rows after close.. Classic ASP

Question

I am getting old records from record set object even after I have closed the record set object.

Below is a code snippet from my file -

SQL = "Select col1, col2 from [Sheet1$]"

Set ExcelConnection = Server.createobject("ADODB.Connection")
ExcelConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ExcelFile & ";Extended Properties='Excel 5.0;HDR=Yes;IMEX=1';"
SET RS = Server.CreateObject("ADODB.Recordset")
RS.Open SQL, ExcelConnection

strSQL = "insert into tbl1 (col1, col2) values ("

IF NOT RS.EOF THEN
    WHILE NOT RS.eof
        FOR EACH Field IN RS.Fields
            values = values & "'" & Field.value & "',"
        NEXT        
        strSQL = strSQL & trim(left(values,Len(values)-1)) & ")"
        objConn.Execute(strSQL)
        RS.movenext
    WEND
END IF       

RS.Close

Now I want read from another sheet and insert to a different table -

SQL = "Select col1, col2 from [Sheet2$]"

Set ExcelConnection = Server.createobject("ADODB.Connection")
ExcelConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ExcelFile & ";Extended Properties='Excel 5.0;HDR=Yes;IMEX=1';"    
RS.Open SQL, ExcelConnection

strSQL = "insert into tbl1 (col1, col2) values ("

IF NOT RS.EOF THEN
    WHILE NOT RS.eof
        FOR EACH Field IN RS.Fields
            values = values & "'" & Field.value & "',"
        NEXT        
        strSQL = strSQL & trim(left(values,Len(values)-1)) & ")"
        Response.write(strSQL)      
        objConn.Execute(strSQL) <-- Error here.
        RS.movenext
    WEND
END IF       

RS.Close

Below is the error I am getting -

insert into tbl1(col1, col2) values ('1','2','3','4') 

Microsoft OLE DB Provider for SQL Server error '80040e14'

There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement. 

I have no idea why the recordset is keeping the old values... any idea?

Thanks in advance.

Answer 1

Looks like you're not clearing the value of "value". Just before you close your recordsets, use:

value = ""

... to make sure it's empty. Better still, use different variables between different code blocks.