ADODB.Recordset.addnew can't add a record in when an identity column exists

Hello,

I'm having a problem with this SQL Server table.
The whole project was once based on MS Access 2000 and all was fine. These are the steps followed:

1) Imported the DB into a new SQLServer 2008 DB ---> OK
2) Set what were "counter" columns" to "identity" in SQLSERVER ---->OK

if I try to do an INSERT INTO from within the SQL Server frontend gui, with the same data as specified below (so without specify any value for the identity column) all goes well.

when I try this VB code:

        Dim rst As ADODB.Recordset
        Dim SQL As String
        
        SQL = "SELECT * FROM t_RigheCorso"
        Set rst = SkiApp.SWDB.DataServer.OpenRecordset(SQL, False)
        
        Err.Clear
            rst.AddNew Array("IDCorso", _
                            "Data", _
                            "AP", _
                            "Dalle", _
                            "Alle", _
                            "GiornataIntera", _
                            "Idzona"), _
                        Array(0, _
                            0, _
                            0, _
                            0, _
                            0, _
                            False, _
                            0)

Open in new window

err will be: -2147217873  - Impossibile inserire il valore NULL nella colonna 'IDRigaCorso' della tabella 'Sw.dbo.t_RigheCorso'. La colonna non ammette valori Null. INSERT avrà esito negativo.

In english should be something like: Unable to insert a NULL value in the column 'IDRigaCorso' in the table 'sw.dbo.t_RigheCorso. The column doesen't admit NULL values. INSERT will have negative result.

The problem is that if it's possible, I would like to NOT change the VB code at all. I don't know where it faults, the Identity column value should be "autogenerated"...
Kiske01Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

brad2575Commented:
Depending on the site you could set up this ID field to be an AUTO increment field on the database level, it will auto generate this field by adding +1 to previous record.

That will fix the issue and allow you to not change code, but you have to verify if this impacts anything else/locations in your application
0
Anthony PerkinsCommented:
I suspect that when you converted over the schema, the IDENTITY attribute was lost.  If that is the case then all you need to do is make sure that column has it set.
0
Kiske01Author Commented:
First of all thank you both for the quick response.

In response to both, effectively the Identity attribute was lost during the import, but I've manually set it, as with the autoincrement set to 1. In fact adding only the fields specified in the AddNew using the frontend works as expected.

For the rest of the software I've made an automated SQL to T-SQL routine wich checks the systax for the queries, so should be no further problems.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Kiske01Author Commented:
Maybe I was misunderstood, the frontend I was taliking about is not my software, but is the SQL server management studio.. ;) so I still haven't found a solution...
0
brad2575Commented:
These solutions are from SQL Management Studio to set the Identity column property
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kiske01Author Commented:
The Identity property is currently SET from within the Management Studio...
0
Anthony PerkinsCommented:
So what error message are you getting now?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.