Solved

Update Access database with one to ten entries at same time

Posted on 2011-09-22
2
387 Views
Last Modified: 2012-05-12
I have a simple submission page where a user can enter up to 10 new logins for access to a secure website. Sometimes they may enter and submit only three login names, other times they may enter the maximum of ten login names.

I want to be able to enter up to ten logins during one submission without inserting any blanks if a user decides to enter below ten logins.

I have attached my submission page and redirect page for review and update. Any assistance is greatly appreciated. Thank you.

Update/Submission Page
<html>
<head>
<title>New Logins</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<table border="0" cellspacing="0" cellpadding="0" width="600">
  <tr>
    <td><form name="form1" method="post" action="login_new_user_complete.asp">
        <table border="0" cellspacing="0" cellpadding="0" width="600">
          <tr> 
            <td width="10" height="30">&nbsp;</td>
            <td width="20" height="30">&nbsp;</td>
            <td width="570" height="30">ENTER NEW LOGINS BELOW</td>
          </tr>
          <tr> 
            <td height="30">&nbsp;</td>
            <td height="30">1</td>
            <td height="30">
<input name="user1" type="text" id="user1"></td>
          </tr>
          <tr> 
            <td height="30">&nbsp;</td>
            <td height="30">2</td>
            <td height="30">
<input name="user2" type="text" id="user2"></td>
          </tr>
          <tr> 
            <td height="30">&nbsp;</td>
            <td height="30">3</td>
            <td height="30">
<input name="user3" type="text" id="user3"></td>
          </tr>
          <tr> 
            <td height="30">&nbsp;</td>
            <td height="30">4</td>
            <td height="30">
<input name="user4" type="text" id="user4"></td>
          </tr>
          <tr> 
            <td height="30">&nbsp;</td>
            <td height="30">5</td>
            <td height="30">
<input name="user5" type="text" id="user5"></td>
          </tr>
          <tr> 
            <td height="30">&nbsp;</td>
            <td height="30">6</td>
            <td height="30">
<input name="user6" type="text" id="user6"></td>
          </tr>
          <tr> 
            <td height="30">&nbsp;</td>
            <td height="30">7</td>
            <td height="30">
<input name="user7" type="text" id="user7"></td>
          </tr>
          <tr> 
            <td height="30">&nbsp;</td>
            <td height="30">8</td>
            <td height="30">
<input name="user8" type="text" id="user8"></td>
          </tr>
          <tr> 
            <td height="30">&nbsp;</td>
            <td height="30">9</td>
            <td height="30">
<input name="user9" type="text" id="user9"></td>
          </tr>
          <tr> 
            <td height="30">&nbsp;</td>
            <td height="30">10</td>
            <td height="30">
<input name="user10" type="text" id="user10"></td>
          </tr>
          <tr> 
            <td height="30">&nbsp;</td>
            <td height="30">&nbsp;</td>
            <td height="30">&nbsp;</td>
          </tr>
          <tr> 
            <td height="30">&nbsp;</td>
            <td height="30">&nbsp;</td>
            <td height="30"><input type="submit" name="Submit" value="Submit Updates"></td>
          </tr>
          <tr> 
            <td height="30">&nbsp;</td>
            <td height="30">&nbsp;</td>
            <td height="30">&nbsp;</td>
          </tr>
        </table>
      </form></td>
  </tr>
</table>
</body>
</html>

Open in new window


Redirect/Thank You Page
<%
strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("\logins\db\users.mdb") & ""
set conn = server.createobject("adodb.connection")
conn.open strconn
strSQLI = "insert into username (login) values ('"& request.Form("user1") &"', '"& request.Form("user2") &"', '"& request.Form("user3") &"', '"& request.Form("user4") &"', '"& request.Form("user5") &"', '"& request.Form("user6") &"', '"& request.Form("user7") &"', '"& request.Form("user8") &"', '"& request.Form("user9") &"', '"& request.Form("user10") &"')"
conn.execute(strSQLI)
%>
<html>
<head>
<title>Update Complete</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<table width="800" border="0" align="center" cellpadding="0" cellspacing="0">
  <tr>
    <td><div align="center">THANK YOU. UPDATE COMPLETE.</div></td>
  </tr>
</table>
</body>
</html>

Open in new window

0
Comment
Question by:arendt73
2 Comments
 
LVL 14

Accepted Solution

by:
pteranodon72 earned 500 total points
ID: 36583798
The SQL syntax:
INSERT INTO tablename (fieldname1) VALUES (...)
can only be used to insert one record at a time.
The number of field names in parentheses  before the VALUES must match the number of values in parentheses after VALUES. You need to insert up to ten records which have only one field.

replace line 5 & 6 of your thank you page with:
Dim i
For i = 1 to 10
    if request.Forms("user" & i) <> "" Then
        strSQLI = "insert into username (login) values ('" & request.Form("user" & i) & "');"
        conn.execute(strSQLI)
    end if
Next

Open in new window


HTH,
pT72
0
 

Author Comment

by:arendt73
ID: 36583971
Thanks pT72.  I got an error on line 7 - if request.Forms("user" & i) <> "" Then

I changed it to request.form and it works like a champ.

Thank you.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Read about achieving the basic levels of HRIS security in the workplace.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question