Solved

SQL undefined

Posted on 2002-04-14
5
523 Views
Last Modified: 2012-06-22
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>







     

 







































0
Comment
Question by:fanfy
5 Comments
 
LVL 5

Accepted Solution

by:
kcm76 earned 230 total points
ID: 6940165
when u use
<% Option Explicit %> u have to declare variables using dim keyword

try
dim sql

KCM
0
 
LVL 5

Expert Comment

by:kcm76
ID: 6940172
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
0
 
LVL 15

Expert Comment

by:robbert
ID: 6951759
Cause identified.
0
 
LVL 33

Expert Comment

by:hongjun
ID: 7402333
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
0
 

Expert Comment

by:modulo
ID: 7428916
Force accepted as proposed

modulo

Community Support Moderator
Experts Exchange
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now