Inserting a 0

Hi,
Im having trouble with the money data type, for instance I have a column that calculates a price but it will output the price as 470.2 instead of 470.20 which is how I want it displayed on a web page.

Anyone know how to automatically insert a zero on the end of the price?

THanksNoone knows how to insert zeros on the end of numbers??|||I'm gettin '470.2000'
from this simple query I ran from query analyser

declare @.dollar as money
set @.dollar=470.2
select @.dollar

I can't understand why your only getting 470.2. Maybe you can write your calculation query for us to figure?|||I figured it out but thanks anyhow : )sql

inserted/deleted tables

Does the data in the rows in the inserted and deleted tables always correspond? For instance, row 1 in inserted corresponds with row 1 in deleted.
Thanks,

OK, if you

-Insert n rows you have n rows in the inserted.
-Delete n rows you have n rows in the deleted table.
-Update n rows you have n rows in the inserted and n rows in the deleted table.

So for an update the rowcount is always corresponding.

HTH, Jens Suessmeyer

|||

Well, what I was really wanting to know is if I could assume that the data in row [1] (the new data to be inserted) of the inserted table corresponds with the data in row [1] (the data that was deleted) in the deleted table.

Example:

Update people

Set person_id = (select person_id from inserted)

Where people.person_id = (select person_id from deleted)

This type of update statement will only work if there is a single row being updated. I wanted to step through the inserted and deleted tables one row at a time for multiple row updates, but I did not know if it was safe to say that the data in inserted row # corresponded with the data in deleted row #.

|||

> Update people

>

> Set person_id = (select person_id from inserted)

>

> Where people.person_id = (select person_id from deleted)

What table is this trigger attached to? People, or another table? Are you

just trying to undo the update to people, or replicate the update to another

table? In what scenario?

> This type of update statement will only work if there is a single row

> being updated.

Absolutely correct, and a very common tripping point for hundreds of people

before you.

> I wanted to step through the inserted and deleted tables

> one row at a time for multiple row updates

No, no, no. You are going about this all wrong. Think about it in SETS.

