NEWBIE: Insert into Multiple Rows from one form

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

NEWBIE: Insert into Multiple Rows from one form

Author

Topic

kwilliams

194 Posts

Posted - 2003-05-05 : 11:14:48

Hello there,

I really hope that someone can help me with this one. First the facts...I'm creating this application with the following: UltraDev 4, JavaScript, ASP, and SQL Server 7.

I have a page from my Online Employment Application that should allow users to add one or more jobs to their application using dynamic checkboxes with a horizontal loop. With this configuration, I'm able to display only the jobs that the user hasn't already applied for, but my problem is in the insert. I want the insert page to allow a user to insert one or more job selections into a DB table (SQL). I've looked through several tutorials, and I've made several posts on forums, but I've received no help.

The problem is that when a user selects more than one job, the page enters the data into one row in the DB table with the data separated by commas, instead of adding each job selection into separate rows (which is what I want). There are 3 table columns in the DB table named dbo.Employ_jobs: Job_ID, App_ID, and Creation_Date. The data comes from a DB table named dbo.Jobs. The App_ID comes from a Session Variable, but the other 2 come from form data from the first page.

I'm not sure what I'm doing wrong, but I'm hoping that someone can help me out, or someone can point me in the right direction to someone who can. I'd really appreciate ANY help. I've included the code for the 2 pages below.

**P.S. If there's an extension (not a demo) that can do this for me, please let me know where I can find it. Thanks.

Katherine Williams
kwilliams@douglas-county.com

FORM PAGE:

<%@LANGUAGE="JAVASCRIPT"%>


<%
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) AND GetDate() BETWEEN Post_Startdate AND Post_Enddate 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 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);
%>

<%
var HLooper1__numRows = -3;
var HLooper1__index = 0;
rs_App1B_numRows += HLooper1__numRows;
%>
<% if (rs_App1A.EOF) Response.Redirect("http://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
%>

<html>
<head>
<title>Douglas County, Kansas - Online Employment Application</title>
</head>
<body bgcolor="#FFFFFF" text="#000000">
<table width="660" border="0" align="left">

<tr>
<td valign="top" height="123" colspan="2">

<form name="form1" method="post" action="app_jobs3D2.asp">
<table width="100%" 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="3" 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="3" height="37">
<div align="left">

<%
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)) {
%>
<input <%=((rs_App1B.Fields.Item("Job_ID").Value == "Y")?"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)%><a class=roll href="app_jobdesc.asp?<%=("Job_ID=" + rs_App1B.Fields.Item("Job_ID").Value) %>" target="_blank" onClick="NewWindow(this.href,'name','700','300','yes');return false;"><font size="1">View Details</font></a><br>
</font>
<input type="hidden" name="hiddenField" value="<%Response.Write(Date())%>">
<input type="hidden" name="hiddenField2" value="<%=Session("App_ID")%>">
<input type="hidden" name="MM_insert" value="true">
<%
startrw = startrw+1;
rs_App1B.MoveNext();
}
%>
<% }%>
</div>
</td>
</tr>
<tr bgcolor="#FFFFCC">
<td colspan="3">
<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, click on the 'View Details' links above.

</b></font>
</p>
</td>
</tr>
<tr>
<td bgcolor="#FFFFCC" valign="bottom" height="34" width="71%"><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="mailto:webmaster@douglas-county.com">webmaster</a>.</font></td>
<td bgcolor="#FFFFCC" valign="bottom" height="34">
<div align="right">
<input type="hidden" name="hiddenField3" value="<%=Session("Password")%>">

<a class=roll href="javascript:window.close()">
</a><a class=roll href="">
<input type="button" name="Button2" value="Cancel" onClick="window.close()">
</a>
<input type="submit" name="Submit" value="Add Job(s)">
</div>
</td>
</tr>
</table>
</td>
</tr>
</table>
</form>
</td>
</tr>

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

INSERT PAGE:

<%@LANGUAGE="JAVASCRIPT"%>

<%
if(String(Session("App_ID")) != "undefined"){ Command1__varApp_ID = String(Session("App_ID"));}
if(String(Request.Form("hiddenField")) != "undefined"){ Command1__varCreation_Date = String(Request.Form("hiddenField"));}
if(String(Request.Form("checkbox")) != "undefined"){ Command1__varJob_ID = String(Request.Form("checkbox"));}
%>
<%
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) AND GetDate() BETWEEN Post_Startdate AND Post_Enddate 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("http://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
%>
<%
// *** Insert Record: set variables

if (String(Request("MM_insert")) != "undefined") {
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)) {
%>
<%
var Command1 = Server.CreateObject("ADODB.Command");
Command1.ActiveConnection = MM_strConn_STRING;
Command1.CommandText = "INSERT INTO dbo.Employ_Jobs (Job_ID, App_ID, Creation_Date) VALUES ('"+ Command1__varJob_ID.replace(/'/g, "''") + "', '"+ Command1__varApp_ID.replace(/'/g, "''") + "', '"+ Command1__varCreation_Date.replace(/'/g, "''") + "') ";
Command1.CommandType = 1;
Command1.CommandTimeout = 0;
Command1.Prepared = true;
Command1.Execute();
Response.Redirect("app_jobsconf.asp")
}
%>

<%
startrw = startrw+1;
rs_App1B.MoveNext();
}
%>

<% }%>
<% 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>
</head>
<body bgcolor="#FFFFFF" text="#000000">

<p><font size="2" face="Arial, Helvetica, sans-serif">
This form is submitting. You should be redirected to App_Main.asp soon!</font>
</p>
<p><font size="2" face="Arial, Helvetica, sans-serif">Response.Redirect("http://www.douglas-county.com/Employment/redirect_main.asp")</font></p>
</body>
</html>
<%
rs_App1A.Close();
%>
<%
rs_App1B.Close();
%>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources