Looping to add records to the db

Question

Looping to add records to the db

BrentTeam

Newbie

Points: 1
More actions
October 3, 2003 at 11:04 am

#101698

I am writing to a db through a windows form. I am pulling data from a ListView and looping through to write them. Many of the fields have the same data in them. Once the first record is written I get an error that tells me the row already belongs to the table.
I have worked around it by opening and closing the db connection within the loop, however it takes a very very very long time to complete.
Any suggestions on how to get around this. The records are not the same so I am unsure why I get the error. I have been told by others that it is a SQL bug.
Any help or suggestions would be great.
Thanks
Brent

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

charlieo Newbie Points: 7 More actions · Answer 1

Before inserting the next record try to check in the table if it already exists,if it exists then don't insert otherwise insert the record.

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 2

Are you sure that the data in the loop is refreshing properly?

If after the 1st insert you get a violation it sounds like the data is the same..

Good luck

AJ Ahrens

webmaster@kritter.net

Good Hunting!

AJ Ahrens

webmaster@kritter.net

cneuhold Hall of Fame Points: 3375 More actions · Answer 3

hi!

what do you mean by "the data is the same"? are you using a primary key? if not, i'd strongly suggest you think about creating a pk. if the problem persists, you should think of how values for the pk/uk is provided and maybe introduce some of the process into your application respectively (normally the better solution) to the database itself (eg. identity columns, uniqueidentifiers, anything like that).

best regards,

chris.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Hi Brent,

quote:
I am writing to a db through a windows form. I am pulling data from a ListView and looping through to write them. Many of the fields have the same data in them. Once the first record is written I get an error that tells me the row already belongs to the table.
I have worked around it by opening and closing the db connection within the loop, however it takes a very very very long time to complete.
Any suggestions on how to get around this. The records are not the same so I am unsure why I get the error. I have been told by others that it is a SQL bug.

it would surely help if you could post DDL of your table and maybe some snippets of the application code you use. Otherwise it only a shot in the dark

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Hans Lindgren SSChampion Points: 10496 More actions · Answer 5

Hello everyone!

One suggestion could be to dump your records into and ADO recordset and do a BatchUpdate to a temporary table, insert the non-violating records into your Main table and return the rest to your App so it can elaborate them...

Regards, Hans!