Re: Identity Seed vs varchar - Database Discussion Boards

Re: GUID - SQL Server 2005

N a v a n e e t h15-Dec-07 22:33

N a v a n e e t h

15-Dec-07 22:33

Thanks mark. Thank you very much

All C# applications should call Application.Quit(); in the beginning to avoid any .NET problems.- Unclyclopedia

My Website | Ask smart questions

Identity column with LINQ to SQL

Brady Kelly10-Dec-07 23:22

Brady Kelly

10-Dec-07 23:22

I'm trying my hand at using LINQ to SQL as a data source for a little pet project. My main requirement is to capture an invoice, and for this I have a simple master-detail screen with an InvoiceBindingSource and an InvoiceLineBindingSource. The invoice source binds to several textbox edit fields etc. and lines source to a DataGridView. Now when I try and insert a second invoice record, I get a primary key violation because the invoice id 0 already exists. How do I get LINQ to recognise the identity column?

Re: Identity column with LINQ to SQL

Brady Kelly11-Dec-07 2:11

Brady Kelly

11-Dec-07 2:11

Sorry, that was a brain fart. I hadn't made the field IDENTITY in the DB, so the O/R designer didn't set the Auto Generated Value flag.

Pls help: Error converting data type varchar to numeric

masyarial10-Dec-07 15:15

masyarial

10-Dec-07 15:15

Hi, I have this error when doing 'sum' on table,
whereas I convert it fist before doing 'sum'.
here is my code:
sum(convert(decimal(5,5),value)).

After I look the data 'value', I found that there is
char '%', here is my table:
'value'
2.131 %
2.412 %
2.612 %

So any suggestion? or is there a code for mid(string,start,length) in sql
Thanks for your help.

Re: Pls help: Error converting data type varchar to numeric

SimulationofSai10-Dec-07 20:22

SimulationofSai

10-Dec-07 20:22

Try

select sum(convert(decimal, replace(value, '%', ''))) from table1

Good Luck.

SG
Cause is effect concealed. Effect is cause revealed.

Re: Pls help: Error converting data type varchar to numeric

masyarial11-Dec-07 14:48

masyarial

11-Dec-07 14:48

Hi,

Thanks, it's work.
Then I adding char '%' again to my 'value'
convert(varchar,sum(convert(decimal, replace(value, '%', ''))))+' %',

Regards,

Identity Seed vs varchar

Brendan Vogt10-Dec-07 12:23

Brendan Vogt

10-Dec-07 12:23

Hi,

I was advised to stay away from using identity seeds where the number increments inteself by everytime but to use a varchar(10) for example. And I was also told that having a varchar ID slows down insert/update/ But inserting/updating 1 record shouldn't be that bad? But I'm more worried about sting searches on the ID. Will it slow down searches??

Can I please hear your thoughts on this? I am looking for best practices please. I am using SQL Server 2005.

Regards,
Brendan

Re: Identity Seed vs varchar

Colin Angus Mackay10-Dec-07 22:40

Colin Angus Mackay

10-Dec-07 22:40

.NET Enthusiast wrote:
I was advised to stay away from using identity seeds where the number increments inteself by everytime but to use a varchar(10) for example. And I was also told that having a varchar ID slows down insert/update/ But inserting/updating 1 record shouldn't be that bad? But I'm more worried about sting searches on the ID. Will it slow down searches??

If you must use string based IDs then you should go for fixed length columns, e.g. CHAR(10). However, it is better to use an INT with an identity on it. It will be much more efficient.

Upcoming FREE developer events:
* Developer Day Scotland

My website | blog

Re: Identity Seed vs varchar

Brendan Vogt10-Dec-07 22:45

Brendan Vogt

10-Dec-07 22:45

Thanks for both replies Colin.

Sequencing a varchar field

Brendan Vogt10-Dec-07 12:18

Brendan Vogt

10-Dec-07 12:18

Hi,

I have a customer ID of type varchar(10) and I want to use it like a identity seed. I want the first customer to start with 1000 and then incremented each time a new customer is added. I could probably do this with C#, but I think a safer option will maybe be to do it with SQL Server 2005??

Please can someone advise me on this? I need to check first if a record exists, if not then the ID is 1000, other get the number and increment it with 1.

I hope to hear from someone soon.

Regards
Brendan

Re: Sequencing a varchar field

Colin Angus Mackay10-Dec-07 22:37

Colin Angus Mackay

10-Dec-07 22:37

.NET Enthusiast wrote:
I have a customer ID of type varchar(10) and I want to use it like a identity seed. I want the first customer to start with 1000 and then incremented each time a new customer is added. I could probably do this with C#, but I think a safer option will maybe be to do it with SQL Server 2005??

Please can someone advise me on this? I need to check first if a record exists, if not then the ID is 1000, other get the number and increment it with 1.

Why use a varchar? Why not declare the column as INT IDENTITY(1000,1)? That would do everything you described that you want and be a lot safer than writing your own mechanism.

Upcoming FREE developer events:
* Developer Day Scotland

My website | blog

Re: Sequencing a varchar field

Brendan Vogt11-Dec-07 1:47

Brendan Vogt

11-Dec-07 1:47

If I have an int identity starting at 1000 then the ids will obviously be higher. It won't affect performance when searches are being done?

Re: Sequencing a varchar field

Colin Angus Mackay11-Dec-07 1:54

Colin Angus Mackay

11-Dec-07 1:54

.NET Enthusiast wrote:
If I have an int identity starting at 1000 then the ids will obviously be higher. It won't affect performance when searches are being done?

