NVarchar and SelectedValue and InsertCommand

Hi guys,
I've got a problem inserting data into my db.
I've created a NVARCHAR column and I'm using SelectedValue Parameters.
I only have a problem in the INSERT mode.
The UPDATE and DELETE are working fine.
All the fields can be updated or deleted, but I can't insert new data inside my db.
I've changed one column to NVARCHAR : "reference"
I use NVARCHAR because I have some Arabic Fields (unicode) into my db.
But I've copy-pasted everything about the SqlDataSource.
10x a lot anyway !

ASP.NET using MS Visual Studio 2005 :

...

<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

OldValuesParameterFormatString="original_{0}"
OnDeleted="SqlDataSource2_Deleted"
OnUpdated="SqlDataSource2_Updated"
OnInserted="SqlDataSource2_Inserted"
SelectCommand=
"SELECT [reference], [ddf], [description], [quantity], [pru], [supname], [catname]
FROM [Products]
WHERE ([reference] = @.reference)"
InsertCommand="INSERT INTO [Products]
([reference], [ddf], [description], [quantity], [pru], [supname], [catname])
VALUES (@.reference, @.ddf, @.description, @.quantity, @.pru, @.supname, @.catname)"
UpdateCommand="UPDATE [Products]
SET [ddf] = @.ddf,
[description] = @.description,
[quantity] = @.quantity,
[pru] = @.pru,
[supname] = @.supname,
[catname] = @.catname
WHERE [reference] = @.original_reference"
DeleteCommand="DELETE FROM [Products] WHERE [reference] = @.original_reference">

<SelectParameters>
<asp:ControlParameter ControlID="GridView1" Name="reference" PropertyName="SelectedValue" Type="String" />
</SelectParameters>

<InsertParameters>
<asp:Parameter Name="reference"
Type="String" />
<asp:Parameter Name="ddf"
Type="DateTime" />
<asp:Parameter Name="description"
Type="String" />
<asp:Parameter Name="quantity"
Type="String" />
<asp:Parameter Name="pru"
Type="Decimal" />
<asp:Parameter Name="supname"
Type="String" />
<asp:Parameter Name="catname"
Type="String" />
</InsertParameters>

<UpdateParameters>
<asp:Parameter Name="ddf"
Type="DateTime" />
<asp:Parameter Name="description"
Type="String" />
<asp:Parameter Name="quantity"
Type="String" />
<asp:Parameter Name="pru"
Type="Decimal" />
<asp:Parameter Name="supname"
Type="String" />
<asp:Parameter Name="catname"
Type="String" />
<asp:Parameter Name="original_reference"
Type="String" />
</UpdateParameters>

<DeleteParameters>
<asp:Parameter Name="original_reference"
Type="String" />
</DeleteParameters>

</asp:SqlDataSource>

SQL SERVER 2005 : Create Database File

USE master
GO

IF EXISTS(SELECT * FROM sysdatabases
WHERE name='Products')
DROP DATABASE Products
GO

CREATE DATABASE Products
ON ( NAME=Product,
FILENAME = 'C:\WebApp\App_Data\Products.mdf',
SIZE=10 )
GO

USE Products
GO

CREATE TABLE Categories (
catname VARCHAR(25) NOT NULL,
PRIMARY KEY (catname) )
GO

CREATE TABLE Suppliers (
supname VARCHAR(25) NOT NULL,
tel VARCHAR(50) ,
cell VARCHAR(50) ,
fax VARCHAR(50) ,
pob VARCHAR(25) ,
address VARCHAR(300) ,
nearby VARCHAR(100) ,
website VARCHAR(100) ,
email VARCHAR(100) ,
skypephone VARCHAR(100) ,
PRIMARY KEY (supname) )
GO

CREATE TABLE Products (
reference NVARCHAR(25) NOT NULL,
ddf DATETIME NOT NULL,
description VARCHAR(50) NOT NULL,
quantity VARCHAR(10) NOT NULL,
pru MONEY NOT NULL,
supname VARCHAR(25) NOT NULL,
catname VARCHAR(25) NOT NULL,
pv MONEY NOT NULL,
PRIMARY KEY(reference),
FOREIGN KEY(catname) REFERENCES Categories(catname),
FOREIGN KEY(supname) REFERENCES Suppliers(supname) )
GO

what type of exception give the .net runtime, ensure that your size of reference parameter is 25 chars

|||

Hi,

From your description, it is a database operation issue. Based on the code you provided, we found that it was the following sql statement that handles with your inserting operation.

InsertCommand="INSERT INTO [Products]
([reference], [ddf], [description], [quantity], [pru], [supname], [catname])
VALUES (@.reference, @.ddf, @.description, @.quantity, @.pru, @.supname, @.catname)"

And in your database creation file, you are creating your [Products] table in the following way:

CREATE TABLE Products (
reference NVARCHAR(25) NOT NULL,
ddf DATETIME NOT NULL,
description VARCHAR(50) NOT NULL,
quantity VARCHAR(10) NOT NULL,
pru MONEY NOT NULL,
supname VARCHAR(25) NOT NULL,
catname VARCHAR(25) NOT NULL,
pv MONEY NOT NULL,
PRIMARY KEY(reference),
FOREIGN KEY(catname) REFERENCES Categories(catname),
FOREIGN KEY(supname) REFERENCES Suppliers(supname) )
GO

We can see that pv is a NOT NULL field, but in your insert statement, you haven't inserted the pv field, so the inserting operation couldn't work.

For this kinds of issue, it's better to run your sql statement in some tools like SQLServer management studio to check if the statement can work. After that, you can use it in your .NET application.

Thanks.

|||

10x a lot guys ! :D
I forgot inserting the "pv" field.
I though the problem was from the NVarchar.
Sorry to bother u but i'm still a beginner ! :D

nvarchar & varchar

Hi,

I am new to MS SQL. When I create a column in a table, when shall I
use nvarchar or varchar? Please help.

Thanks,
MikeThe nvarchar data type provides support for Unicode characters. This is
needed if you are building an international system that must store different
languages. However, if you have no need to store Unicode characters then you
are better using varchar. The nvarchar data type occupies twice the space of
varchar as it uses 2 bytes to encode each character.

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||On 26 Feb, 15:13, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

The nvarchar data type provides support for Unicode characters. This is
needed if you are building an international system that must store different
languages. However, if you have no need to store Unicode characters then you
are better using varchar. The nvarchar data type occupies twice the space of
varchar as it uses 2 bytes to encode each character.
>
HTH,
>
Plamen Ratchevhttp://www.SQLStudio.com

