Anjello Joshua Anjello Joshua - 7 days ago 5
C# Question

How SET IDENTITY_INSERT works?

How an I use

SET IDENTITY_INSERT dbo.TableName ON
?

Here's my error code:


An explicit value for the identity column in table 'tbl_PurchaseRequest' can only be specified when a column list is used and IDENTITY_INSERT is ON.


I run this query in SQL Server 2014 and it says
Command successful
but still in C# not worked.

My Code:

DBPurCon dbcon = new DBPurCon();

public void Purchase1 (BELPurchase BELPur)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = dbcon.getcon();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SET IDENTITY_INSERT dbo.tbl_PurchaseRequest ON";
cmd.ExecuteNonQuery();

SqlCommand cmd1 = new SqlCommand();
cmd1.Connection = dbcon.getcon();
cmd1.CommandType = CommandType.Text;
cmd1.CommandText = "INSERT INTO tbl_PurchaseRequest VALUES (@SID1,@Vendor,@Address,@Date,@Terms,@Qty1,@ITD1,@Unit,@UP1,@TP1,@ATT,@REQBY);";
cmd1.Parameters.AddWithValue("@SID1", BELPur.SID1);
cmd1.Parameters.AddWithValue("@Vendor", BELPur.Vendor);
cmd1.Parameters.AddWithValue("@Address", BELPur.Address);
cmd1.Parameters.AddWithValue("@Date", BELPur.Date);
cmd1.Parameters.AddWithValue("@Terms", BELPur.Terms);
cmd1.Parameters.AddWithValue("@Qty1", BELPur.Qty1);
cmd1.Parameters.AddWithValue("@ITD1", BELPur.ItD1);
cmd1.Parameters.AddWithValue("@Unit", BELPur.Unt1);
cmd1.Parameters.AddWithValue("@UP1", BELPur.UP1);
cmd1.Parameters.AddWithValue("@TP1", BELPur.TP1);
cmd1.Parameters.AddWithValue("@ATT", BELPur.AllTot);
cmd1.Parameters.AddWithValue("@REQBY", BELPur.ReqBy);
cmd1.ExecuteNonQuery();
}

public void Request1 (BELPurchase BELPur)
{
SqlCommand cmd2 = new SqlCommand();
cmd2.Connection = dbcon.getcon();
cmd2.CommandType = CommandType.Text;
cmd2.CommandText = "INSERT INTO tbl_RequestPreview VALUES (@ReqTit,@ReqBy,@Terms,@Unlock,@AllTot,'Request')";
cmd2.Parameters.AddWithValue("@ReqTit", BELPur.ReqTit);
cmd2.Parameters.AddWithValue("@ReqBy", BELPur.ReqBy);
cmd2.Parameters.AddWithValue("@Terms", BELPur.Terms);
cmd2.Parameters.AddWithValue("@Unlock", BELPur.Unlock);
cmd2.Parameters.AddWithValue("@AllTot", BELPur.AllTot);
cmd2.ExecuteNonQuery();
}
}


If I write this code in here, it always run
SET IDENTITY_INSERT
query whenever I click
Purchase
button. Is it can only run once or multiple times?

Answer

You should not be executing the commands separately. You need to include the identity column name and its value.

We normally need to IDENTITY_INSERT on when we want to insert an Identity field to insert value from SQL Command instead of Auto so please include the request_id and its value.

cmd1.CommandText = "SET IDENTITY_INSERT [dbo].[tbl_PurchaseRequest] ON; 
        INSERT INTO tbl_PurchaseRequest VALUES (@ReqID,@SID1,@Vendor,@Address,@Date,@Terms,@Qty1,@ITD1,@Unit,@UP1,@TP1,@ATT,@REQBY); 
                    SET IDENTITY_INSERT [dbo].[tbl_PurchaseRequest] OFF;";

cmd1.Parameters.AddWithValue("@ReqID", SomeNumber++);

Is it can only run once or multiple times

Yes, you can run it multiple times as we set OFF after the Command

Comments