Update Access database with one to ten entries at same time

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

arendt73Asked:
Who is Participating?
 
pteranodon72Connect With a Mentor Commented:
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
 
arendt73Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.