473,698 Members | 1,947 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 2691
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
9455
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
5470
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 Campaign (CampaignID, Name, DateStart, DateEnd, ParentID, ListID) SELECT ...
1
3714
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
3244
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 error checking and don't run insert until select returns nothing? Cheers
1
2626
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( 'http://www.robertboschindia.com/example.xsd',
2
1708
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: Cannot insert the value NULL into column 'Dr_no', table 'myDB.username.tablename'; column does not...
0
2907
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 could, but I'm lost in what I am doing wrong. Can you help please? Here is my code... ...
2
3117
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 not in the db it will display "record added". Below is my asp code I found on the net and...
4
2890
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= $_POST; $Make=$_POST; $Model=$_POST;
0
8598
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9016
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 captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8856
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 choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6515
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4360
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4613
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3037
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2321
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1997
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.