How to diplay a dataset in a table

Hi all..
I have a dataset named "Primas" that returns 3 records with 3 fields each.
I want to display a list of those records with a header and a summarize row.
To do that, I placed a Table control into the layout. Assigned "Primas" as
the dataset and I placed a textbox control into it with the value of
=Fields!CODIGOPRIMA.value (CODIGOPRIMA belongs to Primas dataset)
When I build the report, I get the error:
"Expression value of object CODIGOPRIMA reference field CODIGOPRIMA. Report
element expressions can only reference a field in actual dataset scope or, if
they are inside an aggregate, the specified dataset scope"
(I translated the messaege from Spanish, so I'm not sure if it is accurate,
but that's the idea).
The question.. why I get that message although I have the dataset specified
for that table? When I go to a field property inside the table, under
expressions, system shows me only the fields from a dataset that is the
parent of the table (a List)
Any help will be greately appreciated,
Thanks
JaimeYou might check that the Dataset is aware of the field you are trying to
use. You can do this by clicking on the Refresh button on the Data tab with
that dataset selected. I get that same message if I've added or changed a
field in the underlying database and forget to refresh the dataset.
Jared
"Jaime Stuardo" <JaimeStuardo@.discussions.microsoft.com> wrote in message
news:8C285D68-701D-451F-A2A8-363B80874025@.microsoft.com...
> Hi all..
> I have a dataset named "Primas" that returns 3 records with 3 fields each.
> I want to display a list of those records with a header and a summarize
> row.
> To do that, I placed a Table control into the layout. Assigned "Primas" as
> the dataset and I placed a textbox control into it with the value of
> =Fields!CODIGOPRIMA.value (CODIGOPRIMA belongs to Primas dataset)
> When I build the report, I get the error:
> "Expression value of object CODIGOPRIMA reference field CODIGOPRIMA.
> Report
> element expressions can only reference a field in actual dataset scope or,
> if
> they are inside an aggregate, the specified dataset scope"
> (I translated the messaege from Spanish, so I'm not sure if it is
> accurate,
> but that's the idea).
> The question.. why I get that message although I have the dataset
> specified
> for that table? When I go to a field property inside the table, under
> expressions, system shows me only the fields from a dataset that is the
> parent of the table (a List)
> Any help will be greately appreciated,
> Thanks
> Jaime|||Hi Tom...
I have done so but the same problem happens. And when I go to the field
value combobox, only dataset fields associated with the List are shown, not
table daaset fields.
Jaime
"Tom Rocco" wrote:
> You might check that the Dataset is aware of the field you are trying to
> use. You can do this by clicking on the Refresh button on the Data tab with
> that dataset selected. I get that same message if I've added or changed a
> field in the underlying database and forget to refresh the dataset.
> Jared
> "Jaime Stuardo" <JaimeStuardo@.discussions.microsoft.com> wrote in message
> news:8C285D68-701D-451F-A2A8-363B80874025@.microsoft.com...
> > Hi all..
> >
> > I have a dataset named "Primas" that returns 3 records with 3 fields each.
> >
> > I want to display a list of those records with a header and a summarize
> > row.
> > To do that, I placed a Table control into the layout. Assigned "Primas" as
> > the dataset and I placed a textbox control into it with the value of
> > =Fields!CODIGOPRIMA.value (CODIGOPRIMA belongs to Primas dataset)
> >
> > When I build the report, I get the error:
> > "Expression value of object CODIGOPRIMA reference field CODIGOPRIMA.
> > Report
> > element expressions can only reference a field in actual dataset scope or,
> > if
> > they are inside an aggregate, the specified dataset scope"
> > (I translated the messaege from Spanish, so I'm not sure if it is
> > accurate,
> > but that's the idea).
> >
> > The question.. why I get that message although I have the dataset
> > specified
> > for that table? When I go to a field property inside the table, under
> > expressions, system shows me only the fields from a dataset that is the
> > parent of the table (a List)
> >
> > Any help will be greately appreciated,
> > Thanks
> > Jaime
>
>

How to detect overlapping Time Entries

Hello,

I am trying to create a SQL Statement which will identify if an entry can be added to a table or not. My table consists of 4 fields which are:

. UserID (Integer)
. StartTime (datetime)
. EndTime (datetime)
. Activity (varchar)

This is a timesheet application. I am trying to identify if a time entered by a user is valid or not. Basically, times cannot overlap. I'm trying to figure out how to code for the following conditions:

Assume an entry already exists for User 1 as follows:

. UserID: 1
. StartTime: 2006-12-30 08:00:00
. EndTime: 2006-12-30 08:15:00
. Activity: Test

I want to make sure that the following entries cannot be added by that user because they would overlap the existing entry:

. StartTime: 2006-12-30 07:50:00
. EndTime: 2006-12-30 08:05:00

OR

. StartTime: 2006-12-30 07:45:00
. EndTime: 2006-12-30 08:45:00

OR

. StartTime: 2006-12-30 08:05:00
. EndTime: 2006-12-30 08:30:00

OR

. StartTime: 2006-12-30 08:05:00
. EndTime: 2006-12-30 08:10:00

Any help is appreciated.

Thanks

Something like this...

select *
from timetrack_tbl
where
userid = 1
AND
(
(
'2006-12-30 07:50:00' between starttime and endtime
OR
'2006-12-30 08:05:00' between starttime and endtime
)
OR
(
'2006-12-30 07:50:00' <= starttime
AND
'2006-12-30 08:05:00' >= endtime
)
)
If you are going to be doing this continually, it might be a good choice for a function with the above code. Create the function to accept the userid, starting and ending dates and return some value indicating whether there is an overlap.

Example:
select dbo.CheckForOverlap(userid, '2006-12-30 07:50:00', '2006-12-30 08:05:00')

return something from the function that tells you if there is an overlap (like a bit, 1 = Overlap, 0 = No Overlap

|||

That worked like a charm.

Thank you so much for the quick response.

-- Val

How to detect if column data changed and know prev. and new value

I have a need to insert rows into an Audit type table when values
change in certain fields in a table. I thought I could do this via a
trigger. However, on requirement is to include in the audit both the
old and new value.

Is there a "simple" way to do this? I know I could query the table
before the update and compare to what the new value is and react
accordingly.

Just wondering if there is something nifty in Sql Server that I am
missing that could help me with this.

Thanks in advance for your help.

BillHi

Check out CREATE TRIGGGER in Books Online or at
http://msdn.microsoft.com/library/d...asp?frame=true

In particular the COLUMNS_UPDATED example of the IF UPDATE clause.

John

"Bill Tepe" <billtepe@.mssonline.net> wrote in message
news:7364847c.0309060600.7023b89a@.posting.google.c om...
> I have a need to insert rows into an Audit type table when values
> change in certain fields in a table. I thought I could do this via a
> trigger. However, on requirement is to include in the audit both the
> old and new value.
> Is there a "simple" way to do this? I know I could query the table
> before the update and compare to what the new value is and react
> accordingly.
> Just wondering if there is something nifty in Sql Server that I am
> missing that could help me with this.
> Thanks in advance for your help.
> Bill|||[posted and mailed, please reply in news]

Bill Tepe (billtepe@.mssonline.net) writes:
> I have a need to insert rows into an Audit type table when values
> change in certain fields in a table. I thought I could do this via a
> trigger. However, on requirement is to include in the audit both the
> old and new value.

In a trigger you can retrieve the new value in the "inserted" table
and the old value in the "deleted" tables. These tables are virtual
and are accessible only in the trigger.

Beware that a trigger in SQL Server fires once per statement, not once
per row as in some other products. Thus, the tables can old many rows.

You should also be aware of access to these tables when they contain
many rows can be slow. Therefore it is often good idea to start a trigger
with:

select * INTO #tblname_inserted FROM inserted
select * INTO #tblname_deleted FROM deleted

Since you are into auditing... If you are doing this on any large
scalce, you should probably consider third-party solutions rather
than reinventing the wheel. www.redmatrix.com has a product SQLAudit,
which I have no experience of myself.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||If you need to do this at more of an enterprise level, you might look
into Lumigent's Entegra (haven't used it but buying it next year :))

http://lumigent.com/products/entegra/entegra.htm

HTH

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

How to design SP with variable number of params?

I have a form that allows a user to update contact info. For example:
First name
Last name
PO Box
City
State
There are actually more fields (up to 50) but I have only used five for
simplicity. Sometimes a user will submit an update for all fields.
However, for a web service, some one may only send the First Name or any
other one field. In that case, is it better to design an SP for each case?
I see that having scaling issues.
Another approach is to design one SP with many conditionals (50). Both
approaches are inefficient. What is a better way?
Thanks,
BrettSpecify default values for your parameters. I.e.,
CREATE PROCEDURE sample
@.paramLast VARCHAR(30) = NULL, -- NULL default value
@.paramFirst VARCHAR(30) = NULL, -- NULL default value
@.paramPoBox VARCHAR(30) = NULL,
@.paramCity VARCHAR(30) = '', -- Empty string default value
@.paramState CHAR(2) = 'NY' -- 'NY' default value
It will be a little tedious for 50 fields, but will allow you to not specify
parameters on calling.
"Brett" <no@.spam.net> wrote in message
news:OkwoMaUGFHA.2676@.TK2MSFTNGP12.phx.gbl...
>I have a form that allows a user to update contact info. For example:
> First name
> Last name
> PO Box
> City
> State
> There are actually more fields (up to 50) but I have only used five for
> simplicity. Sometimes a user will submit an update for all fields.
> However, for a web service, some one may only send the First Name or any
> other one field. In that case, is it better to design an SP for each
> case? I see that having scaling issues.
> Another approach is to design one SP with many conditionals (50). Both
> approaches are inefficient. What is a better way?
> Thanks,
> Brett
>|||Michael C# wrote:
> Specify default values for your parameters. I.e.,
> CREATE PROCEDURE sample
> @.paramLast VARCHAR(30) = NULL, -- NULL default value
> @.paramFirst VARCHAR(30) = NULL, -- NULL default value
> @.paramPoBox VARCHAR(30) = NULL,
> @.paramCity VARCHAR(30) = '', -- Empty string default
> value @.paramState CHAR(2) = 'NY' -- 'NY' default
> value
> It will be a little tedious for 50 fields, but will allow you to not
> specify parameters on calling.
>
I'm not sure that will work for the OP for updating.
Specify all updatable values in the parameter list. 50 is a lot, and I
might question the number of attributes on the underlying table. Unless
you're dealing with more than one table and could break up the updates
in a meaningful way.
David Gugick
Imceda Software
www.imceda.com|||Commonly we would just update all data on an update in the stored procedure
unless there is a great reason not to. You could do something like:
CREATE PROCEDURE TABLE_UPDATE
@.LastName VARCHAR(30) = NULL,
@.FirstName VARCHAR(30) = NULL,
as
update table
set lastName = coalesce(@.lastName, lastName),
firstName = coalesce(@.firstName, firstName)
go
Then if you call it with table_update @.firstName ='Bob'
The current value of lastName will be used, and the new value for
@.firstName.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Brett" <no@.spam.net> wrote in message
news:OkwoMaUGFHA.2676@.TK2MSFTNGP12.phx.gbl...
>I have a form that allows a user to update contact info. For example:
> First name
> Last name
> PO Box
> City
> State
> There are actually more fields (up to 50) but I have only used five for
> simplicity. Sometimes a user will submit an update for all fields.
> However, for a web service, some one may only send the First Name or any
> other one field. In that case, is it better to design an SP for each
> case? I see that having scaling issues.
> Another approach is to design one SP with many conditionals (50). Both
> approaches are inefficient. What is a better way?
> Thanks,
> Brett
>|||there is one thing that bothers me with this approach (regardles of number
of fields). the thing is that on update, event if the value for the column
is unchanged, the constraints are being checked all the same. eg, if there
is a foreign key constraint, updating a fk column (with the same value, thus
in fact not updating at all) will cause a lookup in the referenced table,
which is absolutely unnecessary, imho. but the alternatives - dynamically
constructing the update statement, or creating a separate statement for
every combination of params - make even less sense.
any thoughts?
dean
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:%23sEA5TWGFHA.4088@.TK2MSFTNGP09.phx.gbl...
> Commonly we would just update all data on an update in the stored
procedure
> unless there is a great reason not to. You could do something like:
> CREATE PROCEDURE TABLE_UPDATE
> @.LastName VARCHAR(30) = NULL,
> @.FirstName VARCHAR(30) = NULL,
> as
> update table
> set lastName = coalesce(@.lastName, lastName),
> firstName = coalesce(@.firstName, firstName)
> go
> Then if you call it with table_update @.firstName ='Bob'
> The current value of lastName will be used, and the new value for
> @.firstName.
>
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Brett" <no@.spam.net> wrote in message
> news:OkwoMaUGFHA.2676@.TK2MSFTNGP12.phx.gbl...
>|||This seems to be the best approach of the posts here. I see there probably
isn't a way to get around conditionals for NULL checks. coalesce is a type
of conditional but probably better than using multiple IF statements
correct?
Thanks,
Brett
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:%23sEA5TWGFHA.4088@.TK2MSFTNGP09.phx.gbl...
> Commonly we would just update all data on an update in the stored
> procedure unless there is a great reason not to. You could do something
> like:
> CREATE PROCEDURE TABLE_UPDATE
> @.LastName VARCHAR(30) = NULL,
> @.FirstName VARCHAR(30) = NULL,
> as
> update table
> set lastName = coalesce(@.lastName, lastName),
> firstName = coalesce(@.firstName, firstName)
> go
> Then if you call it with table_update @.firstName ='Bob'
> The current value of lastName will be used, and the new value for
> @.firstName.
>
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Brett" <no@.spam.net> wrote in message
> news:OkwoMaUGFHA.2676@.TK2MSFTNGP12.phx.gbl...
>|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%2359VcIWGFHA.524@.TK2MSFTNGP14.phx.gbl...
> Michael C# wrote:
> I'm not sure that will work for the OP for updating.
>
Why not? Here's an example of a stored procedure, with a variable number of
params, that updates a table.
--Create Table and Primary Key
CREATE TABLE [dbo].[Table1] (
[IDNum] [int] NOT NULL ,
[LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[IDNum]
) ON [PRIMARY]
GO
--Populate table
INSERT INTO Table1 (IDNum, LastName, FirstName) VALUES (0, 'Jetson',
'George')
INSERT INTO Table1 (IDNum, LastName, FirstName) VALUES (1, 'Flintstone',
'Fred')
INSERT INTO Table1 (IDNum, LastName, FirstName) VALUES (2, 'Rubble',
'Barney')
GO
--Create stored procedure with variable number of parameters
CREATE PROCEDURE usp_UpdateRecord
@.paramID INT,
@.paramLast VARCHAR(50) = NULL,
@.paramFirst VARCHAR(50) = NULL
AS
UPDATE Table1 SET LastName = @.paramLast
WHERE IDNum = @.paramID
AND @.paramLast IS NOT NULL
UPDATE Table1 SET FirstName = @.paramFirst
WHERE IDNum = @.paramID
AND @.paramFirst IS NOT NULL
GO
--Now call the stored procedure with a variable
--number of parameters each time
EXEC usp_UpdateRecord @.paramID = 0, @.paramLast = 'Johnson'
EXEC usp_UpdateRecord @.paramID = 1, @.paramFirst = 'Wilma'
EXEC usp_UpdateRecord @.paramID = 2, @.paramLast = 'Public', @.paramFirst =
'John'
GO

> Specify all updatable values in the parameter list. 50 is a lot, and I
> might question the number of attributes on the underlying table. Unless
> you're dealing with more than one table and could break up the updates in
> a meaningful way.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||I personally would not recommend you design a procedure to perform up to
50 distinct updates to update a single row in the table. Seems more work
and overhead than a single update to me.
David G.|||Are you talking about the overhead incurred when typing in the code once, or
the overhead incurred each time you UPDATE 50 fields in order to change one?
Michael C.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:eunWozbGFHA.3112@.tk2msftngp13.phx.gbl...
>I personally would not recommend you design a procedure to perform up to 50
>distinct updates to update a single row in the table. Seems more work and
>overhead than a single update to me.
> --
> David G.
>|||Michael C# wrote:
> Are you talking about the overhead incurred when typing in the code
> once, or the overhead incurred each time you UPDATE 50 fields in
> order to change one?
> Michael C.
I just mean the possibly running up to 50 individual updates to satisfy
what a single update can do. Plus, the implementation does not allow you
to return a column value to NULL, if needed.
My only real point here is issuing a single update and supplying all
parameters is generally the easiest, most maintainable, and safest
implementation. If the OP has a component in ASP.net or his/her
fat-client app that automates the execution of the update, then he only
has to write it once.
David G.

how to deploy a script

Hello,

What i'm looking for is a way to write a script which would change few data type fields in a specific table, in a specific database. Like i want to change it from char(4) to varchar(15). And then deploy this script, so that when he executes it, changes are reflected in his database. I may use query analyzer to excute i think. Plz also tell me how to write that script.(not sure if its called script).

Thank You.

Use the ALTER TABLE ALTER COLUMN command. Here is an example:

Code Snippet

ALTER TABLE MyTable ALTER COLUMN Col1 VarChar(100)

where MyTable is the table you're updating and Col1 is the column that you want to change its type.

I hope this answers your question.

Best regards,

Sami Samir

|||

if you don't need to alter your source database you can use this approach,

You can get all the table scripts from "SQL Server - Enterprise Manager" using Generate SQL Script.

After that you can find the Char(7) datatype and replace with varchar(15). (you can simply use any text editors Find & Replace)

|||

ya, i used following,

ALTER TABLE GRNs
ALTER COLUMN GRNNo VARCHAR(15) NOT NULL

i was just wondering if it can be conditional, like what's use of running it if its already varchar.

is their anything like if else?

|||

Try the following script:

Code Snippet

Declare @.Type as Int

Declare @.Length as Int

Select @.Type = System_Type_ID, @.Length = Max_Length

From sys.objects Inner Join sys.columns On sys.objects.Object_ID = sys.columns.Object_ID

Where sys.objects.Name = GRNs And sys.columns.Name = GRNNo

--Check that the column actually exists

If @.@.RowCount <> 0

Begin

--Check if it is already Varchar (ID is 167) and length is already 15

If @.Type <> 167 Or @.Length <> 15

Alter Table GRNs Alter Column GRNNo Varchar(15)

End

I hope this helps.

Best regards,

Sami Samir