Raj Raj - 5 days ago 5
SQL Question

What will be the correct SQL Statement for inserting into a SQL Server table having Identity and timestamp

I have a table ...

if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[ADDRESS_BOOK]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ADDRESS_BOOK]
GO

CREATE TABLE [dbo].[ADDRESS_BOOK]
(
[ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[FIRST_NAME] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LAST_NAME] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CONTACT_NOS] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MODIFIED_BY] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MODIFIED_TIME] [timestamp] NULL
) ON [PRIMARY]
GO


My question is what should be the SQL statement for inserting data into this table ?

I tried

Dim sb As New StringBuilder
With sb
.Append("SET IDENTITY_INSERT ADDRESS_BOOK ON ")
.Append("INSERT INTO ADDRESS_BOOK(ID,FIRST_NAME,LAST_NAME,")
.Append("CONTACT_NOS,MODIFIED_BY,MODIFIED_TIME) VALUES(NULL,")
.Append("'" & TbFName.Text & "','" & TbLName.Text & "','" & TbContactNos.text & "','" & TbUser.Text & "',NULL)")
.Append(" SET IDENTITY_INSERT ADDRESS_BOOK OFF")
End With
Dim cmd As New SqlCommand
cmd.Connection = conn
cmd.CommandText = sb.ToString
cmd.ExecuteNonQuery()


But it is giving me error...

Cannot insert the value NULL into column 'ID', table 'MY_DB.dbo.ADDRESS_BOOK'; column does not allow nulls. INSERT fails. The statement has been terminated.

Answer

The line here:

.Append("CONTACT_NOS,MODIFIED_BY,MODIFIED_TIME) VALUES(NULL,")

You are trying to insert NULL in a column which does not accept NULLS.

The ID column is set to auto increment (IDENTITY (1,1)).

Just leave that column out of the query parameters (it will automatically increment) and your query should be fine.

SUGGESTION: Use a parameterized query. It is a much more cleaner and optimized way of querying a database. It has plenty of advantages (which you can read yourself, I won't shed light on them here).

Also, you don't need to use append on every line. Its just bad coding and quite irritating to read!!! Also, code like this is extremely unlikely to get through any round of code review.

EDIT:

   INSERT INTO ADDRESS_BOOK(FIRST_NAME, LAST_NAME, CONTACT_NOS, MODIFIED_BY, MODIFIED_TIME)  VALUES('FirstName', 'LastName', '1234567890', 'SomeName', '2014-01-01 12:15:17.227')

Note that I have not mentioned the column ID at all here. When this statement will execute, the table containing this row will automatically have the ID value.

Hope this helps!!!

Comments