?
Solved

How can I insert Multiple Lines into my sql server database from a form

Posted on 2007-11-23
4
Medium Priority
?
2,328 Views
Last Modified: 2012-06-22
Hi,

I want to be able to insert multiple rows into my sql server database using the data captured by my form.

I know how to inert data into a table, but not how to loop and insert several rows.

What I need to do is insert say:

ireferredby
ifriendname1
ifriendemail1

Into the first row,

Then

ireferredby
ifriendname2
ifriendemail2

Into the second and so on, obviously ignoring a row if ifriendemail is null.

Any validation on the email field would be great as well.


<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
 
<% Dim ModeType
 
ModeType = "view"
If (Request.QueryString("mode") <> "") Then 
  ModeType = Request.QueryString("mode")
 
End If
 
%>
 
 
<%
Set OBJdbConn = Server.CreateObject("ADODB.Connection")
OBJdbConn.Open = "Provider=SQLOLEDB;" & _
       "Data Source=xxx;" & _
       "Initial Catalog=xxx;" & _
	   "User ID=xx;" & _
       "Password=xx"
%>
<%
Function DoubleUpSingleQuotes(strInput)
    DoubleUpSingleQuotes = Replace(strInput, "'", "''")
End Function
%>
 
<%
Select Case SaveType
  case "save"%>
 
<%
ireferredby = request.form("frmreferredby")
ifriendname1 = request.form("frmfriendname1")
ifriendemail1 = request.form("ffrmfriendemail1")
ifriendname2 = request.form("frmfriendname2")
ifriendemail2 = request.form("ffrmfriendemail2")
ifriendname3 = request.form("frmfriendname3")
ifriendemail3 = request.form("ffrmfriendemail3")
 
%>
 
 
<%
SQLQuery = "INSERT INTO CustomerRefer (referredby, friendname, friendemail)" 
SQLQuery = SQLQuery & " VALUES (" 
 
SQLQuery = SQLQuery & "'" & DoubleUpSingleQuotes(ireferredby) & "',"
SQLQuery = SQLQuery & "'" & DoubleUpSingleQuotes(ifriendname1) & "',"
SQLQuery = SQLQuery & "'" & DoubleUpSingleQuotes(ifriendemail1) & "'"
       
SQLQuery = SQLQuery & ")"
 
Set RS = OBJdbConn.Execute(SQLQuery)
 
OBJdbConn.Close
Set OBJdbConn = Nothing
 
 
response.Redirect("default.asp?mode=saved")
%>
 
 
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>
 
<body>
 
<table width="544" height="297" border="1" align="center">
  <tr>
    <td width="97">&nbsp;</td>
    <td width="400"><table width="200%" border="1">
      <tr>
        <td>Refer A Friend</td>
      </tr>
      <tr>
        <td>Enter your friends details below, and they will soon start receiving the same exclusive offers as you.</td>
      </tr>
    </table></td>
    <td width="25">&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>
	<% case "saved"%> 
	<table width="100%" border="1">
  <tr>
    <td>Thank you, your friends details have been saved. </td>
  </tr>
</table>
 
	<% case "view"%> 
	<form id="form1" name="form1" method="post" action="default.asp?mode=save">
      <table width="100%" border="1">
        <tr>
          <td width="5%">&nbsp;</td>
          <td width="46%">Your Name </td>
          <td width="49%"><label>
            <input name="frmreferredby" type="text" id="frmreferredby" />
          </label></td>
        </tr>
        <tr>
          <td>&nbsp;</td>
          <td>Your Friends Name </td>
          <td>Your Friends Email </td>
        </tr>
        <tr>
          <td>1</td>
          <td><input name="frmfriendname1" type="text" id="frmfriendname1" /></td>
          <td><input name="frmfriendemail1" type="text" id="frmfriendemail1" size="30" /></td>
        </tr>
        <tr>
          <td>2</td>
          <td><input name="frmfriendname2" type="text" id="frmfriendname2" /></td>
          <td><input name="frmfriendemail2" type="text" id="frmfriendemail2" size="30" /></td>
        </tr>
        <tr>
          <td>3</td>
          <td><label>
            <input name="frmfriendname3" type="text" id="frmfriendname3" />
          </label></td>
          <td><label>
            <input name="frmfriendemail3" type="text" id="frmfriendemail3" size="30" />
          </label></td>
        </tr>
        <tr>
          <td colspan="3"><label>
            <div align="center">
              <input type="submit" name="Submit" value="Send" />
              </div>
          </label></td>
          </tr>
      </table>
        </form>
<%
end select
%>
    </td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
</table>
 
</body>
</html>

Open in new window

0
Comment
Question by:BenthamLtd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 4

Accepted Solution

by:
MeCanHelp earned 2000 total points
ID: 20339803
The best way to achive repetative actions in a script is to write a routine or a function depending on whether or not you need a return value. This way you could call the rutine and pass arguments into the function instead of having to write simular code over and over again.

Sub enterSql (var1, var2, var3)

     referredby = var1
     friendname = var2
     friendemail = var3

     SQLQuery = "INSERT INTO CustomerRefer (referredby, friendname, friendemail)"
     SQLQuery = SQLQuery & " VALUES ("

     SQLQuery = SQLQuery & "'" & DoubleUpSingleQuotes(ireferredby) & "',"
     SQLQuery = SQLQuery & "'" & DoubleUpSingleQuotes(ifriendname1) & "',"
     SQLQuery = SQLQuery & "'" & DoubleUpSingleQuotes(ifriendemail1) & "'"
       
     SQLQuery = SQLQuery & ")"
 
     Set RS = OBJdbConn.Execute(SQLQuery)
End Sub

So now when you want it to run you would call and pass one right after the other:

enterSql (ireferredby,ifriendname1,ifriendemail1)
enterSql (ireferredby,ifriendname2,ifriendemail2)

Hope this helps some.
0
 
LVL 4

Expert Comment

by:MeCanHelp
ID: 20339815
Sorry just noticed a mistake in the code

This:

referredby = var1
friendname = var2
friendemail = var3

Should be this:

ireferredby = var1
ifriendname1 = var2
ifriendemail1= var3

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Welcome, welcome!  If you are new to the series and haven't been following along, please take a brief moment to review the first three installments: Part 1 (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/A_266-VBScri…
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

719 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