add rows from textbox in insert select command sql c#

Question

I am creating a web app in which i am using insert into select command

for which i have taken a stored procedure which looks like this

alter PROCEDURE profinalinstexpensesonid      
(                    
@from varchar(5000),                    
@to varchar(5000),                    
@trainer varchar(5000),      
@sonvinid varchar(5000),  
@button varchar(5000),
@bill_id varchar(5000)               
)                
AS                    
BEGIN      
if(@button='allselect')  
begin  
insert into listinvoice(sonvinid,tid,date,brandname,zone,location,area,venuename,venue,instructore,amount)    
select                     
instructoreexpense.sonvinid,    
sonvininsert.trainer,                   
convert(varchar,sonvininsert.date,105) as date ,                    
sonvininsert.brandname,                    
substring(sonvininsert.zone,1,1)as zone,                    
sonvininsert.location,                    
sonvininsert.area,                    
companysonvinunitvenue.venuename,                  
sonvininsert.venue,                    
sonvininsert.instructore,                                
instructoreexpense.amount      
from                     
instructoreexpense                     
left outer join sonvininsert on                     
sonvininsert.sonvinid=instructoreexpense.sonvinid and                     
sonvininsert.status='0'                      
left outer join finalinstructoreexpense on                     
finalinstructoreexpense.sonvinid=instructoreexpense.sonvinid                      
left outer join companysonvinunitvenue on                     
companysonvinunitvenue.id=sonvininsert.comsonvinid      
where                     
sonvininsert.sonvinid not in(select sonvinid from listinvoice)    
and    
sonvininsert.date                     
between convert(datetime,@from,105) and                     
convert(datetime,@to,105) and                     
sonvininsert.trainer=(select empname from trainerdetails where trid=@trainer)      
and instructoreexpense.sonvinid NOT IN (      
            SELECT CAST(Item AS INTEGER)      
            FROM SplitString(@sonvinid, ',')      
      )      
order by instructoreexpense.sonvinid 


end  
end

now this is working absolutely fine, now on this line

insert into listinvoice(sonvinid,tid,date,brandname,zone,location,area,venuename,venue,instructore,amount)    

i want to add bill_id

and i will get my bill_id from a textbox in my program

so i want to change this line into something like this

insert into listinvoice(sonvinid,tid,date,brandname,zone,location,area,venuename,venue,instructore,amount,bill_id=@bill_id)

but i am gettig the error of

Msg 102, Level 15, State 1, Procedure profinalinstexpensesonid, Line 14 Incorrect syntax near '='.

what i need to do,

i am getting bill_id from my textbox,

is there any other options???

Answer 1

You have to change your statement. You are currently doing this:

insert into listinvoice(sonvinid,tid,date,brandname,zone,location,area,venuename,venue,instructore,amount,bill_id=@bill_id)

Where it should be something like this:

insert into listinvoice(sonvinid,tid,date,brandname,zone,location,area,venuename,venue,instructore,amount,bill_id)
select                     
instructoreexpense.sonvinid,    
sonvininsert.trainer,                   
convert(varchar,sonvininsert.date,105) as date ,                    
sonvininsert.brandname,                    
substring(sonvininsert.zone,1,1)as zone,                    
sonvininsert.location,                    
sonvininsert.area,                    
companysonvinunitvenue.venuename,                  
sonvininsert.venue,                    
sonvininsert.instructore,                                
instructoreexpense.amount,
@bill_id
from                     
instructoreexpense                     
left outer join sonvininsert on                     
sonvininsert.sonvinid=instructoreexpense.sonvinid and                     
sonvininsert.status='0'                      
left outer join finalinstructoreexpense on                     
finalinstructoreexpense.sonvinid=instructoreexpense.sonvinid                      
left outer join companysonvinunitvenue on                     
companysonvinunitvenue.id=sonvininsert.comsonvinid      
where                     
sonvininsert.sonvinid not in(select sonvinid from listinvoice)    
and    
sonvininsert.date                     
between convert(datetime,@from,105) and                     
convert(datetime,@to,105) and                     
sonvininsert.trainer=(select empname from trainerdetails where trid=@trainer)      
and instructoreexpense.sonvinid NOT IN (      
            SELECT CAST(Item AS INTEGER)      
            FROM SplitString(@sonvinid, ',')      
      )      
order by instructoreexpense.sonvinid 

Answer 2

The problem is with your INSERT statement:

insert into listinvoice(sonvinid,tid,date,brandname,zone,
location,area,venuename,venue,instructore,amount,bill_id=@bill_id)

You haven't finished the statement. You would typically format it like this:

INSERT INTO (Col1, Col2, Col3) VALUES (@Val1, @Val2, @Val3)

You would first list the columns for which you want to provide values in the first group and then list the values themselves in the second group after the keyword VALUES).

In yours, you appear to be listing the columns you want to insert with the exception of the last one, where you're trying to also assign a value somehow. You need something like this:

insert into listinvoice(sonvinid,tid,date,brandname,zone,
location,area,venuename,venue,instructore,amount,bill_id)
VALUES (@sonvinid, ..., @bill_id)

Obviously you'd replace the ellipsis with your actual parameters.