Error while inserting data into table

[pradeepss]

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

[Killer42]

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

Oh, and a question - where is crID getting its value?

[pradeepss]

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?

[Killer42]

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.

[Killer42]

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?

[Killer42]

...
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.

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

[pradeepss]

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.

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

Sorry but #iDate is a different file name which i close.

[Killer42]

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.

[pradeepss]

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?

[Killer42]

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.

[MMcCarthy]

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

Please post all the declarations of the variables used here. The array, etc.

Also how are you looping through crID

Mary

[NeoPa]

Points I would make :
1. It is a good idea not to include the code for your whole database, but you must select the code to leave out based on an understanding of what is required. It would appear that you have missed out some code that is helpful for others to understand what's going on.
2. You should really post the data structure (What tables do you have involved in this process? What are the relevant fields involved within those tables? What are their types? Which ones are keyed Primary and Foreign?)
Example :
Posting Table/Dataset MetaData

Table Name=tblStudent
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
MaxMark; Numeric
MinMark; Numeric

More specifically :
1. As mentioned earlier, Closing #iDate in code which deals with the file #iDrop is unhelpful. Especially as you don't close (or even open) #iDrop within this code.
2. When posting code, it is always important to include variable definitions and Procedure definitions as they contain a lot of very important information.

If you are using an Access database, you could use the Text File import instead to do the import part for you. Would this not be a better solution?


NB NB NB NB NB NB NB NB NB NB NB NB NB NB NB NB NB NB NB
Please respond to all points made (by all experts kind enough to get involved with your request for help).
It's very difficult for people to help you if they don't have enough information with which to work.