Solved

Update Access database with one to ten entries at same time

Posted on 2011-09-22
2
353 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Conditional Formatting - Date() 8 25
ASP Button to clear text 4 20
DCount using "OR" 4 19
add more styles to my code 2 19
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Read about achieving the basic levels of HRIS security in the workplace.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now