varchar will support a lot of characters from different languages
though (depending on the collation codepage) so no need to rush into
doubling your storage if you dont "really" need to|||On Feb 26, 10:26 am, "oliver" <oraus...@.hotmail.comwrote:

Quote:

Originally Posted by

On 26 Feb, 15:13, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:
>

Quote:

Originally Posted by

The nvarchar data type provides support for Unicode characters. This is
needed if you are building an international system that must store different
languages. However, if you have no need to store Unicode characters then you
are better using varchar. The nvarchar data type occupies twice the space of
varchar as it uses 2 bytes to encode each character.

>

Quote:

Originally Posted by

HTH,

>

Quote:

Originally Posted by

Plamen Ratchevhttp://www.SQLStudio.com

>
varchar will support a lot of characters from different languages
though (depending on the collation codepage) so no need to rush into
doubling your storage if you dont "really" need to

Plamen, Oliver Thanks a lot!

Mike|||On Feb 26, 9:47 am, haid...@.gmail.com wrote:

Quote:

Originally Posted by

Hi,
>
I am new to MS SQL. When I create a column in a table, when shall I
use nvarchar or varchar? Please help.
>
Thanks,
Mike

Mike,

Clearly you need to go back to reading the manual or get a Dummy's
book if you don't know the difference between unicode and ascii
strings.

HTH,

Carl Tegeder
Master MS-SQL Administrator|||Carl Tegeder wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>I am new to MS SQL. When I create a column in a table, when shall I
>use nvarchar or varchar? Please help.

Quote:

Originally Posted by

Clearly you need to go back to reading the manual or get a Dummy's
book if you don't know the difference between unicode and ascii
strings.

Not the most tactful of responses, but hey.

To the original poster: Google and Wikipedia are your friends.

Numeric[DT_NUMERIC] - comma or dot

Hi,

I have this problem:

In one SSIS project that I have, I convert (by using Data Conversion) my numeric column into Numeric[DT_NUMERIC] and get:

1.000000

Then, in another project I convert the same column again into Numeric[DT_NUMERIC] and get:

2,000000

Does anybody know how I can control if I′m using a dot or a comma?

Thank you.

Cannot say I have seen this. Are the packages run on the same machine? If not, are the regional settings the same?|||

Yes, they are running on the same machine.

|||

Arg! My fault, the regional settings of the Flat File was different.

Thanks for the help!!

Numeric data column and insert from ASP.NET

I am trying to insert some values into a table where the column is of the data type "numeric". The insert works fine.Update does not work.

Update BUT_BREAKDOWN_PCT SET BDP_EFFORT_BREAKDOWN_PCT=0.15 WHERE BDP_BREAKDOWN_ID =1 AND BDP_PHASE_ID = 3 AND BDP_START_EFF_DT = '12/31/2004'

BDP_EFFORT_BREAKDOWN_PCT is a numeric column with a size 5 (4,3)

When I do the updatedirectly from QA, it works fine.

I was googling it and read a KB article saying it's a problem with Service Pack of SQL Server 2000. If it is, then the query should not work even from QA...isn't it?

Anyone had this problem before? Please help.

In SQL Server 2000 you can change the data type to Decimal and your problem will go away but in SQL Server 7.0 Numeric was more stable than Decimal from my experience. Hope this helps.|||If it works OK in Query Analyzer (works fine for me, too), and it isnot working in your application, then your application is not sendingthe exact same information.

What results are you seeing? An error message? Unexpected data in theBDP_EFFORT_BREAKDOWN_PCTcolumn? No update at all?

I am suspecting that you are not seeing any update at all, which meansthat your WHERE condition is not being met. And since you seem tohave a datetime column in your WHERE condition, I would guess furtherthat that is where your problem lies. (Datetime columnstrip up a lot of people.)

If you are having difficulty troubleshooting this further, let us see your code and maybe we can point out the problem.

numeric conversion

I have a source table with a varchar field like 0000005467.
My target table has a numeric 18,2 column which I am trying to populate with 54.67 but it keeps rounding the last 2 digits to ZERO's.
Any ideas?
-KTry something likes that:

declare @.str varchar(25)
select @.str='00005667'
select @.str=left(@.str,len(@.str)-2)+'.'+right(@.str,2)
select convert(decimal(10,2),@.str),@.str|||The data field numeric(18,2) will support 2 decimal places but it doesn't assume that a value stored or converted has a 2 decimal value. So you need to tell the system that the converted value has a 2 decimal value, divide by 100.

declare @.x varchar(15)

set @.x = '0000005467'

select convert(numeric(18,2),@.x)/100

Numeric column names are pre-pended with "ID" string

Hello

I'm using SQL Server 2005 Business Intelligence Studio and have noticed an odd behavior with column names.

Specifically, if you query for a table that returns a numeric column name it is pre-pended with "ID".

For example, if my databaseReporting Services will show these fields in the "Report Datasets" as follows:

For example,

"Select identity as '1' from group_header, 1" shows a column name of "ID1" instead of "1".

Has anyone else run into this? If so, is there a way to remove the "ID".

Jay

Fields names in RDL must be CLS-compliant. I.e. they have to start with a character. If the query defines a field name so that it is not CLS-compliant, report designer will automatically generate a unique, CLS-compliant field name, such as ID1.

Note: the field name has nothing to do with the actual field values returned by the query.

-- Robert

Numbers and Crystal Reports 9

I exported a column of type float to SQL Server using DTS. In Crystal
reports, it 'sees' the column as a string! Is 'float' the best type to
use? Must I create a formula field in CR to 'correct' for this?Hi

From the database side you should choose the most appropriate datatype for
the data which it will hold, at a guess decimal might be more appropriate.

Its been a while since I used Crystal so I can't remember how it determined
the datatypes, but I seem to remember that you mask the output to be in the
format that you require.

John

"chrispycrunch" <chrispycrunch@.gmail.com> wrote in message
news:1107712517.428328.79850@.o13g2000cwo.googlegro ups.com...
>I exported a column of type float to SQL Server using DTS. In Crystal
> reports, it 'sees' the column as a string! Is 'float' the best type to
> use? Must I create a formula field in CR to 'correct' for this?|||chrispycrunch (chrispycrunch@.gmail.com) writes:
> I exported a column of type float to SQL Server using DTS. In Crystal
> reports, it 'sees' the column as a string! Is 'float' the best type to
> use? Must I create a formula field in CR to 'correct' for this?

So if you sp_help on the table in Query Analyzer, it is reported to be
a float?

