I am trying to retrieve info from one table and then put it into another table...I can retrieve the info just fine I'm just having trouble putting it into the new table...

code is below.....

deaID=request("deaID")
region=request("region")

sql = "SELECT RegionCodes.zipcode FROM RegionCodes WHERE RegionName='" & region & "'"

'debugging code
Response.Write "DEBUG: SQL is " & sql & "<BR>"


set getregioncodes = conn.execute(sql)

'Response.Write DebugRs(getregioncodes)
If not getregioncodes.eof then

        Do while not getregioncodes.eof
            For each zipcode in getregioncodes.zipcode '<----------REPLACE WITH CORRECT NAME OF CHECKBOXES
              sql2 = "INSERT INTO NewDealercodes (deaID, zipcode) Values ('"&deaID&"','"&zipcode&"')"

            Next

            getregioncodes.MoveNext

            loop

            response.write "SQL is " & sql2 & "<BR>"
            set addzipcodes= conn.execute(sql2)


end if
__________________________________________________ ______

I know it has to do with my "zipcode" in the FOR statement. But I don't know how to define it from the recordset.

which database are you using? If you are using SQL server then you can use stored procedure to do this

nevermind solved it myself

New problem...it is only inserting 1 value into the new table...what am I doing wrong?


here is the code again

deaID=request("deaID")
region=request("region")

sql = "SELECT DISTINCT RegionCodes.zipcode FROM RegionCodes WHERE RegionName='" & region & "'"

'debugging code
Response.Write "DEBUG: SQL is " & sql & "<BR>"


set getregioncodes = conn.execute(sql)

'Response.Write DebugRs(getregioncodes)


        Do while not getregioncodes.eof

              sql2 = "INSERT INTO NewDealercodes (deaID, dealercode) Values ('"&deaID&"','"&getregioncodes("zipcode")&"')"

            getregioncodes.MoveNext

            loop

            response.write "SQL is " & sql2 & "<BR>"
            set addzipcodes= conn.execute(sql2)

You are creating a new insert query each time you loop, but as you execute the query outside the loop, only the last query built is getting used.

Chris