Inserting Data into Multiple Rows of Database

This is in response to a user that is "looking for a tutorial on inserting data from a UD4 form into multiple rows of a SQL Server database table." This tutorial explains how to use to allow insertion of multiple rows into a database table based on the data entered into a form by a user.

Inserting Data into Multiple Rows of Database

If you need to have a page that inserts mulitple rows based on the input submitted from a form, your best bet is to write a bit of custom VBSript.

The MX Update Server Behavior works only for single row updates based on a unique row ID. You need a very similar statement that looks like this:

update table set column2 = 'new text' where column1 = 'value user entered'

Encoded with form values, it will look like this:

sSQL = "update dbo.TEST set DESCRIPTION = '" & Request("Description") & "' where name = '" & Request("Name") & "'"

If you examine the SQL statement that MX generates, you'll see that it uses the variables MM_EditColumn and MM_RecordID to hold the unique column name and the rowid of the record selected with the recordset you defined (required to create an update behavior). You may be tempted to doctor the MX code once you notice this, and modify the select statement in the chunk of code that does the update. Beware, though, that the next time you open the updage dialog and apply changes, your code will likely be vaperized.

The easiest and safest thing to do is copy the code that MX generates when you create an Update Behavior and modify it for your needs. Delete the "official" MX update bahavior when you're done scalping the code so things don't get confusing. This may sound strange, but it saves a good deal of time, and if you're a lazy coder like me, you'll appreciate the time savings.

I did just that will the following page. Note that it iterates on itself and just writes a message to the same page when it completes. You'll want to handle errors better than this, and probably redirect to another page when you're done.

< !--#include file="Connections/MyConnectionsFile.asp" -->
< %
Dim sSQL
Dim DBCmd

If (CStr(Request("Submit")) <> "" And CStr(Request("Name")) <> "") Then

sSQL = "update dbo.TEST set DESCRIPTION = '" & Request("Description") & "' where name = '" & Request("Name") & "'"

' execute the update
Set DBCmd = Server.CreateObject("ADODB.Command")
DBCmd.ActiveConnection = My_Connection_STRING
DBCmd.CommandText = sSQL

response.write "Records updated</p>"

End If

< head>
< title>Untitled Document</title>
< meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
< /head>

< form name="form1" action="tutorial.asp">
< p>Pick a name and a description for each person of that name to have.</p>
< p>Name
<input name="Name" type="text" id="Name">
< /p>
< p>New Description
<input name="Description" type="text" id="Description">
< /p>
< p>
<input type="submit" name="Submit" value="Submit">
< /p>

< /body>
< /html>



Can ASP Upload be added to this routine?

April 19, 2006 by Justin Moss
Hi, This is great, but can ASP Upload be integrated to allow multiple file upload into multiple records? Somewhat like NeXTensio product Regards. Justin

You must me logged in to write a comment.