If so, you should probably ask in a forum devoted to Crystal Reports. We
use Crystal in our shop, but I don't have it installed on my machine...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4206972c$0$7707$fa0fcedb@.news.zen.co.uk...
> Hi
> From the database side you should choose the most appropriate datatype for
> the data which it will hold, at a guess decimal might be more appropriate.
> Its been a while since I used Crystal so I can't remember how it
> determined the datatypes, but I seem to remember that you mask the output
> to be in the format that you require.
> John
> "chrispycrunch" <chrispycrunch@.gmail.com> wrote in message
> news:1107712517.428328.79850@.o13g2000cwo.googlegro ups.com...
>>I exported a column of type float to SQL Server using DTS. In Crystal
>> reports, it 'sees' the column as a string! Is 'float' the best type to
>> use? Must I create a formula field in CR to 'correct' for this?
>
I would imagine decimal is probably more appropriate than float.

I usually base my crystal reports on stored procedures rather than directly
on tables.
I can then do any "work" necessary to make the crystal report easier to put
together in the stored procedure which I, quite frankly, find a lot easier
to work with than the crystal alternatives.
I'd do any maths I could in the stored procedure as well.
If float is best for storage and decimal for presentation then you could
cast the field in your stored procedure's select statement.

If these are no good for you then, is it just a presentation thing?
You can specify a formula for display format of a field in crystal.

HTH
--
Regards,
Andy O'Neill

Numbering Query Results

Is there a way to have a column in a query result that is an "autonumber"?
Say I have 10 records returned, I want them to be numbered 1 - 10. I'm sure
there's a way using a stored procedure or something, I just can't think of a
way.
Thanks in advance.
Chuck Foster
Programmer Analyst
Eclipsys Corporation - St. Vincent Health SystemSee:
http://support.microsoft.com/defaul...b;EN-US;q186133
Anith|||How to dynamically number rows in a SELECT Statement
http://support.microsoft.com/defaul...kb;en-us;186133
AMB
"chuckdfoster" wrote:

> Is there a way to have a column in a query result that is an "autonumber"?
> Say I have 10 records returned, I want them to be numbered 1 - 10. I'm su
re
> there's a way using a stored procedure or something, I just can't think of
a
> way.
> Thanks in advance.
> --
> Chuck Foster
> Programmer Analyst
> Eclipsys Corporation - St. Vincent Health System
>
>|||Thanks...
"chuckdfoster" <chuckdfoster@.hotmail.com> wrote in message
news:O7e$7waSFHA.3296@.TK2MSFTNGP15.phx.gbl...
> Is there a way to have a column in a query result that is an "autonumber"?
> Say I have 10 records returned, I want them to be numbered 1 - 10. I'm
sure
> there's a way using a stored procedure or something, I just can't think of
a
> way.
> Thanks in advance.
> --
> Chuck Foster
> Programmer Analyst
> Eclipsys Corporation - St. Vincent Health System
>

Numbering column with a start number of 109

Hi,
I have a table as follows:
StatId AgencyID Value
1 10
2 47
3 38
4 59
5 60
.. ..
All the fields in the StatId field is blank. However,
I have to fill up the field in statId starting from
109 with increment of 1 for each row. Altogether I have
about 10,000 row in the above agency table.
Any help as to how to proceed programmatically is highly
appreciated. Thanks in advance.
How will you determine what the order should be? That is, should the row
with AgencyID = 10 have a StatId of 109?
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:F4B0FBC9-1CFF-4E72-A572-1AA9273C5D29@.microsoft.com...
> Hi,
> I have a table as follows:
> StatId AgencyID Value
> 1 10
> 2 47
> 3 38
> 4 59
> 5 60
> .. ..
> All the fields in the StatId field is blank. However,
> I have to fill up the field in statId starting from
> 109 with increment of 1 for each row. Altogether I have
> about 10,000 row in the above agency table.
> Any help as to how to proceed programmatically is highly
> appreciated. Thanks in advance.
|||That's correct. Row with Agencyid = 10 will have a StatID of 109. Thanks.
"Adam Machanic" wrote:

> How will you determine what the order should be? That is, should the row
> with AgencyID = 10 have a StatId of 109?
>
> "Jack" <Jack@.discussions.microsoft.com> wrote in message
> news:F4B0FBC9-1CFF-4E72-A572-1AA9273C5D29@.microsoft.com...
>
>
|||What's the logic then? How will you programatically number these rows?
You'll probably have to write a loop to manually update the rows, one by
one, based on whatever logic you're ordering them by.
Or, you could try creating a new table with StatId INT IDENTITY(109, 1),
then insert the entire batch at once using INSERT SELECT, with an ORDER BY,
but there is no guarantee that the rows will show up in the right order. So
although you could try that, it may not work the way you want.
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:65435A6C-DB6B-4828-A4F4-754005BB31E7@.microsoft.com...[vbcol=seagreen]
> That's correct. Row with Agencyid = 10 will have a StatID of 109. Thanks.
> "Adam Machanic" wrote:
row[vbcol=seagreen]

Numbering column with a start number of 109

Hi,
I have a table as follows:
StatId AgencyID Value
1 10
2 47
3 38
4 59
5 60
.. ..
All the fields in the StatId field is blank. However,
I have to fill up the field in statId starting from
109 with increment of 1 for each row. Altogether I have
about 10,000 row in the above agency table.
Any help as to how to proceed programmatically is highly
appreciated. Thanks in advance.How will you determine what the order should be? That is, should the row
with AgencyID = 10 have a StatId of 109?
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:F4B0FBC9-1CFF-4E72-A572-1AA9273C5D29@.microsoft.com...
> Hi,
> I have a table as follows:
> StatId AgencyID Value
> 1 10
> 2 47
> 3 38
> 4 59
> 5 60
> .. ..
> All the fields in the StatId field is blank. However,
> I have to fill up the field in statId starting from
> 109 with increment of 1 for each row. Altogether I have
> about 10,000 row in the above agency table.
> Any help as to how to proceed programmatically is highly
> appreciated. Thanks in advance.|||What's the logic then? How will you programatically number these rows?
You'll probably have to write a loop to manually update the rows, one by
one, based on whatever logic you're ordering them by.
Or, you could try creating a new table with StatId INT IDENTITY(109, 1),
then insert the entire batch at once using INSERT SELECT, with an ORDER BY,
but there is no guarantee that the rows will show up in the right order. So
although you could try that, it may not work the way you want.
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:65435A6C-DB6B-4828-A4F4-754005BB31E7@.microsoft.com...
> That's correct. Row with Agencyid = 10 will have a StatID of 109. Thanks.
> "Adam Machanic" wrote:
> > How will you determine what the order should be? That is, should the
row
> > with AgencyID = 10 have a StatId of 109?
> >
> >
> > "Jack" <Jack@.discussions.microsoft.com> wrote in message
> > news:F4B0FBC9-1CFF-4E72-A572-1AA9273C5D29@.microsoft.com...
> > > Hi,
> > > I have a table as follows:
> > >
> > > StatId AgencyID Value
> > > 1 10
> > > 2 47
> > > 3 38
> > > 4 59
> > > 5 60
> > > .. ..
> > > All the fields in the StatId field is blank. However,
> > > I have to fill up the field in statId starting from
> > > 109 with increment of 1 for each row. Altogether I have
> > > about 10,000 row in the above agency table.
> > >
> > > Any help as to how to proceed programmatically is highly
> > > appreciated. Thanks in advance.
> >
> >
> >

