473,554 Members | 3,237 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Error while inserting data into table

10 New Member
I am developing an application which grab a comma delimited file and inserts them into a database. but while inserting it gives me an error. the code is below.

Do While Not EOF(iDrop)
Line Input #iDrop, iLine
iArray = Split(iLine, ";")
iArray(5) = Round(iArray(5) / 21.21, 0)
With rs
.AddNew
.Fields("ID") = crID
.Fields("NO") = iArray(0)
.Fields("SID") = iArray(1)
.Fields("CID") = iArray(2)
.Fields("CClass ") = iArray(3)
.Fields("Cfier" ) = iArray(4)
.Fields("DB") = iArray(5)
.Update
End With
Loop
Close #iDate

Error:
unexpected error -2147217873 in subroutine dosomething.
[microsoft][odbc sql server driver][sql server] violation of primary key constrain PK_DBand. cannot insert duplicate key in object DB


here Dband is my table with primary key ID and DB is one of my column in the table Dband.

i am not sure why i am getting this error. It works for some records until i get this message.

in all i have about 1000 records and it does only 70-80 records and gives me this error.

any clues why this is happening. i am not sure the explaination helps or not please ask me question to understand more.
thanks
Jan 3 '07 #1
11 2676
Killer42
8,435 Recognized Expert Expert
You are splitting on a semicolon (;) but you said the file is comma-delimited.

Oh, and a question - where is crID getting its value?
Jan 3 '07 #2
pradeepss
10 New Member
sorry its semicolon delimited...and also crID is query done on the another table and is a statis value from a table. crID i get it correctly and is not an issue.


You are splitting on a semicolon (;) but you said the file is comma-delimited.

Oh, and a question - where is crID getting its value?
Jan 3 '07 #3
Killer42
8,435 Recognized Expert Expert
sorry its semicolon delimited...and also crID is query done on the another table and is a statis value from a table. crID i get it correctly and is not an issue.
So, does that mean we're looking at a cut-down version of the code? As it stands, it looks as though it should produce a duplicate-value error on your primary key for the second record, because of .Fields("ID") = crID.
Jan 4 '07 #4
Killer42
8,435 Recognized Expert Expert
I wonder whether the 70-80 records might just reflect some buffer size, and the error occurs when (for instance) the buffer is filled and passed to the server.

Could you try chopping up the text file and loading the first couple of hundred records into 50-record chunks, to see whether it behaves the same?
Jan 4 '07 #5
Killer42
8,435 Recognized Expert Expert
...
Do While Not EOF(iDrop)
Line Input #iDrop, iLine
...
Close #iDate
I doubt this is significant. But just to satisfy my curiosity, is #iDate a typo, or a different file to #iDrop, or an error in the code? Also, just for clarity, I'm going to post a copy of your code here with the appropriate COD tags around it. That way, the indenting will come through.
Expand|Select|Wrap|Line Numbers
  1. Do While Not EOF(iDrop)
  2.     Line Input #iDrop, iLine
  3.     iArray = Split(iLine, ";")
  4.     iArray(5) = Round(iArray(5) / 21.21, 0)
  5.     With rs
  6.         .AddNew
  7.         .Fields("ID") = crID
  8.         .Fields("NO") = iArray(0)
  9.         .Fields("SID") = iArray(1)
  10.         .Fields("CID") = iArray(2)
  11.         .Fields("CClass") = iArray(3)
  12.         .Fields("Cfier") = iArray(4)
  13.         .Fields("DB") = iArray(5)
  14.         .Update
  15.     End With
  16. Loop
  17. Close #iDate
Jan 4 '07 #6
pradeepss
10 New Member
I doubt this is significant. But just to satisfy my curiosity, is #iDate a typo, or a different file to #iDrop, or an error in the code? Also, just for clarity, I'm going to post a copy of your code here with the appropriate COD tags around it. That way, the indenting will come through.
Expand|Select|Wrap|Line Numbers
  1. Do While Not EOF(iDrop)
  2.     Line Input #iDrop, iLine
  3.     iArray = Split(iLine, ";")
  4.     iArray(5) = Round(iArray(5) / 21.21, 0)
  5.     With rs
  6.         .AddNew
  7.         .Fields("ID") = crID
  8.         .Fields("NO") = iArray(0)
  9.         .Fields("SID") = iArray(1)
  10.         .Fields("CID") = iArray(2)
  11.         .Fields("CClass") = iArray(3)
  12.         .Fields("Cfier") = iArray(4)
  13.         .Fields("DB") = iArray(5)
  14.         .Update
  15.     End With
  16. Loop
  17. Close #iDate


