Web Analytics Made Easy -
StatCounter Insert multiple data in a single query - CodingForum

Announcement

Collapse
No announcement yet.

Insert multiple data in a single query

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Insert multiple data in a single query

    Hi all,

    Hope everyone is doing well. Actually I am not a programmer and I do know how to use dreamweaver to create a simple programming task. Recently my boss ask me to create this small application to isert some data into the database. But the trick is, he wanted the user to key in 5 different email in five textfield and submit into the db in the same column.

    Sample I key in 5 textfiled and insert into one column name "email"

    Below is my code where i encounter duplicate insertion

    I try using array but since I am not a programmer I dun which is the proper way to program it.

    Please help!


    <%@LANGUAGE="VBSCRIPT"%>
    <!--#include file="../Connections/test_multi.asp" -->


    <%

    Set objConn = CreateObject("ADODB.Connection")
    objConn.Open MM_test_multi_STRING

    date1 = request.form("date")
    StaffID = request.form("StaffID")
    recCount = request.form("Email").count

    Redim ArryEmail(recCount)
    Redim ArryEmail2(recCount)
    Redim ArryEmail3(recCount)
    Redim ArryEmail4(recCount)

    for i = 0 to recCount - 1
    ArryEmail(i) = request.form("Email")(i+1)
    ArryEmail2(i) = request.form("Email2")(i+1)
    ArryEmail3(i) = request.form("Email3")(i+1)
    ArryEmail4(i) = request.form("Email4")(i+1)
    next

    %>
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
    <html>
    <head>
    <title>RHB Unit Trust Management Berhad</title>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    <link href="../../css/default.css" rel="stylesheet" type="text/css">
    </head>
    <%
    for i = 0 to reccount -1
    strSql = " insert into test_multi (Email, Email, Email, Email, StaffID, [Date]) values ( " & ArryEmail(i) & ", '" & ArryEmail2(i) & "', '" & ArryEmail3(i) & "', '" & ArryEmail4(i) & "', '" & StaffID & "', #" & Date1 & "#)"

    'response.write("<BR>" & strSql)
    objConn.Execute(strSql)
    next
    %>
    <body>
    <p align="center"><font face="Arial, Helvetica, sans-serif" size="2">Data update
    sucessfully</font></p>
    <p align="center">&nbsp;</p>
    <p align="center"><font color="#000000"><font size="1" face="Arial, Helvetica, sans-serif">&#169;2004,
    RHB Unit Trust Management Berhad- </font></font><font size="1" face="Arial, Helvetica, sans-serif"><br>
    </font></p>
    </body>
    </html>
    <%
    objConn.Close()
    %>

  • #2
    Insert multiple rows in single query

    I need to know which BackEnd you're using Sql server or MySql ..?

    So anyway this is how you could fix it:

    For MySql:

    Code:
    insert into table1 (First,Last) values ("Fred","Smith"),
      ("John","Smith"),
      ("Michael","Smith"),
      ("Robert","Smith");
    For Sql Server 2000 & 2005:

    Code:
    insert into table1 (field1,field2) values (value1,value2);
    insert into table1 (field1,field2) values (value1,value2);
    insert into table1 (field1,field2) values (value1,value2);
    insert into table1 (field1,field2) values (value1,value2)
    For Sql Server 2008:

    INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
    Don't forget to mark solution providing post as "Answered".
    It helps others to find correct solutions!

    Comment


    • #3
      He is obviously using Access, since the DATE is given as #mm/dd/yyyy# and only Access uses that. So he can't use the MySQL or SQL Server 2008 schemes.

      This code is all wrong, in any case:
      Code:
      recCount = request.form("Email").count
      
      Redim ArryEmail(recCount)
      Redim ArryEmail2(recCount)
      Redim ArryEmail3(recCount)
      Redim ArryEmail4(recCount)
      
      for i = 0 to recCount - 1
      ArryEmail(i) = request.form("Email")(i+1)
      ArryEmail2(i) = request.form("Email2")(i+1)
      ArryEmail3(i) = request.form("Email3")(i+1)
      ArryEmail4(i) = request.form("Email4")(i+1)
      next
      It doesn't even make sense. If there are 5 fields all named "Email" in the <form>, that would pick them up, yes. But it would be *EXPECTING* to pick up 15 *MORE* values! From fields name "Email2" and "Email3" and "Email4"!!!

      Keep it *SIMPLE*.

      Code:
      <%@LANGUAGE="VBSCRIPT"%>
      <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
      <html>
      <head>
      <title>RHB Unit Trust Management Berhad</title>
      <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
      <link href="../../css/default.css" rel="stylesheet" type="text/css">
      </head>
      <body>
      <!--#include file="../Connections/test_multi.asp" -->
      <%
      Set objConn = CreateObject("ADODB.Connection")
      objConn.Open MM_test_multi_STRING
      
      date1 = CDATE( TRIM( REQUEST("date") ) ) ' using CDate ensures it *is* a date
      StaffID = Replace( TRIM( REQUEST("StaffID") ), "'", "''" ) ' sanitize input!
      recCount = request.form("Email").count
      
      oops = 0
      For i = 1 to recCount
          email = Replace( Trim( Request("Email")(i) ), "'", "''" ) ' sanitize input!
          If Len(Email) > 4 Then ' really should validate the email, but at least make THIS check!
              strSql = "INSERT INTO test_multi(Email, StaffID, [Date]) "
                        & " values ( '" & email & "','" & StaffID & "',#" & date1 & "#)"
              On Error Resume Next
                  added = 0
                  objConn.Execute  strSql, added
              On Error GoTo 0
              If added <> 1 Then 
                  oops = oops + 1
                  Response.Write "Unable to add " & email & "<br/>"
              End If
          End If ' end of skipping blank emails
      Next
      
      If oops = 0 Then
      %>
      <p align="center"><font face="Arial, Helvetica, sans-serif" size="2">Data updated
      sucessfully</font></p>
      <%
      End If
      objConn.Close
      </body>
      </html>
      Could be written even simpler if the <form> fields were named "Email1" through "Email5" instead of all having the same name.

      If you are willing to make that change, ask.
      Be yourself. No one else is as qualified.

      Comment

      Working...
      X