They are all stored as 32bits whether is it 1, 1000, or 2147483647. The value of the number is not going to affect performance.

Upcoming FREE developer events:
* Developer Day Scotland

My website | blog

Stored Proc

alok_2k310-Dec-07 2:05

alok_2k3

10-Dec-07 2:05

Hi,

I am developing a web page for searching user records from DB. In advance search option there are 5 fields like firstname, lastname, years of experience,experience type, location.

I want to write a storedprocedure which take all these 5 parameter as input and return me record from a table.All these fields are in single table.

User can search a record by providing all the 5 fields are by providing any of these.

Plz help me in writing this query.

I am very week in T-Sql.

Thanks in Advance

Alok....
Alok...

Re: Stored Proc

Mark Churchill10-Dec-07 2:36

Mark Churchill

10-Dec-07 2:36

Write a stored procedure that takes the values you want. Pass the value the user is searching for, or null if no value on that column

Then your query can look like:

select * from sometable
where
(ColumnA = @SearchFieldA or @SearchFieldA is null)
and (ColumnB = @SearchFieldB or @SearchFieldB is null)

etc

Mark Churchill
Director
Dunn & Churchill
Diamond Binding: Zero to Data Layer in 3 mins

Re: Stored Proc

alok_2k310-Dec-07 20:29

alok_2k3

10-Dec-07 20:29

Thanx Mark

How to insert Data in the Following Tables

Vimalsoft(Pty) Ltd10-Dec-07 0:57

Vimalsoft(Pty) Ltd

10-Dec-07 0:57

Good afternoon All

I have an Access Table(Property_Mass_3) and a SQL table(Property_Mass), these two table have the same structure,and they have a Primary_key named "Property_ID". So i want to Export Data from an access table to a SQl table, so am using the Export and import data wizard. so in the SQL table, there are records, these records have the Field "Property_ID", and i dont want to import records from "Property_Mass_3" that have the same Property_id of the record that exists in Property_Mass, Basically i want to import records from Accees, and if the process of importing find that the Property_ID that it tries to import exists, then it should not import that record.

Thanks

Vuyiswa Maseko,

Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding

VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za

Re: How to insert Data in the Following Tables

pmarfleet10-Dec-07 5:11

pmarfleet

10-Dec-07 5:11

You don't get this level of control with the Import/Export wizard.

If you are using SQL Server 2005, you could write a package using SQL Server Integration Services (SSIS) to perform the import. SSIS gives you fine control over the import logic.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Date format in sql

imnotso#9-Dec-07 23:49

imnotso#

9-Dec-07 23:49

I've a varchar variable in my stored procedure returning either a date or a piece of text depending on the condtional code.

Both return fine but the date comes back as '6 DEC 2007 08:04am' because of the varchar.

How do I get it to return in the format 'dd/mm/yyy hh:mm:ss' ?

I've tried cast as, convert and the date formatting, it always returns as described.

Should I format it in my c# code behind, it that eaiser?

Thanks in advance.

Harvey

Re: Date format in sql

SimulationofSai10-Dec-07 0:30

SimulationofSai

10-Dec-07 0:30

When you convert date to varchar, you can specify styles which will tell how to format the output of the convert function.

imnotso# wrote:
How do I get it to return in the format 'dd/mm/yyy hh:mm:ss' ?

You can try

CONVERT(varchar, Getdate(), 131)

SG
Cause is effect concealed. Effect is cause revealed.

Re: Date format in sql

imnotso#10-Dec-07 0:54

imnotso#

10-Dec-07 0:54

Thanks for the quick response, I have tried that but it converts it to a strange date. not the date entered. I have done it in the code behind in c#.

Thanks though.

Harvey

MS Exchange connection?

Mr.Kode9-Dec-07 22:27

Mr.Kode

9-Dec-07 22:27

i wanna know how can i use SQL connection to Access MS Exchange server ,
and what is the benefits?

how to avoid data redundancy in insert query?

kssknov9-Dec-07 20:50

kssknov

9-Dec-07 20:50

hi all
i am a beginner, i have written an insert stored procedure.
i need my SP to check for the ID field whether the data is already present.

pls help ,me.

ALTER PROCEDURE [dbo].[SP_SaveGroupClassification]
@SubGroupName nvarchar(50),
--@GroupId int,
@ParentId int,
@ClassType nchar(2),
@RootId int
as
begin
INSERT INTO GroupClassification
(GroupName, ParentId, ClassType, RootId)
VALUES (@SubGroupName,@ParentId,@ClassType,@RootId)
end
return

Here i need to check the value in (@SubGroupName not to be repeated.it is my key field.

thank u

senthil

Re: how to avoid data redundancy in insert query?

Tobias Schoenig9-Dec-07 21:50

Tobias Schoenig

9-Dec-07 21:50

Hi,

what you could do is try to get the ID for the specified @SubGroupName. If the ID does not exisit, you can insert the new row.

DECLARE @ID int<br />
SET @ID = (SELECT ID FROM GroupClassification WHERE GroupName = @SubGroupName ORDER BY ID DESC)<br />
IF(@ID IS NULL)<br />
BEGIN<br />
	INSERT INTO GroupClassification(GroupName, ParentId, ClassType, RootId)<br />
                    VALUES (@SubGroupName,@ParentId,@ClassType,@RootId)	<br />
END

Re: how to avoid data redundancy in insert query?

kssknov16-Dec-07 20:15

kssknov

16-Dec-07 20:15

hi,
it is working

Thank u Tobias Schoenig.

senthil

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.