Numbering column with a start number of 109

Hi,
I have a table as follows:
StatId AgencyID Value
1 10
2 47
3 38
4 59
5 60
. ..
All the fields in the StatId field is blank. However,
I have to fill up the field in statId starting from
109 with increment of 1 for each row. Altogether I have
about 10,000 row in the above agency table.
Any help as to how to proceed programmatically is highly
appreciated. Thanks in advance.How will you determine what the order should be? That is, should the row
with AgencyID = 10 have a StatId of 109?
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:F4B0FBC9-1CFF-4E72-A572-1AA9273C5D29@.microsoft.com...
> Hi,
> I have a table as follows:
> StatId AgencyID Value
> 1 10
> 2 47
> 3 38
> 4 59
> 5 60
> .. ..
> All the fields in the StatId field is blank. However,
> I have to fill up the field in statId starting from
> 109 with increment of 1 for each row. Altogether I have
> about 10,000 row in the above agency table.
> Any help as to how to proceed programmatically is highly
> appreciated. Thanks in advance.|||That's correct. Row with Agencyid = 10 will have a StatID of 109. Thanks.
"Adam Machanic" wrote:

> How will you determine what the order should be? That is, should the row
> with AgencyID = 10 have a StatId of 109?
>
> "Jack" <Jack@.discussions.microsoft.com> wrote in message
> news:F4B0FBC9-1CFF-4E72-A572-1AA9273C5D29@.microsoft.com...
>
>|||What's the logic then? How will you programatically number these rows?
You'll probably have to write a loop to manually update the rows, one by
one, based on whatever logic you're ordering them by.
Or, you could try creating a new table with StatId INT IDENTITY(109, 1),
then insert the entire batch at once using INSERT SELECT, with an ORDER BY,
but there is no guarantee that the rows will show up in the right order. So
although you could try that, it may not work the way you want.
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:65435A6C-DB6B-4828-A4F4-754005BB31E7@.microsoft.com...[vbcol=seagreen]
> That's correct. Row with Agencyid = 10 will have a StatID of 109. Thanks.
> "Adam Machanic" wrote:
>
row[vbcol=seagreen]

Number/enumerate rows in a table from scratch?

I have a table with 10 rows - one int column and 3 varchar cols. There is n
o
unique data. How can I number/enumerate the rows from say 1 to 10 with Tsq
l?
create table tbl1(
RowNum int,
fld1 varchar(5),
fld2 varchar(5),
fld3 varchar(5))
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Thanks,
RichConsider making the RowNum column an identity.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:DAD8E08B-207D-4EBF-BF4F-A8273B1D3536@.microsoft.com...
I have a table with 10 rows - one int column and 3 varchar cols. There is
no
unique data. How can I number/enumerate the rows from say 1 to 10 with
Tsql?
create table tbl1(
RowNum int,
fld1 varchar(5),
fld2 varchar(5),
fld3 varchar(5))
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Thanks,
Rich|||Thanks. I thought about that. But I was just wondering - based on my
criteria, if it would be possible to enumerate a table with Tsql - like mayb
e
using a cursor? For example, in VBA you could use DAO code to enumerate a
table:
Set RS = DB.OpenRecordset("tbl1")
Do While Not RS.EOF
RS.Edit
RS!RowNum = i
RS.Update
i = i + 1
RS.MoveNext
Loop
This is kind of like a cursor except that a cursor seems to require
something unique. I was thinking in pseudocode Update tbl1 set top 1 Rownum
= 1. Then use a self join and set next row to max(Rownum) + 1. But how do
I
determine the next row with Tsql in my scenario?
"Tom Moreau" wrote:

> Consider making the RowNum column an identity.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:DAD8E08B-207D-4EBF-BF4F-A8273B1D3536@.microsoft.com...
> I have a table with 10 rows - one int column and 3 varchar cols. There is
> no
> unique data. How can I number/enumerate the rows from say 1 to 10 with
> Tsql?
> create table tbl1(
> RowNum int,
> fld1 varchar(5),
> fld2 varchar(5),
> fld3 varchar(5))
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Thanks,
> Rich
>|||If this is a useful table in your system, you must remove the duplicates and
explicitly assign a primary key for data integrity purposes. For details
refer to:
http://support.microsoft.com/defaul...b;EN-US;q139444
Once you have unique rows, ranking becomes much simpler. For instance see:
http://support.microsoft.com/defaul...b;EN-US;q186133
Though it makes little sense, with you current table schema with no keys,
you can simply add the "pseudo rank" using an identity column.
Alternatively, you can use a rank like:
ALTER TABLE tbl1 ADD idCol INT NOT NULL IDENTITY
GO
SELECT ( SELECT COUNT( * )
FROM tbl1 t2
WHERE t2.fld1 = t1.fld1
AND t2.fld2 = t1.fld2
AND t2.fld3 = t1.fld3
AND t2.idCol <= t1.idCol ),
t1.fld1, t1.fld2, t1.fld3
FROM tbl1 t1 ;
GO
ALTER TABLE tbl1 DROP COLUMN idCol
GO
SELECT * FROM tbl1
Another approach is to use a table of sequentially incrementing numbers. You
can create one like :
SELECT IDENTITY( INT ) "n" INTO Nbrs FROM sysobjects s1, sysobjects s2 ;
Now you can do:
SELECT Nbrs.n, fld1, fld2, fld3
FROM ( SELECT fld1, fld2, fld3, COUNT( * )
FROM tbl1
GROUP BY fld1, fld2, fld3 ) D ( fld1, fld2, fld3, n )
INNER JOIN Nbrs
ON D.n >= Nbrs.n ;
Another way of doing this would be like:
SELECT n, fld1, fld2, fld3
FROM tbl1, Nbrs
GROUP BY fld1, fld2, fld3, n
HAVING n <= COUNT(*) ;
Anith|||Without something to provide uniqueness, you're stuck.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:57F251E0-A033-4047-9502-A9A5415814B2@.microsoft.com...
Thanks. I thought about that. But I was just wondering - based on my
criteria, if it would be possible to enumerate a table with Tsql - like
maybe
using a cursor? For example, in VBA you could use DAO code to enumerate a
table:
Set RS = DB.OpenRecordset("tbl1")
Do While Not RS.EOF
RS.Edit
RS!RowNum = i
RS.Update
i = i + 1
RS.MoveNext
Loop
This is kind of like a cursor except that a cursor seems to require
something unique. I was thinking in pseudocode Update tbl1 set top 1 Rownum
= 1. Then use a self join and set next row to max(Rownum) + 1. But how do
I
determine the next row with Tsql in my scenario?
"Tom Moreau" wrote:

