Solved

Error in insert

Posted on 2004-04-20
19
318 Views
Last Modified: 2008-02-01
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
Comment
Question by:DeLaVegaz
  • 5
  • 5
  • 4
  • +2
19 Comments
 
LVL 6

Expert Comment

by:Lord_McFly
ID: 10874749
Try SELECT @@IDENTITY AS NewID

The iNewID = rs("NewID")
0
 
LVL 11

Expert Comment

by:Sven
ID: 10875629
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
 
LVL 10

Expert Comment

by:Shailesh15
ID: 10878198
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
 
LVL 11

Expert Comment

by:Sven
ID: 10878259
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10878268
Shailesh15,

Pst! The questioner is asking about MS SQL Server not MS Access 2000.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10878297
DeLaVegaz,

As you can tell, without seeing the code (or a more detailed explanation) all we can do is make wild guesses.
0
 
LVL 10

Expert Comment

by:Shailesh15
ID: 10878470
Oh!...My mistake. please ignore that.
0
 
LVL 6

Expert Comment

by:Lord_McFly
ID: 10878559
It might be that within the actual SQL DB more than 2 columns have been set as Identity columns.
0
 
LVL 11

Expert Comment

by:Sven
ID: 10878575
It might be this, it might be that... *g*

@DeLaVegaz: Without a detailed description and code parts we can not help you!
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:DeLaVegaz
ID: 10878745
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
 
LVL 11

Expert Comment

by:Sven
ID: 10878800
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
 
LVL 11

Assisted Solution

by:Sven
Sven earned 20 total points
ID: 10878823
...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
 

Author Comment

by:DeLaVegaz
ID: 10878875
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
 
LVL 10

Expert Comment

by:Shailesh15
ID: 10878969
>>"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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 80 total points
ID: 10879005
>>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
 

Author Comment

by:DeLaVegaz
ID: 10879033
Hmmm, but it was working before like this.....
I'll try all options anyway...
Thanks
0
 

Author Comment

by:DeLaVegaz
ID: 10880843
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
 

Author Comment

by:DeLaVegaz
ID: 10880847
I forgot, Thank you all :)
0
 
LVL 75

Expert Comment

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 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

13 Experts available now in Live!

Get 1:1 Help Now