Sorry but #iDate is a different file name which i close.
Jan 5 '07 #7
Killer42
8,435 Recognized Expert Expert
Sorry but #iDate is a different file name which i close.
Ok. So long as it is meant to be there, I'm happy. :) Thought it might have been an error.
Jan 5 '07 #8
pradeepss
10 New Member
So, does that mean we're looking at a cut-down version of the code? As it stands, it looks as though it should produce a duplicate-value error on your primary key for the second record, because of .Fields("ID") = crID.
Well my file is about 6 MB in size with 400,000 record in each line. Its comma delimited with 7 fields.

the way i am looking at the table is this way. i have a parent table say A with 3 fields, with ID as primary key. A child table B is the table i want to insert the data (thats what the code is doing trying to insert the data) with primary key as DateID which is a foreign key for ID in A. So each record with ID in A corresponds to more then 1 records in B (maybe 100,000 records). I do a query to pick newest record and then insert the whole 6MB file with same value in DateID for each ID which i query.

is there a better way to do this?
Jan 5 '07 #9
Killer42
8,435 Recognized Expert Expert
Well my file is about 6 MB in size with 400,000 record in each line. Its comma delimited with 7 fields.

the way i am looking at the table is this way. i have a parent table say A with 3 fields, with ID as primary key. A child table B is the table i want to insert the data (thats what the code is doing trying to insert the data) with primary key as DateID which is a foreign key for ID in A. So each record with ID in A corresponds to more then 1 records in B (maybe 100,000 records). I do a query to pick newest record and then insert the whole 6MB file with same value in DateID for each ID which i query.

is there a better way to do this?
Oh, there's almost always a better way to do things. But I don't have a clue what it would be in this case. :)

However, things have been a bit confusing so far. You told us ID was your primary key, then listed code which inserts duplicate values into ID. What are we supposed to think? :) I may just be misreading the question, of course.

Can you please clarify exactly what is happening at the time you get the error? For a start, is the error happening on insert to table A or B?

I'll try to get another expert or two to have a look, as well. Access is not really my strong point.

P.S. I still think you need to check for bad data in the input file.
Jan 5 '07 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

2
9427
by: Charles Wilt | last post by:
I have a IBM iSeries (aka AS-400) running v5r3 of OS/400 that I access via a linked server from SQL Server 2000. The following select works fine: select * from prod400db.test.meldbf.InventoryHistory However, this insert statement fails: insert into prod400db.TEST.MELDBF.InventoryHistory
1
5464
by: suslikovich | last post by:
Hi all, I am getting this error when insert values from one table to another in the first table the values are varchar (10). In the second they are datetime. The format of the data is mm/dd/yyyy to be easily converted to dates. The conversion in this case is implicit as indicated in SQL Server documentation. Here is my query: INSERT INTO...
1
3708
by: ing42 | last post by:
I have a problem with inserting records into table when an indexed view is based on it. Table has text field (without it there is no problem, but I need it). Here is a sample code: USE test GO CREATE TABLE dbo.aTable ( INT NOT NULL
10
3226
by: Phil Latio | last post by:
I am inserting data into user table which contains 5 fields, sounds simple enough normally but 2 of the fields are designated as UNIQUE. If someone does enter a value which already exists, how do I capture this specific error? Would it make more sense to actually run a SELECT query first and if that returned a result, then I use that for...
1
2615
by: madhuxml82 | last post by:
Dear Forum Members, I have generated an XML Schema and a Table of XMLType referencing the XML Schema. Now When I am Inserting the Data into the Table. I am getting the Error 0RA-30937: Error is Comming. I am pasting the Code which I have written. Appreciate any Help... begin dbms_xmlschema.registerSchema(...
2
1704
ak1dnar
by: ak1dnar | last post by:
Hi, I am getting some sql errors when trying to insert a record to mysql table using asp.net application. Let me explain the scenario for your reference. This asp.net web form is executing perfectly when i am trying it in my localhost. The error is getting when I tried it with my hosting server. System.Data.SqlClient.SqlException:...
0
2897
by: mbenedict | last post by:
I am rather new at this code and am attempting to modify existing code to use clob datatypes, which I have never used before. The database tables have been set up for clob data. When trying to use dbms_lob.writeappend, I am getting the following error..Command text was not set for the command object. I've researched this in the net as much as I...
2
3100
by: hakkatil | last post by:
Hi to all, I have a page that inserts excel sheet to access database. I am using asp. What I want to do is to check the inserting record if it is in the database. Basicly checking the dublicate record. My code inserts records one by one using addnew-updatebatch. If there is a duplicate in the db, it will display "already exists" and if it is...
4
2876
by: ghjk | last post by:
I wan to add some data using php page to mysql db. But I got the error saying "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sql' at line 1". Could someone please help me. This is my php code <?php if(isset($_POST)){ $VehicleNo = $_POST; $Type=...
0
7798
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8039
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7560
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7887
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5431
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5152
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3556
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3545
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1130
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.