> Consider making the RowNum column an identity.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:DAD8E08B-207D-4EBF-BF4F-A8273B1D3536@.microsoft.com...
> I have a table with 10 rows - one int column and 3 varchar cols. There is
> no
> unique data. How can I number/enumerate the rows from say 1 to 10 with
> Tsql?
> create table tbl1(
> RowNum int,
> fld1 varchar(5),
> fld2 varchar(5),
> fld3 varchar(5))
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Thanks,
> Rich
>|||Thank you all for your replies and suggestions. These have really helped me
to understand about uniqueness and numbering. Actually, I sort of lost sigh
t
of why I was pursuing this, but I realized that with the VBA DAO each row in
an MS Access table, for example has a unique binary row identifier which is
now exposed for usage. But DAO uses it to movenext. So I can see that ther
e
is no way to movenext without some unique Identifier in a Sql Table.
Thanks all for your help.
Rich
"Rich" wrote:

> I have a table with 10 rows - one int column and 3 varchar cols. There is
no
> unique data. How can I number/enumerate the rows from say 1 to 10 with T
sql?
> create table tbl1(
> RowNum int,
> fld1 varchar(5),
> fld2 varchar(5),
> fld3 varchar(5))
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Thanks,
> Rich

Number of rows depending on a column value

Hi all,

I have a table with artikels and count, sample:

Art Count

12A 3

54G 2

54A 4

I would like to query this table and for each 'count' retrieve one row:

query result:

Art Count

12A 3

12A 3

12A 3

54G 2

54G 2

54A 4

54A 4

54A 4

54A 4

Is this possible?

Thanks, Perry

Certainly one way to do this would be to join with a table of numbers. Give a look to this article about a table of numbers here. In the meantime, I will see about getting you an example. Also, if this info is meant for a front end application, you might consider doing this work in the application instead of the database. Here is an example:

Code Snippet

declare @.artikels table
( Art varchar(5),
[Count] integer
)
insert into @.Artikels
select '12A', 3 union all
select '54G', 2 union all
select '54A', 4

select Art, [count]
from @.artikels
join numbers
on [count] >= n
and n <= 10 -- setting an arbitrary upper bound
order by Art

/*
Art count
-- --
12A 3
12A 3
12A 3
54A 4
54A 4
54A 4
54A 4
54G 2
54G 2
*/

|||

Thanks

This can do the job, but not the (easy) way i was looking for. I need a view to feed a report writer (data dynamics active reports) to print barcodes, for each product 1 barcode.

Now i will query the view and copy records in the resultset as many as needed, then feed this dataset to the report writer

Thanks

Perry

I

Number of rows depending on a column value

Hi all,

I have a table with artikels and count, sample:

Art Count

12A 3

54G 2

54A 4

I would like to query this table and for each 'count' retrieve one row:

query result:

Art Count

12A 3

12A 3

12A 3

54G 2

54G 2

54A 4

54A 4

54A 4

54A 4

Is this possible?

Thanks, Perry

Certainly one way to do this would be to join with a table of numbers. Give a look to this article about a table of numbers here. In the meantime, I will see about getting you an example. Also, if this info is meant for a front end application, you might consider doing this work in the application instead of the database. Here is an example:

Code Snippet

declare @.artikels table
( Art varchar(5),
[Count] integer
)
insert into @.Artikels
select '12A', 3 union all
select '54G', 2 union all
select '54A', 4

select Art, [count]
from @.artikels
join numbers
on [count] >= n
and n <= 10 -- setting an arbitrary upper bound
order by Art

/*
Art count
-- --
12A 3
12A 3
12A 3
54A 4
54A 4
54A 4
54A 4
54G 2
54G 2
*/

|||

Thanks

This can do the job, but not the (easy) way i was looking for. I need a view to feed a report writer (data dynamics active reports) to print barcodes, for each product 1 barcode.

Now i will query the view and copy records in the resultset as many as needed, then feed this dataset to the report writer

Thanks

Perry

I

Number of Reads in Profiler

Hi,

Can any of can explain, what the "Reads" column in Profiler exactly mean ? I'm not comfortable with the explanation given in BOL.

"The number of read operations on the logical disk that are performed by the server on behalf of the event. These read operations include all reads from tables and buffers during the statement's execution"

For the same procedure with same parameters, if the server is not loaded much, the Reads are in a few hundreds, but when there are more than 1000 concurrent users, why it is going to millions ? What other parameters affecting this reads ? And how can I reduce it ?

Environment: SQL Server 2005 64-bit Enterprise Edition on Windows Server 2003 R2 Server x64 Enterprise Edition SP2

Thanks in Advance.

Regards

Babu

This is a good question.

The reads column in the profiler represents the number of logical reads for a statement or batch.

What is a logical read?

SQL Server uses much of its virtual memory as a buffer to cache and reduce physical I/O. So SQL Server caches the physical I/O and then requests pages from the cache. Everytime your statement requests a page (pages are stored as 8K blocks) from the cache a "logical" read occurs.

The best way to reduce the number of logical reads is to tune your query. If you are using a lot of subqueries, aggregrates on subqueries, etc... this can lead to high logical i/o. One thing you should take a look at is to make sure your statements are utilizing indexes. Try using an index hint to force your statement to use a specific index. This can make a big difference in terms of performance.

Mike
|||

From the below mentioned Link

The I/O from an instance of SQL Server is divided into logical and physical I/O. A logical read occurs every time the database engine requests a page from the buffer cache. If the page is not currently in the buffer cache, a physical read is then performed to read the page into the buffer cache. If the page is currently in the cache, no physical read is generated; the buffer cache simply uses the page already in memory. A logical write occurs when data is modified in a page in memory. A physical write occurs when the page is written to disk. It is possible for a page to remain in memory long enough to have more than one logical write made before it is physically written to disk.

