One DataRegion(Table) Multiple DataSets

I have a complexed report
It makes use of two queries and 2 tables
I need to use a group in order to display the information correctly,
If I had one query it would have worked perfectly, But the data I am
retrieving is so complexed that I need to make use of two queries other wise
I get duplicate data
Table 1 contains section1, and 2 of the displayed info
Table 2 contains the 3rd section
it looks like this;
Page 1
header
Section1
Section 2
Section 3
Footer
Page 2
header
Section1
Section 2
Section 3
Footer
So in order to accomplish this I take two tables link them to one dataset.
Add a group, But this results in the following. I need page breaks so I set
the page break option in the group properties
Page 1
header
section 1
section2
Footer
Page 2
section1
section2
Page 3
Section 3
Page 4 Section 3
I then put the 2 tables in a list box, and set the grouping on the list, And
This works 100 %. It groups all the data brilliantly. The problem is I cant
use one query, I need to use two!
SO Is their a work around or some way to link 2 datasets to one list
control.By adding the full path or something. The only way I can currently
reference more than one dataset per table is by using aggeragate funtions.
But =First(Fields!SIZE.Value, "DataSet2") will only return the top 1 result
so that doesnt work I tried (Fields!SIZE.Value, "DataSet2") but that returns
an errorData regions, in SQL Server 2000 Reporting Services, can only be bound to a
single data set with once exception: All secondary data references must be
contained in an aggregate function with the dataset specified. For example,
First(=Fields!<SomeField>.Value), "<SomeDataSet>"), is allowed. To achieve
the effect you want will have to be done in the query. Some of the tools
available to you are joins, unions, openrowset, or linked servers.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Griffen" <Griffen@.discussions.microsoft.com> wrote in message
news:B7A2C3C0-4522-466A-B5D2-08ECCD3471C1@.microsoft.com...
> I have a complexed report
> It makes use of two queries and 2 tables
> I need to use a group in order to display the information correctly,
> If I had one query it would have worked perfectly, But the data I am
> retrieving is so complexed that I need to make use of two queries other
wise
> I get duplicate data
> Table 1 contains section1, and 2 of the displayed info
> Table 2 contains the 3rd section
> it looks like this;
> Page 1
> header
> Section1
> Section 2
> Section 3
> Footer
>
> Page 2
> header
> Section1
> Section 2
> Section 3
> Footer
> So in order to accomplish this I take two tables link them to one dataset.
> Add a group, But this results in the following. I need page breaks so I
set
> the page break option in the group properties
>
> Page 1
> header
> section 1
> section2
> Footer
> Page 2
> section1
> section2
> Page 3
> Section 3
> Page 4 Section 3
> I then put the 2 tables in a list box, and set the grouping on the list,
And
> This works 100 %. It groups all the data brilliantly. The problem is I
cant
> use one query, I need to use two!
> SO Is their a work around or some way to link 2 datasets to one list
> control.By adding the full path or something. The only way I can currently
> reference more than one dataset per table is by using aggeragate funtions.
> But =First(Fields!SIZE.Value, "DataSet2") will only return the top 1
result
> so that doesnt work I tried (Fields!SIZE.Value, "DataSet2") but that
returns
> an errorsql

OleDbException ErrorCode

When a user finally confirms his order in a shopping cart application (using MS-Access as the backend), the items he has purchased are populated in a Access DB table namedOrders & his personal details (like name, mail, billing address, shipping address etc.) are populated in another table namedCustDetails. Both the DB tables have a column namedOrderID. This column is thePrimary Key in theCustDetails table & theForeign Key in theOrders table. TheSession.SessionID becomes the unique OrderID.

After confirming an order, if the user refreshes the page, the app will try to populate the sameSession.SessionID in theCustDetails table but since the columnOrderID is aPrimary Key column in the tableCustDetails, it won't accept duplicate OrderIDs. Under such circumstances, anOleDbException will be raised.

Since a DB app can throw otherOleDbExceptions other than the one about which I mentioned above, I want to display custom error messages to the user. For e.g. if he refreshes the page after confirming his order, I want to display a message saying "Your order has already been placed".

To do this, I tried using theErrorCode property of theOleDbException class but what I found is theErrorCode changes from time to time! Had a particularErrorCode been assigned to the error, I could have done something like this (assuming that theErrorCode for the above error is-12345 which is constant):

Try
'some code
Catch ex As OleDbException
If (ex.ErrorCode = -12345) Then
Response.Write("Your order has already been placed")
ElseIf (ex.ErrorCode = <some other constant ErrorCode>) Then
Response.Write("Another custom error message")
End If
End Try

But I can't do the above since theErrorCode changes from time to time.

So how do I display custom error messages to users under such circumstances?

Of course, I can use theMessage property of theOleDbException class but that would be a rather tedious workaround.

I would provide user friendly messages for the the common errors in a shared function.

There are too many error codes to redefine them all. Putting the code in a central function allows you to call it from anywhere (excuse my C# but you get the idea)

String ExceptionToFriendlyString(OldDbExcption Ex){switch(Ex.ErrorCode) {case 1234:return"That record has already been inserted";case 5678:return"Friendly error message";default:return Ex.Message; // default to Exception Message }}

|||Thanks for your suggestion, Steve, but as already pointed in post #1 in this thread, the ErrorCode goes on changing. For e.g. when I try to insert a record in a DB table that already exists in that DB table, the ErrorCode turns out to be, say, 1234.

Next I shut down my machine & restart it. Now when I try to insert a record in the same DB table which already exists in the table, then the ErrorCode changes to, say, 5678. Of course, I can use a default message as you have shown in your code but (again) as already pointed out, I want the error messages to be as precise as possible.

Any other suggestions?|||

I think you will find that the reason you are getting different error codes is because you are getting different errors.

|||

Hi RN5A,

I agree that the error code stays fixed when you get certain kind of error. When error code changes, the type of error gets changed.

OLE DB Command

Hi All.
I'm using OLE DB Command in order to update my oracle DB. I use MS provider to connect to oracle (I tried the oracle provider too).

I try to execute the simple query as

UPDATE TABLE1
SET
VALID_DATE_TO = to_date(?,'dd/mm/yyyy')
WHERE UNIT_CODE=?
AND VALID_DATE_TO = to_date('01/01/9999','dd/mm/yyyy')

I declare the appropriate parameters.
So, the error is fell:

[OLE DB Command [1247]] Error: An OLE DB error has occurred. Error code: 0x80040E07. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E07 Description: "ORA-01861: literal does not match format string ".

[OLE DB Command [1247]] Error: The "input "OLE DB Command Input" (1252)" failed because error code 0xC020906E occurred, and the error row disposition on "input "OLE DB Command Input" (1252)" specifies failure on error. An error occurred on the specified object of the specified component.

[DTS.Pipeline] Error: The ProcessInput method on component "OLE DB Command " (1247) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

What is this?!!

Any ideas ...

Thanks in advance.

Eli

What type is your first parameter? It must be a string judging by the SQL, and that string must already be formatted in dd/mm/yyy format. Is that so?|||

yes, the first parameter is string in 'dd/mm/yyyy' format.

|||

Maybe you just cannot use a parameter in this way. We are now pushing my Oracle limits, but perhaps the equivalent of a SQL Profiler trace might shed some light on what is really happening to that parameter. For SQL Server I would expect all sorts of calls preparation and passing paramaters that would make some sense in this context.

An alternative is to convert the string column to a date in the pipeline, Derived Column or Data Conversion, and dispense with the to_date alltogether.