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

Error in insert

Hello, I'm doing an SQL Insert statement and I'm getting this error (in Spanish, I translated it):

Microsoft OLE DB Provider for SQL Server (0x80040E14)
Only one explicit value can be specified for the identity column of the table 'AAA' when a list of columns is used and IDENTITY_INSERT is ON.

I just don't know what is happening, i tried the insert in the query analyzer and it worked, then I put the same statement in the asp page (even though without using data of the form, directly with data written by me in the SQL Insert statement, just like I tested in the query analyzer) and I got the error.
It says that the error is in the line when I do:

Ob_Command.Execute

Below, I'm using SELECT @@IDENTITY to recover the identity and insert in other tables, but I erase all of this part of the code and the error it's still there, in the line of Execute.

Thank you, hope you can help me ;)
0
DeLaVegaz
Asked:
DeLaVegaz
  • 5
  • 5
  • 4
  • +2
2 Solutions
 
Lord_McFlyCommented:
Try SELECT @@IDENTITY AS NewID

The iNewID = rs("NewID")
0
 
SvenCommented:
post the whole code for Ob_Command to see what you are executing! I think the line Ob_Command.Execute throws the error, is it right?
0
 
Shailesh15Commented:
This might help...

Identity works with Access 2000 ONLY if you use the "Jet OLEDB Provider 4.0"!
So define your ODBC connection not with "Microsoft Access Driver" but with "Jet OLEDB Provider 4.0". So your connection string should look like:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydata.mdb;Persist Security Info=False"

If you use a DSN-Less connection. Otherwise its in the ODBC Settings.

Read for more info...
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q232144

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
SvenCommented:
As I see, he is trying to INSERT per ADO Command and Parameters. In think the part of code is interessting to see, cause the error he mentions has nothing to do with the SELECT @@IDENTITY!
0
 
Anthony PerkinsCommented:
Shailesh15,

Pst! The questioner is asking about MS SQL Server not MS Access 2000.
0
 
Anthony PerkinsCommented:
DeLaVegaz,

As you can tell, without seeing the code (or a more detailed explanation) all we can do is make wild guesses.
0
 
Shailesh15Commented:
Oh!...My mistake. please ignore that.
0
 
Lord_McFlyCommented:
It might be that within the actual SQL DB more than 2 columns have been set as Identity columns.
0
 
SvenCommented:
It might be this, it might be that... *g*

@DeLaVegaz: Without a detailed description and code parts we can not help you!
0
 
DeLaVegazAuthor Commented:
I do this to enter a NULL value or the data, checking before if the variable requested of the form is empty or not.

Ob_Command.CommandText = "INSERT INTO AAA VALUES('" & idSub & "','" & denomination & "','" & street & "','" & number & "',"

If local = "" Then
Ob_Command.CommandText = Ob_Command.CommandText & "NULL,"
else
Ob_Command.CommandText = Ob_Command.CommandText & "'" & local & "',"
End If

If street1 = "" Then
Ob_Command.CommandText = Ob_Command.CommandText & "NULL,"
else
Ob_Command.CommandText = Ob_Command.CommandText & "'" & street1 & "',"
End If

If street2 = "" Then
Ob_Command.CommandText = Ob_Command.CommandText & "NULL,"
                  else
Ob_Command.CommandText = Ob_Command.CommandText & "'" & street2 & "',"
End If

If floor = "" Then      
Ob_Command.CommandText = Ob_Command.CommandText & "NULL,"
else
Ob_Command.CommandText = Ob_Command.CommandText & "'" & floor & "',"
End If

If dept = "" Then
Ob_Command.CommandText = Ob_Command.CommandText & "NULL,"
else
Ob_Command.CommandText = Ob_Command.CommandText & "'" & dept & "',"
End If
If social = "" Then
Ob_Command.CommandText = Ob_Command.CommandText & "NULL,"
else
Ob_Command.CommandText = Ob_Command.CommandText & "'" & social & "',"
End If
day1 = date
'Entering twice, first enter and last modification
Ob_Command.CommandText = Ob_Command.CommandText & day1 & "," & day1 & ")"
Ob_Command.Execute ' The error is in this line
            
Ob_Command.CommandText = "SELECT @@IDENTITY" ' I use this to enter the previously id in other tables, anyway like I said I erased all of this part to make sure that the error is above, and yes, the error is the same, in the same line
idEnterprise = Ob_Command.Execute

The database structure has an id with an autoincrement, that is omitted in the insert (idSub forms part of the key too, but not with autoincrement). But this was working ok (I have done this before).
Hope you can help me, thank you ;)

0
 
SvenCommented:
Your SQL Statement is wrong, it should look like:
INSERT INTO Table (Field1, Field2, Field3) VALUES (Value1, Value2, Value3)

also you can tweak your script by not using Command Object. Only use a Connection object, save your SQL statement in a string and then execute it:
oConn.Execute SQLString
0
 
SvenCommented:
...forgot to mention:

As long as your connection object is not closed you can get the last added ID with:

Set oRs = oConn.Execute("SELECT @@IDENTITY")
LastID = oRs(0)
oRs.Close
Set oRs = Nothing
0
 
DeLaVegazAuthor Commented:
I think that the SQL statement can be written in two ways, with the fields or not. In the way I used, they have to be in order and all of them (anybody correct me if I'm wrong but I almost always used it like this).
I'll check executing through the connection and I'll let you know, but it should work in both ways(I'm crazy at this point ;)
Thanks :)
0
 
Shailesh15Commented:
>>"The database structure has an id with an autoincrement, that is omitted in the insert"

Then you can not use your style of calling INSERT statement. It has to be like insert into table (f1,f1) values(v1,v2).
How does the server know which field you have ommited?
0
 
Anthony PerkinsCommented:
>>In the way I used, they have to be in order and all of them (anybody correct me if I'm wrong but I almost always used it like this).<<
That is correct, providing you are in fact adding to all the columns.  Since you cannot insert to an IDENTITY column (unless you use SET IDENTITY_INSERT AAA ON) you will have to explicitly name the columns (of course excluding the IDENTITY column).  

By the way, if you can post the original (un-translated) error message, it may help.
0
 
DeLaVegazAuthor Commented:
Hmmm, but it was working before like this.....
I'll try all options anyway...
Thanks
0
 
DeLaVegazAuthor Commented:
Well, don't know why (I would like to know, I changed so many things) but now it works....
Anyway, for those who are interested to know the Insert don't need to have the fields detailed if you put all the values, like someone said, above.
Well, how can I share points?
0
 
DeLaVegazAuthor Commented:
I forgot, Thank you all :)
0
 
Anthony PerkinsCommented:
>>Well, how can I share points?<<
More than one Expert helped solve my problem. What do I do?
http://www.experts-exchange.com/help.jsp#hi69

Suerte.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

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