I want to check if a Key already exists in a Table. So the user can see a message indicating that the key is already entered in the table. How do i do that?
Any help
Im using Asp.Net
I want to check if a Key already exists in a Table. So the user can see a message indicating that the key is already entered in the table. How do i do that?
Any help
Im using Asp.Net
Primary keys are unique. You shouldn't be needing to check for duplicate keys. If it is not unique, you need to create a another column that contains a unique value and use it as the primary key.
"The best software is the one that fits your needs." - A_A
Virus free since: date unknown
Anti-virus free since: August 2008
don't generate yoru primary key on your own. make the db do it automatically and set the field to primary key.. (auto increment).. generating it your self and maintaining its integrity would mean you'ld have to pull the entire field and check the new value against all the results and if it is already there you'ld have to regenerate and recheck untill a unique value is genreated which would make your script really slow.
death_knight i agree, dito all u said... but since not all databases support auto-incremented primary keys... we have a manual-increment table setup.
it works a little like this:
SequenceNumbers table has last incremental number for any table u want to have an (auto) incremented key.
when u need a key u call a stored procedure which increments a field in the table, and returns that value.
u may pass in the type of increment u want and use one procedure... or make a different procedure for each increment
[will edit this to add SQL for tables]
Code:CREATE TABLE SequenceNumber( SequenceID int NOT NULL, NextCellNo int NOT NULL default 0, NextRowNo int NOT NULL default 0, NextColumnNo int NOT NULL default 0, CONSTRAINT PK_SequenceNumber PRIMARY KEY (SequenceID) )
Code:create procedure spGetNextCellNo @SequenceID int, @CellNo int output as update SequenceNumber set @CellNo = NextCellNo, CellNo = CellNo + 1 where SequenceID = @SequenceID return @CellNo
Last edited by icymint3; Mar 7, 2007 at 02:13 PM.
Cultured in Aggression and Koding like a Warrior!!
“Common sense is instinct. Enough of it is genius.” - George Bernard Shaw.
"The significant problems we face cannot be solved by the same level of thinking that created them." - Albert Einstein
Thanks guys but the thing is im using their Id numbers as the primary key. i dont want when adding a new user to the table more that one users have the same id number. so i want to validate if its there already without going to the ugly screen saying that the primary key already exists
Use a Try Catch to catch the error and display a more friendly error message...
but jjerry, the thing is, if u generate it... u will be certain that it does not already exist... without checking it... and that is faster... than checking and give error message until a key that does not exist is found.
what database u using... maybe it has the said functionality built in already
Cultured in Aggression and Koding like a Warrior!!
“Common sense is instinct. Enough of it is genius.” - George Bernard Shaw.
"The significant problems we face cannot be solved by the same level of thinking that created them." - Albert Einstein
im kinda new to ASP.net
im using sql server 2000.
its a good idea to let the database autogenerate the PK but due to the requirements of my project thats what i need to do.
ok...since u insist on not using auto incr. or user prog. gen key...
do this
/* code starts here*/
Create Procedure InsertRecord
@columnKey varchar(10),
@otherColumn varchar(20),
@msg varchar(200) output
AS
set @msg = 'inserted'
if exists(Select * from tablename where primarykeycol = @columnKey )
begin
set @msg = 'user already exists/ or whatever u wanna say'
goto Handle_err
end
...insert statement...
return 0
Handle_err:
return -1
GO
/* code ends here*/
this way it won't throw a db exeception but instead do a soft handle...u can also do other forms of logging and stuff...Also u can display the @msg variable as the user message on you frontend app.
That should do it... u could also implement check constraints on ur table/fields but the sp will work little better for u.
Last edited by codecarnage; Mar 9, 2007 at 11:39 AM.