check this ...

http://msdn2.microsoft.com/en-US/library/aa224763(SQL.80).aspx

Madhu

|||

Hi Mike/Madhu,

Thanks for your interest in this topic.

I'm still not clear, what's the "unit" for the number represented in this column.

1. Is it number of Pages ? If so, why the number of Reads increases when I request for same volume of data when the number of users connectd are more.

2. Is it number of attempts made to read a Page or Record ? Is the Locks influencing the Reads.

Database is having all feasible indexes and the procedures are tuned for the level best possible ( by me :-) ).

Thanks once again.

Regards

Babu

|||

logical reads Number of pages read from the data cache. physical reads Number of pages read from disk.

Refer SET SET STATISTICS IO documentation in BOL. ITs clearly documented there

Madhu

Number of Columns

How can I tell how many column is returned in a query like this?
Select *.general, lname.newbusiness, contact.newbusiness, ….
ThanksThere is no built-in functions in SQL Server which does this. However, most
client side data access APIs will have a mechanism of identifying the number
of columns in the columns collection of the resultset.
Anith|||Depends how you are returning the data. For example, the Count porperty
of the ADO Fields collection gives this information.
Anyway, it is generally considered bad practice to use SELECT * in a
production application. List all the column names individually. Query
Analyzer lets you click and drag the column list into the editing
window so you don't have to do lots of typing.
--
David Portas
SQL Server MVP
--|||Actually, I don't really know that syntax, but I guess you mean something
similar to this :
select table1.*, table2.some_field, table2.some_otherfield, table3.*
etc
The easiest way I can see is by actually counting the fields in the
resultset. If you do not want the results than add a WHERE 1 = 2 to the end,
that way you will get the structure of the resultset, but without any data
in it (and without having to wait for the server to do all the work)
A rather complex route to find out upfront would be to do it like this :
select total_number_of_columns = (SELECT COUNT(*) FROM syscolumns col JOIN
sysobjects obj ON obj.id = col.id and obj.name = 'table1' and xtype =
'U') -- table 1 : * = all columns
+ 2 -- table 2 only two
columns asked for
+ (SELECT COUNT(*) FROM
syscolumns col JOIN sysobjects obj ON obj.id = col.id and obj.name =
'table3' and xtype = 'U') -- table 3 : * = all columns
Probably works, but I wonder what it's use is.
Cu
Roby
"Emma" <Emma@.discussions.microsoft.com> wrote in message
news:678A98EB-AC14-4C4B-B3F0-9D81D837B24F@.microsoft.com...
> How can I tell how many column is returned in a query like this?
> Select *.general, lname.newbusiness, contact.newbusiness, ..
> Thanks
>|||Emma,
Not sure what you are trying to accomplish, but here is one way to do it in
t-sql:
Select 1 as '1', 2 as '2', 3 as '3', 4 as '4', 5 as '5', 6 as '6'
into ##p
select count(*) from tempdb..syscolumns where id = object_id('tempdb..##p')
Ilya
"Emma" <Emma@.discussions.microsoft.com> wrote in message
news:678A98EB-AC14-4C4B-B3F0-9D81D837B24F@.microsoft.com...
> How can I tell how many column is returned in a query like this?
> Select *.general, lname.newbusiness, contact.newbusiness, ..
> Thanks
>|||This won't work in every case. SELECT INTO requires that the column
names are unique so if you join two tables and don't alias the columns
then it may fail. Perhaps the OP knows that her two tables won't have
conflicting column names but if the columns were really fixed and known
in advance then she wouldn't need a query to count them. If all the
columns are aliased then they are presumably known and therefore the
query is still pretty pointless. I guess the real question here is
exactly why the OP wouldn't know at development time how many columns
would be returned by her queries.
--
David Portas
SQL Server MVP
--|||Since you havent provided the full query, I'll try to help you with
what you had given me.
Run the following in Qeary Analyzer.
SP_HELP general
find the number of columns and then add each columns that followes
afterwords...
NOTE:
It will help if people post questions with proper information and Code
and be clear on what they are looking to solve!!!!!!!!!!!!!!!!!|||Since you havent provided the full query, I'll try to help you with
what you had given me.
Run the following in Qeary Analyzer.
SP_HELP general
find the number of columns and then add each columns that followes
afterwords...
NOTE:
It will help if people post questions with proper information and Code
and be clear on what they are looking to solve!!!!!!!!!!!!!!!!!|||Since you havent provided the full query, I'll try to help you with
what you had given me.
Run the following in Qeary Analyzer.
SP_HELP general
find the number of columns and then add each columns that followes
afterwords...
NOTE:
It will help if people post questions with proper information and Code
and be clear on what they are looking to solve!!!!!!!!!!!!!!!!!

number manipulation in non-identity columns

hi.

i am using ms sql server 2000.

can somebody tell me what the code would be to remove all the values
in a given column and replace them with the associated number of the row
with each execution.

so, if i have a column:

nums
|1|
|2|
|3|
|4|

and somebody deletes record |2|

i would like the nums colum to update to

|1|
|2|
|3|

not:

|1|
|3|
|4|

it seems simple but i am having a hard time with this.
how is it done?

thanks.

SET XACT_ABORT ON
BEGIN TRANSACTION
DELETE FROM MyTable WHERE nums=@.nums
SET NOCOUNT ON
UPDATE MyTable SET nums=nums-1 WHERE nums>@.nums
SET NOCOUNT OFF
COMMIT TRANSACTION

Now I would never recommend actually doing that. It's resource intensive. You'd be better off not using a "nums" column, and using something like a CreateDate column of type datetime. Then eitehr using a subquery or SQL 2005's rank command, or a stored procedure to return a "nums" to you (In the case of using CreateDate, return back the number of other columns with a lower CreateDate), Like:

SELECT t1.*,(SELECT COUNT(*) FROM MyTable WHERE MyTable.CreateDate<t1.CreateDate) nums
FROM MyTable

Now you can go ahead and delete records and nums will adjust for you in order of CreateDate.

|||hi. thanks a lot for your reply.

i am trying to follow your last (least expensive) suggestion.
i am a little confused by the "t1" selects.

i have the following table:

PersonalPhotos
photo_id PK
photo_name
photo_location
photo_size
user_name
photo_date
photo_number

I was previouly using a stored procedure to create the non-identity number column in
photo_number.

I am now trying your code:

