SQL error 2

[MarkZK]

Hi all,

I'm trying to set a column to auto increase the row value by one from the last row added to give it a unique value, I used....

RowID INT NOT NULL IDENTITY(1,1)

when creating the table, but I get this error when adding "1" into the insert statement...

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]An explicit value for the identity column in table 'Tbl_IPs' can only be specified when a column list is used and IDENTITY_INSERT is ON.

is there a way to turn on "IDENTITY_INSERT" without having physical access to the server ? or to even check "IDENTITY_INSERT" is actually off programmatically.

 

[gmmastros]

Show us the query you use to insert a row.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MarkZK]

Hi,

sorry, yeah,

the query (statement?) is..

SQL = "INSERT INTO Tbl_IPs VALUES ('1', '" & UserIP & "', '" & Now() & "');"

the UserIP is just a variable with " Request.ServerVariables("REMOTE_ADDR") "

Thanks.

 

[SQLSister]

Don't try to set the identity value when inserting. Specify the fields you are inserting to instead. Assume table1 has and identity field called myid and two other fields,the insert would look like this

insert table1 (field1, field2)
values (1, 'test')

You really do not want to turn identity insert on and manually insert an identity unless you are doing a one-time import of records from another database to initialize the data inthe table. The whole purpose of identity is to free you from specifying the value.

"NOTHING is more important in a database than integrity." ESquared

 

[MarkZK]

oh, I didn't realise you could do that, it's working fine now.

Thank you

 

[gmmastros]

When you have an identity column in a table, you should NOT assign it your own value. That's the whole point of identity columns.

Now, please understand that it is possible to do so. The only time you should force an identity value is when you are loading lots of data in different tables and you want to preserve the id values.

Normally, you should do something like this...

SQL = "SET NOCOUNT ON INSERT INTO Tbl_IPs(UserIP, YourDateColumn) VALUES ('" & UserIP & "', '" & Now() & "') Select Scope_Identity() As NewIdValue"

When you run this from your ASP page, you should call this like any other query that returns a recordset. In this case, there will be 1 row and 1 column named NewIdValue.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MarkZK]

Perfect sense, Thank you