Link to home
Start Free TrialLog in
Avatar of fanfy
fanfy

asked on

SQL undefined

i have with the folllowing script when i m trying to addd to databse.


this code interface the user so as to enter the required data then submit to database.


 '<% Response.Buffer = True %>
 '<% If session("allow") = False Then Response.Redirect "main.asp" %>


<html>
<head>
<SCRIPT LANGUAGE="JavaScript">
<!--
floatX=1;
floatY=1;
layerwidth=2100;
layerheight=50;
align="left";
valign="top";
delayspeed=3;

// This script is copyright (c) Henrik Petersen, NetKontoret
// Feel free to use this script on your own pages as long as you do not change it.
// It is illegal to distribute the script as part of a tutorial / script archive.
// Updated version available at: http://www.echoecho.com/toolfloatinglayer.htm
// This comment and the 4 lines above may not be removed from the code.

NS6=false;
IE4=(document.all);
if (!IE4) {NS6=(document.getElementById);}
NS4=(document.layers);

function adjust() {
if ((NS4) || (NS6)) {
if (lastX==-1 || delayspeed==0)
{
lastX=window.pageXOffset + floatX;
lastY=window.pageYOffset + floatY;
}
else
{
dx=Math.abs(window.pageXOffset+floatX-lastX);
dy=Math.abs(window.pageYOffset+floatY-lastY);
d=Math.sqrt(dx*dx+dy*dy);
c=Math.round(d/10);
if (window.pageXOffset+floatX>lastX) {lastX=lastX+delayspeed+c;}
if (window.pageXOffset+floatX<lastX) {lastX=lastX-delayspeed-c;}
if (window.pageYOffset+floatY>lastY) {lastY=lastY+delayspeed+c;}
if (window.pageYOffset+floatY<lastY) {lastY=lastY-delayspeed-c;}
}
if (NS4){
document.layers['floatlayer'].pageX = lastX;
document.layers['floatlayer'].pageY = lastY;
}
if (NS6){
document.getElementById('floatlayer').style.left=lastX;
document.getElementById('floatlayer').style.top=lastY;
}
}
else if (IE4){
if (lastX==-1 || delayspeed==0)
{
lastX=document.body.scrollLeft + floatX;
lastY=document.body.scrollTop + floatY;
}
else
{
dx=Math.abs(document.body.scrollLeft+floatX-lastX);
dy=Math.abs(document.body.scrollTop+floatY-lastY);
d=Math.sqrt(dx*dx+dy*dy);
c=Math.round(d/10);
if (document.body.scrollLeft+floatX>lastX) {lastX=lastX+delayspeed+c;}
if (document.body.scrollLeft+floatX<lastX) {lastX=lastX-delayspeed-c;}
if (document.body.scrollTop+floatY>lastY) {lastY=lastY+delayspeed+c;}
if (document.body.scrollTop+floatY<lastY) {lastY=lastY-delayspeed-c;}
}
document.all['floatlayer'].style.posLeft = lastX;
document.all['floatlayer'].style.posTop = lastY;
}
setTimeout('adjust()',50);
}

function define()
{
if ((NS4) || (NS6))
{
if (align=="left") {floatX=ifloatX};
if (align=="right") {floatX=window.innerWidth-ifloatX-layerwidth-20};
if (align=="center") {floatX=Math.round((window.innerWidth-20)/2)-Math.round(layerwidth/2)};
if (valign=="top") {floatY=ifloatY};
if (valign=="bottom") {floatY=window.innerHeight-ifloatY-layerheight};
if (valign=="center") {floatY=Math.round((window.innerHeight-20)/2)-Math.round(layerheight/2)};
}
if (IE4)
{
if (align=="left") {floatX=ifloatX};
if (align=="right") {floatX=document.body.offsetWidth-ifloatX-layerwidth-20}
if (align=="center") {floatX=Math.round((document.body.offsetWidth-20)/2)-Math.round(layerwidth/2)}
if (valign=="top") {floatY=ifloatY};
if (valign=="bottom") {floatY=document.body.offsetHeight-ifloatY-layerheight}
if (valign=="center") {floatY=Math.round((document.body.offsetHeight-20)/2)-Math.round(layerheight/2)}
}
}
//-->
</script>

