I have the following code that populates 3 independent drop-down boxes with
data from arrays. From there the user can select a value from a drop-down
list, or input data into a text box. The user then submits this to be
inserted into the database. Currently I've only programmed to have the first
input box/drop-down valid. The problem I am having is that, even though I've
inserted my SQL into a sub procedure, the code is still run prior to me
making a call to the sub, therefore allowing the data to be inserted twice
into the database. I know the obvious thing would be to add "no duplicates"
on the db table, but I really would like to know who/why the Insert gets
executed twice. I apologize for the amount of code. Thanks!
<html>
<head>
<script language="JavaScript">
alert("hello - head1")
</script>
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<title>EITA SA, Env, Plat Update</title>
<script language="JavaScript">
alert("hello - head2")
</script>
</head>
<body topmargin=0 leftmargin=0>
<script language="JavaScript">
alert("hello - body1")
</script>
<script language="JavaScript">
alert("hello - body2")
</script>
<%
dim compMode
dim objConn, objRS, duplicate
dim test
dim executeDisplay, executeUpdate
dim sSQL, SAarr, Env_arr, PF_arr
dim i, nRows, bRows
duplicate = false
dim SAaarr(100,100)
'''''''''''''''''''''''''''''''''SA''''''''''''''' ''''''''''''''''''''''
'-------------------------------------------'
'Start Main
'-------------------------------------------'
strMode = Request.QueryString("compMode")
if not isEmpty(Request.QueryString("compMode")) then
strMode = Request.QueryString("compMode")
else
strMode = "new"
end if
%>
<script language="VBScript">
msgTxt = "strMode: " & "<%=strMode%>"
msgbox msgTxt
</script>
<%
select case strMode
case "new"
getFormData()
displayForm()
case "save"
updateTables()
%>
<script language="VBScript">
msgTxt = "Tables updated. Stopping execution."
msgbox msgTxt
</script>
<%
displayForm()
case else
%>
<script language="VBScript">
msgTxt = "strMode NOT set: *" & "<%=strMode%>" & "*"
msgbox msgTxt
</script>
<%
end select
'---------------------------------------------'
'End Main
'---------------------------------------------'
sub getFormData()
sSQL = "Select service_area_id, service_area_title From d_service_area
order by d_service_area.service_area_title;"
'msg="Error: "
msg = "drb-a"
Call ExecSQL(sSQL, msg, false)
'Set objRS1 = dbConn.Execute(sSQL1)
SAarr = objRS.GetRows
nColumn = ubound(SAarr) ' lenght of the column
nRows = ubound(SAarr, 2)
objRS.Close
Set objRS = Nothing
'insert space in the first element
For i = 0 to nRows
SAaarr(0,i+1) = SAarr(0,i)
next
For i = 0 to nRows
SAaarr(1,i+1) = SAarr(1,i)
next
''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''En'''''''''''''''''' ''''''''''''''''''''''''
sSQL = "Select environment_id, environment_key From d_environment order by
d_environment.environment_key;"
'msg="Error: "
msg = "drb-b"
Call ExecSQL(sSQL, msg, false)
Env_arr = objRS.GetRows
bColumn = ubound(Env_arr)
bRows = ubound(Env_arr, 2)
objRS.Close
Set objRS = Nothing
'''''''''''''''''''''''''''''PL''''''''''''''''''' ''''''''''''''''''''''''
sSQL = "Select platform_function_id, platform_function From
d_platform_function order by d_platform_function.platform_function;"
'msg="Error: "
msg = "drb-c"
Call ExecSQL(sSQL, msg, false)
PF_arr = objRS.GetRows
cColumn = ubound(PF_arr)
cRows = ubound(PF_arr, 2)
objRS.Close
Set objRS = Nothing
'Response.Write ("strMode = " & strMode)
end sub
sub updateTables()
'objConn.close
dim msgTxt
'sSQL = "INSERT INTO d_service_area(service_area_title) VALUES ('" &
Request.Form("SA_InputBox") & "');"
%>
<script language="VBScript">
msgTxt = "Getting ready to execute INSERT"
msgbox msgTxt
</script>
<%
dim serviceAreaTitle
serviceAreaTitle = Request.Form("SA_InputBox")
sSQL = ""
sSQL = "INSERT INTO d_service_area ( service_area, service_area_title,
sa_logical_link, sa_physical_link ) " & _
"VALUES ('drbSvcArea', '" & serviceAreaTitle & "', 'noLogLink',
'noPhysLink');"
%>
<script language="VBScript">
msgTxt = "sSQL= " & "<%=sSQL%>"
msgbox msgTxt
</script>
<script language="VBScript">
msgTxt = "Service Area input value: " & "<%=serviceAreaTitle%>"
msgTxt = msgTxt & vbCrLf & "sSQL= " & "<%=sSQL%>"
msgbox msgTxt
</script>
<%
'????????????????????????????????????????????????? ?????????????????????????????????????????????????
' drbTest
'????????????????????????????????????????????????? ?????????????????????????????????????????????????
'dim rsSA, dbConn
On Error Resume Next
%>
<script language="VBScript">
msgbox "before ExecSQL"
</script>
<%
msg = "drb-d"
Call ExecSQL(sSQL,msg,true)
%>
<script language="VBScript">
msgTxt = "sSQL= " & "<%=sSQL%>"
msgTxt = msgTxt & "after ExecSQL"
msgbox msgTxt
</script>
<%
'????????????????????????????????????????????????? ?????????????????????????????????????????????????
' drbTest
'????????????????????????????????????????????????? ?????????????????????????????????????????????????
'set rsSA = dbConn.Execute(sSQL)
if Err.number <> 0 then
Response.write
"************************************************* ****************<br>"
Response.write " APPLICATION ERROR: " & Err.number & "<br>"
Response.write " Error description: [" & err.description & "]<br>"
Response.write
"************************************************* ****************<br>"
else
Response.write " INSERT Successful. Error number: " & Err.number
end If
'Response.Write "ENDING AFTER SQL EXECUTION"
'reponse.end
'response.write "strSA:" & strSA
'Response.Write "ran SQL"
if duplicate then
%>
<script language="javascript">
temp = "Duplicate Record1. ";
temp = temp + "This item already exists. ";
temp = temp + "Please review your values. ";
alert(temp);
history.go(-1)
</script>
<%
objConn.close
set objRS = nothing
end if
end sub
dim ErrsList, errLoop
'------------------------------------------------------------------
'Pre: datastore.asp need to be included to the same file as this one
' : sql statement for sql variable
' : error message for msg variable
' : True/False value for CloseInd variable
'Pro: sql statement executed or Erro msg pop up
'-------------------------------------------------------------------
Function ExecSQL(sql,msg,closeInd)
' The ind determines whether to close the connection or not.
set objConn = nothing
set objConn = server.createobject("ADODB.Connection")
objConn.Open strConnect
'Response.Write("sSQL = " & sSQL)
On Error Resume Next
set objRS = objConn.Execute(sql)
'Response.Write "Error" & Err.number & "<br>"
'Response.Write "Error" & Err.description & "<br>"
'Response.Write "Error" & Err.source & "<br>"
'Response.Write "Error" & Err.line & "<br>"
%>
<script language="VBscript">
'msgbox ("from execSQL: msg = " & "<%=msg%>" & vbCrLf & "SQL = " &
"<%=sql%>")
</script>
<%
'Response.Write "VB:" & vbobjecterror & "<br>"
if Err.number <> 0 then
if (err.number - vbobjecterror) = 3604 then
duplicate = true
else
%>
<script language="javascript">
temp = "<%=msg%>";
temp = temp + "Database Error (" + <%=err.number%> + "): " +
"<%=CSTR(err.description)%>" + ".";
temp = temp + " " + "<%=sSQL%>";
alert(temp);
// history.go(-1)
</script>
<%
end if
end if
if closeInd = true then
objConn.close
set objRS = nothing
end if
End Function
%>
<%
sub displayForm()
%>
<center><span class="hdrAr12">EITA SA, Env, Plat Update</span></center><br>
<Form action="SAEnvPlatUpd.asp?compMode=save" Method="post" name="Form_1">
<table name="tbl1" id="tbl1" border=0 align="center" width="98%">
<tr>
<!--------------Service Area--------------->
<th>
<font COLOR='navy'><b>Service Area:</b></font>
<BR>
<input type="text" name="SA_InputBox" Size="35" value="">
<BR>
<select name="SA_DropDown" onChange="displaySABox()">
<%for i = 0 to nRows %>
<option value="<%=(SAaarr(0,i))%>"><%=(SAaarr(1,i))%></option>
<%next%>
</select>
<input type="hidden" value="" name="SA_hidden">
</th>
<!--------------Environment----------------->
<th>
<font COLOR='navy'><b>Environment:</b></font>
<BR>
<input type="text" name="En_InputBox" Size="32.5" value="">
<BR>
<select name="En_DropDown" onClick="displayEnBox()">
<%for i = 0 to bRows%>
<option value="<%=(Env_arr(0,i))%>"><%=(Env_arr(1,i))%></option>
<%next%>
</select>
<input type="hidden" value="" name="En_hidden">
</th>
<!--------------Platform--------------------->
<th>
<font COLOR='navy'><b>Platform Area:</b></font>
<BR>
<input Name="PF_InputBox" Size="39" value="">
<BR>
<select name="PF_DropDown" onChange="displayPFBox()">
<%for i = 0 to cRows %>
<option value="<%=(PF_arr(0,i))%>"><%=(PF_arr(1,i))%> </option>
<%next%>
</select>
<input type="hidden" value="" name="PF_hidden">
</th>
</tr>
<tr>
<th>
</th>
<th>
<br>
<!--<center> <INPUT type="submit" name="SaveButton" Value="save/exit"
onClick="save()" >-->
<center>
<!--------------ZONK--------------------->
<INPUT type="submit" name="SaveButton" Value="save/exit"
onClick="javascript:Form_1.submit()" >
<INPUT type="button" name="btnCancel" value="Return"
onclick="exitPage()" accesskey="r"></input>
<!--<INPUT type="submit" onClick="Message_pop()" name="SaveButton"
Value="save/exit" >-->
</center>
</th>
</tr>
<!-- <input type="hidden" name="mode" value="GO"></input>
-->
</table>
</Form>
<%
end sub
%>
<script language="JavaScript">
function displaySABox()
{
var sel = document.Form_1.SA_DropDown;
var val = sel.options[sel.selectedIndex].value;
var txt = sel.options[sel.selectedIndex].text;
document.Form_1.SA_hidden.value = val;
document.Form_1.SA_InputBox.value = txt;
}
function displayEnBox()
{
var sel = document.Form_1.En_DropDown;
var val = sel.options[sel.selectedIndex].value;
var txt = sel.options[sel.selectedIndex].text;
document.Form_1.En_hidden.value = val;
document.Form_1.En_InputBox.value = txt;
}
function displayPFBox()
{
var sel = document.Form_1.PF_DropDown;
var val = sel.options[sel.selectedIndex].value;
var txt = sel.options[sel.selectedIndex].text;
document.Form_1.PF_hidden.value = val;
document.Form_1.PF_InputBox.value = txt;
}
function exitPage()
{
document.Form_1.action = "a_eita1.asp";
document.Form_1.submit();
}
</script>
</body>
</html>
