How to insert values to the table in SQL DATABASE having identity column using stored procedure in asp.net

Change Your Code Like this ................

TO Create Table

CREATE TABLE tblbilldetails(
sno int IDENTITY(1,1),
billno INT,
itemcode INT,
qty INT,
price MONEY,
total MONEY)

SPC

  CREATE PROCEDURE [dbo].[spaddtogrid] 
	
@billno int,
	@itemcode int,
	@qty int,
	@price money,
	@total money
	
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	
	-- interfering with SELECT statements.
	
	SET NOCOUNT ON;
 
   INSERT INTO  tblbilldetails(
   
  
   billno,
   itemcode,
   qty,
   price,
   total)
   VALUES(  
   @billno,
   @itemcode,
   @qty,
   @price,
   @total)

If you really wanted to specify the sno value for an insert, you can use IDENTITY_INSERT.
http://technet.microsoft.com/en-us/library/aa259221(v=sql.80).aspx[^]

Here is a working example:

--setup table and sp
create table tblbilldetails (sno int identity(1,1), billno int, itemcode int, qty int, price money, total money);
go
create procedure spaddtogrid
(@sno int, @billno int,@itemcode int,@qty int,@price money,@total money)
as
begin
	if @sno is not null begin
		SET IDENTITY_INSERT tblbilldetails ON;
			insert into tblbilldetails
			(sno,billno,itemcode,qty,price,total)
			values(@sno,@billno,@itemcode,@qty,@price,@total)
		SET IDENTITY_INSERT tblbilldetails OFF;
	end
	else if @sno is null begin
		insert into tblbilldetails
		values(@billno,@itemcode,@qty,@price,@total)
	end
end
go

--add without sno
exec spaddtogrid null, 1, 1, 1, 1, 1;
exec spaddtogrid null, 2, 1, 2, 1, 2;
exec spaddtogrid null, 3, 1, 3, 1, 3;

--add with sno
exec spaddtogrid 11, 4, 1, 1, 1, 1;
exec spaddtogrid 12, 5, 1, 2, 1, 2;
exec spaddtogrid 13, 6, 1, 3, 1, 3;

--add without sno
exec spaddtogrid null, 7, 1, 1, 1, 1;
exec spaddtogrid null, 8, 1, 2, 1, 2;
exec spaddtogrid null, 9, 1, 3, 1, 3;

--note: using this technique can produce duplicate records
--if you run the following, it will get inserted
exec spaddtogrid 2, 2, 1, 2, 1, 2;

--get records
select * from tblbilldetails;

--remove table and sp
drop table tblbilldetails;
drop procedure spaddtogrid;
go

Ordinarily I would not create a stored procedure to perform an insert in this manner.
But rather create an insert and update to the table like so:

create procedure SaveToGrid
(@sno int, @billno int,@itemcode int,@qty int,@price money,@total money)
as
begin
    if @sno is not null begin
        --Update Statement Here
    end
    else if @sno is null begin
        --Insert Statement Here
    end
end
go

drop the table you had created. and fire these queries

CREATE TABLE tblbilldetails(
   sno int IDENTITY(1,1)
   billno INT,
   itemcode INT,
   qty INT,
   price MONEY,
   total MONEY);

CREATE PROC insproc 
(@billno int,@itemcode int,@qty int,@price money,@total money)
 
AS
BEGIN
 
INSERT INTO tblbilldetails(billno,itemcode,qty,price,total)
VALUES(@billno,@itemcode,@qty,@price,@total)
 
END

Now call the procedure with right number of parameters.

Remove @sno from query because it's identity column,

Run this query

CREATE PROCEDURE [dbo].[spaddtogrid]
@billno int,
@itemcode int,
@qty int,
@price money,
@total money

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

INSERT INTO tblbilldetails(

billno,
itemcode,
qty,
price,
total)
VALUES(
@billno,
@itemcode,
@qty,
@price,
@total)


END