[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2012-03-19
7
Medium Priority
?
1,323 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

650 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