</head>
<SCRIPT Language=javascript> <!--

     function validity(MPayment){
              if (pula <= 0)
              alert("You entered wrong Principal and Deposit !Please try again")
      }
           
     function formatCurrency(num) {
            num = num.toString().replace(/\P|\,/g,'');
            if(isNaN(num))
            num = "0";
            sign = (num == (num = Math.abs(num)));
            num = Math.floor(num*100+0.50000000001);
            thebes = num%100;
            num = Math.floor(num/100).toString();
            if(thebes<10)
            thebes = "0" + thebes;
            for (var i = 0; i < Math.floor((num.length-(1+i))/3); i++)
            num = num.substring(0,num.length-(4*i+3))+','+
            num.substring(num.length-(4*i+3));
            return (((sign)?'':'-') + num + '.' + thebes);
                form.mortgage.value = validity(MPayment);
      }
      min=100;
      max=999;
   function GenerateLoanNumber() {
                //LoanNumber represents loan number generated
                 var str='LO';
                 var numb;
                 var loan;
                 numb = (Math.round((max-min) * Math.random() + min));
                 loan =str + numb;
                 return loan
                 
           }
      function FixedPayment(form) {
                //
            // P represents the loan amount
            // i Interest on the loan
            // n Number of years
            // q Number of payments per year
            var P = parseFloat(form.principal.value);
            var i //= parseFloat(form.interest.value)/100;
            var n = parseFloat(form.term.value);
            var d = parseFloat(form.deposit.value);
            var q = 12;
       var NPeriods;
            var Power;
            var Compound;
            var Divide;
            var MPayment;
                NPeriods = -(n * q);  
               
                if (P <= 20000){
                   i=5;  
                 } else
                if ((P > 20000) && (P <=150000)){
                 i=10.25
                 }else if ((P > 150000) && (P<=500000)){
                 i=15.75;
                }else
                 {
                  i=25.5;
                   }
            Power    = 1 + (i/q);
            Compound = Math.pow(Power, NPeriods);
            Divide   = q *(1 - Compound);
            MPayment  = (((P - d)* i)/Divide)/12;
                if (MPayment <= 0){
            alert("You entered wrong Principal and Deposit !Please try again")
            form.interest.value =0.00;
            }else
               form.LoanNumber.value = GenerateLoanNumber();
               form.MonthlyInstallment.value = formatCurrency(MPayment);
               form.interest.value =i;
             
      }
//--></SCRIPT>
<meta name="Microsoft Theme" content="frb_coff 011">
</head>
<BODY background = "CHEESE.jpg">
<!--mstheme--></font>
<form name="Armotize" method="Post"action="loanformation.asp">
<center><table width=600 border=0 cellspacing=0 cellpadding=0>
<tr><td valign=top width=440>
<!--mstheme--><font face="Arial, Arial, Helvetica">
<center>

<!--mstheme--></font>
<TABLE WIDTH=400 HEIGHT=400
BORDER="5" CELLSPACING="5" CELLPADDING="0" bordercolorlight="PaleGoldenrod" bordercolordark="Palegoldenrod">
<TR bgcolor=PaleGoldenrod text=Sienna >
<TD COLSPAN=2 HEIGHT=25 ALIGN=center>
<!--mstheme--><font face="Arial, Arial, Helvetica">
<FONT SIZE="+1" face=arial color="#000000">Personal Loan Calculator</FONT>
<!--mstheme--></font>
</TD></TR>
<TR>
<TD COLSPAN=2 HEIGHT=25 ALIGN=center><!--mstheme--><font face="Arial, Arial, Helvetica"><font color="#000000">Use this
tool to estimate your monthly payment for your Personal Loan. Simply enter the appropriate
  information in the BLAcK colored fields. Click the "Compute"
  button and your monthly payment will appear in the field below
  </font>
  <!--mstheme--></font>