SELECT t1.*,(SELECT COUNT(*) FROM PersonalPhotos WHERE PersonalPhotos.photo_date<t1.CreateDate) nums
FROM PersonalPhotos

I have tried creating a t1 table with a t1_date column but that doesnt seem to work. If i replace
all the t1s with personalphoto and all the CreateDates with photo_date, i get results, but no
values in the nums column (which i could have guessed).

I am sorry, i am not a sql programmer (but i am an eager learner) and nothing is obvious.
further clarification would be appreciated.

thanks.|||i am still working on this. can somebody help clarify?
thanks|||I am pretty sure Motley meant to use a T1 table alias:

SELECT t1.*,(SELECT COUNT(*) FROM MyTable WHERE MyTable.CreateDate<t1.CreateDate) nums
FROM MyTableT1|||ok, thanks for helping out. i'll try that when i get home from work.
much appreciated.|||

hi.

i am home now and trying this. i am still confused.

as stated above i have a PersonalPhotos table with

a photo_date column that i'd like to use to delete

records and adjust numbers so they are *always* sequential.

I do not have a T1 table, but created one per Motley's suggestion.

I gave it 2 columns (t1_id, t1_date).

now, i have adjusted Motley's recommendation:

SELECT t1.*,(SELECT COUNT(*) FROM MyTable WHERE MyTable.CreateDate<t1.CreateDate) nums
FROM MyTable

to apply to the table i am working with - "PersonalPhotos":

SELECT t1.*,(SELECT COUNT(*) FROM PersonalPhotos WHERE PersonalPhotos.photo_date<t1.t1_date) nums
FROM PersonalPhotos

When that didnt work, I tried Tmorton's suggestion:

SELECT t1.*,(SELECT COUNT(*) FROM PersonalPhotos WHERE PersonalPhotos.photo_date<t1.t1_date) nums
FROM PersonalPhotos t1

None of this works :(

The errors I am getting are all about the existence (or lack thereof) of the t1

table/columns. In the above select statement, the error I get is:

"Invalid column name 't1_date'." but, t1.t1_date clearly lives in table t1.

could somebody PLEASE explain to me:

1) what is the point of creating the new t1 table (or, do i need to manually create it)?

2) what should the columns be in the new t1 table?

3) why i am getting the Invalid column error?

clarification greatly appreciated.

|||

pbd22:

1) what is the point of creating the new t1 table (or, do i need to manually create it)?

You should NOT create a new table. Using the alias "t1" makes it possible to use the same table "PersonalPhotos" twice in the same query.

pbd22:

2) what should the columns be in the new t1 table?

There should be no new t1 table.

pbd22:

3) why i am getting the Invalid column error?

And actually, I had put the table alias against the wrong table reference.

Try your query like this; you should have better luck:
SELECT t1.*,(SELECT COUNT(*) FROM PersonalPhotos t1 WHERE PersonalPhotos.photo_date<t1.photo_date) nums
FROM PersonalPhotos
ORDER BY PersonalPhotos.photo_date
|||ok, thank you.
that makes more sense to me. i am at work now but will
try your suggestion when i get home tonight. thanks for
helping to clarify.|||Hi.

I have tried your suggestion. I am not getting the following:

Server: Msg 107, Level 16, State 2, Line 1
The column prefix 't1' does not match with a table name or alias name used in the query.

The SQL i used was as you suggested:

SELECT t1.*,(SELECT COUNT(*) FROM PersonalPhotos t1 WHERE PersonalPhotos.photo_date<t1.photo_date) nums
FROM PersonalPhotos
ORDER BY PersonalPhotos.photo_date

I have also tried variations on this select statement:

SELECT t1.*,(SELECT COUNT(*) FROM PersonalPhotos t1 WHERE PersonalPhotos.photo_date<t1.photo_date) nums
FROM PersonalPhotos t1
ORDER BY PersonalPhotos.photo_date

The above throws the following error:

Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'PersonalPhotos' does not match with a table name or alias name used in the query.

I will keep trying (i seem to not be having much luck with this SQL) and will let you know if i stumble
on the answer. In the mean time, if you have more suggestions, I would appreciate it.

thank you.|||OK, this suggestion comes with Terri's Golden Guarantee that it will not generate an error:

SELECTPersonalPhotos.*,(SELECT COUNT(*) FROM PersonalPhotos t1 WHERE PersonalPhotos.photo_date<t1.photo_date) nums
FROM PersonalPhotos
ORDER BY PersonalPhotos.photo_date

Note that the t1.* was replaced with a PersonalPhotos.*, because this is the table name being referred to in the FROM clause (and is what threw me off on my first reply).|||thank you terri! you rock. that one did the trick. I have one last tiny question :)
this select statement now does exactly what i want, but i need the numbers to
read 1 - N for the current user, not all users. Right now, the select is for all users
and, as a result, a user may see (9,10,11,12,13,14) next to his six pictures. My
user column is "user_name". also, how do i get the count to start at 1 (not zero)
for each user? so:

1) how do i get the count to be user-specific?
2) how do i get the count to start at 1, not zero?

thanks.|||You can use a query like this:

SELECT PersonalPhotos.*,(SELECT COUNT(*)+1 FROM PersonalPhotos t1 WHERE PersonalPhotos.photo_date<t1.photo_date AND PersonalPhotos.user_name = t1.user_name) nums
FROM PersonalPhotos
ORDER BY PersonalPhotos.photo_date

I need to add that typically this sort of thing (adding row numbers) is done on the front end, where it is much less intensive. SQL Server has to work pretty hard to execute this query, as I believe it is doing an extra SELECT statement for each row in your table.|||hi. thanks. this solution now works.

i put the following code in my stored procedure (with a few changes):

BEGIN
SELECT PersonalPhotos.user_name,(SELECT COUNT(*)+1 FROM PersonalPhotos t1 WHERE PersonalPhotos.photo_date<t1.photo_date AND PersonalPhotos.user_name = t1.user_name) nums
FROM PersonalPhotosWHERE user_name = Context.User.Identity.Name.ToString()
ORDER BY PersonalPhotos.photo_dateDESC
END

i am assuming it returns a "nums" column that can be read by ASP. In the HTML,
I have the following line in my GridView control:

<asp:BoundField HeaderText="Number"DataField="nums" ReadOnly="True" HeaderStyle-HorizontalAlign="Center" ItemStyle-HorizontalAlign="Center" /
and, get the following error:

A field or property with the name 'nums' was not found on the selected data source.

how do i get the created "nums" column to appear next to the pics on the client?

thanks again.|||

thanks. i took your advice and am currently figuring this out for the client.

