Error when inserting data with Auto-Increment field 1

[mana2]

Hi,

I have a table that has an ID field (integer) that is the Primary Key and it auto-increments.
When I try to add a record to the database, I get the following error:

Cannot insert the value NULL into column 'id', table 'group.dbo.tblmembers'; column does not allow nulls. INSERT fails.

If I remove the Id column, I can add data to the other fields in the table. If I manually edit the table and add data to the other columns the id field increments.

Thanks for any help.

 

[markros]

How exactly does your INSERT command look like? You should exclude identity field from the list of the fields, it will be populated automatically.

PluralSight Learning Library

 

[mana2]

Thank You. I didn't include the identity field.

strSQL = "INSERT INTO tblmembers ([username],[password]) VALUES " & _
"('" & fixQuotes(Request.Form("username")) & "','" & _
fixQuotes(Request.Form("password")) & "')"

 

[markros]

Does your table have extra fields? Can you please post
CREATE TABLE statement?

PluralSight Learning Library

 

[mana2]

No extra fields.

CREATE TABLE [dbo].[tblmembers] (
[id] int NOT NULL,
[username] varchar(50) NOT NULL,
[password] varchar(50) NOT NULL
)

 

[markros]

Your ID field is not set to be identity. It means you either have to change the structure to be
CREATE TABLE [dbo].[tblmembers] ([id] int identity(1,1) NOT NULL,[username] varchar(50) NOT NULL,[password] varchar(50) NOT NULL)

(I also assume that ID field is a primary key).

or you need to supply ID field and value with the insert command.

PluralSight Learning Library

 

[mana2]

Thanks for your help. I did try creating the table using the select statement above but it still didn't work. What I do next is export the database to the Web Host server and I get the error that the insert fails because of the id being null. The table I create on my server does have the identity field in MSSQL set.

So I can't really see how it gets incremented. I guess I need to insert it manually.

 

[markros]

What is the SET IDENTITY_INSERT setting ?

PluralSight Learning Library

 

[SQLSister]

HOw did you export the database? If you used a GUI to do that or an export wizard, it very well may not have set up the table with an identity field. You alawys need to create database objects through scripts to have complete control over what gets created.

Have your provider script out the table on their end (or you can do it yourself if you have the rights) and see how it is set up.

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

 

[mana2]

I'm creating the database in Microsoft SQL Server Management Studio and set the Identity on the ID column.

I did use the Import and Export Wizard to export the database and I was wondering if it drops the identity on the ID field. I did try creating the table with the script Markros had but I got the same error. So I'm trying to find out from the host if there's a way to view the database.

Thanks

 

[mana2]

Thank you so much for your help. The identity was being dropped on the ID field when I exported the database. It's working now.