</TD></TR>
<TR>
<TD WIDTH="48%" HEIGHT=25 ALIGN=right><!--mstheme--><font face="Arial, Arial, Helvetica"><font color="black">Customer ID#:</font>&nbsp;<!--mstheme--></font></TD>
<TD WIDTH="52%" bgcolor=PaleGoldenrod text=Sienna ALIGN=center><!--mstheme--><font face="Arial, Arial, Helvetica">&nbsp&nbsp&nbsp;
<INPUT NAME="CustID" TYPE="number"  ><!--mstheme--></font></TD></TR>

<TR>
<TD WIDTH="48%" HEIGHT=25 ALIGN=right><!--mstheme--><font face="Arial, Arial, Helvetica"><font color="black">First Name:</font>&nbsp;<!--mstheme--></font></TD>
<TD WIDTH="52%" bgcolor=PaleGoldenrod text=Sienna ALIGN=center><!--mstheme--><font face="Arial, Arial, Helvetica">&nbsp&nbsp&nbsp;
<INPUT NAME=txtFName TYPE=text-- SIZE=10 ><!--mstheme--></font></TD></TR>

<TR>
<TD WIDTH="48%" HEIGHT=25 ALIGN=right><!--mstheme--><font face="Arial, Arial, Helvetica"><font color="black">Loan#:</font>&nbsp;<!--mstheme--></font></TD>
<TD WIDTH="52%" bgcolor=PaleGoldenrod text=Sienna ALIGN=center><!--mstheme--><font face="Arial, Arial, Helvetica">&nbsp&nbsp&nbsp;
<INPUT NAME=LoanNumber TYPE=text SIZE=10 VALUE=><!--mstheme--></font></TD></TR>
<TR>
<TD WIDTH="48%" HEIGHT=25 ALIGN=right><!--mstheme--><font face="Arial, Arial, Helvetica"><font color="black">Principal Loan:</font>&nbsp;<!--mstheme--></font></TD>
<TD WIDTH="52%" bgcolor=PaleGoldenrod text=Sienna ALIGN=center><!--mstheme--><font face="Arial, Arial, Helvetica"><B>P</B>
<INPUT NAME=principal TYPE=currency SIZE=10 VALUE=0.00><!--mstheme--></font></TD></TR>
<TR>
<TD WIDTH="48%" HEIGHT=25 ALIGN=right><!--mstheme--><font face="Arial, Arial, Helvetica"><font color="black">Deposit:</font>&nbsp;<!--mstheme--></font></TD>
<TD WIDTH="52%" bgcolor=PaleGoldenrod text=Sienna ALIGN=center><!--mstheme--><font face="Arial, Arial, Helvetica"><B>P</B>
<INPUT NAME=deposit TYPE=text SIZE=10 VALUE=0.00><!--mstheme--></font></TD></TR>
<TR>
<TD HEIGHT=25 ALIGN=right><!--mstheme--><font face="Arial, Arial, Helvetica"><font color="black">Length of Loan:</font><font color="black">&nbsp;</font><!--mstheme--></font></TD>
<TD bgcolor=PaleGoldenrod text=Sienna  ALIGN=center>
<!--mstheme--><font face="Arial, Arial, Helvetica">
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<INPUT NAME=term TYPE=text SIZE=6 VALUE=0> <B>years</B><!--mstheme--></font></TD></TR>
<tr>
<TD HEIGHT=25 ALIGN=right><!--mstheme--><font face="Arial, Arial, Helvetica"><B><font color="black">*Monthly Installment:</font></B>&nbsp;<!--mstheme--></font></TD>
<TD bgcolor=PaleGoldenrod text=Sienna ALIGN=center><!--mstheme--><font face="Arial, Arial, Helvetica"><B>P</B>
<INPUT NAME=MonthlyInstallment TYPE=currency SIZE=10 VALUE=><!--mstheme--></font></TD>
<TR>
<TD HEIGHT=25 ALIGN=right><!--mstheme--><font face="Arial, Arial, Helvetica"><font color="black">Interest Rate:&nbsp;</font><!--mstheme--></font></TD>
<TD bgcolor=PaleGoldenrod text=Sienna  ALIGN=center>
<!--mstheme--><font face="Arial, Arial, Helvetica">
&nbsp;
<INPUT NAME=interest TYPE=text SIZE=6 VALUE=> <B>%</B><!--mstheme--></font></TD></TR>

