|
| |
|
|
evansforsyth
Posts: 277
Joined: 12/11/2001
From: Grande Prairie, Alberta, Canada
Status: offline
 |
Creating a number of records - 1/14/2008 2:06:10
Has anyone found a way???
We have a "Customer" table with about 20 records in it. Once a month we need to record the customer payments.
I've tried to use the "Customer" table to populate a "Payment" table, once a month. Then this "Payment" table can be used to record payments.
The "Customer" table supplies the "CustomerID" and the only other information needed in the new records is a date, like "2008 01" (text).
I've spent quite a bit of time on this--but the results are so scrappy that I won't bore you with the code that didn't work!
Is there a technique?
Thanks!
Evans
_____________________________
Over the hill? What hill, where, when?
I don't remember any hill!
|
|
|
|
bernieboy31
Posts: 79
Joined: 3/5/2005
From: London, United Kingdom
Status: offline
|
RE: Creating a number of records - 1/14/2008 12:20:04
Initial Thoughts...
When you need to record a payment use a "drop down " from the 'customer' table to select the "customer ID" and add whatever payment fields you require to a form. This can then be a new entry your 'payment' table.
You could also automatically add a 'mmm yy' field in the 'payment' table so you can filter by month and so only need one 'payment' table - This will also give you historical records.
Or have I missed the point entirely??
HTH
BB
| |
|
|
|
evansforsyth
Posts: 277
Joined: 12/11/2001
From: Grande Prairie, Alberta, Canada
Status: offline
|
RE: Creating a number of records - 1/18/2008 23:51:43
Hi BB--my explanation was poor. Here goes again, with the progress made recently.
1. A form is used to send data, for a number of records, to an .asp page.
2. On the .asp page, a Spooky DRW has been created. This Spooky works just fine outside of the loop. Inside the loop it fails.
<Do While starts>
<Variables collected by Request.Form()>
<INSERT INTO Spooky needs to be here>
<Loop ends>
The following error message pops up:
--------------------------
Microsoft VBScript compilation error '800a03ea'
Syntax error
/_fpclass/fpdblib.inc, line 3
Sub FP_SetLocaleForPage
^
--------------------------
However, that Spooky works just fine outside of the loop!
Here is the Spooky code:
--------------------------
<table border="0" cellpadding="10" style="border-collapse: collapse" width="100%" id="table10">
<tr>
<td>
<table id="table11">
<thead>
</thead>
<tbody>
<!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="INSERT INTO Payments (ID_From_Rental_Homes, jsPmtMonth) VALUES ("&ID_From_Rental_Homes&" , '"&jsPmtMonth&"')"
fp_sDefault=""
fp_sNoRecords="<tr><td colspan=16 align=""LEFT"" width=""100%"">Database updated--Spooky here!</td></tr>"
fp_sDataConn="RentalHomes_V2"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&"
fp_iDisplayCols=16
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<!--#include file="_fpclass/fpdbrgn2.inc"-->
</tbody>
</table>
<!--webbot bot="PurpleText" PREVIEW="Spooky here but not working--needs to be here! (Or other solution needed.)" --></td>
</tr>
</table>
-----------------------
Any suggestions? Anyone! Please!!
Thanks in advance!
Evans
_____________________________
Over the hill? What hill, where, when?
I don't remember any hill!
| |
|
|
|
rdouglass
Posts: 9186
From: Biddeford, ME USA
Status: offline
|
RE: Creating a number of records - 1/19/2008 11:15:21
quote:
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<!--#include file="_fpclass/fpdbrgn2.inc"-->
The problem with nesting the DRW in a loop like that is in the include lines like that. You end up including them multiple times and that's a no-no.
I personally don't know how to do it with the DRW and I think it's going to be a struggle using it. Using 'straight ASP' is not trivial if you're not up on ASP. However you must absolutely be able to idenify a specific row and cell of the table instead of all name fields being "Name".
What I do is to basically include the record ID somehow in each row; let's make it hidden.
<input type=hidden name=ID value="<%theRecordsetID%>">
Then I'd make the payment field something like this as well.
<input type=text name="Payment<%theRecordsetID%>" >
See what I did? Now I have payment fields named "Payment1", Payment238", Payment94", etc. based on the record ID. That make sense? Good.
So now if I post the table (inside a form of course), I can now collect all the ID's of the records I want to add payments to by simply Request.Form("ID"). That will give me a comma separated list of ID's like "1,238,94".
Now that I have a list of ID's, I can separate them into an array like so:
myIDArray = split(Request.Form("ID"),",")
I now have each ID separated into myID array. So as a rule, I always clean up a numerical array like so to remove spaces:
FOR i = 0 TO ubound(myIDArray)
myIDArray(i) = trim(myIDArray(i) & "")
NEXT
OK, so now that we have a clean array of ID's, we can generate a new INSERT line for each one like this:
FOR i = 0 TO ubound(myIDArray)
mySQL = "INSERT INTO Payments (ID_From_Rental_Homes, jsPmtMonth) VALUES (" & myIDArray(i) & " , '" & Request.Form("Payment" & myArray(i)) & "')"
NEXT
That make sense? The bold area is how I distinguish which payment with which ID.
Now it's an exercise of opening the database, loop thru and execute each of the INSERT statements, and close the DB. Something like this maybe:
<%
myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("/fpdb/mydatabase.mdb")
myIDArray = split(Request.Form("ID"),",")
set conntemp=server.createobject("adodb.connection")
conntemp.open myDSN
FOR i = 0 TO ubound(myIDArray)
myIDArray(i) = trim(myIDArray(i) & "")
IF trim(Request.Form("Payment" & myArray(i)) & "") > "" Then
mySQL = "INSERT INTO Payments (ID_From_Rental_Homes, jsPmtMonth) VALUES (" & myIDArray(i) & " , '" & trim(Request.Form("Payment" & myArray(i)) & "") & "')"
conntemp.execute(mySQL)
End IF
NEXT
conntemp.close
%>
See what I'm doing? I didn't check the specific syntax but it should be close. Hope it helps
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
evansforsyth
Posts: 277
Joined: 12/11/2001
From: Grande Prairie, Alberta, Canada
Status: offline
|
RE: Creating a number of records - 1/21/2008 22:26:14
All Hail rdouglass!
All Hail Spooky!
Here is the solution, thanks to our omnicient ones!
<%
myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("/fpdb/database_file_name.mdb")
set conntemp=server.createobject("adodb.connection")
conntemp.open myDSN
mySQL = "INSERT INTO Payments (ID_From_Rental_Homes, jsPmtMonth) VALUES ("&ID_From_Rental_Homes&" , '"&jsPmtMonth&"')"
conntemp.execute(mySQL),,129
conntemp.close
%>
Some additional comments for others working on databases that are Spooky and beyond:
Remember to use [ ] for field names--when a reserved word is used for a field name.
For numeric values: "&ID&"
For text values: '"&Input&"' (note two types of quote marks)
For date: #"&dateInput&"#
Again, thanks!
Evans
< Message edited by evansforsyth -- 1/23/2008 23:22:07 >
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
