|
Please help me. I have a program I wrote in VB.net My database is in Ms Access.
I want to produce a report. I am using Crystal report XI.
Data for the report are in 9 different tables (all in the same database).There is no way I can limit the number of tables. My SQL select statement joins and selects information from all the 9 tables base on a field data_ID that is common to all.
The problem is this:
When data exists in all the 9 tables the SQL select statement works fine, but if data does not exist in all the tables (some table have data , some table may not have data) the SQL statement does not work at all.
I would like the retrieval mechanism to be such that data in available tables are retrieved and displayed in the report, irrespective of the absence of data in another table.
I have tried producing a report that consist of several sub-reports but on printing the final report is more than one A1 paper.
The final report must not be greater than one A1 sheet of paper.
Thank you.
ihe
|
|
|
|
|
Check your joins in the SQL statement and ensure that you show all data in the parent tables.
Example:-
SELECT FROM Id, CompanyName FROM CompanyTable RIGHT OUTER JOIN Contacts ON Contacts.CompanyId = Company.Id
Steve Jowett
|
|
|
|
|
Thank you.
The tables in my database are:
1. BitsNozzles
2. BottomHoleAssembly
3. ComplusoryFields
4. DataIdentity
5. DrillingMudProperty
6. DrillingPumps
7. PressureLoss
8. Reports
9. TotalValues
How would I join them?
Your example:
SELECT FROM Id, CompanyName FROM CompanyTable RIGHT OUTER JOIN Contacts
ON Contacts.CompanyId = Company.Id
Used two tables but I have 9 tables.
ihe
|
|
|
|
|
Which tables link to which now? Post the SQL code you have now and I'll modify it for you
Steve Jowett
|
|
|
|
|
Thank you very much
The tables are:
1. BitsNozzles
2. BottomHoleAssembly
3. ComplusoryFields
4. DataIdentity
5. DrillingMudProperty
6. DrillingPumps
7. PressureLoss
8. Reports
9. TotalValues
It works like this, on starting the program you must supply
1. Wellname (a string)
2. Date (DateTime)
3. report type ( a string)
4. and the values for the ComplusoryFields
The report type may be AM or PM. (AM represents a morning report and PM an evening report. Reports are produced twice a day.).
When you submit these information .The program would do the following
1. The program generates a string (The data_id) and check if this Data_ID already exist in the database.)
2. Save the values for ComplusoryFields into the ComplusoryFields table. This table contains information that almost all the other tables need to calculate some values or the other.
With the Data_ID you now have access to the other tab pages .Each tab page represent a table .You may now save, edit, delete into/out of the database .Each table is linked by the Data_ID
When you wish to produce a report, you click a button. This button would do the following.
1. Run a private sub GetTotal .
GetTotal would calculate the totals from several tables and overwrite the data in the TotalValue table. (There is no need to store calculated values)
I intend to use an Update SQL statement here. Is this right?
2 Run a private sub GetReportData .The sub would get the data from the 8 other tables and the TotalValue table that now contain data for the selected Data_ID.
The SQL statement below is the one I used before your first letter.
SELECT BitsNozzles.BisNozzleRecordsID, BitsNozzles.TotalBitsUsed, BitsNozzles.TotalNozzlesUsed, BitsNozzles.BitsID, BitsNozzles.BitsType, BitsNozzles.BitsSize, BitsNozzles.NumberOfNozzle, BitsNozzles.NozzleSize, BitsNozzles.DataID AS BitsNozzles_DataID, BottomHoleAssembly.BHARecordID, BottomHoleAssembly.HoleOrCasingType, BottomHoleAssembly.HoleInnerDiameter, BottomHoleAssembly.PipeOrCollarType, BottomHoleAssembly.PipeInnerDiameter, BottomHoleAssembly.PipeOuterDiameter, BottomHoleAssembly.PipeAndCasingLength, BottomHoleAssembly.DataID AS BottomHoleAssembly_DataID, ComplusoryFields.ComplusoryRecordID, ComplusoryFields.DataID AS ComplusoryFields_DataID, ComplusoryFields.CFactor, ComplusoryFields.MudWeight, ComplusoryFields.CirculationRate, ComplusoryFields.CurrencyForMudCost, DataIdentity.IdentityRecordsID, DataIdentity.Measurement, DataIdentity.DataID AS DataIdentity_DataID, DataIdentity.WellName, DataIdentity.DataDate, DataIdentity.ReportType, DrillingMudProperty.DrillingMudRecordsID, DrillingMudProperty.Alkanity, DrillingMudProperty.DataID AS DrillingMudProperty_DataID, DrillingMudProperty.API, DrillingMudProperty.Chlorides, DrillingMudProperty.FlowLineTemperature, DrillingMudProperty.Gel0Mins, DrillingMudProperty.Gel10Mins, DrillingMudProperty.HTHPFilterates, DrillingMudProperty.MBT, DrillingMudProperty.YP, DrillingMudProperty.PV, DrillingMudProperty.MudWeightIn, DrillingMudProperty.MudWeightOut, DrillingMudProperty.Water, DrillingMudProperty.Solids, DrillingMudProperty.Oil, DrillingMudProperty.AveROP, DrillingMudProperty.MaxROP, DrillingMudProperty.VIS, DrillingMudProperty.ShaleDensity, DrillingMudProperty.ShaleFactor, DrillingPumps.DrillingPumpsRecordsID, DrillingPumps.PumpNumber, DrillingPumps.RodDiameter, DrillingPumps.StrokeLength, DrillingPumps.Efficency, DrillingPumps.DataID AS DrillingPumps_DataID, DrillingPumps.PumpOutPut, DrillingPumps.PumpSize, DrillingPumps.LinearDiameter, DrillingPumps.PumpType, DrillingPumps.PumpStatus, PressureLosses.PressureRecordsID, PressureLosses.DxC, PressureLosses.DataID AS PressureLosses_DataID, PressureLosses.EMWLOT, PressureLosses.EstOverBurden, PressureLosses.EstFratureLoss, PressureLosses.EstPorePressureLoss, PressureLosses.MWDPressureLoss, PressureLosses.PipeRunSpeed, PressureLosses.CasingShoePressureLoss, PressureLosses.DxTrend, Reports.ReportRecordsID, Reports.ADT1, Reports.ADT2, Reports.Operator, Reports.Contractor, Reports.TVDDepth, Reports.MDDepth, Reports.DataID AS Reports_DataID, Reports.Rig, Reports.Operation, Reports.ImagePath, Reports.SpudDate, TotalValues.TotalRecordsID, TotalValues.PropertyName, TotalValues.TotalValue, TotalValues.DataID AS TotalValues_DataID
FROM TotalValues INNER JOIN (Reports INNER JOIN (PressureLosses INNER JOIN (DrillingPumps INNER JOIN (DrillingMudProperty INNER JOIN (ComplusoryFields INNER JOIN (BottomHoleAssembly INNER JOIN (BitsNozzles INNER JOIN DataIdentity ON BitsNozzles.DataID = DataIdentity.DataID) ON BottomHoleAssembly.DataID = DataIdentity.DataID) ON ComplusoryFields.DataID = DataIdentity.DataID) ON DrillingMudProperty.DataID = DataIdentity.DataID) ON DrillingPumps.DataID = DataIdentity.DataID) ON PressureLosses.DataID = DataIdentity.DataID) ON Reports.DataID = DataIdentity.DataID) ON TotalValues.DataID = DataIdentity.DataID;
would changing all the INNER JOIN to OUTER JOIN help?
ihe
|
|
|
|
|
Yes, change the joins to OUTER rather than INNER.
An OUTER JOIN is one in which each row of the result set does not necessarily have to contain a row from both tables begins joined; one or both tables are retreated as optional.
An INNER JOIN relates each row in one table to one more corresponding rows in another table. Each result row from an inner join represents the combination of values from two related rows, one from each of the tables being joined.
Steve Jowett
|
|
|
|
|
Thank you I will try that and get back if an error occurs.
ihe
|
|
|
|
|
I'm currently working on a project that requires the user to move files from one client system to another for the purpose of synchronization. I want these files compressed/zipped/archived so that the user gets to move just one file (i.e the zipped file) and at the other end, I want to be able to decompress/unzip the file and start necessary processing.
What is the best way to achieve this?
Regards,
Luther
Object-Oriented Programmer
lutherium - feel the weight of object-oriented programming
|
|
|
|
|
|
Hi Manowj,
Thanks for the help. The link was quite helpful. I have however found an alternative means of handling it.
Regards,
Luther.
lutherium - feel the weight of object-oriented programming
|
|
|
|
|
If you are not looking specifically to zip format and want good compression like winrar then try using zlib.dll its open source and free
Their site is zlib.net
|
|
|
|
|
Hello everyone,
During the installation of project, how can i save any file of project in system registry.
regards,
Syed Ali Raza Rizvi
|
|
|
|
|
You can't save a file in the registry. What are you really trying to do with this?
Dave Kreskowiak
Microsoft MVP - Visual Basic
|
|
|
|
|
Thanks to reply,
Actually i want that my project should work like "Trial Versions" does.
If project is installed on one machine then it should expire itself after 10 days of installation.
if someone try to install it again after changing the system date then it should check that, has project been installed on same machine before or not? if yes then it should stop installation.
I want to do such stuff for Piracy point of view of project.
Waiting for solution.
Regards,
Syed Ali Raza Rizvi
|
|
|
|
|
Your best bet is to use a 3rd party protection library. I don't know of any off the top of my head, simply because I don't sell my apps to the public.
Google for something like "vb.net developer copy protection library" and you'll come up with a bunch of libraries...
Dave Kreskowiak
Microsoft MVP - Visual Basic
|
|
|
|
|
Hi,
i want to create font creator uisng vb.net. Fonts are ttf and otf fonts.Like i should create an editor where they can draw the fonts with the help of mouse.But it should be off TTf and OTf characteristics.So can any one help in this to approach.It is very urgent...
Regards
Ravindra
|
|
|
|
|
You could look up the TrueType file format, I believe it can be found at Wotsit's format[^].
Maybe you can find something useful at the FreeType[^] project.
---
b { font-weight: normal; }
|
|
|
|
|
|
I have some problems/bugs related to DataGrid .NET Framework 1.1 in Visual Studio 2003. These may be the real bugs or unawareness of how to use it. I faced this bug, when I used Data Grid Control for more specific purpose. The detail of the said bug is given below, please check it and give me your opinion or solution.
1- To read the value of any cell in current row which has not yet been the part of data source an exception is thrown with message “There is no value at index 0” here 0 is the index of row I am specifying syntax I am using is as follows
Messagebox.show ( grdDetail.Item( grdDetail.CurrentRowIndex, 0))
2- Validate Event of column style is raised 2 or 3 times
3- The focus did not stay in the same cell even when e.Cancel is set to true. The code lines are below:
Addhandler csUnitName.TextBox.Validating, new ComponentModel.CancelEventHandler(AddressOf csUnitName_Validating)
The code of event handler is written below.
Private sub csUnitName_Validating(byval sender as object, byval e as CanceEventArgs)
e.Cancel = True
End sub
4- I want to confirm deletion of a row from data grid when user tries to select and delete a row, I wrote following code
Addhandler dtUnits.rowDeleting, new rowChangedeventhandler(AddressOf Unit_RowDeleting)
Private sub Unit_RowDeleting(byval sender as object,byval e as rowchangedeventargs)
If messagebox.show(“Are you sure you want to delete”,vbYesNo) = vbNo then
Throw new Exception (“Action Canceled”)
End if
End sub
Everything is working well and good but when user presses No button and exception is thrown the value in first cell of that row is lost I tried a lot to save it but all in vain.
I request further that if anyone has implemented fully loaded grid with maximum events of its data source (Data Table) please send me its code for it could guide me through the right way
|
|
|
|
|
how to make windows programs terminate when the vb.net application is running. beside that the users cannot touch any system inside windows again until the vb.net application logoff.
|
|
|
|
|
ghost181 wrote: how to make windows programs terminate when the vb.net application is running.
Check out the Process class[^]. It'll do what you want as far as stopping processes.
ghost181 wrote: the users cannot touch any system inside windows again until the vb.net application logoff
That is something you CAN'T do in code. This is better left up to Group Policies. You can find more information about setting up a machine for Kiosk mode operation in the Windows XP Resource Kit.
Dave Kreskowiak
Microsoft MVP - Visual Basic
|
|
|
|
|
How can I get the CPU Temperature in vb.net please help me. it's needed by me.
|
|
|
|
|
Not every machine exposes this and not every manufacturer does it in the same way. You're best hope is that your motherboard manufacturer exposes this through Windows Management Instrumentation (WMI). Check out the docs on WMI here[^].
Dave Kreskowiak
Microsoft MVP - Visual Basic
|
|
|
|
|
I WANT TO BUILD A MEDIAPLYER WHICH WILL PLAY .MP3 FILES OR ANY OTHER AUDIO FILES.NOW I WANT TO CUT A PORTION OF THAT AUDIO FILE AND SAVE THAT. PLAEASE HELP ME.
-- modified at 0:33 Wednesday 16th August, 2006
|
|
|
|
|
STOP SHOUTING !!!!
Do you want to build one, or do you want to wrap Media Player in your own GUI, like everyone else does ?
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
|
|
|
|
|