Solved

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

Posted on 2012-03-19
7
1,232 Views
Last Modified: 2012-03-29
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
Comment
Question by:Kiske01
  • 3
  • 2
  • 2
7 Comments
 
LVL 16

Expert Comment

by:brad2575
ID: 37738711
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37738744
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
 

Author Comment

by:Kiske01
ID: 37739514
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:Kiske01
ID: 37746581
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
 
LVL 16

Accepted Solution

by:
brad2575 earned 350 total points
ID: 37746783
These solutions are from SQL Management Studio to set the Identity column property
0
 

Author Comment

by:Kiske01
ID: 37747980
The Identity property is currently SET from within the Management Studio...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37748688
So what error message are you getting now?
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now