I went to a page on DMXZone named "Multiple delete using Javascript " at dmxzone.com/ShowDetail.asp?NewsId=3430, and I thought I could somehow change it to an Insert to do what I need...a page that inserts multiple records from one form into one table. My plan is to get the Delete Behavior to work first using the script from that page, then I was going to change it to an Insert page.

But I'm getting an error message (see below). I've also included the page's code, and the query that's generated upon submittal. If someone can help me out with this, or point me in the right direction, it would be greatly appreciated. If anyone knows of a working script or extension (no demos) that I can get a hold of that will do a multiple insert from one form into one DB table, please let me know. Thanks in advance.

KWilliams

I'm getting this error message:

>Microsoft OLE DB Provider for SQL Server error '80040e14'

>Line 1: Incorrect syntax near '(03-005 )'.

>/Employment/app_jobs3C.asp, line 28

This is the query that's generated upon submittal of the form:

>www.douglas-county.com/Employment/app_jobs3C.asp?checkbox=03-005++++&Submit=Add+Job%28s%29[/Q]

And finally, this is the page's code using the JavaScript from DMXZone's page referenced above:

<%@LANGUAGE="JAVASCRIPT"%>
<!--#include file="Connections/strConn.asp" -->
<%
if(String(Request.Querystring("checkbox") != "undefined"{ Command1__varJob_ID = String(Request.Querystring("checkbox");}
%>
<%
// Script enabling multiple delete of records

// If there is no choice, the page "app_jobsconf.asp" is loaded again
// and a message box alerts the user
var emptystring=String(Request.Querystring("checkbox") // The var "emptystring" is used to check the "choice or no choice"
if (emptystring !="undefined" { // If the var "emptystring" contains the user's choice, the datas are deleted

var parenthesisin="("; //var containing the string "("
var parenthesisout=""; //var containing the string ""
//Var "parenthesisin" and "parenthesisout" are added before and after the var "String(Request.Querystring("checkbox")"
if (String(Request.Querystring("checkbox") != "undefined"{ Command1__varJob_ID =parenthesisin + String(Request.Querystring("checkbox")+ parenthesisout;}
var Command1 = Server.CreateObject("ADODB.Command";
Command1.ActiveConnection = MM_strConn_STRING;
// Create a command in "Data Bindings", type="DELETE", SQL="DELETE * FROM Employ_Jobs WHERE Job_ID IN Command1__varJob_ID"
// varJob_ID is a var which value is "Request.Querystring("checkbox""
var Command1 = Server.CreateObject("ADODB.Command";
Command1.ActiveConnection = MM_strConn_STRING;
Command1.CommandText = "DELETE FROM dbo.Employ_Jobs WHERE Job_ID IN '"+ Command1__varJob_ID.replace(/'/g, "''" + "'";
Command1.CommandType = 1;
Command1.CommandTimeout = 0;
Command1.Prepared = true;
Command1.Execute();
Response.Redirect("app_jobsconf.asp"
}

else {%>
<%
var rs_App1A__MMColParam = "Seqno";
if(String(Request.QueryString("id") != "undefined" {
rs_App1A__MMColParam = String(Request.QueryString("id");
}
%>
<%
var rs_App1A__varApp_ID = "%";
if(String(Session("App_ID") != "undefined" {
rs_App1A__varApp_ID = String(Session("App_ID");
}
%>

<%
var rs_App1A__varPassword = "%";
if(String(Session("Password") != "undefined" {
rs_App1A__varPassword = String(Session("Password");
}
%>
<%
var rs_App1A = Server.CreateObject("ADODB.Recordset";
rs_App1A.ActiveConnection = MM_strConn_STRING;
rs_App1A.Source = "SELECT * FROM dbo.Employment_App WHERE App_ID LIKE '"+ rs_App1A__varApp_ID.replace(/'/g, "''" + "' AND Password LIKE '"+ rs_App1A__varPassword.replace(/'/g, "''" + "' AND Seqno = "+ rs_App1A__MMColParam.replace(/'/g, "''" + ""; rs_App1A.CursorType = 0; rs_App1A.CursorLocation = 2; rs_App1A.LockType = 3; rs_App1A.Open(); var rs_App1A_numRows = 0; %>


<% var rs_App1B = Server.CreateObject("ADODB.Recordset";
rs_App1B.ActiveConnection = MM_strConn_STRING;
rs_App1B.Source = "SELECT * FROM Jobs WHERE Job_ID IN (SELECT Job_ID FROM Employ_Jobs) ORDER BY Job_Title ASC"; rs_App1B.CursorType = 0; rs_App1B.CursorLocation = 2; rs_App1B.LockType = 3; rs_App1B.Open(); var rs_App1B_numRows = 0; %>
<%
var Repeat1__numRows = -1;
var Repeat1__index = 0;
rs_App1B_numRows += Repeat1__numRows;
%>

<% if (rs_App1A.EOF) Response.Redirect("www.douglas-county.com/Employment/noaccess.asp")%>
<%
Session("Seqno" = rs_App1A.Fields.Item("Seqno".Value
Session("App_ID" = rs_App1A.Fields.Item("App_ID".Value
Session("Password" = rs_App1A.Fields.Item("Password".Value
%>

<% var MM_paramName = ""; %>

<%
// *** Go To Record and Move To Record: create strings for maintaining URL and Form parameters

// create the list of parameters which should not be maintained
var MM_removeList = "&index="; if (MM_paramName != "" MM_removeList += "&" +
MM_paramName.toLowerCase() + "=";
var MM_keepURL="",MM_keepForm="",MM_keepBoth="",MM_keepNone="";

// add the URL parameters to the MM_keepURL string
for (var items=new Enumerator(Request.QueryString); !items.atEnd();
items.moveNext()) {
var nextItem = "&" + items.item().toLowerCase() + "=";
if (MM_removeList.indexOf(nextItem) == -1) {
MM_keepURL += "&" + items.item() + "=" + Server.URLencode(Request.QueryString(items.item()));
}
}

// add the Form variables to the MM_keepForm string
for (var items=new Enumerator(Request.Form); !items.atEnd();
items.moveNext()) {
var nextItem = "&" + items.item().toLowerCase() + "=";
if (MM_removeList.indexOf(nextItem) == -1) {
MM_keepForm += "&" + items.item() + "=" + Server.URLencode(Request.Form(items.item()));
}
}

// create the Form + URL string and remove the intial '&' from each of the strings MM_keepBoth = MM_keepURL + MM_keepForm; if (MM_keepBoth.length > 0) MM_keepBoth = MM_keepBoth.substring(1); if (MM_keepURL.length > 0) MM_keepURL = MM_keepURL.substring(1); if (MM_keepForm.length > 0) MM_keepForm = MM_keepForm.substring(1); %>

<html>

<head>

<title>Douglas County, Kansas - Online Employment Application-Step 1</title>

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

<META NAME="keywords" CONTENT="Douglas County,Douglas County KS,Douglas County Kansas,Douglas,County,Kansas,Government,KS Government,Kansas Government,Stephen A. Douglas,Quantrill,Quantrill's Raid,Lawrence,Lecompton,Baldwin,Baldwin
City,Eudora,Clinton,Jayhawks;KU,Kansas University,University of Kansas,University of KS,Baker University,Haskell,Haskell Indian Nations University,John Baldwin,Amos A. Lawrence,Samuel D. Lecompte,Kanzas Nation.">

<META NAME="description" CONTENT= "Official site for Douglas County, Kansas - Our mission is to provide for the safety and well being of the citizens of Douglas County through the professional and efficient delivery of essential public services in response to the needs of Douglas County citizens.">

<style type="text/css">
<!--
.roll { font-family:"Arial, Helvetica, sans-serif" size="1"; font-style:normal; font-weight:normal; letter-spacing:normal; text-decoration:underline; color:330066; }
A.roll:hover { size="1"; color:red; text-decoration:none;}
-->
INPUT, TEXTAREA {
font-family: "Arial, Helvetica, sans-serif";
padding: 1px;
font-size: 12px;
color: #000000;
background-color: #FFFFFF;
border: inset 2px #660000;
}
</style>
<script language="JavaScript">
<!--

<!-- Begin
if (window != top) top.location.href = location.href;

function NewWindow(mypage, myname, w, h, scroll)
{
var winl = (screen.width - w) / 1.5;
var wint = (screen.height - h) / 1.65;
winprops = 'height='+h+',width='+w+',top='+wint+',left='+winl+',scrollbars='+scroll
+',resizable'
win = window.open(mypage, myname, winprops)
if (parseInt(navigator.appVersion) >= 4) { win.window.focus(); }
}

var isNN = (navigator.appName.indexOf("Netscape"!=-1);
function autoTab(input,len, e) {
var keyCode = (isNN) ? e.which : e.keyCode;
var filter = (isNN) ? [0,8,9] : [0,8,9,16,17,18,37,38,39,40,46];
if(input.value.length >= len && !containsElement(filter,keyCode)) {
input.value = input.value.slice(0, len);
input.form[(getIndex(input)+1) % input.form.length].focus();
}
function containsElement(arr, ele) {
var found = false, index = 0;
while(!found && index < arr.length)
if(arr[index] == ele)
found = true;
else
index++;
return found;
}
function getIndex(input) {
var index = -1, i = 0, found = false;
while (i < input.form.length && index == -1)
if (input.form[i] == input)index = i;
else i++;
return index;
}
return true;
}
// End -->
//-->
</script>
</head>
<body bgcolor="#FFFFFF" text="#000000">
<form name="form1">

<table width="660" border="0" cellspacing="0" cellpadding="0" bgcolor="#FFFFCC">

<tr>

<td valign="top" height="2">

<table width="100%" border="0" cellspacing="1" cellpadding="1">

<tr>

<td colspan="2" bgcolor="#660000"><font size="2" face="Arial, Helvetica, sans-serif" color="#FFFFFF"><b>Position(s) applying for:</b></font></td>
</tr>

<tr bgcolor="#FFFFCC" valign="top">

<td colspan="2" height="37">

<div align="left">

<% while ((Repeat1__numRows-- != 0) && (!rs_App1B.EOF)) { %>
<input type="checkbox" name="checkbox" value="<%=(rs_App1B.Fields.Item("Job_ID".Value)%>">
<font size="2" face="Arial, Helvetica, sans-serif"><%=(rs_App1B.Fields.Item("Job_Title".Value)%><font size="1"><a class=roll href="app_jobdesc.asp?<%=("Job_ID=" + rs_App1B.Fields.Item("Job_ID".Value)%>">View Details</a></font></font><br>

<%
Repeat1__index++;
rs_App1B.MoveNext();
}
%>
</div>
</td>
</tr>

<tr bgcolor="#FFFFCC">

<td colspan="2">

<p><font size="1" face="Arial, Helvetica, sans-serif" color="#FF0000"><b><img src="Images/bullet2.gif" width="8" height="8">To view details for each position, visit the'View Details' links above.


</b></font>
</p>
</td>
</tr>

<tr>

<td bgcolor="#FFFFCC" valign="bottom" height="2" width="66%"><font size="2" face="Arial, Helvetica, sans-serif">If you have any questions about this form, please contact the Douglas County <a class=roll href=" webmaster <mailto: >
.mailto: ">webmaster</a>.</font></td>
<td bgcolor="#FFFFCC" valign="top" height="2" width="34%" colspan="-1">

<div align="right">

<input type="button" name="Submit2" value="Cancel" onClick="window.close()">
 
<input type="submit" name="Submit" value="Add Job(s)">
</div>
</td>
</tr>

</table>
</td>
</tr>

</table>
</form>
</body>
</html>
<%
rs_App1A.Close();
%>
<%
rs_App1B.Close();
%>
<%
}%>

Hi I see that you have had no response from this message board... I have a similar problem I need to multiple upload files is it possible?

Hi jonpanky,

I haven't received any responses from any of the forums I've posted this question on, including this one. I'm hoping that someone will try to answer my question soon, as I'm about to pull all of my hair out. It would be great if someone would develop a UD extension for multi-insert forms. If I do ever receive any help, I'll make sure to forward the information to you. Good luck.

KWilliams

Hello,

I'm afraid I can't help you with this but Owen Eastwick wrote some articles about multiple database actions using ASP.
www.drdev.net/article01.htm

Oh, and please request a multiple file upload tutorial from the request a tutorial link at the main page.

with kind regards,

Dennis van Galen
DMXzone Manager
FAQ, Tutorial and Extension Manager


Studio MX / CFMX PRO / SQL 2000 / NT4 AND win2kPRO / IIS5

Hi Dennis,

Thanks for pointing me in the right direction. I'll let you know how my search goes..hopefully well. And jonpanky, if and when I'm able to come up with a solution, I'll forward it on to you. Thanks again Dennis, I was beginning to think no one cared<img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>

KWilliams

Hello jonpanky and Dennis,

Guess what...I came up with a solution<img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle> I had to make a few adjustments to the code to get it working, but I was able to create a Command Statement using JavaScript that allows a user to insert one or more entries into separate rows of a DB tables.

To jonpanky (or anyone else that wants it)...I know that you wanted me to let you know when and if I got a solution to this isue, but the code is too long to include on this post. So please forward your email address to , and I'll send you the code.

If you have any questions jonpanky, please feel free to ask. I hope that my solution can give you a solution as well. Good luck!

I've received several emails on the solution that I created for multiple inserts using ASP and JScript, so I thought that I'd include the code here. Here it is:

----------CODE BEGINS----------
FORM CODE TO PULL CURRENT RECORDS FROM DB (form.asp):
<pre id=code><font face=courier size=2 id=code>&lt;%
var RS__varApp_ID = "%";
if(String(Session("MM_Username") != "undefined" {
RS__varApp_ID = String(Session("MM_Username");
}
%&gt;
&lt;%
var RS = Server.CreateObject("ADODB.Recordset";
RS.ActiveConnection = MM_strConn_STRING;
RS.Source = "SELECT * FROM dbo.TABLE1 WHERE (GetDate() BETWEEN Start_Date AND Deadline_Date + 1) AND Job_ID NOT IN (SELECT Job_ID FROM dbo.TABLE2 WHERE App_ID = '"+ RS__varApp_ID.replace(/'/g, "''" + "')";
RS.CursorType = 0;
RS.CursorLocation = 2;
RS.LockType = 3;
RS.Open();
var RS_numRows = 0;
%&gt;
&lt;%
var Repeat1__numRows = -1;
var Repeat1__index = 0;
RS_numRows += Repeat1__numRows;
%&gt;</font id=code></pre id=code>

INPUT FORM W/LOOPED JOBS (form.asp):
<pre id=code><font face=courier size=2 id=code>&lt;form name="form1" method="post" action="insert.asp"&gt;
&lt;table width="100%" border="0"&gt;
&lt;tr&gt;
&lt;td&gt;
&lt;table width="100%" border="0"&gt;
&lt;tr&gt;
&lt;td colspan="2"&gt;
&lt;% while ((Repeat1__numRows-- != 0) && (!RS.EOF)) { %&gt;
&lt;input &lt;%=((RS.Fields.Item("Job_ID".Value == "job"?"CHECKED":""%&gt; type="checkbox" name="job" value="&lt;%=(RS.Fields.Item("Job_ID".Value)%&gt;"&gt;
&lt;%=(RS.Fields.Item("Job_Title".Value)%&gt;&lt;a class="small" href="appjobdesc.asp?&lt;%=("Job_ID=" + RS.Fields.Item("Job_ID".Value) %&gt;" target="_blank" onclick="NewWindow(this.href,'name','700','300','yes');return false;"&gt;View
Details&lt;/a&gt;&lt;br /&gt;
&lt;%
Repeat1__index++;
RS.MoveNext();
}
%&gt;
&lt;input type="hidden" name="hiddenField2" value="&lt;%=Session("MM_Username"%&gt;"&gt;
&lt;input type="hidden" name="hiddenField" value="&lt;%= kc_Date()%&gt;"&gt;
&lt;% if (RS.EOF && RS.BOF) { %&gt;
&lt;strong&gt;There are no additional positions open at this time.
Please check back.&lt;/strong&gt;
&lt;% } // end RS.EOF && RS.BOF %&gt;
&lt;/td&gt;
&lt;/tr&gt;
&lt;% if (!RS.EOF || !RS.BOF) { %&gt;
&lt;tr&gt;
&lt;td&gt;
&lt;div align="right"&gt;
&lt;input type="hidden" name="hiddenField3" value="&lt;%=Session("MM_Password"%&gt;"&gt;
&lt;input type="button" class="formButton" value="Cancel" onclick="cancel2()"&gt;
&lt;input type="submit" class="formButton" value="Add Job(s)"&gt;
&lt;/div&gt;
&lt;/td&gt;
&lt;/tr&gt;
&lt;% } // end !RS.EOF || !RS.BOF %&gt;
&lt;/table&gt;
&lt;/td&gt;
&lt;/tr&gt;
&lt;/table&gt;
&lt;/form&gt;
&lt;%
RS.Close();
RS = null;
%&gt;</font id=code></pre id=code>

MULTIPLE INSERT LOOP (insert.asp):
<pre id=code><font face=courier size=2 id=code>&lt;%
Session("MM_Username" = String(Request.Form("hiddenField2");
%&gt;
&lt;%
if(String(Session("MM_Username") != "undefined"{ Command1__varApp_ID = String(Session("MM_Username");}
if(String(Request.Form("hiddenField") != "undefined"{ Command1__varLast_Update_Date = String(Request.Form("hiddenField");}
if(String(Request.Form("job") != "undefined"{ Command1__MM_Insert = String(Request.Form("job");}
%&gt;
&lt;%
var MM_Insert=String(Request.Form("job")
if (MM_Insert != "undefined"
{
var Insert_array = MM_Insert.split(",";
var loop = 0
while (loop &lt; Insert_array.length)
{
var New_Insert=Insert_array[loop]
var Command1 = Server.CreateObject("ADODB.Command";
Command1.ActiveConnection = MM_strConn_STRING;
Command1.CommandText = "INSERT INTO dbo.TABLE2 (App_ID, Creation_Date, Job_ID) VALUES('"+ Command1__varApp_ID.replace(/'/g, "''" + "', '"+ Command1__varLast_Update_Date.replace(/'/g, "''" + "', '"+ New_Insert.replace(" ","" + "')";
Command1.CommandType = 1;
Command1.CommandTimeout = 0;
Command1.Prepared = true;
Command1.Execute();
Command1.Close;
loop++
}
}
else {%&gt;
&lt;script language="javascript" type="text/javascript"&gt;
onload=window.alert('You did not select any new jobs. Please try again or click CANCEL.') // An alert message is showed if no items were selected
window.history.go(-1)
&lt;/script&gt;
&lt;%
}
%&gt;</font id=code></pre id=code>

I hope it helps<img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>

Hi Katherine,

seems like you solve the problems in here...i have a lil bit problem with my programming skills iam new with asp...i have problem its quite the same topic in here...how do i insert csv to my webhosting db...the db is already configure and setup...looking forward katherin...thanks..

Hi Jerus,

I had to do this recently. What you do is to:
1) Open SQL Server Enterprise Manager
2) Select Tool &gt; Wizards &gt; Data Transformation Services &gt; DTS Import Wizard

3) Select the "Text File" option under the "Data Source" menu, and click "Next". (NOTE: Make sure that the path to the file is not local, like this: f://folder/filename.csv, but is like this: //server/folder/filename.csv. Otherwise, this job will not work when scheduled.)
4) Select your options under "Select file format", including fixed or deliminated columns, file type, row delimeter, text qualifier, skip first row, etc. You will also see a preview of your source file at the bottom. If you're not sure which to choose, leave the default choice as yours.
5) If you chose deliminated columns, you'll need to select which type: comma, tab, semicolon, other. If you chose fixed columns, you'll need to move the fixed column lines, and click "Next".
6) Set which database will contain your destination table, and click "Next".
7) Now you have the option of leaving the name the way it is, which will automatically create a new table, or renaming the destination table, which will append an already-existing table.
6) You can then view exactly what data will go where by clicking on the "Transform..." button.
7) To save this DTS package so that you can run it later, check the "Schedule DTS package for later execution".
If you want to create a new table, you'll then set the DB table as the destination. To change the name of the new table, and click "Next".
8) Enter the name and properties if this new DTS package, and click "Next".
9) Click the "Finish" button to run the job. If it was successful, then you should be good-to-go. If not, then you'll need to check the packages error logs for more information.

If you when wanted to call this DTS package from an ASP page, you'd first need to create a Stored Procedure that would run the DTS package. And then you'd want to call it from the page. There's more information on this in SQL Server's Books Online.

I hope that this information helps. Good luck to you.