Anjello Joshua Anjello Joshua - 1 year ago 157
C# Question


How an I use


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";

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);

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);

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

Answer Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download