<TR>
<TD COLSPAN="2" HEIGHT="30" ALIGN="CENTER"><!--mstheme--><font face="Arial, Arial, Helvetica">&nbsp;
<INPUT TYPE="button" VALUE="Compute" onClick="FixedPayment(this.form)">
<INPUT TYPE="reset" NAME="reset" VALUE="Clear Fields">
<input type="submit" name="btnSearch" value="Add">
  <!--mstheme--></font>
</TD></TR>      </TABLE></center>
<!--mstheme--><font face="Arial, Arial, Helvetica">
</FORM>
</center>
<!--mstheme--></font>
</td></tr></table></center>
<!--mstheme--><font face="Arial, Arial, Helvetica"><!--mstheme--></font>
<table width=600 border=0 cellspacing=4 cellpadding=0>
<tr><td>
<!--mstheme--><font face="Arial, Arial, Helvetica">
<font face=arial size="-2" color="#000000">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
*This amount does not include payments for taxes or loan insurance.</font>
</center>
  <!--mstheme--></font>
</td></tr></table>
<!--mstheme--><font face="Arial, Arial, Helvetica"><!--mstheme--></font>
<CENTER>


</form>

<script>
if (NS4) {document.write('<LAYER NAME="floatlayer" LEFT="'+floatX+'" TOP="'+floatY+'">');}
if ((IE4) || (NS6)) {document.write('<div id="floatlayer" style="position:absolute; left:'+floatX+'; top:'+floatY+';">');}
</script>
<table bgcolor="#CCCCCC"   width="775" border="1">
<tr><td bgcolor="#FFFFCC" align="center"><b><font size="+1">PERSONAL LOAN DETERMINING SYSTEM MENU</font></b></td></tr>
<tr><td>
<b><font size="-1">
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp;:: <a href="form.asp">Home Page</a>&nbsp;
&nbsp&nbsp&nbsp;:: <a href="http://Test/200003674/project/form.asp">Login</a>&nbsp;
&nbsp;:: <a href="form.asp">New User</a>&nbsp;
&nbsp;:: <a href="http://Test/200003674/project/loanform.html">Loan Application</a>&nbsp;
&nbsp;:: <a href="form.asp">Search</a>&nbsp;
&nbsp;:: <a href="form.asp">Loan Status</a>&nbsp;
&nbsp;:: <a href="form.asp">Administrator</a>&nbsp;
&nbsp;:: <a href="form.asp">Balance Statement</a>&nbsp;
</font>

</td></tr>
</table><script>
if (NS4)
{
document.write('</LAYER>');
}
if ((IE4) || (NS6))
{
document.write('</DIV>');
}
ifloatX=floatX;
ifloatY=floatY;
define();
window.onresize=define;
lastX=-1;
lastY=-1;
adjust();
</script>
</body>
</html>


The issue is that i have to check whether the customer id # exists in blacklisted database(table) which stores all customers who failed to pay back the loan , before he/she can apply for loan.The following extract gives a headache since when i try executes (add to database) the following error message is dsiplayed.


"Error Type:
Microsoft VBScript runtime (0x800A01F4)
Variable is undefined: 'SQL'
/200003674/project/loanformation.asp, line 24"

as in the following code



<%@ Language=VBScript %>
<% Option Explicit %>
<!--#include file="adovbs.inc."-->
<html>

<BODY background = "images/CHEESE.jpg">

<%
Dim MyConn,RS,strCustID,txtFName,strFName,strLoanNumber,strPrincipal, strDeposit,strInterest,strmortgage,strterm,strMonthlyInstallment



'grab the form contents
strCustID=Request.Form("CustID")
strFName=Trim(Request.Form("txtFName"))
strLoanNumber = Request.Form("LoanNumber")
strPrincipal = Request.Form("Principal")
strDeposit = Request.Form("Deposit")
strterm = Request.Form("Term")
strInterest = Request.Form("Interest")
strMonthlyInstallment = Request.Form("MonthlyInstallment")

