Question On PreparedStatements

General
1

Hello,

I am connecting to an MSSQL Server Database. I am having an issue with prepared statements.

I have index set up that requires unique values for a few columns. There is also an auto-increment primary key. I notice if I do not use a prepared statement and do an insert using straight SQL everything works as expected. If the user tries to enter a record with a duplicate value for one of the unique values, SQL throws an error which I report to the user. The auto-increment primary key is not incremented because the record was not added.

I am trying to migrate this to use prepared statements. I have noticed that using a prepared statement when I try to add a duplicate record I get error 3621"The Statement Is Terminated." Not a very good error, but the odd part is that auto-increment primary key is incremented even though the record was not added successfully to the table. The next successful add will have a gap in the sequence of the auto-increment primary key.

I’m curious why this is?

I’m doing this to prevent SQL Injection, but seems like the regular Database INSERT and RecordSet UPDATE might be more appropriate?

Thoughts or suggestions?

Thank you.

2

Are you doing an SQLExecute(“BEGIN TRANSACTION”) before you make your call and a Rollback when erroring and Commit when things works?

3

Hi Julian,

Thanks for the response. Yes I have tried that and the results are the same, I still get a gap in my autoincrement row id for the next successful add. It’s very odd.

4

Just had a quick google before bed, see http://stackoverflow.com/questions/16358966/how-to-reset-autoincremented-id-when-rollback-occurs-in-sql for more info

Basically don’t worry about it. Autoincrements should be throw away numbers that you don’t care about so you shouldn’t worry about a gap.