How to avoid primary key violation error
without using Select Query to loop through for checking EOF before the insert
regards,
Murali

What is the methodology you are using to check that? Can you post some code of it to have a better idea about that?

-Vijay G

Are you connecting to just one database or do you have any JOINS?

Hey,

If you are using SQL, specify the ID as an identity. If you are using Oracle, use a sequence number (which requires a trigger). Then when you do the insert, specify no ID value. These are obviously numeric ID's, by the way.

Then when you do the insert, you specify no value for the ID parameter, and it auto updates to the next value.

Brian

Hi vijay,brian
say for sample Tablename called PRODUCT_MASTER i have 3 columns in Product_Master
3 columns are composite primary key.
Now my Question is How to avoid primary key violation without loop through the table
strSQL= "select * from Product_Master where Column1='value1' and column2='value2' and column3='value3'"
set rs=conobj.execute(strSql)
if rs.eof then
conobj.execute("insert Statement")
else
response.write "Message"
end if
Note i dont want like this above code becoz it will take much more time to loop through even thoug we have index in the column i need to trap the error number and display the message

What is the database you are using? Is that MSSQL server db or Oracle or ACCESS or anything else? That should show us light in which way to proceed with.

If you are using MSSQL, then you can do it this way.

Write a procedure something like this.
Create proc proc_name(Param_list_for_Insert_Values)
as
If (Select count(*) from TableName where column1=Value1 and column2=Value2 and column3=value3)>0
  Begin
     Select -1 as Result
     Return
  End
Else
  Begin
     BEGIN TRAN
     Insert TableName Values(.....)
     If @@ERROR<>0
         ROLLBACK TRAN
     ELSE
         COMMIT TRAN
     Select @@ERROR as Result
  End

You can call this procedure in you ASP code, and pass the insert values as parameters, which on execution would return

-1 --> if already record exists with those composite key values.
any +ve number --> if transaction fails due to any other reason.
ZERO --> If insert succeeds.

This is a commonly used methodology. Since you do this at the backend using stored proc, performance is better than what you were trying to do from the code you posted there and you don't have to loop through your select to see if violation occurs.

Anyone, pls post if you have a better solution to this.

Hope that helps.
Cheers!

-Vijay G

Vijay,
i am using msSQL
If u use select statement in storeproc to count also it will take much more time even i have clustured index.. coz i've got 2 lakhs of data in my table so i hav got one idea----
would u please suggest me whether i can proceed with that.
" we can collect the ado Error Numbers and write a function and include it in the page & trap the Err Number and check with the (Include ) function " this is my idea ...........
But I need ur help to proceed with that..................
thanx in Advance

Hello,

You could insert the data, in a transactional mode (begin transaction, commit transaction), and check the @@errors property if there is an error, then rollback the transaction. Using this method may actually return an error which you could catch in your code.

However, for errors in ASP, You would have to use On Error Goto, and I don't think ASP supports that... It's been a while for me using ASP, so I'm probably not the best person to make a recommendation.

Brian

Hi Muralikeane,

Actually the volume of data I have in my environment is more than 30 lakhs of records. The volume of transactions that happens is high, if not that huge. I would see a maximum of around 300 to 800 records being inserted per minute. Not only that, there are other applications that do other jobs like checking for the expiry of the time limit and moving those records to another table called archive and many other update activities too happen. At an average, a minimum of more than a lakh of records being processed per day. If that had been posing me problems, I wouldn't have suggested you that. Also I have seen this as a widely used methodology. So you can go ahead using it.

As suggested in my previous post, when you go for a stored proc, it is a compiled planned query that exists in your sql server, which increases the performance, than doing as you have given in your post.

In the above way, you will have to connect to DB twice. Once to check for primary key violation condition and the second time you would actually have to pass the Insert statement with values, then you got to check if that fails or succeeds. So this has multiple hops to actually finish off the transaction and in every hop you got to connect to DB from your frontend. Also, when you send a statement to DB for processing, be it select or insert or update whatever, it got to be compiled to check for errors, and then gets executed, which takes more time to be processed.

Whereas in the stored proc method that I suggested, you will have to call the proc once with all parameters, and the responibiltiy is with the sql server to decide if to insert and commit or to roll back, and return the status of transaction all in one hop. You dont have to connect to database multiple times to do a single valid transaction. So I would strongly suggest you to go with it.

If that can wait, please look around if you get any post supporting this and can go by majority

Cheers!

-Vijay G