thanks for all your help!

Number Formatting in SQLSERVER

say, i have a column in the database that has number values. I want to display these number with comma separators (Eg: if the column values is 1654, then i want to display it as 1,654).
How can i achieve this in my sQL queryFormatting is done on the client. The simplest answer is to use a client such as MS-Access, MS-Excel, Crystal Reports, etc that does this formatting for you.

You can choose to do the formatting on the server, but this is a really poor choice.

-PatP|||it's a really poor choice, but it's also a fun challenge for a monday morning

w00h00!!
create table teststuff
( id tinyint not null primary key identity
, foo integer
);
insert into teststuff(foo) values(-1)
insert into teststuff(foo) values(0)
insert into teststuff(foo) values(937)
insert into teststuff(foo) values(1000)
insert into teststuff(foo) values(345678)
insert into teststuff(foo) values(1234567890)
insert into teststuff(foo) values(-111111111)
insert into teststuff(foo) values(3456789)
insert into teststuff(foo) values(-1000)
insert into teststuff(foo) values(-11444)
insert into teststuff(foo) values(-1555666)
insert into teststuff(foo) values(-1333555777)

select id, foo
, case when foo > -999 and foo < 999
then right(space(14)
+cast(foo as varchar)
,14)
when foo > -999999 and foo < 999999
then right(space(14)
+reverse(
stuff(reverse(foo),4,0,','))
,14)
when foo > -999999999 and foo < 999999999
then right(space(14)
+reverse(
stuff(
stuff(reverse(foo),7,0,',')
,4,0,','))
,14)
else right(space(14)
+reverse(
stuff(
stuff(
stuff(reverse(foo),10,0,',')
,7,0,',')
,4,0,','))
,14)
end as formatted
from teststuff

1 -1 -1
2 0 0
3 937 937
4 1000 1,000
5 345678 345,678
6 1234567890 1,234,567,890
7 -111111111 -111,111,111
8 3456789 3,456,789
9 -1000 -1,000
10 -11444 -11,444
11 -1555666 -1,555,666
12 -1333555777 -1,333,555,777

Nulls in indexes

Hi,
If I have a column that allows NULLs and the majority of the queries that
run against the table look for rows when that column is NULL.
Would an index increase the performance of the query?
Thanks,
--AML--Possibly. NULL, seen from am index perspective, is just another value. But i
n the end, usefulness of
an index here is determined by selectivity (how many Null's do you have comp
ared to number of rows),
the query in whole (perhaps there are better indexes) and stuff like that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aaron M. Lowe" <alowe@.uic.edu> wrote in message news:e4TTDQ3PFHA.1236@.TK2MSFTNGP14.phx.gbl
..
> Hi,
> If I have a column that allows NULLs and the majority of the queries that
run against the table
> look for rows when that column is NULL.
> Would an index increase the performance of the query?
> Thanks,
> --AML--
>|||It might, if the majority of the rows are not NULL. In this case, the index
will be highly selective with regard to NULLs and a s will be possible
when you query for Col IS NULL. On the other hand, if most of the rows have
a NULL for that column, the index will not be selective and a scan will most
likely be used...
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Aaron M. Lowe" <alowe@.uic.edu> wrote in message
news:e4TTDQ3PFHA.1236@.TK2MSFTNGP14.phx.gbl...
> Hi,
> If I have a column that allows NULLs and the majority of the queries that
> run against the table look for rows when that column is NULL.
> Would an index increase the performance of the query?
> Thanks,
> --AML--
>|||In addition to the other responses: this index may also be useful if it
is a covering index or clustered index.
Gert-Jan
"Aaron M. Lowe" wrote:
> Hi,
> If I have a column that allows NULLs and the majority of the queries that
> run against the table look for rows when that column is NULL.
> Would an index increase the performance of the query?
> Thanks,
> --AML--

Nulls in columns additions when 1 or more column values is blank

I am running into an issue when adding data from multiple columns into
one alias:

P.ADDR1 + ' - ' + P.CITY + ',' + ' ' + P.STATE AS LOCATION

If one of the 3 values is blank, the value LOCATION becomes NULL. How
can I inlcude any of the 3 values without LOCATION becoming NULL?

Example, if ADDR1 and CITY have values but STATE is blank, I get a
NULL statement for LOCATION. I still want it to show ADDR1 and CITY
even if STATE is blank.

ThanksISNULL(P.CITY,'')
Techhead wrote:

Quote:

Originally Posted by

I am running into an issue when adding data from multiple columns into
one alias:
>
P.ADDR1 + ' - ' + P.CITY + ',' + ' ' + P.STATE AS LOCATION
>
If one of the 3 values is blank, the value LOCATION becomes NULL. How
can I inlcude any of the 3 values without LOCATION becoming NULL?
>
Example, if ADDR1 and CITY have values but STATE is blank, I get a
NULL statement for LOCATION. I still want it to show ADDR1 and CITY
even if STATE is blank.
>
Thanks
>

|||You can use COALESCE, something like this will do it:

COALESCE(P.ADDR1, '') + ' - ' + COALESCE(P.CITY, '') + ', ' +
COALESCE(P.STATE, '') AS LOCATION

Also, you can play with formatting variations based on what you want to get
when one of the columns is NULL, like this:

COALESCE(P.ADDR1, '') + COALESCE(' - ' + P.CITY, '') + COALESCE(', ' +
P.STATE, '') AS LOCATION

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||On Jun 4, 3:29 pm, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

You can use COALESCE, something like this will do it:
>
COALESCE(P.ADDR1, '') + ' - ' + COALESCE(P.CITY, '') + ', ' +
COALESCE(P.STATE, '') AS LOCATION
>
Also, you can play with formatting variations based on what you want to get
when one of the columns is NULL, like this:
>
COALESCE(P.ADDR1, '') + COALESCE(' - ' + P.CITY, '') + COALESCE(', ' +
P.STATE, '') AS LOCATION
>
HTH,
>
Plamen Ratchevhttp://www.SQLStudio.com

Somebody at work told me to use this:

SELECT CASE WHEN P.STATE IS NULL THEN '' ELSE P.STATE END

It seems to work. Is this similar as to what is described above?|||Techhead (jorgenson.b@.gmail.com) writes:

Quote:

Originally Posted by

Somebody at work told me to use this:
>
SELECT CASE WHEN P.STATE IS NULL THEN '' ELSE P.STATE END
>
It seems to work. Is this similar as to what is described above?

Yes, coalesce is a shortcut for the above. The nice thing with coalesce is
that it accept a list of values, and will return the first value that
is non-NULL.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx