Announcement

**dotnetmind** · Aug 24, 2011, 08:34 PM

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);

**Old Pedant** · Aug 24, 2011, 10:03 PM

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.

Announcement

Insert multiple data in a single query

Insert multiple data in a single query

Comment

Comment