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
 General SQL Server Forums
 New to SQL Server Programming
 dublicate row in one row insert problem

Author  Topic 

derneer
Starting Member

3 Posts

Posted - 2011-06-23 : 15:33:42
Hi Everyone,

I have a problem when insert to a table.

I have a vb windows form applicaton and mssql 2007 db server. I insert row to db in vb code in store proceder.

Sometimes, Although i insert one row in code, we see dublicate same row in db (only milisecond differ)

like :

(IDENTITY)(OTHER INSERT VALUE)(OTHER INSERT VALUE)(OTHER INSERT VALUE)
35739 81 69860 2011-06-22 10:33:43.160
35734 81 69860 2011-06-22 10:33:17.723

How can i solve this problem ?

Thnks...



sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-23 : 15:41:36
Are your users double-clicking on the insert button? Can you log how many times the insert code gets called in the VB app and see if that matches with the number of inserts in the database?

If the query/stored proc that inserts the data is a simple insert (i.e., no recursive triggers or other hairy and scary things), source of your problem may not be the database.
Go to Top of Page

derneer
Starting Member

3 Posts

Posted - 2011-06-23 : 16:04:51
quote:
Originally posted by sunitabeck

Are your users double-clicking on the insert button? Can you log how many times the insert code gets called in the VB app and see if that matches with the number of inserts in the database?

If the query/stored proc that inserts the data is a simple insert (i.e., no recursive triggers or other hairy and scary things), source of your problem may not be the database.



Thanks quick reply..

if users one clicking insert button,i do that button enabled false.
In log same time clicking and inserting

Also, on the button clcik, i have different an insert and an update statement in different table. if i one insert and see double inserted, update statement do only one because update count increase only one, not two.

Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-23 : 16:09:10
Can you post your insert and update queries/stored procs along with the DDL for the tables? Brett's post here might be helpful: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

derneer
Starting Member

3 Posts

Posted - 2011-06-24 : 03:08:07
Hi,

My insert table

[ code]
USE [mydb]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[MYTABLE_INSERT](
[LID] [int] IDENTITY(1,1) NOT NULL,
[ID] [int] NULL,
[X] [nvarchar](50) NULL,
[Y] [nvarchar](50) NULL,
[Z] [nvarchar](max) NULL,
[INSERT_DATE] [smalldatetime] NULL,
[OTHER_DATE] [smalldatetime] NULL,
[AID] [int] NULL
) ON [PRIMARY]

GO

[ /code]

my update table
[ code]
USE [MYDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[MYTABLE_UPDATE](
[A] [nvarchar](255) NULL,
[B] [float] NULL,
[C] [nvarchar](255) NULL,
[STATUS] [int] NULL,
[AID] [int] NULL,
[ID] [int] IDENTITY(1,1) NOT NULL,
[SONUC] [nvarchar](255) NULL,
[UPDATE_DATE] [datetime] NULL,
) ON [PRIMARY]

GO

[ /code]

my insert store prosedure

[code]
USE [mydb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO






CREATE PROCEDURE [dbo].[sp_MYTABLE_INSERT]
@ID INT,
@X nvarchar(50),
@Y nvarchar(50),
@Z nvarchar(MAX),
@OTHER_DATE smalldatetime,
@AID INT

as
INSERT INTO MYTABLE_INSERT(ID,X,Y,Z,INSERT_DATE,OTHER_DATE,AID)
VALUES(@ID,@X,@Y,@Z,GETDATE(),@OTHER_DATE,@AID)


GO
[ /code]

my update store prosedure

[code]
USE [mydb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[sp_MYTABLE_UPDATE] (
@ID INT,
@AID INT,
@Z nvarchar(MAX)

)
AS
BEGIN
UPDATE MYTABLE_UPDATE
SET STATUS = STATUS + 1, AID=@AID, SONUC=@Z, UPDATE_DATE=GETDATE()
WHERE ID=@ID
END
GO
[ /code]

my db scenerio is that. please help me
Go to Top of Page

jfarrugia
Yak Posting Veteran

55 Posts

Posted - 2011-06-24 : 06:14:41
if there is a diff of milliseconds, there might be some issue you vb code thats triggering the update twice

Where software development knowledge meets the reader
Go to Top of Page
   

- Advertisement -