Copy checked records to table

chg:  strSQL="INSERT INTO newtable(item1) SELECT item1 FROM originaltable

to:  strSQL="INSERT INTO newtable(item1) SELECT originaltable.item1 FROM originaltable

Hello kylen1010 ~

I think the error results form the syntax of your SQL statement.
Could you post your entire SQL statement.

How is rs1 defined?

SQL statement for inserting data into a second table containing the same field names as original table would look like:

strSQL = "INSERT INTO tbl2 ( field1, field2, field3 )" & _
               " SELECT tbl1.field1, tbl1.field2, tbl1.field3" & _
               " FROM tbl1" & _
               " WHERE (((tbl1.field3)="yes"));"

Jacob

Hi, thank you for your responses!  Here is the code (the part you need) from the first page with the form (this part works just fine):

--------------------------------------------------
<table class=h3 border=1 id="cBoxes">
<form name="tobecalled" action="page2.asp" method="post">

<tr><td><b>Check to Select</td><td><b>First Name</td><td><b>Last Name</td><td><b>Home Phone</td><td><b>Cell</td><td><b>Email</td></tr>

<%
set rs1 = server.createobject("ADODB.Recordset")
rs1.open "select * from contactdata order by lastname ", Connection
%>

<% DO WHILE NOT rs1.EOF %>

<tr><td><center><input type="checkbox" name="<%=rs1("username")%>" value="<%=rs1("username")%>"><td><%=rs1("firstname")%></td><td><%=rs1("lastname")%></td><td><%=rs1("phone")%></td>
<td><%=rs1("cell")%></td><td><%=rs1("email")%></td>

<% rs1.MoveNext
loop
%>

<%
rs1.close
set rs1 = nothing
%>
-----------------------------------------

Here is the code for the second page (page2.asp) (i've been playing with this all day so this is what i have so far. I think im right on the tip of getting it right, but there is somethign little that is wrong.  when i load this page it does not come up with any errors, but no records are inserted into the table. that means that the WHERE statement is excluding everything - if that makes any sense):



---------------------------------------------
<%
set rs1 = server.createobject("ADODB.Recordset")
rs1.open "select * from callinglist", Connection
%>

<% DO WHILE NOT rs1.EOF %>

<%
Dim strSQL1, objConn1

 Set objConn1=Server.CreateObject("ADODB.Connection")
 objConn1.Open Connection
 
strSQL1="INSERT INTO callinglist(username, firstname, lastname, phone, cell, email) SELECT username, firstname, lastname, phone, cell, email FROM contactdata WHERE '" & request(rs1("username")) & "' = '" & rs1("username") & "'"

 objConn1.Execute(strSQL1)

 objConn1.Close

%>

<% rs1.MoveNext
loop
%>

<%
rs1.close
set rs1 = nothing
%>
--------------------------------------

Thanks!

Yeah, I think it's your WHERE statement too.

What is "request" in: request(rs1("username"))?   -  I don't understand this one

Also, I reference fields in recordsets: rs1!username  or rs1.Fields("username") - Try: request(rs1.Fields("username"))

Perhaps, explore the strSQL1 statement without the WHERE clause, then begin building the WHERE clause from the ground up.  I often find looking at pieces can yield clues.

 

thanks for the response.

the "request" is to get data from the form submitted from the previous page... is that not how that is done? it seems to work if it type "response.write request(field_from_form)".  I am "requesting" whatever rs1(username) is because i named the checkboxes in the form rs1(username).  I think the problem might be with the apostrophies and stuff - when do you use ' or " or '" &...        ?  Thanks!

also - when i take out the whole WHERE statement like you said - it works fine (it inserts everything into the new table (callinglist)

Kyle

Great! I get some of my best solutions in the woods.  Nearly always a good idea.

Glad you got it to run.

Best Regards, Jacob

Closed, 255 points refunded.
Vee_Mod
Community Support Moderator