SET IDENTITY_INSERT not work - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SET IDENTITY_INSERT not work

Author

Topic

programer
Posting Yak Master

221 Posts

Posted - 2010-08-04 : 13:27:50

Hi,

What am I doing wrong that does not work?

Please help!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-04 : 13:33:46

The column [PaymentId] does not accept NULLS. But it seems the variable @PaymentId is NULL and hence the INSERT is failing.

programer
Posting Yak Master

221 Posts

Posted - 2010-08-04 : 13:39:32

quote:
Originally posted by programer

Hi,

What am I doing wrong that does not work?

Error:
Cannot insert the value NULL into column 'PaymentId', table 'C:\BURNBET\APP_DATA\ASPNETDB.MDF.dbo.tbl_Payments'; column does not allow nulls. INSERT fails.
The statement has been terminated.

My code asp.net:

InsertCommand="SET IDENTITY_INSERT [tbl_payments] ON; INSERT INTO [tbl_payments] ([UserId], [PaymentId], [PaymentType]) VALUES (@UserId, @PaymentId, @PaymentType);"

Please help!

Yes I know.
Therefore I need to know where to include this code: SET IDENTITY_INSERT [tbl_payments] ON;

Please help!

ann
Posting Yak Master

220 Posts

Posted - 2010-08-04 : 13:43:27

look here:
http://msdn.microsoft.com/en-us/library/ms188059.aspx

programer
Posting Yak Master

221 Posts

Posted - 2010-08-04 : 13:47:41

quote:
Originally posted by ann

look here:
http://msdn.microsoft.com/en-us/library/ms188059.aspx

This means that I have to use Stored procedure?

thanks

ann
Posting Yak Master

220 Posts

Posted - 2010-08-04 : 13:50:11

No, anything you can do in a stored procedure you can do inline

programer
Posting Yak Master

221 Posts

Posted - 2010-08-04 : 13:53:17

quote:
Originally posted by ann

No, anything you can do in a stored procedure you can do inline

Ok thanks.

If I will needed help, I will contacted you again.

programer
Posting Yak Master

221 Posts

Posted - 2010-08-04 : 14:31:24

quote:
Originally posted by programer

quote:
Originally posted by ann

No, anything you can do in a stored procedure you can do inline

Ok thanks.

If I will needed help, I will contacted you again.

I have not come to a solution.

Please help me.

My code:

<asp: SqlDataSource ID = "SqlDataSource2" runat = "server"
ConnectionString ="<%$ ConnectionStrings: ApplicationServices%> "
InsertCommand = "set IDENTITY_INSERT tbl_payments ON; INSERT INTO [tbl_payments] ([UserId], [PaymentId] [PaymentType]) VALUES (@ UserId, @ PaymentId, @ PaymentType)"
SelectCommand = "SELECT [UserId], [PaymentId] [PaymentType] FROM [tbl_payments]>
<InsertParameters>
<asp:Parameter Name="UserId" Type="String" />
<asp:Parameter Name="PaymentId" Type="Int16" />
<asp:Parameter Name="PaymentType" Type="String" />
</ InsertParameters>
<selectparameters>
<asp:controlparameter name="Title" controlid="DropDownList1" propertyname="SelectedValue"/>
</ Selectparameters>
</ Asp: SqlDataSource>

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-04 : 14:47:43

is PaymentID an identity column?

I think you misunderstand the purpose of SET IDENTITY_INSERT ON/OFF

Try changing your command to (without the set identity insert and without paymentid)

InsertCommand = "INSERT INTO [tbl_payments] ([UserId], [PaymentType]) VALUES (@UserId, @PaymentType);"

If PaymentID is NOT an identity column, then you can go back to vijayisonly's post. @PaymentID can't be null.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-04 : 15:31:21

quote:
Originally posted by programer

quote:
Originally posted by programer

quote:
Originally posted by ann

No, anything you can do in a stored procedure you can do inline

Ok thanks.

If I will needed help, I will contacted you again.

I have not come to a solution.

Please help me.

My code:

<asp: SqlDataSource ID = "SqlDataSource2" runat = "server"
ConnectionString ="<%$ ConnectionStrings: ApplicationServices%> "
InsertCommand = "set IDENTITY_INSERT tbl_payments ON; INSERT INTO [tbl_payments] ([UserId], [PaymentId] [PaymentType]) VALUES (@ UserId, @ PaymentId, @ PaymentType)"
SelectCommand = "SELECT [UserId], [PaymentId] [PaymentType] FROM [tbl_payments]>
<InsertParameters>
<asp:Parameter Name="UserId" Type="String" />
<asp:Parameter Name="PaymentId" Type="Int16" />
<asp:Parameter Name="PaymentType" Type="String" />
</ InsertParameters>
<selectparameters>
<asp:controlparameter name="Title" controlid="DropDownList1" propertyname="SelectedValue"/>
</ Selectparameters>
</ Asp: SqlDataSource>

Show us the DDL of tbl_payments

programer
Posting Yak Master

221 Posts

Posted - 2010-08-04 : 15:43:22

quote:
Originally posted by vijayisonly

quote:
Originally posted by programer

quote:
Originally posted by programer

quote:
Originally posted by ann

No, anything you can do in a stored procedure you can do inline

Ok thanks.

If I will needed help, I will contacted you again.

I have not come to a solution.

Please help me.

My code:

<asp: SqlDataSource ID = "SqlDataSource2" runat = "server"
ConnectionString ="<%$ ConnectionStrings: ApplicationServices%> "
InsertCommand = "set IDENTITY_INSERT tbl_payments ON; INSERT INTO [tbl_payments] ([UserId], [PaymentId] [PaymentType]) VALUES (@ UserId, @ PaymentId, @ PaymentType)"
SelectCommand = "SELECT [UserId], [PaymentId] [PaymentType] FROM [tbl_payments]>
<InsertParameters>
<asp:Parameter Name="UserId" Type="String" />
<asp:Parameter Name="PaymentId" Type="Int16" />
<asp:Parameter Name="PaymentType" Type="String" />
</ InsertParameters>
<selectparameters>
<asp:controlparameter name="Title" controlid="DropDownList1" propertyname="SelectedValue"/>
</ Selectparameters>
</ Asp: SqlDataSource>

Show us the DDL of tbl_payments