Hi there,

I'm working with an insert page that inserts one or more jobs for an applicant into a database table called "Employ_Jobs" from a database table of jobs called "Jobs". The form enters in the user's App_ID, Creation_Date, and Job ID. The form works great when you select one job, but when a user selects more than one job, it enters all of the selected jobs into one row in the DB table separated by commas (ex: Job_ID: 02-034 , 02-035). I want to to insert each job selected into separate rows.

I've tried both a listbox and checkbox options, and they both have the same problem. When I originally created the insert page, I used a repeat behavior to get all of the jobs. When it didnt work, I tried to use the horizontal loop extension instead, but it had the exact same problem.

I have no idea how to do this. Can anyone here help me to do this?? I've included the code from the page at the end of this post to reference. This page was created using UD4, ASP, JavaScript, & SQL Server 7. Thanks for ANY help in advance.

KWilliams



INSERT & RECORDSET CODE:

<%@LANGUAGE="JAVASCRIPT"%>
<!--#include file="Connections/strConn.asp" -->
<%
// *** Edit Operations: declare variables

// set the form action variable
var MM_editAction = Request.ServerVariables("URL";
if (Request.QueryString) {
MM_editAction += "?" + Request.QueryString;
}

// boolean to abort record edit
var MM_abortEdit = false;

// query string to execute
var MM_editQuery = "";
%>

<%
// *** Insert Record: set variables

if (String(Request("MM_insert") != "undefined" {
var MM_editConnection = MM_strConn_STRING;
var MM_editTable = "dbo.Employ_Jobs";
var MM_editRedirectUrl = "app_jobsconf.asp";
var MM_fieldsStr = "checkbox|value|hiddenField|value|hiddenField2|value";
var MM_columnsStr = "Job_ID|',none,''|Creation_Date|',none,''|App_ID|',none,''";

// create the MM_fields and MM_columns arrays
var MM_fields = MM_fieldsStr.split("|";
var MM_columns = MM_columnsStr.split("|";

// set the form values
for (var i=0; i+1 < MM_fields.length; i+=2) {
MM_fields[i+1] = String(Request.Form(MM_fields));
}

// append the query string to the redirect URL
if (MM_editRedirectUrl && Request.QueryString && Request.QueryString.length > 0) {
MM_editRedirectUrl += ((MM_editRedirectUrl.indexOf('?') == -1)?"?":"&" + Request.QueryString;
}
}
%>

<%
// *** Insert Record: construct a sql insert statement and execute it

if (String(Request("MM_insert") != "undefined" {

// create the sql insert statement
var MM_tableValues = "", MM_dbValues = "";
for (var i=0; i+1 < MM_fields.length; i+=2) {
var formVal = MM_fields[i+1];
var MM_typesArray = MM_columns[i+1].split(",";
var delim = (MM_typesArray[0] != "none" ? MM_typesArray[0] : "";
var altVal = (MM_typesArray[1] != "none" ? MM_typesArray[1] : "";
var emptyVal = (MM_typesArray[2] != "none" ? MM_typesArray[2] : "";
if (formVal == "" || formVal == "undefined" {
formVal = emptyVal;
} else {
if (altVal != "" {
formVal = altVal;
} else if (delim == "'" { // escape quotes
formVal = "'" + formVal.replace(/'/g,"''" + "'";
} else {
formVal = delim + formVal + delim;
}
}
MM_tableValues += ((i != 0) ? "," : "" + MM_columns;
MM_dbValues += ((i != 0) ? "," : "" + formVal;
}
MM_editQuery = "insert into " + MM_editTable + " (" + MM_tableValues + " values (" +

MM_dbValues + "";

if (!MM_abortEdit) {
// execute the insert
var MM_editCmd = Server.CreateObject('ADODB.Command');
MM_editCmd.ActiveConnection = MM_editConnection;
MM_editCmd.CommandText = MM_editQuery;
MM_editCmd.Execute();
MM_editCmd.ActiveConnection.Close();

if (MM_editRedirectUrl) {
Response.Redirect(MM_editRedirectUrl);
}
}

}
%>
<%
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 NOT 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 HLooper1__numRows = -3;
var HLooper1__index = 0;
rs_App1B_numRows += HLooper1__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
Session("Job_ID" = rs_App1B.Fields.Item("Job_ID".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);
%>

HORIZONTAL LOOP CODE:

<%
var startrw=0;
var endrw=HLooper1__index;
var numberColumns=3;
var numrows=-1;
while((numrows-- !=0) && (!rs_App1B.EOF)) {
startrw=endrw + 1;
endrw = endrw + numberColumns;
%>

<%
while ((startrw <= endrw) && (!rs_App1B.EOF)) {
%>

<div align="left">

<input <%=((rs_App1B.Fields.Item("Job_ID".Value == 0)?"CHECKED":""%> 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>
</div>
<%
startrw = startrw+1;
rs_App1B.MoveNext();
}
%>

<% }%>