SQL = "SELECT CustID FROM Customers,Blacklisted "
Set RS = MyConn.Execute(SQL)


if (strCustID <> "Blacklisted.CustID") Then

Set MyConn=Server.CreateObject("ADODB.Connection")
Set RS=Server.CreateObject("ADODB.RecordSet")

'since you are working with the RecordSet you need to create an instance of
'the RecordSet Object
'Set RS2=Server.CreateObject("ADODB.RecordSet")
'Set RS3=Server.CreateObject("ADODB.RecordSet")
'in this case, since we're adding to two separate tables we need to create
'two instances of the RecordSet Object

MyConn.Open "PLDSloans"
'getdata is your DSN (data source name) you created through ODBC
RS.Open "Select * From Loans", MyConn, adOpenDynamic, adLockPessimistic, adCMDText
'open both RecordSets

'SQL = "SELECT * FROM Customerss,Blacklisted "

'While Not RS.EOF
'If Customers.CustID <> "Blacklisted.CustID" Then

RS.AddNew
RS("LoanNumber")= strLoanNumber
RS("CustID")=strCustID
RS("Principal")= strPrincipal
RS("Deposit")= strDeposit
RS("Duration")= strterm
RS("Interest")= strInterest
RS("MonthlyInstallment")= strMonthlyInstallment
RS.Update
'Update the first RecordSet


Response.Write "<hr>"
Response.Write "<center><h3><b>CONFIRMATION LETTER</b></h3></center>"
Response.Write"<br></br><br></br>"
Response.Write "<b>To Our Dear Customer</b><br>"
Response.Write "<b>NAME: Mr/Mrs/Miss  </b>"&strFName&"<br>"
Response.Write "<b>CUSTOMER ID #:</b>"&strCustID&"<br>"
Response.Write "<br>"
Response.Write "This serves to inform you that you application is succesful, therefore you are expected to comply with the following payments in relation to momey you borrowed "
Response.Write "You are liable to pay a Monthly Payment of about :P"&strMonthlyInstallment&". You are also reminded that you should note  down cause you are expectted ton use it every time you want to view your Loan status"
Response.Write" starting from the end of next month .The following are extracted from your Loan Application form<br>"
Response.Write "<br></br>"
Response.Write "<b>Monthly Payment : P</b>"&strMonthlyInstallment&"<br>"
Response.Write "<b>Principal :       P  </b>"&strPrincipal&"<br>"
Response.Write "<b>Deposit :         P</b>"&strDeposit&"<br>"
Response.Write "<b>Duration :       </b>"&strterm&" Year(s)<br>"
Response.Write "<b>Loan Number :     </b>"&strLoanNumber&"<br>"
Response.Write "<br></br>"
Response.Write "If you have any queries with above outlined statements please forward your <font color=red><u>QUERIES</u></font> so that they can be analysed "
Response.Write "<br></br>"
Response.Write "<br></br>"
Response.Write "<b>From  Fanaferian Mogapi </b><br> "
Response.Write "<b>Your Financial Advisor</b><br>"
Response.Write "<b>Personal Loan Determinibg System<b>"  
Response.Write "<hr>"
'Clean up
RS.Close
MyConn.Close
Set RS = Nothing
Set MyConn = Nothing

Else
Response.Write"yuo are not liable to be lopaned since u still owe"
End If
'Wend
%>

 </body>
</html>







     

 







































ASKER CERTIFIED SOLUTION
Avatar of Chandramouli k
Chandramouli k
Flag of India image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Chandramouli k
Chandramouli k
Flag of India image
sorry experts, for locking this question.
fanfy, for variable not defined error, and the above is the solution u r looking for, then to be honest this question is not worth of more than 25 points.

KCM
Avatar of robbert
robbert
Cause identified.
Avatar of hongjun
hongjun
Flag of Singapore image
This question appears to have been abandoned. Please come back to this question and do the necessary evaluation or feedback. I will invite a Moderator to close this question if there's no reply from you after seven days.

I suggest
<points to kcm76>

** PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER **

hongjun
Avatar of modulo
modulo
Force accepted as proposed

modulo

Community Support Moderator
Experts Exchange