SQL/ASP Insert Data Confusion

[cconlin]

Recently, I picked up where another developer left off on a project. I am quite new to SQL and I have only been working with ASP for a few months. I have altered a table (bank_info) to include a 'Monthly' column to store a monthly payment calculated by the ASP code on one particular page, bankaccount.asp Now, I need to get the information about the payment (which is just a mathematical equation using variables) to the SQL table. This is my code:

This is the code that determines what the monthly payment will be. I need to get the information from the variable "Payment" to my column "Monthly" However, I don't really understand the previous developers' code for inserting data.

Dim TotalPayment,Total,Principal,Interest,Total1,Length,Payment,Time
	TotalPayment=0
	Total=0
	Principal=0
	Interest=0
	Total1=0
	Length=0
	Payment=0
	Time=0
	strSQL="Select Current_Balance  from creditor_info where Login='" & Login & "'"
	rsSQL=ExecuteSelect(strSQL)
	if isArray(rsSQL) then
		for i = 0 to ubound(rsSQL,2)
			Total=cdbl(Total) + cdbl(rsSQL(0,i))		
		next
	end if	
	Principal=(cdbl (Total)*.025)
	Interest=(cdbl (Principal)*.15)
	Total1=(cdbl(Principal)+(Interest))
	TotalPayment=(cdbl(Total1)+10+30)
	Length=(cdbl(Total)/(TotalPayment))
	Payment=CInt(TotalPayment)
	Time=CInt(Length)

This is the code that writes the current information to the table. It is at the top of my ASP page:

<%
	Login=Session(&quot;Login&quot;)
	strSQL=&quot;SELECT ID FROM creditor_info WHERE Login='&quot; & Login & &quot;'&quot;		
	rsSQL=ExecuteSelect(strSQL)
	If isArray(rsSQL) Then
		strSQL=&quot;SELECT ID FROM bank_info WHERE Login='&quot; & Login & &quot;'&quot;		
		rsSQL=ExecuteSelect(strSQL)
		if isArray(rsSQL) then
			ID=rsSQL(0,0)
		end if		
	
		dim msg
		If Request.Form(&quot;flag&quot;)=&quot;1&quot; Then
			Name=request.form(&quot;Name&quot;)
			BankName=request.form(&quot;BankName&quot;)
			BankAccountNumber=request.form(&quot;BankAccountNumber&quot;)
			SwiftCode=request.form(&quot;SwiftCode&quot;)
			BankAddress=request.form(&quot;BankAddress&quot;)
			BankAddress2=request.form(&quot;BankAddress2&quot;)
			BankCity=request.form(&quot;BankCity&quot;)
			BankState=request.form(&quot;BankState&quot;)
			BankZip=request.form(&quot;BankZip&quot;)
			TotalPayment=Request.Form (&quot;TotalPayment&quot;)
	
				strSQL = &quot;INSERT INTO bank_info (Login, Name,Bank_Name,Bank_Account_Number,Swift_Code,Bank_Address,&quot;
				strSQL = strSQL & &quot;Bank_Address2,Bank_City,Bank_State,Bank_Zip) VALUES('&quot;& Login & &quot;','&quot; & Name & &quot;','&quot; & BankName & &quot;','&quot; & BankAccountNumber & &quot;','&quot; & SwiftCode & &quot;','&quot; & BankAddress & &quot;','&quot; & BankAddress2 & &quot;','&quot; & BankCity & &quot;','&quot; & BankState & &quot;',&quot; 
				
				If IsNull(BankZip) Or BankZip = &quot;&quot; then
					strSQL = strSQL & &quot;NULL)&quot;
				Else
					strSQL = strSQL & BankZip & &quot;)&quot;
				End If	
				ExecuteInsert(strSQL)
				msg=&quot;Thank you for choosing Do-it-Yourself Debt Consolidation.<br><br>To view your estimated monthly payment, <a href='bankaccount.asp'>Click here</a>.&quot;
	
		End If	
		If ID<>&quot;&quot; then
			strSQL=&quot;SELECT * FROM bank_info WHERE ID='&quot; & ID & &quot;'&quot;
			rsSQL=ExecuteSelect(strSQL)
			Login=rsSQL(1,0)
			Name=rsSQL(2,0)
			BankName=rsSQL(3,0) 
			BankAccountNumber=rsSQL(4,0) 
			SwiftCode=rsSQL(5,0) 
			BankAddress=rsSQL(6,0)     
			BankAddress2=rsSQL(7,0)     
			BankCity=rsSQL(8,0)        
			BankState=rsSQL(9,0)       
			BankZip=rsSQL(10,0)
			
		end if           
%>

I just need to insert the data from the variable "Payment" into the column "Monthly" If someone could please help me I would appreciate it so much! Thanks!!

Chris

 

[bperry]

>>I have altered a table (bank_info)
>>to include a 'Monthly' column

Hi there:
The general format of the Insert statement will be:
[tt]
INSERT bank_info
(Col1Name, Col2Name, Col3Name, ...)
VALUES (Col1Val, Col2Val, Col3Val....)
[/tt]

The relevant section in that code where the Insert statement is found about 2/3 down the code is:
strSQL = "INSERT INTO bank_info (Login, ....

So that's what you need to modify. We want to insert the new column name (Monthly), and we want to tell it where to get the value (Payment).

I'll take a crack at it for you, but these are tricky with commas, apos, and such: give it a try and you will get it.
Find these sections in your code and make the changes:

strSQL = "INSERT INTO bank_info (Monthly, Login,

VALUES('" & Payment & ",'" & Login & "','" & Name & "','" &


Like I said, it's just a little tricky.
In the INSERT statement, the columns don't have to be in the same order as they actually appear in the table. (Insert will figure out where they go). I usually like to put them in the order they do appear, however, just out of habit. In this case I put the new Monthly column right at the front of the Insert as the first column, just because it was easiest to see how it works. If you want to put the new column farther down in the Insert, go right ahead.

If Monthly was not numeric, it would have needed single quotes around the values, like some of those other column values have.

Hopefully that will do it for you,
bperry

 

[bperry]

I'm not sure the syntax on the second change, for the VALUES section, is correct. I think there might be an extra apos. If it doesn't seem to compile, try this:

VALUES(" & Payment & ",'" & Login & "','" & Name & "','" &

See what I mean about being a little tricky.

 

[cconlin]

I will give that a shot! Thank you so much for taking valuable time to answer such a basic question. I really do appreciate your kindness!