April 23, 2003 at 6:41 am
hi whoteegan!
I think it is more complicated than that.
Sorry I didn't let you guys in in all of the tables involved.
The witness table relates to the accident main table. So does the passenger table, the driver table and injured table.
I can provide full schematic representation.
April 23, 2003 at 6:49 am
In that case you would have to do multiple different returns (to avoid dups)
In the case of my SQL statement sample could you just add WHERE accidentId = n??
This way you could then do your load of all single entity data and then load your witnesses.
AJ Ahrens
SQL DBA
Revenue Assurance Management - AT&T
Good Hunting!
AJ Ahrens
webmaster@kritter.net
April 24, 2003 at 12:28 am
simflex,
I actually tried to help you with this in my previous post :-
SELECT 1 AS Recorset_Id, *
FROM table1
WHERE IncidentId = @l_Parm_Incident_Id
SELECT 2 AS Recorset_Id, *
FROM table2
WHERE IncidentId = @l_Parm_Incident_Id
SELECT 3 AS Recorset_Id, *
FROM table3
WHERE IncidentId = @l_Parm_Incident_Id
And so on....
Then in your ASP Code you can interpret the Recordset_Id field to determine which set of data you are processing.
(ASP Code follows)
Select Case rst_Data("Recordset_Id")
Case 1
*** Process Recordset 1 Data
Case 2
*** Process Recordset 2 Data
Case 3
*** Process Recordset 3 Data
End Select
Put the Select statement in a stored procedure and call it passing a single parameter of IncidentId. This will allow you to have multi-recordset object with all your data in it in one go. You then iterate through all the Recordsets processing each section based on the Recordset_Id.
The bit I think you may be missing (apologies if you are not) If your ASP contains a form then have the action set to the same ASP page. The idea behind this is simple, the same page can be used to display or provide data input dependent on parameters you pass to the page.
For example, if your page is called Incident.asp you could have it recognise two parameters :-
mode - 0-View, 1-Insert, 2-Update
incid - Incident Id
Hence, calling your page with :-
Incident.asp?mode=2&incid=10223
Would result in the details of Incident Id 10223 being displayed for update purposes. Or,
Incident.asp?mode=1
Should result in a blank page being displayed ready for the Insert of a new Incident.
In order to make your ASP page re-entrant just have a hidden field, that gets set the first time it is displayed. You can then interoogate this hidden field to see if it is the first or second time the form has been displayed. This allows you to perform server side validation.
If the same page is used for the display, validation and update it will make your life a whole lot simpler, especially if you use stored procedures to do most of the DB work for ya.
Remember, you can build your form dynamically using the Response.Write() method. You don't have to have it all coded as HTML. eg.
<form id="frmIncident" action="Incident.asp">
<input id="txt_Entry_Count" value="<% = l_Entry_Count %>">
<% = VB_Write_Form_Section_1 %>
<% = VB_Write_Form_Section_2 %>
<% = VB_Write_Form_Section_3 %>
<% = VB_Write_Form_Section_4 %>
</form>
Within the asp code for VB_Write_Form_Section_1-4 you could construct the form elements appropriately and either put the values you have read from the database or display empty ones during insert processing.
Here is a block of code I use for almost all my form processing, and believe me there are hundreds of 'em :-
'#
'### Determine the number of attempts to enter the page
'#
l_Entry_Count = CInt(Request.Form("txt_Entry_Count"))
'#
'### Validate the Form Contents after the first Attempt
'#
if l_Entry_Count > 0 Then
If gl_Update_Mode = lk_MAINT_MODE_INSERT _
Or gl_Update_Mode = lk_MAINT_MODE_UPDATE _
Or gl_Update_Mode = lk_MAINT_MODE_DELETE _
Or gl_Update_Mode = lk_MAINT_MODE_DELETE_PHYSICAL Then
'#
'### Validate the Deduction Definition Form Data
'#
If gl_Update_Mode = lk_MAINT_MODE_INSERT _
Or gl_Update_Mode = lk_MAINT_MODE_UPDATE Then
'#
'### Store the Request Form Data
'#
VB_Store_Request_Form_Data
'#
'### Validate the Form Data
'#
b_Data_Valid = VB_Valid_Form()
Else
'#
'### Get the Data Details and Store in the Form
'#
VB_Retrieve_Data_Def gs_Data_Id, gl_Update_Mode
'#
'### Delete Operation is Assumed to be OK
'#
b_Data_Valid = True
End If
'#
'### Provided the Details are valid continiue
'#
If b_Data_Valid Then
'#
'### Set the Page to expire immediately
'#
Response.Expires = 0
'#
'### Default the Data Id during Insertion
'#
If gl_Update_Mode = lk_MAINT_MODE_INSERT Then
gs_Frm_Data_Id = 0
End If
'#
'### Apply the Update to the Data identified by the Form data into the WWW Database
'#
l_Return = VB_Update_Data_Def(gl_Update_Mode, gs_Frm_Data_Id)
'#
'### Verify successful Update of the Data
'#
If l_Return = 0 Then
'#
'### E:Mail the Data Definition changes to the Powers that be
'#
VB_EMail_Audit gl_Update_Mode
'#
'### Redirect User to Data Confirmation Page
'#
Response.Clear
Response.Redirect "Data_Confirm_Update.asp?Mode=" & gl_Update_Mode & "&" & sk_QS_KEY_DATA_ID & "=" & gs_Frm_Data_Id
End If
End If
Else
'#
'### Redirect Deduction to the Search Page
'#
Response.Clear
Response.Redirect "Data_List.asp"
End If
Else
'#
'### Retrieve the definition if an Id has been supplied
'#
If Len(gs_Data_Id) <> 0 Then
'#
'### Get the Deduction Definition Details and Store in the Form
'#
VB_Retrieve_Data_Def gs_Data_Id, gl_Update_Mode
End if
End If
'#
'### Increment the Entry Count
'#
l_Entry_Count = l_Entry_Count + 1
Hopefully you can get the gist from this code snippet.
Some things to note.
I generally load all the data from the database into variables.
If you have undetermined number of rows to display in the form, just use arrays and UBOUNDS - carefully.
When reading the form data load it back into the same variables as if you had just read it from the database, that means the update code only ever refers to the page local variables.
Blah...Blah...And so on.
Again, I hope I am not trying to teach you to suck eggs.
April 25, 2003 at 11:21 am
Thanks to all of you who have shown interest in helping me get through this.
Here is where my biggest need and biggest problem lie right now.
I have a total of 14 tables that need to be joined in some way to produce a screen similar to what I have here.
This is the display screen for this web app:
http://www26.brinkster.com/simflex/kenig/accid_display.asp
These tables are accidentEvent(main table).
This table joins CountyDriver, OtherDriver,Passengers ,Witnesses,Poice,CountyVehicle, OtherVehicle( by AccidentEventID);
CountyDriver table joins CountyInjuredDriver table by DriverID, and Dept table by DeptID;
OtherDriver table joins OtherDriverInjured by DriverID and Dept table by DeptID;
Passenger table joins injuredPassenger table by PassengerID.
What I have tried so far has not worked.
Can you please help with joining these tables to produce a result similar to above based on information I have provided.
I can certainly provide more.
Thanks again to all of you for the immense help.
April 25, 2003 at 11:46 pm
Simflex,
Your form is getting longer and longer and longer and longer and longer etc.....
You may be better off re-thinking your interface. I would.
It is not logical to have one form hold all the details. You would be far better off breaking it down into sections. You could build a fairly neat interface to facilitate the capture of the details to each section. It certainly would make it easier to code.
Think about wizards and the way they are used to guide a user through a setup of something with next and previous buttons. It would not be rocket science to emulate this on the web.
Just go to Insurance Quote form where there is a mass of information being requested, but you are gently guided through the questions, with the ability to jump between sections. One of the beauties of doing it this way are that if you have a breakdown in communication of some sort, the user will only possibly lose the section they are currently completing.
Imagine the scenario where a user gets all the way to the bottom and all of a sudden there is a Server hiccup, or the Client locks. How pissed would they be having compltede all of your 435 fields, only to have lost them.
Also, on the form there seems to be very little use of select lists of check boxes. Especially for yes and no type answers. You really need to get away from giving a user the chance of entering such simple items of information into free-format inputs. You are making a rod for your own back.
It may seem like a daunting task, but you will find that many of your problems about how to retrieve the information will all be answered by re-designing your interface. What you appear to have done is come up with a db table design and then tried to dump all the data into the tables in one go from one form.
There is a term ofetn used in database and application development - Checkpoint Restartability.
By using sections you have sections at which you can restart if ever there is a problem.
Look at the dialog used in Word or in IE when defining the Options. It's called a Tabbed Dialog. You can easily do a similar thing for your form.
Think about how this type of data is currently captured in a paper based system. There are sections. Each section follows on from each other. Some sections may be omitted where there is no necessity for it to be completed. And it definitely will be on more than one page. It won't be on a dead-sea-scroll.
Think about putting question leaders on such as "Were other passengers involved in the accident" with a checkbox ticked yes or no. If it's checked display a dropdown or input which allows the user to specify "How Many". This can then be used to control how many passenger details are entered. Similarily, "Were there other vehicles involved", "If so how many" and so on.
By saving the details for each section as they are entered the user has some recovery if the page dies. Remember, if it can happen it definitely will. The first section must always be completed before moving onto other sections because it is at that part the AccidentEventID is generated. This could help keep all the sections linked. Only at the final stage could you ask the user to confirm all the details as being true and complete and then update a status field on the main table to indicate that all sections have been completed. eg. Accident_Event_Completion_Status with possible values :-
0-Base Accident Event Details Established
1-Accident Event Sections Being Entered
2-Accident Event Details Completed
And then internally you could have other processing status for :-
3-Accident Event Verified by assessor
4-Claim processing Requested
5-Claim Rejected
Blah....Blah....
Personally I would have a few Status type fields on each of the tables to assist in the ongoing processing of the Accident/Event.
You have applied the classic data-processing approach to your problem domain. Solve the problem in one go. This is wrong. Break the problem down into smaller parts and then you will find each part much easier to code, it will be more robust, more intuitive and easier to solve the problems when and if they occur.
Apologies for length of response. Still pissed from last nights Guinness infusion and have probably got verbal diarrhoea.
If you need more help and since this is going way outside of the scope of this forum email me direct on kiss{at}isb21{dot}com.
April 26, 2003 at 6:18 am
hi!
Hope you have fully recoverd from your guiness infusion.
I truly understand and agree with everything you have said so far but just a few things and if they make sense to you, I can communicate with you outside of this forum.
The forum you looked, is NOT the input form.
This is the display form.
The original form is still there and will be used as an input for.
The client wants there form to display records like I have it right now.
I didn't choose to do it that way.
We are currently using sql server to code this project.
So the form that allows the user the ability to create and store records in the database is the original form.
The client wants record displayed in this format and since I don't know crystal report, and since we are not using oracle reports, I have to mimick a true form.
Like I indicated earlier, I need a multiple table joined select statements that will pull all data that have been entered and displayed them on the form you are looking at.
No data will be entered into this form, it is to display records entered in the other form.
Let know if this makes sense and if there is a better way of displaying the records.
thanks again for taking the time to help.
April 26, 2003 at 12:35 pm
simflex,
Right, here goes. This is how I do more or less what you are wanting to do, ie. present data from multiple queries on a form/report.
Below is the sp that I execute to create a recordset containing all the data I want to present from disparate sources:-
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE [dbo].[usp_Resource_Deduction_Statement_Details]
@l_parm_resource_idudt_resource_id
,@l_parm_deduction_control_idudt_deduction_control_id
AS
-- ###############################################################################
-- ### Function- Blah...Blah....
-- ###
-- ### Input- See parameter list above
-- ###
-- ### Process- Blah...Blah....
-- ###
-- ### Output- Multiple Recordsets
-- ###
-- ### Special - None
-- ###
-- ###############################################################################
-- ### M o d i f i c a t i o n s
-- ###############################################################################
-- ### Initials | Date | Description
-- ###############################################################################
-- ### SWJS | 02/09/02 | Procedure Created
-- ### SWJS | 03/04/03 | Amended order on Deduction List
-- ###############################################################################
SET NOCOUNT ON
SELECT 0 AS Recordset_Id, *
FROM view_fuel_charge_list
WHERE resource_id = @l_parm_resource_id
ANDdeduction_control_id = @l_parm_deduction_control_id
ORDER BY fuel_invoice_no, fuel_charge_txn_date
SELECT 1 AS Recordset_Id, *
FROM view_phone_charge_recovery_list
WHERE resource_id = @l_parm_resource_id
ANDdeduction_control_id = @l_parm_deduction_control_id
ORDER BY phone_no
SELECT 2 AS Recordset_Id, *
FROM view_fine_list
WHERE fine_resource_id = @l_parm_resource_id
ANDdeduction_control_id = @l_parm_deduction_control_id
ORDER BY fine_issuer_name, fine_issue_date
SELECT 3 AS Recordset_Id, *
FROM view_deduction_list
WHERE deduction_resource_id = @l_parm_resource_id
ANDdeduction_control_id = @l_parm_deduction_control_id
ORDER BY deduction_category_id, deduction_date
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Now here is the ASP code I use to build the HTML containing all the data to be reported/displayed :-
'#
'#####################################################################
'###
'###VB_Write_Charge_Detail_Lists
'###
'###This function uses a Stored Procedure to retrieve multiple recordsets
'###containing the detailed charges for
'###
'#####################################################################
'#
Function VB_Write_Charge_Detail_Lists(l_Parm_Resource_Id, l_Parm_Deduction_Control_Id)
Const lk_RS_ID_FUEL_CHARGE= 0
Const lk_RS_ID_PHONE_CHARGE= 1
Const lk_RS_ID_FINE_CHARGE= 2
Const lk_RS_ID_DEDUCTION_CHARGE= 3
Dim rst_Charge
Dim cmd_ADO_Object
Dim l_Store_Recordset_Id
Dim l_Store_Deduction_Category_Id
Dim s_Store_Deduction_Category_Descr
Dim s_HTML
Dim s_HTML_SPAN_Heading
Dim s_HTML_TABLE_Heading
Dim s_HTML_TR_Heading
Dim s_HTML_TR_Total
Dim s_HTML_TR_Detail
Dim s_HTML_TD_Heading
Dim s_HTML_TD_Total
Dim s_HTML_TD_Detail
Dim s_HTML_TD_Detail_Number
Dim s_Row_Class
Dim d_Sum_Fuel_Charge_Gross_Amount
Dim d_Sum_Fuel_Charge_Deduction_Amount
Dim d_Sum_Rental_Cost
Dim d_Sum_Total_Call_Cost_Allowed_Phone_No
Dim d_Sum_Total_Call_Cost_Non_Allowed_Phone_No
Dim d_Sum_Total_Allowance_Amount
Dim d_Sum_Total_Recovery_Amount
Dim d_Sum_Fine_Deduction_Amount
Dim d_Sum_DC_Deduction_Amount
Dim d_Sum_DC_Deduction_Deposit_Amount
Dim d_Sum_DC_Deduction_Payment_Amount
Dim d_Sum_Deduction_Amount
Dim d_Sum_Deduction_Deposit_Amount
Dim d_Sum_Deduction_Payment_Amount
'#
'### Initialise the HTML String
'#
s_HTML = ""
'#
'### Initialise the HTML SPAN Table Heading Definition
'#
s_HTML_SPAN_Heading = "<span class=""TEXTB"">"
'#
'### Initialise the HTML TR Table Heading Definition
'#
s_HTML_TABLE_Heading = "<table width=""100%"" border=1 bordercolor=""#000033"" cellspacing=""0"" cellpadding=""1"" class=""TABRHL"">"
'#
'### Initialise the HTML TR Table Heading Definition
'#
s_HTML_TR_Heading = "<tr class=""TABBC1"">"
'#
'### Initialise the HTML TD Table Heading Definition
'#
s_HTML_TD_Heading = "<td align=""center"" class=""TABHDG"" valign=""top"">"
'#
'### Initialise the HTML TR Table Total Definition
'#
s_HTML_TR_Total = "<tr class=""TABBC1"">"
'#
'### Initialise the HTML TD Table Total Definition
'#
s_HTML_TD_Total = "<td align=""right"" class=""TABHDG"" valign=""top"">"
'#
'### Initialise the HTML TD Table Detail Definition
'#
s_HTML_TD_Detail = "<td valign=""top"" align=""left"" class=""TABDATA"">"
s_HTML_TD_Detail_Number = "<td valign=""top"" align=""right"" class=""TABDATA"">"
'#
'### Initialise the Sub-Report Totals
'#
d_Sum_Fuel_Charge_Gross_Amount = 0
d_Sum_Fuel_Charge_Deduction_Amount = 0
d_Sum_Rental_Cost = 0
d_Sum_Total_Call_Cost_Allowed_Phone_No = 0
d_Sum_Total_Call_Cost_Non_Allowed_Phone_No = 0
d_Sum_Total_Allowance_Amount = 0
d_Sum_Total_Recovery_Amount = 0
d_Sum_Fine_Deduction_Amount = 0
d_Sum_Deduction_Amount = 0
d_Sum_Deduction_Deposit_Amount = 0
d_Sum_Deduction_Payment_Amount = 0
'#
'### Initialise the Stored Recordset Id
'#
l_Store_Recordset_Id = -1
'#
'### Initialise the Stored Deduction Category Id
'#
l_Store_Deduction_Category_Id = -1
'#
'### Create an ADO Command Object
'#
Set cmd_ADO_Object = Server.CreateObject("ADODB.Command")
'#
'### Update the Command Object to facilitate the execution
'### of a stored procedure.
'#
cmd_ADO_Object.ActiveConnection = gobj_Connect
cmd_ADO_Object.CommandType = adCmdStoredProc
'#
'### Add the Stored Procedure Name to the Command Object
'#
cmd_ADO_Object.CommandText = "usp_Resource_Deduction_Statement_Details"
'#
'### Add the Parameters to the ADO Command Object
'#
'### DO NOT EVER CHANGE THE ORDER OF THESE IN RELATION TO THE PARAMETERS
'### DEFINED IN THE QUERY/JOB_EQUIPMENTD PROCEDURE
'#
cmd_ADO_Object.Parameters.Append cmd_ADO_Object.CreateParameter ("l_parm_resource_id", adInteger ,adParamInput, , CLng(l_Parm_Resource_Id))
cmd_ADO_Object.Parameters.Append cmd_ADO_Object.CreateParameter ("l_parm_deduction_control_id", adInteger ,adParamInput, , CLng(l_Parm_Deduction_Control_Id))
'#
'### Create Recordset Objects
'#
Set rst_Charge = server.CreateObject("ADODB.Recordset")
'#
'### Set the Recordset Cursor Location to allow disconnected Recordset Access
'#
rst_Charge.CursorLocation = adUseClient
'#
'### Open Job Recordset
'#
rst_Charge.Open cmd_ADO_Object, , adOpenForwardOnly
'#
'### Check for existance of some data
'#
If rst_Charge Is Nothing Then
'#
'### Indicate no Detail Deduction Transactions
'#
s_HTML = s_HTML & "<br /><b>No Detail Deduction Transactions</b><br />"
Else
'#
'### Process Recordset until there is nothing left to process
'#
Do Until rst_Charge Is Nothing
'#
'### Loop through the Recordset
'#
While Not rst_Charge.EOF
'#
'### Check to see if the Recordset Id has changed
'#
If rst_Charge("Recordset_Id") <> l_Store_Recordset_Id Then
'#
'### Close the table of the Previously reported Recordset
'#
If l_Store_Recordset_Id<> -1 Then
'#
'### Swap the Row Class
'#
s_Row_Class = GVB_Toggle_Strings(s_Row_Class, Array("TABBC3", "TABBC2"))
'#
'### Output the Table Total Row
'#
s_HTML_TR_Detail = "<tr class=""" & s_Row_Class & """>" & vbCrLf
'#
'### Interogate the Recordset Id and build the appropriate Sub-Report Totals
'#
Select Case l_Store_Recordset_Id
Case lk_RS_ID_FUEL_CHARGE
s_HTML = s_HTML & s_HTML_TR_Total
s_HTML = s_HTML & "<td colspan=""6""><b>* Total of reported Fuel Charges :</b></td>"
s_HTML = s_HTML & s_HTML_TD_Total & "<b>" & FormatNumber(d_Sum_Fuel_Charge_Gross_Amount,2,,,-1) & "</b></td>"
s_HTML = s_HTML & s_HTML_TD_Total & "<b>" & FormatNumber(d_Sum_Fuel_Charge_Deduction_Amount,2,,,-1) & "</b></td>"
s_HTML = s_HTML & "</tr>"
Case lk_RS_ID_PHONE_CHARGE
s_HTML = s_HTML & s_HTML_TR_Total
s_HTML = s_HTML & "<td colspan=""2""><b>* Total of reported Phone Charges :</b></td>"
s_HTML = s_HTML & s_HTML_TD_Total & "<b>" & FormatNumber(d_Sum_Rental_Cost,2,,,-1) & "</b></td>"
s_HTML = s_HTML & s_HTML_TD_Total & "<b>" & FormatNumber(d_Sum_Total_Call_Cost_Allowed_Phone_No,2,,,-1) & "</b></td>"
s_HTML = s_HTML & s_HTML_TD_Total & "<b>" & FormatNumber(d_Sum_Total_Call_Cost_Non_Allowed_Phone_No,2,,,-1) & "</b></td>"
s_HTML = s_HTML & s_HTML_TD_Total & "<b>" & FormatNumber(d_Sum_Total_Allowance_Amount,2,,,-1) & "</b></td>"
s_HTML = s_HTML & s_HTML_TD_Total & "<b>" & FormatNumber(d_Sum_Total_Recovery_Amount,2,,,-1) & "</b></td>"
s_HTML = s_HTML & "</tr>"
Case lk_RS_ID_FINE_CHARGE
s_HTML = s_HTML & s_HTML_TR_Total
s_HTML = s_HTML & "<td colspan=""6""><b>* Total of reported Fine Charges :</b></td>"
s_HTML = s_HTML & s_HTML_TD_Total & "<b>" & FormatNumber(d_Sum_Fine_Deduction_Amount,2,,,-1) & "</b></td>"
s_HTML = s_HTML & "</tr>"
Case lk_RS_ID_DEDUCTION_CHARGE
'#
'### Write the Deduction Category Id Sub-Totals provided it's not first-time thru
'#
If l_Store_Deduction_Category_Id <> -1 Then
s_HTML = s_HTML & s_HTML_TR_Total
s_HTML = s_HTML & "<td colspan=""3""><b>* Total of " & s_Store_Deduction_Category_Descr & " : </b></td>"
s_HTML = s_HTML & s_HTML_TD_Total & "<b>" & FormatNumber(d_Sum_DC_Deduction_Deposit_Amount,2,,,-1) & "</b>" & "</td>"
s_HTML = s_HTML & s_HTML_TD_Total & "<b>" & FormatNumber(d_Sum_DC_Deduction_Payment_Amount,2,,,-1) & "</b>" & "</td>"
s_HTML = s_HTML & "</tr>"
'#
'### Set the Row Class
'#
s_Row_Class = "TABBC3"
'#
'### Output the Table Total Row
'#
s_HTML_TR_Detail = "<tr class=""" & s_Row_Class & """>" & vbCrLf
End If
l_Store_Deduction_Category_Id = -1
s_Store_Deduction_Category_Descr = ""
s_HTML = s_HTML & s_HTML_TR_Total
s_HTML = s_HTML & "<td colspan=""3""><b>* Total of reported Deduction Charges :</b></td>"
s_HTML = s_HTML & s_HTML_TD_Total & "<b>" & FormatNumber(d_Sum_Deduction_Deposit_Amount,2,,,-1) & "</b></td>"
s_HTML = s_HTML & s_HTML_TD_Total & "<b>" & FormatNumber(d_Sum_Deduction_Payment_Amount,2,,,-1) & "</b></td>"
s_HTML = s_HTML & "</tr>"
End Select
s_HTML = s_HTML & "</table>"
s_HTML = s_HTML & "<br /><br />"
End If
'#
'### Interogate the Recordset Id and build the appropriate Charge Section Headings
'#
Select Case rst_Charge("Recordset_Id")
Case lk_RS_ID_FUEL_CHARGE
s_HTML = s_HTML & s_HTML_SPAN_Heading & "Fuel Charge Deduction Details :</span><br /><br />"
s_HTML = s_HTML & s_HTML_TABLE_Heading
s_HTML = s_HTML & s_HTML_TR_Heading
s_HTML = s_HTML & s_HTML_TD_Heading & "Invoice No.</td>"
s_HTML = s_HTML & s_HTML_TD_Heading & "Txn Date</td>"
s_HTML = s_HTML & s_HTML_TD_Heading & "Txn No</td>"
s_HTML = s_HTML & s_HTML_TD_Heading & "Reg.</td>"
s_HTML = s_HTML & s_HTML_TD_Heading & "Fuel Card Holder</td>"
s_HTML = s_HTML & s_HTML_TD_Heading & "Location</td>"
s_HTML = s_HTML & s_HTML_TD_Heading & "Fuel Amount</td>"
s_HTML = s_HTML & s_HTML_TD_Heading & "Deduction</td>"
s_HTML = s_HTML & "</tr>"
Case lk_RS_ID_PHONE_CHARGE
s_HTML = s_HTML & s_HTML_SPAN_Heading & "Phone Charge Deduction Details :</span><br /><br />"
s_HTML = s_HTML & s_HTML_TABLE_Heading
s_HTML = s_HTML & s_HTML_TR_Heading
s_HTML = s_HTML & s_HTML_TD_Heading & "Phone No.</td>"
s_HTML = s_HTML & s_HTML_TD_Heading & "Billing Period</td>"
s_HTML = s_HTML & s_HTML_TD_Heading & "Rental Cost</td>"
s_HTML = s_HTML & s_HTML_TD_Heading & "Cost Allowed Calls</td>"
s_HTML = s_HTML & s_HTML_TD_Heading & "Cost Non-Allowed Calls</td>"
s_HTML = s_HTML & s_HTML_TD_Heading & "Allowance</td>"
s_HTML = s_HTML & s_HTML_TD_Heading & "Deduction</td>"
s_HTML = s_HTML & "</tr>"
Case lk_RS_ID_FINE_CHARGE
s_HTML = s_HTML & s_HTML_SPAN_Heading & "Fine Charge Deduction Details :</span><br /><br />"
s_HTML = s_HTML & s_HTML_TABLE_Heading
s_HTML = s_HTML & s_HTML_TR_Heading
s_HTML = s_HTML & s_HTML_TD_Heading & "Issuer</td>"
s_HTML = s_HTML & s_HTML_TD_Heading & "Issue Date</td>"
s_HTML = s_HTML & s_HTML_TD_Heading & "Post Code</td>"
s_HTML = s_HTML & s_HTML_TD_Heading & "Reg.</td>"
s_HTML = s_HTML & s_HTML_TD_Heading & "Ref No.</td>"
s_HTML = s_HTML & s_HTML_TD_Heading & "Location</td>"
s_HTML = s_HTML & s_HTML_TD_Heading & "Deduction</td>"
s_HTML = s_HTML & "</tr>"
Case lk_RS_ID_DEDUCTION_CHARGE
s_HTML = s_HTML & s_HTML_SPAN_Heading & "Deduction Charge Details :</span><br /><br />"
s_HTML = s_HTML & s_HTML_TABLE_Heading
s_HTML = s_HTML & s_HTML_TR_Heading
s_HTML = s_HTML & s_HTML_TD_Heading & "Date</td>"
s_HTML = s_HTML & s_HTML_TD_Heading & "Deduction Type</td>"
s_HTML = s_HTML & s_HTML_TD_Heading & "Reason</td>"
s_HTML = s_HTML & s_HTML_TD_Heading & "Deposit Amount</td>"
s_HTML = s_HTML & s_HTML_TD_Heading & "Deduction</td>"
s_HTML = s_HTML & "</tr>"
End Select
'#
'### Store the new Recordset Id
'#
l_Store_Recordset_Id = rst_Charge("Recordset_Id")
End If
'#
'### Swap the Row Class
'#
s_Row_Class = GVB_Toggle_Strings(s_Row_Class, Array("TABBC3", "TABBC2"))
'#
'### Output the Table Total Row
'#
s_HTML_TR_Detail = "<tr class=""" & s_Row_Class & """>" & vbCrLf
'#
'### Interogate the Recordset Id and build the appropriate Job Detail string
'#
Select Case rst_Charge("Recordset_Id")
Case lk_RS_ID_FUEL_CHARGE
s_HTML = s_HTML & s_HTML_TR_Detail
s_HTML = s_HTML & s_HTML_TD_Detail & rst_Charge("Fuel_Invoice_No") & "</td>"
s_HTML = s_HTML & s_HTML_TD_Detail & Day(rst_Charge("Fuel_Charge_Txn_Date")) & "/" & Month(rst_Charge("Fuel_Charge_Txn_Date")) & "/" & Year(rst_Charge("Fuel_Charge_Txn_Date")) & "</td>"
s_HTML = s_HTML & s_HTML_TD_Detail & rst_Charge("Fuel_Charge_Txn_No") & "</td>"
s_HTML = s_HTML & s_HTML_TD_Detail & rst_Charge("Resource_Vehicle_Reg") & "</td>"
s_HTML = s_HTML & s_HTML_TD_Detail & rst_Charge("Fuel_Card_Holder_Name") & "</td>"
s_HTML = s_HTML & s_HTML_TD_Detail & rst_Charge("LOS_Name") & "</td>"
s_HTML = s_HTML & s_HTML_TD_Detail_Number & FormatNumber(rst_Charge("Gross_Amount"),2,,,-1) & "</td>"
s_HTML = s_HTML & s_HTML_TD_Detail_Number & FormatNumber(rst_Charge("Fuel_Charge_Deduction_Amount"),2,,,-1) & "</td>"
s_HTML = s_HTML & "</tr>"
d_Sum_Fuel_Charge_Gross_Amount = d_Sum_Fuel_Charge_Gross_Amount + rst_Charge("Gross_Amount")
d_Sum_Fuel_Charge_Deduction_Amount = d_Sum_Fuel_Charge_Deduction_Amount + rst_Charge("Fuel_Charge_Deduction_Amount")
Case lk_RS_ID_PHONE_CHARGE
s_HTML = s_HTML & s_HTML_TR_Detail
s_HTML = s_HTML & s_HTML_TD_Detail & rst_Charge("Phone_No") & "</td>"
s_HTML = s_HTML & s_HTML_TD_Detail & rst_Charge("Billing_Year") & " / " & rst_Charge("Billing_Month") & "</td>"
s_HTML = s_HTML & s_HTML_TD_Detail_Number & FormatNumber(rst_Charge("Rental_Cost"),2,,,-1) & "</td>"
s_HTML = s_HTML & s_HTML_TD_Detail_Number & FormatNumber(rst_Charge("Total_Call_Cost_Allowed_Phone_No"),2,,,-1) & "</td>"
s_HTML = s_HTML & s_HTML_TD_Detail_Number & FormatNumber(rst_Charge("Total_Call_Cost_Non_Allowed_Phone_No"),2,,,-1) & "</td>"
s_HTML = s_HTML & s_HTML_TD_Detail_Number & FormatNumber(rst_Charge("Total_Allowance_Amount"),2,,,-1) & "</td>"
s_HTML = s_HTML & s_HTML_TD_Detail_Number & FormatNumber(rst_Charge("Total_Recovery_Amount"),2,,,-1) & "</td>"
s_HTML = s_HTML & "</tr>"
d_Sum_Rental_Cost = d_Sum_Rental_Cost + rst_Charge("Rental_Cost")
d_Sum_Total_Call_Cost_Allowed_Phone_No = d_Sum_Total_Call_Cost_Allowed_Phone_No + rst_Charge("Total_Call_Cost_Allowed_Phone_No")
d_Sum_Total_Call_Cost_Non_Allowed_Phone_No = d_Sum_Total_Call_Cost_Non_Allowed_Phone_No + rst_Charge("Total_Call_Cost_Non_Allowed_Phone_No")
d_Sum_Total_Allowance_Amount = d_Sum_Total_Allowance_Amount + rst_Charge("Total_Allowance_Amount")
d_Sum_Total_Recovery_Amount = d_Sum_Total_Recovery_Amount + rst_Charge("Total_Recovery_Amount")
Case lk_RS_ID_FINE_CHARGE
s_HTML = s_HTML & s_HTML_TR_Detail
s_HTML = s_HTML & s_HTML_TD_Detail & rst_Charge("Fine_Issuer_Name") & "</td>"
s_HTML = s_HTML & s_HTML_TD_Detail & Day(rst_Charge("Fine_Issue_Date")) & "/" & Month(rst_Charge("Fine_Issue_Date")) & "/" & Year(rst_Charge("Fine_Issue_Date")) & "</td>"
s_HTML = s_HTML & s_HTML_TD_Detail & rst_Charge("Fine_Post_Code") & "</td>"
s_HTML = s_HTML & s_HTML_TD_Detail & rst_Charge("Vehicle_Reg") & "</td>"
s_HTML = s_HTML & s_HTML_TD_Detail & rst_Charge("Fine_Issuer_Ref_No") & "</td>"
s_HTML = s_HTML & s_HTML_TD_Detail & rst_Charge("Fine_Location") & "</td>"
s_HTML = s_HTML & s_HTML_TD_Detail_Number & FormatNumber(rst_Charge("Fine_Deduction_Amount"),2,,,-1) & "</td>"
s_HTML = s_HTML & "</tr>"
d_Sum_Fine_Deduction_Amount = d_Sum_Fine_Deduction_Amount + rst_Charge("Fine_Deduction_Amount")
Case lk_RS_ID_DEDUCTION_CHARGE
'#
'### Test for change in the Deduction Category Id
'#
If rst_Charge("Deduction_Category_Id") <> l_Store_Deduction_Category_Id Then
'#
'### Write the Deduction Category Id Sub-Totals provided it's not first-time thru
'#
If l_Store_Deduction_Category_Id <> -1 Then
s_HTML = s_HTML & s_HTML_TR_Total
s_HTML = s_HTML & "<td colspan=""3""><b>* Total of " & s_Store_Deduction_Category_Descr & " : </b></td>"
s_HTML = s_HTML & s_HTML_TD_Total & "<b>" & FormatNumber(d_Sum_DC_Deduction_Deposit_Amount,2,,,-1) & "</b>" & "</td>"
s_HTML = s_HTML & s_HTML_TD_Total & "<b>" & FormatNumber(d_Sum_DC_Deduction_Payment_Amount,2,,,-1) & "</b>" & "</td>"
s_HTML = s_HTML & "</tr>"
'#
'### Swap the Row Class
'#
s_Row_Class = "TABBC3"
'#
'### Output the Table Total Row
'#
s_HTML_TR_Detail = "<tr class=""" & s_Row_Class & """>" & vbCrLf
End If
'#
'### Write the Deduction Category Id Sub-Heading
'#
s_HTML = s_HTML & s_HTML_TR_Detail
s_HTML = s_HTML & "<td colspan=""5""><b>" & rst_Charge("Deduction_Category_Descr") & " Deductions :- </b></td>"
s_HTML = s_HTML & "</tr>"
d_Sum_DC_Deduction_Amount = 0
d_Sum_DC_Deduction_Deposit_Amount = 0
d_Sum_DC_Deduction_Payment_Amount = 0
l_Store_Deduction_Category_Id = rst_Charge("Deduction_Category_Id")
s_Store_Deduction_Category_Descr = rst_Charge("Deduction_Category_Descr")
'#
'### Swap the Row Class
'#
s_Row_Class = GVB_Toggle_Strings(s_Row_Class, Array("TABBC3", "TABBC2"))
'#
'### Output the Table Total Row
'#
s_HTML_TR_Detail = "<tr class=""" & s_Row_Class & """>" & vbCrLf
End If
s_HTML = s_HTML & s_HTML_TR_Detail
s_HTML = s_HTML & s_HTML_TD_Detail & Day(rst_Charge("Deduction_Date")) & "/" & Month(rst_Charge("Deduction_Date")) & "/" & Year(rst_Charge("Deduction_Date")) & "</td>"
s_HTML = s_HTML & s_HTML_TD_Detail & rst_Charge("Deduction_Type_Descr") & "</td>"
s_HTML = s_HTML & s_HTML_TD_Detail & rst_Charge("Deduction_Reason") & "</td>"
s_HTML = s_HTML & s_HTML_TD_Detail_Number & FormatNumber(rst_Charge("Deduction_Deposit_Amount"),2,,,-1) & "</td>"
s_HTML = s_HTML & s_HTML_TD_Detail_Number & FormatNumber(rst_Charge("Deduction_Payment_Amount"),2,,,-1) & "</td>"
s_HTML = s_HTML & "</tr>"
d_Sum_DC_Deduction_Amount = d_Sum_DC_Deduction_Amount + rst_Charge("Deduction_Amount")
d_Sum_DC_Deduction_Deposit_Amount = d_Sum_DC_Deduction_Deposit_Amount + rst_Charge("Deduction_Deposit_Amount")
d_Sum_DC_Deduction_Payment_Amount = d_Sum_DC_Deduction_Payment_Amount + rst_Charge("Deduction_Payment_Amount")
d_Sum_Deduction_Amount = d_Sum_Deduction_Amount + rst_Charge("Deduction_Amount")
d_Sum_Deduction_Deposit_Amount = d_Sum_Deduction_Deposit_Amount + rst_Charge("Deduction_Deposit_Amount")
d_Sum_Deduction_Payment_Amount = d_Sum_Deduction_Payment_Amount + rst_Charge("Deduction_Payment_Amount")
End Select
'#
'### Locate to the Next Row
'#
rst_Charge.MoveNext
Wend
'#
'### Locate to the Next Recordset in the Collection
'#
Set rst_Charge = rst_Charge.NextRecordset
Loop
'#
'### Swap the Row Class
'#
s_Row_Class = GVB_Toggle_Strings(s_Row_Class, Array("TABBC3", "TABBC2"))
'#
'### Output the Table Total Row
'#
s_HTML_TR_Detail = "<tr class=""" & s_Row_Class & """>" & vbCrLf
'#
'### Interogate the Recordset Id and build the appropriate Sub-Report Totals
'#
Select Case l_Store_Recordset_Id
Case lk_RS_ID_FUEL_CHARGE
s_HTML = s_HTML & s_HTML_TR_Total
s_HTML = s_HTML & "<td colspan=""6""><b>* Total of reported Fuel Charges :</b></td>"
s_HTML = s_HTML & s_HTML_TD_Total & "<b>" & FormatNumber(d_Sum_Fuel_Charge_Gross_Amount,2,,,-1) & "</b></td>"
s_HTML = s_HTML & s_HTML_TD_Total & "<b>" & FormatNumber(d_Sum_Fuel_Charge_Deduction_Amount,2,,,-1) & "</b></td>"
s_HTML = s_HTML & "</tr>"
Case lk_RS_ID_PHONE_CHARGE
s_HTML = s_HTML & s_HTML_TR_Total
s_HTML = s_HTML & "<td colspan=""2""><b>* Total of reported Phone Charges :</b></td>"
s_HTML = s_HTML & s_HTML_TD_Total & "<b>" & FormatNumber(d_Sum_Rental_Cost,2,,,-1) & "</b></td>"
s_HTML = s_HTML & s_HTML_TD_Total & "<b>" & FormatNumber(d_Sum_Total_Call_Cost_Allowed_Phone_No,2,,,-1) & "</b></td>"
s_HTML = s_HTML & s_HTML_TD_Total & "<b>" & FormatNumber(d_Sum_Total_Call_Cost_Non_Allowed_Phone_No,2,,,-1) & "</b></td>"
s_HTML = s_HTML & s_HTML_TD_Total & "<b>" & FormatNumber(d_Sum_Total_Allowance_Amount,2,,,-1) & "</b></td>"
s_HTML = s_HTML & s_HTML_TD_Total & "<b>" & FormatNumber(d_Sum_Total_Recovery_Amount,2,,,-1) & "</b></td>"
s_HTML = s_HTML & "</tr>"
Case lk_RS_ID_FINE_CHARGE
s_HTML = s_HTML & s_HTML_TR_Total
s_HTML = s_HTML & "<td colspan=""6""><b>* Total of reported Fine Charges :</b></td>"
s_HTML = s_HTML & s_HTML_TD_Total & "<b>" & FormatNumber(d_Sum_Fine_Deduction_Amount,2,,,-1) & "</b></td>"
s_HTML = s_HTML & "</tr>"
Case lk_RS_ID_DEDUCTION_CHARGE
If l_Store_Deduction_Category_Id <> -1 Then
s_HTML = s_HTML & s_HTML_TR_Total
s_HTML = s_HTML & "<td colspan=""3""><b>* Total of " & s_Store_Deduction_Category_Descr & " : </b></td>"
s_HTML = s_HTML & s_HTML_TD_Total & "<b>" & FormatNumber(d_Sum_DC_Deduction_Deposit_Amount,2,,,-1) & "</b>" & "</td>"
s_HTML = s_HTML & s_HTML_TD_Total & "<b>" & FormatNumber(d_Sum_DC_Deduction_Payment_Amount,2,,,-1) & "</b>" & "</td>"
s_HTML = s_HTML & "</tr>"
End If
s_HTML = s_HTML & s_HTML_TR_Total
s_HTML = s_HTML & "<td colspan=""3""><b>** Total of reported Deduction Charges :</b></td>"
s_HTML = s_HTML & s_HTML_TD_Total & "<b>" & FormatNumber(d_Sum_Deduction_Deposit_Amount,2,,,-1) & "</b></td>"
s_HTML = s_HTML & s_HTML_TD_Total & "<b>" & FormatNumber(d_Sum_Deduction_Payment_Amount,2,,,-1) & "</b></td>"
s_HTML = s_HTML & "</tr>"
End Select
'#
'### Close the table of the Previously reported Recordset
'#
s_HTML = s_HTML & "</table>"
s_HTML = s_HTML & "<br /><br />"
End If
'#
'### Clear the Recordset Object
'#
set rst_Charge = Nothing
'#
'### Clear the Command Object
'#
set cmd_ADO_Object = Nothing
'#
'### Return the previously constructed HTML
'#
VB_Write_Charge_Detail_Lists = s_HTML
End Function
Basically execute VB_Write_Charge_Detail_Lists at the point in your page where you want the data to be displayed.
So's you know if the variable is lk_UPPERCASE it is a constant.
Hope this helps.
Please note it looks complicated but is in fact fairly straight forward, given that all I am doing is interogating the Recordset Id (See the initial sp query) and then displaying the data as it comes in.
I started to look at your data and thought you should be able to work it out based on this multi-recordset approach. eg.
SELECT 1 AS Recordset_Id, *
FROM CountyDriver
WHERE AccidentId = @l_Parm_Accident_Id
SELECT 2 AS Recordset_Id, *
FROM CountyDriver Join To CountyInjuryDriver on DriverId (May have to use an Outer Join)
WHERE AccidentId = @l_Parm_Accident_Id
Blahhh
Good Luck.
April 26, 2003 at 6:29 pm
great stuff swjs!
Your code will certainly be a great help.
I will post again if questions arise out it.
April 26, 2003 at 6:29 pm
great stuff swjs!
Your code will certainly be a great help.
I will post again if questions arise out it.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply