HELP! SubReports Nightmare

Hi, Im relatively new at SQLRS and am having a total nightmare trying to get a subreport to work. I have a main report from which I am trying to pass a parameter (OrderID) to a subreport and all I get is Error: Subreport cannot be displayed and some stuff about "An attempt was made to set a report parameter that was not declared in the subreport" but I have created all the parameters!'...What am I doing wrong'
Any help will be hugely appreciated!
shneeg
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.Have you done the following:
In your project you have:
report A (will be the sub report)
report B (will be the parent report)
Report A has a parameter OrderID. It has a particular data type.
In Report B you drag in the sub report report item from the tool box. You
set the subreport name to point to Report A. In the properties windows for
the sub report, you've navigated to the Parameters tab. From the Parameter
column you use the drop down to select OrderID and in the value column you
specified a value (whether expression or literal).
When you use this procedure, do you continue to get the error?
-Lukasz
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:OfiuYomaEHA.3944@.tk2msftngp13.phx.gbl...
> Hi, Im relatively new at SQLRS and am having a total nightmare trying to
> get a subreport to work. I have a main report from which I am trying to
> pass a parameter (OrderID) to a subreport and all I get is Error:
> Subreport cannot be displayed and some stuff about "An attempt was made to
> set a report parameter that was not declared in the subreport" but I have
> created all the parameters!'...What am I doing wrong'
> Any help will be hugely appreciated!
> shneeg
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
> supports Post Alerts, Ratings, and Searching.

HELP! Is this a potential locking nightmare??

The following table is used to store the points earned each month by
salesmen. Items
purchased by customers on-line all have points assigned to them that are
used for this
purpose. We used to add to this table as each customers application was
completed.
For some reason we have been asked to make it realtime. The work is mostly
done but
I have an issue. As each item is being added/changed/deleted on customers
orders,
these points are accumulated into the corresponding row in this table. So,
if
100 customers purchasing from the same salesman are on-line at the same time
is
this going to be a potential locking issue? These updates are done within a
transaction. Since the sales structure does have an "upline" type of
structure,
these points have to trickle up to several peoples entries in an
all-or-nothing fashion.
This is supposed to roll out soon(tomorrow). I'm worried that once we
really get traffic hitting this that
our server is going to have issues.
CREATE TABLE [dbo].[tblMonthlyPoints](
[lngSalesPersonID] [int] NOT NULL,
[intMonth] [tinyint] NOT NULL,
[intYear] [int] NOT NULL,
[decPoints] [decimal](19, 4) NOT NULL CONSTRAINT
[DF_tblMonthlyPoints_decPoints] DEFAULT (0),
CONSTRAINT [PK_tblMonthlyPoints] PRIMARY KEY CLUSTERED
(
[lngSalesPersonID] ASC,
[intMonth] ASC,
[intYear] ASC
) ON [PRIMARY]
) ON [PRIMARY]On Thu, 18 May 2006 07:17:26 -0700, "Tim Greenwood" <tim_greenwood A-T
yahoo D-O-T com> wrote:

>We used to add to this table as each customers application was
>completed.
>For some reason we have been asked to make it realtime.
Hi Tim,
Have you considered storing the raw data (i.e. the individual sales)
instead of the aggregated monthly totals? I usually prefer to work that
way, since it makes recovery after errors so much easier.
The table you posted could then be replaced with a view.

>if
>100 customers purchasing from the same salesman are on-line at the same tim
e
>is
>this going to be a potential locking issue?
Yes. Based on your table structure, registering the purchase would
require an exclusive lock. It will be a row lock, since the primary key
can be used to locate the row to be updated - but if several connections
want to update the same row (i.e. same salesman, same month, same year),
they'll have to wait. (Or, if your design isn't deadlock-proof, they'll
run into deadlocks).

> These updates are done within a
>transaction. Since the sales structure does have an "upline" type of
>structure,
>these points have to trickle up to several peoples entries in an
>all-or-nothing fashion.
I don't really understand this. Do you mean that you update several rows
in the table, or do yoou mean that one update in the table fires a
trigger that does the trickling-up?
In the former case, try to update all rows in a single update statement:
UPDATE MonthlyPoints
SET Point s = Points + 5
WHERE SalesPersonID IN (123, 456, 7890)
AND Year = 2006
AND Month = 5

>CREATE TABLE [dbo].[tblMonthlyPoints](
> [lngSalesPersonID] [int] NOT NULL,
> [intMonth] [tinyint] NOT NULL,
> [intYear] [int] NOT NULL,
(snip)
Yoou should also consider replacing the individual Month and Year
columns with a single datetime (or smalldatetime) column that holds the
start of the month. Storing year and month as individual columns often
causes more grief than using a single datetime column.
Hugo Kornelis, SQL Server MVP|||Thanks for replying!! I'd given up on this thread...
I understand about updating them all in one statement the problem is the
list of ID's to update has to be created recursively. I know I could use
CTE's but we're not on 2005 just yet...(I am holding my breath here).....
We are storing the raw data already....I have already suggested the view
alternative which I preferred as well. It was shot down by others because I
guess they have some bonus scheme that gets applied as their points increase
( I don't know enough about their scheme yet to disagree ) which is too
complicated to do in that manner. I was just more curious about the
locking issue...I warned them it could be an issue so my hands are clean.
At least the raw data is present on each sales transaction so it can be
reconstructed after the fact "when" I have to fix it.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:lmes6291sa5ju81btj1g28oq367c1j052j@.
4ax.com...
> On Thu, 18 May 2006 07:17:26 -0700, "Tim Greenwood" <tim_greenwood A-T
> yahoo D-O-T com> wrote:
>
> Hi Tim,
> Have you considered storing the raw data (i.e. the individual sales)
> instead of the aggregated monthly totals? I usually prefer to work that
> way, since it makes recovery after errors so much easier.
> The table you posted could then be replaced with a view.
>
> Yes. Based on your table structure, registering the purchase would
> require an exclusive lock. It will be a row lock, since the primary key
> can be used to locate the row to be updated - but if several connections
> want to update the same row (i.e. same salesman, same month, same year),
> they'll have to wait. (Or, if your design isn't deadlock-proof, they'll
> run into deadlocks).
>
> I don't really understand this. Do you mean that you update several rows
> in the table, or do yoou mean that one update in the table fires a
> trigger that does the trickling-up?
> In the former case, try to update all rows in a single update statement:
> UPDATE MonthlyPoints
> SET Point s = Points + 5
> WHERE SalesPersonID IN (123, 456, 7890)
> AND Year = 2006
> AND Month = 5
>
> (snip)
> Yoou should also consider replacing the individual Month and Year
> columns with a single datetime (or smalldatetime) column that holds the
> start of the month. Storing year and month as individual columns often
> causes more grief than using a single datetime column.
> --
> Hugo Kornelis, SQL Server MVP|||On Fri, 19 May 2006 15:58:40 -0700, "Tim Greenwood" <tim_greenwood A-T
yahoo D-O-T com> wrote:

>Thanks for replying!! I'd given up on this thread...
>I understand about updating them all in one statement the problem is the
>list of ID's to update has to be created recursively. I know I could use
>CTE's but we're not on 2005 just yet...(I am holding my breath here).....
Hi Tim,
There are many alternatives on SQL Server 2000 as well. The best
alternatives might include a redesign of your table. The most commonly
used method for storing hierarchies is not the must suitable for use in
a relational model. Google for "nested sets model" if you want to find
out the most common alternative.
(snip)
> I
>guess they have some bonus scheme that gets applied as their points increas
e
>( I don't know enough about their scheme yet to disagree ) which is too
>complicated to do in that manner.
I'd like to see more info on that. To me, the words "too complicated"
are like a red rag. <g>
Hugo Kornelis, SQL Server MVP

HELP! Is this a potential locking nightmare??

The following table is used to store the points earned each month by
salesmen. Items
purchased by customers on-line all have points assigned to them that are
used for this
purpose. We used to add to this table as each customers application was
completed.
For some reason we have been asked to make it realtime. The work is mostly
done but
I have an issue. As each item is being added/changed/deleted on customers
orders,
these points are accumulated into the corresponding row in this table. So,
if
100 customers purchasing from the same salesman are on-line at the same time
is
this going to be a potential locking issue? These updates are done within a
transaction. Since the sales structure does have an "upline" type of
structure,
these points have to trickle up to several peoples entries in an
all-or-nothing fashion.
This is supposed to roll out soon(tomorrow). I'm worried that once we
really get traffic hitting this that
our server is going to have issues.
CREATE TABLE [dbo].[tblMonthlyPoints](
[lngSalesPersonID] [int] NOT NULL,
[intMonth] [tinyint] NOT NULL,
[intYear] [int] NOT NULL,
[decPoints] [decimal](19, 4) NOT NULL CONSTRAINT
[DF_tblMonthlyPoints_decPoints] DEFAULT (0),
CONSTRAINT [PK_tblMonthlyPoints] PRIMARY KEY CLUSTERED
(
[lngSalesPersonID] ASC,
[intMonth] ASC,
[intYear] ASC
) ON [PRIMARY]
) ON [PRIMARY]On Thu, 18 May 2006 07:17:26 -0700, "Tim Greenwood" <tim_greenwood A-T
yahoo D-O-T com> wrote:
>We used to add to this table as each customers application was
>completed.
>For some reason we have been asked to make it realtime.
Hi Tim,
Have you considered storing the raw data (i.e. the individual sales)
instead of the aggregated monthly totals? I usually prefer to work that
way, since it makes recovery after errors so much easier.
The table you posted could then be replaced with a view.
>if
>100 customers purchasing from the same salesman are on-line at the same time
>is
>this going to be a potential locking issue?
Yes. Based on your table structure, registering the purchase would
require an exclusive lock. It will be a row lock, since the primary key
can be used to locate the row to be updated - but if several connections
want to update the same row (i.e. same salesman, same month, same year),
they'll have to wait. (Or, if your design isn't deadlock-proof, they'll
run into deadlocks).
> These updates are done within a
>transaction. Since the sales structure does have an "upline" type of
>structure,
>these points have to trickle up to several peoples entries in an
>all-or-nothing fashion.
I don't really understand this. Do you mean that you update several rows
in the table, or do yoou mean that one update in the table fires a
trigger that does the trickling-up?
In the former case, try to update all rows in a single update statement:
UPDATE MonthlyPoints
SET Point s = Points + 5
WHERE SalesPersonID IN (123, 456, 7890)
AND Year = 2006
AND Month = 5
>CREATE TABLE [dbo].[tblMonthlyPoints](
> [lngSalesPersonID] [int] NOT NULL,
> [intMonth] [tinyint] NOT NULL,
> [intYear] [int] NOT NULL,
(snip)
Yoou should also consider replacing the individual Month and Year
columns with a single datetime (or smalldatetime) column that holds the
start of the month. Storing year and month as individual columns often
causes more grief than using a single datetime column.
--
Hugo Kornelis, SQL Server MVP|||Thanks for replying!! I'd given up on this thread...
I understand about updating them all in one statement the problem is the
list of ID's to update has to be created recursively. I know I could use
CTE's but we're not on 2005 just yet...(I am holding my breath here).....
We are storing the raw data already....I have already suggested the view
alternative which I preferred as well. It was shot down by others because I
guess they have some bonus scheme that gets applied as their points increase
( I don't know enough about their scheme yet to disagree ) which is too
complicated to do in that manner. I was just more curious about the
locking issue...I warned them it could be an issue so my hands are clean.
At least the raw data is present on each sales transaction so it can be
reconstructed after the fact "when" I have to fix it.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:lmes6291sa5ju81btj1g28oq367c1j052j@.4ax.com...
> On Thu, 18 May 2006 07:17:26 -0700, "Tim Greenwood" <tim_greenwood A-T
> yahoo D-O-T com> wrote:
>>We used to add to this table as each customers application was
>>completed.
>>For some reason we have been asked to make it realtime.
> Hi Tim,
> Have you considered storing the raw data (i.e. the individual sales)
> instead of the aggregated monthly totals? I usually prefer to work that
> way, since it makes recovery after errors so much easier.
> The table you posted could then be replaced with a view.
>>if
>>100 customers purchasing from the same salesman are on-line at the same
>>time
>>is
>>this going to be a potential locking issue?
> Yes. Based on your table structure, registering the purchase would
> require an exclusive lock. It will be a row lock, since the primary key
> can be used to locate the row to be updated - but if several connections
> want to update the same row (i.e. same salesman, same month, same year),
> they'll have to wait. (Or, if your design isn't deadlock-proof, they'll
> run into deadlocks).
>> These updates are done within a
>>transaction. Since the sales structure does have an "upline" type of
>>structure,
>>these points have to trickle up to several peoples entries in an
>>all-or-nothing fashion.
> I don't really understand this. Do you mean that you update several rows
> in the table, or do yoou mean that one update in the table fires a
> trigger that does the trickling-up?
> In the former case, try to update all rows in a single update statement:
> UPDATE MonthlyPoints
> SET Point s = Points + 5
> WHERE SalesPersonID IN (123, 456, 7890)
> AND Year = 2006
> AND Month = 5
>>CREATE TABLE [dbo].[tblMonthlyPoints](
>> [lngSalesPersonID] [int] NOT NULL,
>> [intMonth] [tinyint] NOT NULL,
>> [intYear] [int] NOT NULL,
> (snip)
> Yoou should also consider replacing the individual Month and Year
> columns with a single datetime (or smalldatetime) column that holds the
> start of the month. Storing year and month as individual columns often
> causes more grief than using a single datetime column.
> --
> Hugo Kornelis, SQL Server MVP|||On Fri, 19 May 2006 15:58:40 -0700, "Tim Greenwood" <tim_greenwood A-T
yahoo D-O-T com> wrote:
>Thanks for replying!! I'd given up on this thread...
>I understand about updating them all in one statement the problem is the
>list of ID's to update has to be created recursively. I know I could use
>CTE's but we're not on 2005 just yet...(I am holding my breath here).....
Hi Tim,
There are many alternatives on SQL Server 2000 as well. The best
alternatives might include a redesign of your table. The most commonly
used method for storing hierarchies is not the must suitable for use in
a relational model. Google for "nested sets model" if you want to find
out the most common alternative.
(snip)
> I
>guess they have some bonus scheme that gets applied as their points increase
>( I don't know enough about their scheme yet to disagree ) which is too
>complicated to do in that manner.
I'd like to see more info on that. To me, the words "too complicated"
are like a red rag. <g>
--
Hugo Kornelis, SQL Server MVP

Help! Date formatting nightmare!

I converted an Access Database to SQL Express. The dates were converted to datetime

I'm using VWD 2005

Here is the source of my date and the query.

sqlDate = (DateTime.Now.AddDays(-7))

sqlTxt ="SELECT Service_Orders.SStore_Assigned_Number, Store_Info.Store_Other, Service_Orders.PO_Number, Service_Orders.SWorkType, Service_Orders.Service_Order_Number, Service_Orders.SDate_Entered, Service_Orders.SContact, Service_Orders.SClosed FROM Service_Orders INNER JOIN Store_Info ON Service_Orders.Store_ID = Store_Info.Store_ID WHERE (Service_Orders.SDate_Entered >= CONVERT(DATETIME, '" + sqlDate +"', 101)) ORDER BY Service_Orders.SDate_Entered DESC"

This retrurns 0 records.

sqlDate =11/28/2005 12:23:27 AM from the function above.

The querywill return records with :

sqlDate ="2005-11-01 21:56:20"

I tried changing the CONVERT(DATETIME, '" + sqlDate + "',1XX from 100 to 120 with no luck

I know this must be an easy fix, but it is beyond me.

I need to know how to

1. convert my date to the dateformat from "11/28/2005 12:23:27 AM" to "2005-11-01 21:56:20"

or

2. find out how to use the CONVERT(DATETIME, '" + sqlDate + "', 1XX properly

Thanks for any help in advance!

Bill

I always convert them after I recieved the data from the database so the way how you do it depends on where you handle them afterwards. Datagrid has it's own dataformatstring property and incode you can do it like this for an example:
lblFormat.Text = String.Format("{0:dd.MM.yyyy}", myData("myField"))
Find outmore
|||

OK here is the problem:

When I run my query, the query return 0 rows.

I narrowed the problem down to the fact that it doesn't like my date.

I construct the date via the function sqlDate = (DateTime.Now.AddDays(-7))

When I pass that date into my query 0 rows are returned. If I hard code the date with

sqlDate ="2005-11-01 21:56:20" I get all the rows for that date.

Here is my query

sqlTxt ="SELECT Service_Orders.SStore_Assigned_Number, Store_Info.Store_Other, Service_Orders.PO_Number, Service_Orders.SWorkType, Service_Orders.Service_Order_Number, Service_Orders.SDate_Entered, Service_Orders.SContact, Service_Orders.SClosed FROM Service_Orders INNER JOIN Store_Info ON Service_Orders.Store_ID = Store_Info.Store_ID WHERE (Service_Orders.SDate_Entered >= CONVERT(DATETIME, '" + sqlDate +"', 102)) ORDER BY Service_Orders.SDate_Entered DESC"

The field SDate_Entered is a datetime field.

Who would make a product where a simple query is such a problem??