Solved

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

Posted on 2012-03-19
7
1,258 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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

685 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