If you give some proper DDL and specs (see http://www.aspfaq.com/5006) we

can help you do this in one statement and abandon this idea of iterating

through every row and trying to match some hypothetical "row number"...

|||

> Does the data in the rows in the inserted and deleted tables always

> correspond? For instance, row 1 in inserted corresponds with row 1 in

> deleted.

There is no "row 1"... a table, by definition, is an unordered set of rows.

Typically you identify a row by some unique value, like a primary key, not

whether it came first or last or somewhere in between.

|||

Hello to everyone.

here i want to know some more details regarding inserted/deleted tables.

consider the scenario that more than 100 users are inserting/updating rows of same or othere tables of a database and tiggers of after update upon each insert and/or update is been fired.

what will be the response of the SQL 2005 server to these operations as i am moving the updated data to the audit tables from the delted table. by using the following trigger.

CREATE TRIGGER [TrigAUTblA]
ON [TblA]
AFTER UPDATE AS
BEGIN
INSERT INTO [TblAHistory]
(
[guidA],
[Description]
)
SELECT deleted.guidA,
deleted.Description
FROM deleted

Also what issues can emerge using this scenario

|||

It is possible to update the unique key for multiple rows in a table. In that case, there is nothing to correlate the rows in "inserted" to the rows in "deleted" other than the order in which they are returned by a select statement.

So the question is a valid one, I think: If a table contains one unique key, and multiple rows in that table are updated such that the value of that key changes, can we count on the rows in the "inserted" and "deleted" tables being returned in the same order so that they can be matched up one to one?

Thanks,

Ron

inserted/deleted tables

Does the data in the rows in the inserted and deleted tables always correspond? For instance, row 1 in inserted corresponds with row 1 in deleted.
Thanks,

OK, if you

-Insert n rows you have n rows in the inserted.
-Delete n rows you have n rows in the deleted table.
-Update n rows you have n rows in the inserted and n rows in the deleted table.

So for an update the rowcount is always corresponding.

HTH, Jens Suessmeyer

|||

Well, what I was really wanting to know is if I could assume that the data in row [1] (the new data to be inserted) of the inserted table corresponds with the data in row [1] (the data that was deleted) in the deleted table.

Example:

Update people

Set person_id = (select person_id from inserted)

Where people.person_id = (select person_id from deleted)

This type of update statement will only work if there is a single row being updated. I wanted to step through the inserted and deleted tables one row at a time for multiple row updates, but I did not know if it was safe to say that the data in inserted row # corresponded with the data in deleted row #.

|||

> Update people

>

> Set person_id = (select person_id from inserted)

>

> Where people.person_id = (select person_id from deleted)

What table is this trigger attached to? People, or another table? Are you

just trying to undo the update to people, or replicate the update to another

table? In what scenario?

> This type of update statement will only work if there is a single row

> being updated.

Absolutely correct, and a very common tripping point for hundreds of people

before you.

> I wanted to step through the inserted and deleted tables

> one row at a time for multiple row updates

No, no, no. You are going about this all wrong. Think about it in SETS.

If you give some proper DDL and specs (see http://www.aspfaq.com/5006) we

can help you do this in one statement and abandon this idea of iterating

through every row and trying to match some hypothetical "row number"...

|||

> Does the data in the rows in the inserted and deleted tables always

> correspond? For instance, row 1 in inserted corresponds with row 1 in

> deleted.

There is no "row 1"... a table, by definition, is an unordered set of rows.

Typically you identify a row by some unique value, like a primary key, not

whether it came first or last or somewhere in between.

|||

Hello to everyone.

here i want to know some more details regarding inserted/deleted tables.

consider the scenario that more than 100 users are inserting/updating rows of same or othere tables of a database and tiggers of after update upon each insert and/or update is been fired.

what will be the response of the SQL 2005 server to these operations as i am moving the updated data to the audit tables from the delted table. by using the following trigger.

CREATE TRIGGER [TrigAUTblA]
ON [TblA]
AFTER UPDATE AS
BEGIN
INSERT INTO [TblAHistory]
(
[guidA],
[Description]
)
SELECT deleted.guidA,
deleted.Description
FROM deleted

Also what issues can emerge using this scenario

|||

It is possible to update the unique key for multiple rows in a table. In that case, there is nothing to correlate the rows in "inserted" to the rows in "deleted" other than the order in which they are returned by a select statement.

So the question is a valid one, I think: If a table contains one unique key, and multiple rows in that table are updated such that the value of that key changes, can we count on the rows in the "inserted" and "deleted" tables being returned in the same order so that they can be matched up one to one?

Thanks,

Ron

Insert Trigger

Is it possible for an insert trigger to trigger an action on another
instance of SQL Server?Can you elaborate more on this ?
From one database you can access objects from another database using the 3
part name (<DB Name>.<Owner>.<Object Name>) ... I hope this would be of some
help ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD
http://www.extremeexperts.com
"CR" <jilesjilesjse@.jilesjsilessef> wrote in message
news:O8PAvG5dDHA.1772@.TK2MSFTNGP10.phx.gbl...
> Is it possible for an insert trigger to trigger an action on another
> instance of SQL Server?
>|||One option would be: inside a trigger run an insert statement on the table
of another server (using linked server) . This table (of another server)
will have an insert trigger which will trigger the required event as soon as
a row is inserted into it thorugh another server.
- Vishal
"CR" <jilesjilesjse@.jilesjsilessef> wrote in message
news:O8PAvG5dDHA.1772@.TK2MSFTNGP10.phx.gbl...
> Is it possible for an insert trigger to trigger an action on another
> instance of SQL Server?
>|||Thanks both Vinodk & Vishal. I think that linked servers is what I need to
do to accomplish this. The 3-part name works, but I think only for
databases on the same instance of SQL Server.
"Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
news:%23Jz34j5dDHA.3128@.TK2MSFTNGP09.phx.gbl...
> Can you elaborate more on this ?
> From one database you can access objects from another database using the 3
> part name (<DB Name>.<Owner>.<Object Name>) ... I hope this would be of
some
> help ...
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD
> http://www.extremeexperts.com
>
> "CR" <jilesjilesjse@.jilesjsilessef> wrote in message
> news:O8PAvG5dDHA.1772@.TK2MSFTNGP10.phx.gbl...
> > Is it possible for an insert trigger to trigger an action on another
> > instance of SQL Server?
> >
> >
>|||> The 3-part name works, but I think only for
> databases on the same instance of SQL Server.
Yup ... Else you need to resort to Vishals option of using a linked Server
...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD
http://www.extremeexperts.com
"CR" <jilesjilesjse@.jilesjsilessef> wrote in message
news:OJwiF$HeDHA.1680@.TK2MSFTNGP09.phx.gbl...
> Thanks both Vinodk & Vishal. I think that linked servers is what I need
to
> do to accomplish this. The 3-part name works, but I think only for
> databases on the same instance of SQL Server.
> "Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
> news:%23Jz34j5dDHA.3128@.TK2MSFTNGP09.phx.gbl...
> > Can you elaborate more on this ?
> >
> > From one database you can access objects from another database using the
3
> > part name (<DB Name>.<Owner>.<Object Name>) ... I hope this would be of
> some
> > help ...
> >
> > --
> > HTH,
> > Vinod Kumar
> > MCSE, DBA, MCAD
> > http://www.extremeexperts.com
> >
> >
> > "CR" <jilesjilesjse@.jilesjsilessef> wrote in message
> > news:O8PAvG5dDHA.1772@.TK2MSFTNGP10.phx.gbl...
> > > Is it possible for an insert trigger to trigger an action on another
> > > instance of SQL Server?
> > >
> > >
> >
> >
>

Insert problem with single quotes

I have a problem with inserting a string with single quotes. For instance,

string testme = "we don't have anything";

insert into tableone (buff) values ("'" + testme + "'");

I get an error with the word "don't" with single quote. But if I delete the single quote "dont" then it inserts okay. Is is a bug in sql 2005? Please help. Thanks.

blumonde

blumonde:

I have a problem with inserting a string with single quotes. For instance,

string testme = "we don't have anything";

insert into tableone (buff) values ("'" + testme + "'");

I get an error with the word "don't" with single quote. But if I delete the single quote "dont" then it inserts okay. Is is a bug in sql 2005? Please help. Thanks.

No, it's not a bug with SQL Server 2005. To use that method, you would need to double up any single quotes within the testme string in order to "escape" them so that your INSERT statement works correctly.

However, you should be using parameters to pass UI-supplied values to your SQL statement. Here's the why:

Please, please, please, learn about injection attacks!

And here's the how:

How To: Protect From SQL Injection in ASP.NET
Using Parameterized Query in ASP.NET, Part 1
Using Parameterized Query in ASP.NET, Part 2
Using Parameterized Queries in ASP.Net

|||

tmorton:

blumonde:

I have a problem with inserting a string with single quotes. For instance,

string testme = "we don't have anything";

insert into tableone (buff) values ("'" + testme + "'");

I get an error with the word "don't" with single quote. But if I delete the single quote "dont" then it inserts okay. Is is a bug in sql 2005? Please help. Thanks.

No, it's not a bug with SQL Server 2005. To use that method, you would need to double up any single quotes within the testme string in order to "escape" them so that your INSERT statement works correctly.

However, you should be using parameters to pass UI-supplied values to your SQL statement. Here's the why:

Please, please, please, learn about injection attacks!

And here's the how:

How To: Protect From SQL Injection in ASP.NET
Using Parameterized Query in ASP.NET, Part 1
Using Parameterized Query in ASP.NET, Part 2
Using Parameterized Queries in ASP.Net

Thank you Tmorton. I think parameters will have solved the problem.

blumonde

Insert parameter to retrieve a specific chart type

Hi everyone,
Is is possible to display a specific type of chart depending on an input parameter in my data. For instance, my dataset contains a column for questiontype.
If the questiontype equals 1, I would like to display to results in a barchart. If the questiontype equals 2 I would like to display the result in a pie-chart.

IS there some kind of way to manage this problem?

thanks so far.

regard,
capsync

I am not 100% certain - but close - I do not think this can be accomplished with "out of the box" RS...
As you know - when you design the report the data from the data set populates the chart, bar, pie, etc. I have not come across a method to alternate what type of chart is displayed because you have to design the report with each chart type (bar, pie, etc...) and once these charts are added via the design wizard - they are just there and that is that...
If you get an answer I would like to know as well...
One other thing I have tried to get to happen as well --
Not display the chart based upon a parameter - but once you "design the report" with a chart it appears (as long as there is data).
So I think the answer is no and I have searched everywhere for answers to the questions.
Best Regards,
joe