[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 187
  • Last Modified:

Copy checked records to table

I have a page that lists all of the items in a MS Access table (originaltable), with a checkbox next to each one.  When someone checks one of those records and then clicks submit, I want it to be copied to another table (newtable), using ASP/ SQL.

(More Details: I've been trying to use this SQL statement: "strSQL="INSERT INTO newtable(item1) SELECT item1 FROM originaltable WHERE request(rs1(record_id_number))='yes'" and then looping so it goes through all the possible record_id_number's that could have been selected on the previous page and then passed on. But when I do that, i get the following error "Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1." which means that one of the column names (item1) does not exist (http://support.microsoft.com/kb/216425) but i checked and double checked and it does.)

Thank you!
Kyle
0
kylen1010
Asked:
kylen1010
1 Solution
 
GRayLCommented:
chg:  strSQL="INSERT INTO newtable(item1) SELECT item1 FROM originaltable

to:  strSQL="INSERT INTO newtable(item1) SELECT originaltable.item1 FROM originaltable
0
 
Chi Is CurrentCommented:
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
0
 
kylen1010Author Commented:
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!
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
Chi Is CurrentCommented:
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.

 

0
 
kylen1010Author Commented:
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
0
 
kylen1010Author Commented:
WAHOO! nevermind... i figured it out. I think walking away for a few hours and then coming back helped because i figured it out! here is the correct way to do it, if anyone ever has a similar situation!!!

______________________
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") & "' AND username = '" & request(rs1("username")) & "'"
______________________

Thanks for everyone's help
Kyle
0
 
Chi Is CurrentCommented:
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
0
 
Vee_ModCommented:
Closed, 255 points refunded.
Vee_Mod
Community Support Moderator
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now