• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1391
  • Last Modified:

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"...
0
Kiske01
Asked:
Kiske01
  • 3
  • 2
  • 2
1 Solution
 
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now