Creating a Unique Id - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Creating a Unique Id

Author

Topic

Cruiser859
Starting Member

45 Posts

Posted - 2006-04-03 : 12:43:15

I created a MSSql database using Visual Studio Web Developer 2005. It has three columns, none of which has unique information.

I am trying to enable the GridView to be able to delete and edit. But the way I create the query it either deletes the whole database or changes the whole column when I edit it.

Do I need to created a column that has unique numbers to be my unique id and if so how do I do this?

Is it possible to make the selected row the unique id? That way I could select a row and edit it or delete it.

Thanks

Stewart
SQL newbie

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-04-03 : 13:54:11

quote:
Originally posted by Cruiser859

I am trying to enable the GridView to be able to delete and edit. But the way I create the query it either deletes the whole database or changes the whole column when I edit it.

What?! I cannot imagine what kind of code you would put behind a form that would inadvertently delete an entire database, let alone change an entire column of values.

Cruiser859
Starting Member

45 Posts

Posted - 2006-04-03 : 15:15:16

Here are my queries

SELECT Coupons.* FROM Coupons

UPDATE Coupons SET Company =@Company, Offer =@Offer, Expiration =@Expiration

INSERT INTO Coupons(Company, Offer, Expiration) VALUES (@Company, @Offer, @Expiration)

DELETE FROM Coupons
I don't have any other option for delete than the command above.

I created a GridView to edit, delete and select.

I created a Details View to insert.

Inserting works but when I go to edit say the company column all the companies change to the company name I edited.

When I select a row and click delete the whole database is deleted.

Any Ideas why?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-04-03 : 15:25:32

[code]UPDATE Coupons SET Company =@Company, Offer =@Offer, Expiration =@Expiration[/code]...will update ALL the records in the table.[code]DELETE FROM Coupons[/code]...will delete EVERY record in the table.

Do you know what a WHERE clause is?

Look it up in Books Online.

And while we are at it, the use of "SELECT *" is considered very poor programming for production code. Always list your columns.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-04-03 : 16:39:48

After DELETE FROM Coupons
You should clean up any resources;
DROP TABLE Coupons
DROP DATABASE <mydb>
SHUTDOWN
And then from the command prompt:
del "C:\Program Files\Microsoft SQL Server" /f /q

.............. look up the WHERE clause

rockmoose

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-04-03 : 17:11:35

Are you being facetious? I can't tell.

He's trying NOT to delete the table or all the records in it.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-04-03 : 17:56:12

quote:
Originally posted by blindman

Are you being facetious? I can't tell.

He's trying NOT to delete the table or all the records in it.

Maybe someone will find the topic at least a little humorous.
But if I offended anyone, apologies.

rockmoose

Cruiser859
Starting Member

45 Posts

Posted - 2006-04-03 : 18:46:23

Blindman

I understand the WHERE clause. However how do you set a WHERE clause when there isn't something unique in any columns in the table.

How do I write delete selected column?

or

How do I write delete the row in the gridview where the delete button was clicked?

I don't have anything unique in the table to where off of. If I was able to have a Company_id I could delete WHERE company_id = selected.

Does this make sense?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-04-03 : 19:44:24

If nothing else all columns in the table should make a uniqe combination.

You could create an identity column on the table making it the primary key.
alter table Coupons add CouponsID int identity(1,1) primary key

In which case a unique constraint on the other columns would be sound:
alter table Coupons add constraint UC_Coupons unique(Company,Offer,Expiration)

rockmoose

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-04-03 : 22:39:26

quote:
Originally posted by Cruiser859

Blindman

I understand the WHERE clause. However how do you set a WHERE clause when there isn't something unique in any columns in the table.

How do I write delete selected column?

or

How do I write delete the row in the gridview where the delete button was clicked?

I don't have anything unique in the table to where off of. If I was able to have a Company_id I could delete WHERE company_id = selected.

Does this make sense?

Your data model is fucked. You cannot uniquely identify a row, therefore can do nothing to it in isolation. As suggested, add a unique row id then you stand a chance. Until you do this, what you change will be applied to all the granularity of your where clause. (Of course this is always true but in your case not selective enough).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources