PB 10 App skips after encountering SQLSTATE = 23000

[furei1976]

Hi Everyone,

I am here again in need of your help. I am currently assigned to do a maintenance of another module. The last one, I was able to do some work around. This time it is totally different.

Here is the scenario, this module uploads one of more text files and checks on duplicate entries. In usual file uploads it will do fine, store the duplicates in the database and to a text file. But in some cases, it just skips the text file encountering "SQLSTATE = 23000 Microsoft OLE DB Provider for SQL Server Violation of PRIMARY KEY constraint".

I was asked to do to make this error go away (if possible) and have the valid and duplicate contents stored in the database as it normally should. I believe this happens whenever a valid row already existed in the database.

I can provide the code/script of the previous programmer if that is needed because I'm at lost here. I might need to put something on some datawindow event, error event perhaps?

Thanks in advance.

frey

 

[thekl0wn]

That sounds more like an issue on the table setup within the database. If you're going to store the duplicate entries into the database, then you're going to need to do something like add another column to use as a unique identifier (primary key).

 

[furei1976]

thank you for replying thekl0wn. yes, it might be a table issue. I found out that it will the data from a staging table (template table with seq_no as unique identifier). on getting the valids and dups, the seq_no will not be included but a field/column (serial_no) will become the primary key.
so this will generate the error I stated on by first post. it will stop the uploading once it encountered that error.

I would like to do a try..catch, if this is possible can someone help me out please?

thanks,

furei

 

[mbalent]

If you want to have both the valid and the duplicate rows stored in the database you need to have a unique key assigned to each one. Try...Catch code isn't going to accomplish this. Your table needs a primary key (perhaps an IDENTITY column) with no other indexes set up on the data which prevent duplicates. In PB, your datawindow object update properties should include the data columns and have the IDENTITY column specified (if you choose to set one up for the table).

Matt

"Nature forges everything on the anvil of time"

http://www.anvil-of-time.com

 

[furei1976]

Sorry it took me a while to reply.

I will follow your advises.

 

[furei1976]

I was able to figure this one out, finally.

Here's what I did, in case you guys are interested (specially the PB newbies like me).

I created a datastore and reference the same database table as data object.
Then I used:
dw_valid.SetSort ("columnA A")
dw_valid.Sort()
dw_valid.SetFilter ("columnA = columnA[-1]")
dw_valid.Filter()
//some for next loop to check for dups
for ll_ctr = 1 to dw_valid.RowCount()
//insert dups to rejects table
if dw_valid.RowCount() > 0 then dw_rejects.scrolltorow(dw_exception.insertrow(0)) //insert dups to dw_rejects from dw_valid
end if
ll_reject++//to record total rejects
next
//move the dups to the datastore created earlier
dw_valid.RowsMove(1, dw_1.RowCount(), Primary!, ds_checkdups, 1, Delete!)
//remove filter
dw_valid.SetFilter ("")
dw_valid.Filter()

I was focusing on creating a stored procedure to fix this and just result me with bad results. And with just few lines of PB script, it seems to have fixed the issue.

thanks again for those who replied to this thread.

furei