Database Discussion Boards

Re: Duplicate with the Same Identity Number

Pete O'Hanlon1-Oct-07 22:19

1-Oct-07 22:19

Hmmm. Did you remove the unique constraint from your primary key field by any chance (the identity column is a primary key, isn't it)? This constraint is there to ensure that you can't have duplicate fields.

Anyway, there are a number of ways that you could do this, but the implementation really depends on factors such as do you have foreign keys linked to your table? If so, then you really need to be careful about deleting.

A quick thought has occurred to me - how did you select these records? Are they part of a multi-table select? If so, it is likely that what you are seeing is the result of a one-to-many relationship. Try selecting the data out of the table on its own.

If a single table select still produces duplicate data and it's not a part of a foreign key then you can do the following:

Open up your table in the designer and drop the identity column. Save it to the database. Then add the identity column back into the table and save it again. The stages here drop the identity column and then force the database to apply a new identity column - which will have the effect of giving a new Id to each record. Voila - you now have unique rows.

Deja View - the feeling that you've seen this post before.

Re: Duplicate with the Same Identity Number

Vimalsoft(Pty) Ltd1-Oct-07 22:50

Vimalsoft(Pty) Ltd

1-Oct-07 22:50

Hi

Thanks for your reply

i did not remove the primary key Constraint. And there is a Foreign key linked to this table.

Vuyiswa Maseko,

Sorrow is Better than Laughter, it may Sudden 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: Duplicate with the Same Identity Number

Pete O'Hanlon1-Oct-07 22:53

Pete O'Hanlon

1-Oct-07 22:53

I fail to see how you've got this problem then because the unique constraint prevents duplicate data from being posted. The identity column is the primary key isn't it?

BTW - just a quick confidence test. You are using SQL Server aren't you?

Deja View - the feeling that you've seen this post before.

Re: Duplicate with the Same Identity Number

Vimalsoft(Pty) Ltd1-Oct-07 23:08

Vimalsoft(Pty) Ltd

1-Oct-07 23:08

yes, it is the Primary key, i have not used the Unique Constraint, sorry about that. i thought if i make a Column Primary key and its identity. it will enforce the Uniqueness. my Field is a Primary key, and i have not defined the unique constraint.

Thanks

Re: Duplicate with the Same Identity Number

ChandraRam2-Oct-07 20:59

ChandraRam

2-Oct-07 20:59

Vuyiswamb wrote:
yes, it is the Primary key, i have not used the Unique Constraint, sorry about that. i thought if i make a Column Primary key and its identity. it will enforce the Uniqueness. my Field is a Primary key, and i have not defined the unique constraint.

Surely if the field is the primary key, the database would not have allowed you to fill duplicate values in the first place?

And I also noticed that you said something like "looked identical" in your first post... how did you determine that you had duplicates in your table?

Re: Duplicate with the Same Identity Number

Vimalsoft(Pty) Ltd2-Oct-07 21:18

Vimalsoft(Pty) Ltd

2-Oct-07 21:18

i have used a Query to Determine that.

SELECT *
FROM sde.Property p1
WHERE (FUNC_KEY = 'PV000000') AND EXISTS
        (SELECT 1
      FROM sde.Property p2
      WHERE p2.lis_key = p1.lis_key
      HAVING COUNT(p2.Property_ID) = 2)
      order by Property_ID Asc

Thanks

Re: Duplicate with the Same Identity Number

ChandraRam2-Oct-07 21:22

ChandraRam

2-Oct-07 21:22

What happens if you just say

SELECT * FROM sde.Property WHERE (FUNC_KEY = 'PV000000')

how many records do you see?

Re: Duplicate with the Same Identity Number

Vimalsoft(Pty) Ltd2-Oct-07 22:04

Vimalsoft(Pty) Ltd

2-Oct-07 22:04

i see Records with Dublicates in Property_ID.

Re: Duplicate with the Same Identity Number

ChandraRam2-Oct-07 22:32

ChandraRam

2-Oct-07 22:32

Vuyiswamb wrote:

i see Records with Dublicates in Property_ID.

Then it looks like Property_ID is not the primary key of this table.

Re: Duplicate with the Same Identity Number

John-ph1-Oct-07 22:52

John-ph

1-Oct-07 22:52

Either you must be referring to a Foreign Key Column for which Unique-Key Constraint is not set or You must be doing a Multi-Table Select. There is no chance of Identiy Primary key Column to generate Duplicate value.

Regards
J O H N

"Even eagles need a push." David McNally

Re: Duplicate with the Same Identity Number

Suaysh Andhare3-Oct-07 0:18

Suaysh Andhare

3-Oct-07 0:18

before deleting the record by below query
say

"delete from table1
where id='20'"

write set rowcount 1

it will delete only one if u had two identical row.
else if u have bulk records to be deleted

create temptable copy all distinct records to tahat table
truncate ur original table and copy the data from temptable ot original table

ex
step 1
create table #temptable
insert into #temptable
select distinct * from original table

step 2
truncate table original table

step 3
insert into #temptable
select * from #temptable x

hope it will work

Regards,
Suyash

export data from sqlserver to excel

mehran.asg1-Oct-07 21:20

mehran.asg

1-Oct-07 21:20

hi. I want to export data from sqlserver to excel . but I want to do this with sql query , not enterprise manager.
is there any query to do this?

thanks.

Re: export data from sqlserver to excel

pmarfleet1-Oct-07 21:48

pmarfleet

1-Oct-07 21:48

Have a look at this article[^].

Paul Marfleet

fetching database schema using ado.net

chanzeb1-Oct-07 21:19

chanzeb

1-Oct-07 21:19

i want to fetch the database schema(either MSACCESS OR SQLSERVER 2005).
language c#.

chanzeb chaudhary

Re: fetching database schema using ado.net

Kschuler2-Oct-07 7:29

Kschuler

2-Oct-07 7:29

You can get schema data from a DataAdapter object. For an Access DB, you can use System.Data.OleDb.OleDbDataAdapter. For SQL, System.Data.SqlClient.SqlDataAdapter. You use this object in conjunction with a connection object, a command object, and either a datatable or dataset. You can get the schema by using the .FillSchema function. Like this:
daMyDataAdapter.FillSchema(dsMyDataSet, SchemaType.Mapped)

You can search for more information in the articles and they can probably explain it better. Another way to get the schema is to setup a regular select statement but add 1=2 to the WHERE clause. Since 1 will never equal 2, it will bring back an empty dataset.

Hope this helps.

Re: fetching database schema using ado.net

PIEBALDconsult3-Oct-07 15:29

PIEBALDconsult

3-Oct-07 15:29

This was asked and answered a few days ago.

http://www.codeproject.com/script/comments/forums.asp?msg=2249166&forumid=1725&XtraIDs=1649&searchkw=getschematable&sd=15+Sep+2007&ed=3+Oct+2007&author=PIEBALDconsult&stype=3#xx2249166xx[^]

Problem with Attach In 2005

mehrdadc481-Oct-07 8:14

mehrdadc48

1-Oct-07 8:14

Hi.
In SQL Server 2005, when I attach a DB that is made in another computer, the primary key and Identity and diagram will be removed.
Oh! the diagram section don't open at all, so that I after attach must create another DB and imports tables in it, and then set primary key, Identity and make diagram!
Why?

Best wishes

Re: Problem with Attach In 2005

Marek Grzenkowicz2-Oct-07 1:22

Marek Grzenkowicz

2-Oct-07 1:22

I've just checked this.

Nothing like this happens. Do you save the changes in the diagram and tables after you make them?

Help with FormView please

sjp7001-Oct-07 8:13

sjp700

1-Oct-07 8:13

Hi,
Is it possible to databind user.identity.name to formview text box that so user name is included when using insert New command. I want to include user name in sql table using formview without user having to input name?

Any help or appropriate link would be much appreciated.

Thanks

Steve

Re: Help with FormView please

Pete O'Hanlon1-Oct-07 10:17

Pete O'Hanlon

1-Oct-07 10:17

If you want to use the User.Identity.Name then you need to either use Windows authentication or Forms Authentication. This really isn't the forum for this, but this[^] or this[^] article might help you.

Deja View - the feeling that you've seen this post before.

Identity after updated

webhay1-Oct-07 7:17

webhay

1-Oct-07 7:17

Hi,

I fixed an after update trigger with a table in Sql server 2005 and i want to get the last Identity updated in the this table to inserted in another table. How can i get the last updated Identity in a table?

Please help
Thank you
Sms

Re: Identity after updated

pmarfleet1-Oct-07 9:50

pmarfleet

1-Oct-07 9:50

Do you mean that you have an Identity column in your table and, following an update, you want to find out he value in this field for the records that were updated? If so, you should look at the values in this field for the rows in the inserted entity in your trigger implementation.

Paul Marfleet

Re: Identity after updated

Pete O'Hanlon1-Oct-07 10:41

Pete O'Hanlon

1-Oct-07 10:41

If you need the identity of the inserted record in the main table, use the scope_identity() function to retrieve the data.

Deja View - the feeling that you've seen this post before.

CurrentDb Connection String...

new_phoenix1-Oct-07 7:04

new_phoenix

1-Oct-07 7:04

I would appreciate some info regarding how to connect to the currently open database using the CurrentDb connection string. I am not certain of the syntax. Here is what I have so far:

Dim dbsHeadcount As Database
Dim Cnxn As ADODB.Connection
Dim strConn As String
Dim rstInputFile As ADODB.Recordset
Dim cmdSQLInputFile As ADODB.Command
Dim strSQLInputFile As String
Dim rstHyperionMany As ADODB.Recordset
Dim cmdSQLHyperionMany As ADODB.Command
Dim strSQLHyperionMany As String
Dim rstHyperionOne As ADODB.Recordset
Dim cmdSQLHyperionOne As ADODB.Command
Dim strSQLHyperionOne As String
Dim strDBPath As String
Dim strFileName As String
Dim strMessage As String

Set dbsHeadcount = CurrentDb
Set cmdSQLInputFile = New ADODB.Command
Set cmdSQLInputFile.ActiveConnection = CurrentDb.Connection   '  IS THIS RIGHT??? IF NOT WHAT IS THE SYNTAX??? :confused::confused::confused:
strSQLInputFile = "SELECT [COUNTRY], [TYPE], [BUSINESS UNIT], " & _
    "[L/R/G], [REGION], [JOB FUNCTION], [09/12/2007 Reported] " & _
    "[NUMINDEX] FROM [TBLINPUTFILE]"
cmdSQLInputFile.CommandType = adCmdText
cmdSQLInputFile.CommandText = strSQLInputFile
Set rstInputFile = cmdSQLInputFile.Execute()
rstInputFile.MoveFirst

Set cmdSQLHyperionMany = New ADODB.Command
Set cmdSQLHyperionMany.ActiveConnection = CurrentDb.Connection
strSQLHyperionMany = "SELECT [COUNTRY], [TYPE], [BUSINESS UNIT], " & _
    "[L/R/G], [REGION], [JOB FUNCTION], [09/12/2007 Reported] " & _
    "FROM [tblInputFile]"
cmdSQLHyperionMany.CommandType = adCmdText
cmdSQLHyperionMany.CommandText = strSQLHyperionMany
Set rstHyperionMany = cmdSQLHyperionMany.Execute()
rstHyperionMany.MoveFirst

The problem is how do I connect to the CurrentDb.Connection that is currently active?

Re: CurrentDb Connection String...

Dave Kreskowiak1-Oct-07 7:13

Dave Kreskowiak

1-Oct-07 7:13

In what context??? In this VBA code in Access?? It looks like it, but we have to be sure.

I think you might be looking for something closer to this:

Dim conn As ADODB.Connection
Dim comm As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim param As ADODB.Parameter

    Set conn = Application.CurrentProject.Connection
    comm.ActiveConnection = conn
    ...

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

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