to update sql table with datatable in StoredProcedure

Question

1.00/5 (1 vote)

See more:

To create DAtatable and to store values in Datatable

C#

protected void btnssubbank_Click(object sender, EventArgs e)
   {
       Session["BankDetails"] = null;
       string acnumber = txtacnumber.Text;
       string bankname = txtbankname.Text;
       string bankaddress = txtbankaddress.Text;
       string ifsccode = txtifsccode.Text;


       DataTable dtbank = new DataTable();
       DataColumn dcbank1 = new DataColumn("Account_NO");
       DataColumn dcbank2 = new DataColumn("Bank_Name");
       DataColumn dcbank3 = new DataColumn("Bank_Address");
       DataColumn dcbank4 = new DataColumn("IFSC_Code");
       DataColumn dcbank5 = new DataColumn("Operation", typeof(string));
       dtbank.Columns.Add(dcbank1);
       dtbank.Columns.Add(dcbank2);
       dtbank.Columns.Add(dcbank3);
       dtbank.Columns.Add(dcbank4);
       dtbank.Columns.Add(dcbank5);
       if (Request.QueryString["Type"] == "2")
       {
           DataRow drbank = dtbank.NewRow();
           drbank[0] = acnumber;
           drbank[1] = bankname;
           drbank[2] = bankaddress;
           drbank[3] = ifsccode;
           drbank[4] = 1;
           dtbank.Rows.Add(acnumber, bankname, bankaddress, ifsccode, 1);
       }
       else
       {
           DataRow drbank = dtbank.NewRow();
           drbank[0] = acnumber;
           drbank[1] = bankname;
           drbank[2] = bankaddress;
           drbank[3] = ifsccode;
           drbank[4] = 0;
           dtbank.Rows.Add(acnumber, bankname, bankaddress, ifsccode, 0);
       }

       //dtbank.Rows.Add(acnumber, bankname, bankaddress, ifsccode, 0);
       Session["BankDetails"] = dtbank;

   }

This is My .cs code to pass datatable as a parameter to StoredProcedure

C#

DataTable dtbankdetails = Session["Bankdetails"] as DataTable;
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.CommandText = "EmployeeBankDetails_SP";
      cmd.Parameters.AddWithValue("@Employee_id", SqlDbType.VarChar).Value = Session["Employee_id"].ToString();
      cmd.Parameters.AddWithValue("@empname", SqlDbType.VarChar).Value = txtfname.Text + txtmname.Text + txtlname.Text;
      cmd.Parameters.AddWithValue("@dtbankdetails", dtbankdetails);
      cmd.Parameters.AddWithValue("@CUser", SqlDbType.VarChar).Value = Session["user"].ToString();
      cmd.Parameters.AddWithValue("@CDate", SqlDbType.DateTime).Value = Convert.ToDateTime(DateTime.Now.ToString());
      cmd.Parameters.AddWithValue("@MUser", SqlDbType.VarChar).Value = Session["user"].ToString();
      cmd.Parameters.AddWithValue("@MDate", SqlDbType.DateTime).Value = Convert.ToDateTime(DateTime.Now.ToString());
      cmd.Parameters.AddWithValue("@Roles", SqlDbType.VarChar).Value = Session["roles"].ToString();
      if (Session["roles"].ToString() == "Hr.Asst")
      {
          cmd.Parameters.AddWithValue("@approvestatus", "1");
      }
      else
      {
          cmd.Parameters.AddWithValue("@approvestatus", ddlstatus.SelectedValue);
      }
      cmd.Parameters.AddWithValue("@Type", "2");
      cmd.Connection = con;
      con.Open();
      cmd.ExecuteNonQuery();// Here,I got Error as "Procedure or function EmployeebankDetails_SP          
      con.Close();          // has too many arguments specified."

My Stored Procedure:-

SQL

ALTER procedure [dbo].[EmployeebankDetails_SP]
@employee_id varchar(30)=null
,@empname varchar(max)=null
,@dtbankdetails EmpType Readonly
,@CUser varchar(50)=null
,@CDate datetime=null
,@MUser varchar(50)=null
,@MDate datetime=null
,@Roles varchar(MAX)=null
,@Type varchar(10)=null
,@approvestatus varchar(5)





As
Begin
Begin Try
Begin Transaction
Declare @Status varchar(10)
declare @count varchar(5)



   if(@Roles='Hr.Asst')
	 Begin
	   IF(@approvestatus='1')
		Begin
			If not exists(select * FROM user_roles where roles='HR')
			  set @Status='2'
			Else if not exists(Select * from user_roles where roles='HRGM')
			  set @Status='3'
			Else if not exists(SELECT * from user_roles where roles='SuperAdmin')
			  Set @Status='4'
			Else 
			   Set @Status='1'
		END
	else if(@approvestatus='2')
		begin
		   If not exists(select * FROM user_roles where roles='HR')
			  set @Status='Hold'
		   Else if not exists(Select * from user_roles where roles='HRGM')
			  set @Status='Hold'
		   Else if not exists(SELECT * from user_roles where roles='SuperAdmin')
			  Set @Status='Hold'
		   Else 
			   Set @Status='Hold'
		END
		
	else if(@approvestatus='3')
		begin
			If not exists(select * FROM user_roles where roles='HR')
			  set @Status='Reject'
			Else if not exists(Select * from user_roles where roles='HRGM')
			  set @Status='Reject'
			Else if not exists(SELECT * from user_roles where roles='SuperAdmin')
			  Set @Status='Reject'
			Else 
			   Set @Status='Reject'
		END
  End 
  Else if(@Roles='HR')
	  Begin
	   IF(@approvestatus='1')
		Begin
		  if not exists(Select * from user_roles where roles='HRGM')
		  set @Status='3'
		  Else if not exists(SELECT * from user_roles where roles='SuperAdmin')
		  Set @Status='4'
		  Else
		  set @Status='2'  
		End
	   else IF(@approvestatus='2')
		Begin
		  if not exists(Select * from user_roles where roles='HRGM')
		  set @Status='Hold'
		  Else if not exists(SELECT * from user_roles where roles='SuperAdmin')
		  Set @Status='Hold'
		  Else
		  set @Status='Hold'  
		End
	   else IF(@approvestatus='3')
		Begin
		  if not exists(Select * from user_roles where roles='HRGM')
		  set @Status='Reject'
		  Else if not exists(SELECT * from user_roles where roles='SuperAdmin')
		  Set @Status='Reject'
		  Else
		  set @Status='Reject'  
		End
	 End
  Else if(@Roles='HRGM')
	  Begin
	    IF(@approvestatus='1')
			Begin
			  if not exists(SELECT * from user_roles where roles='SuperAdmin')
			  Set @Status='4'  
			  Else
			  set @Status='3'  
			END
		  else IF(@approvestatus='2')
			Begin
			  if not exists(SELECT * from user_roles where roles='SuperAdmin')
			  Set @Status='Hold'  
			  Else
			  set @Status='Hold'  
			END
		  else IF(@approvestatus='3')
			Begin
			  if not exists(SELECT * from user_roles where roles='SuperAdmin')
			  Set @Status='Reject'  
			  Else
			  set @Status='Reject'  
			END
	  End
  Else if(@Roles='SuperAdmin')
	  Begin
	     IF(@approvestatus='1')
			  Begin
				 Set @Status='4'  
			  end
		  else IF(@approvestatus='2')
			  begin
			    Set @Status='Hold'  
			  end
		  else IF(@approvestatus='3')
			  begin
			    Set @Status='Reject'  
			  end
	  End
  Else if(@Roles='Chairman Cum Managing Director')
	  Begin
	  	IF(@approvestatus='1')
	      Begin
		    Set @Status='5'  
	      End
	    else IF(@approvestatus='2')
	      Begin
		    Set @Status='Hold'  
	      End
	    else IF(@approvestatus='3')
	      Begin
		    Set @Status='Reject'  
	      End
	  end

if(@Type='3')
	Begin
  		Insert into EmployeeBank_Details (Employee_id,employee_name,created_by,created_Date,status)values(@Employee_id,@empname,@CUser,@CDate,@Status)
  		update e SET e.account_no=d.Account_NO,e.bank_name=d.Bank_Name,e.Bank_Address=d.Bank_Address,e.IFSC_Code=d.IFSC_Code from EmployeeBank_Details e, @dtbankdetails d where e.employee_id=@employee_id
	End
else
	Begin
	  if(@Roles='Hr.Asst')  
			Begin
				--set @count=(SELECT COUNT(id) from EmployeeBank_Details where employee_id=@employee_id)
				--if(@count='0')
				--	Begin
		  --				Insert into EmployeeBank_Details (Employee_id,employee_name,created_by,created_Date,status)values(@Employee_id,@empname,@CUser,@CDate,@Status)
		  --				update e SET e.account_no=d.Account_NO,e.bank_name=d.Bank_Name,e.Bank_Address=d.Bank_Address,e.IFSC_Code=d.IFSC_Code from EmployeeBank_Details e, @dtbankdetails d where e.employee_id=@employee_id
				  		
				--	End
				--else 
				--	Begin
     							     			
     					update e SET e.account_no=d.Account_NO,e.bank_name=d.Bank_Name,e.Bank_Address=d.Bank_Address,e.IFSC_Code=d.IFSC_Code from EmployeeBank_Details e, @dtbankdetails d where e.employee_id=@employee_id
		     			
					--End
		   End
	  else if(@Roles='HR')
		    Begin
				--set @count=(SELECT COUNT(id) from EmployeeBank_Details where employee_id=@employee_id)
				--if(@count='0')
				--  Begin
				--	Insert into EmployeeBank_Details (Employee_id,employee_name,created_by,created_Date,status)values(@Employee_id,@empname,@CUser,@CDate,@Status)
		  --			update e SET e.account_no=d.Account_NO,e.bank_name=d.Bank_Name,e.Bank_Address=d.Bank_Address,e.IFSC_Code=d.IFSC_Code from EmployeeBank_Details e, @dtbankdetails d where e.employee_id=@employee_id
				--  End
				--else
				--  begin
					update e SET e.account_no=d.Account_NO,e.bank_name=d.Bank_Name,e.Bank_Address=d.Bank_Address,e.IFSC_Code=d.IFSC_Code from EmployeeBank_Details e, @dtbankdetails d where e.employee_id=@employee_id
				  --end
			End
	 else if(@Roles='HRGM')
		    Begin
	   --     	set @count=(SELECT COUNT(id) from EmployeeBank_Details where employee_id=@employee_id)
				--if(@count='0')
				--  Begin
				--	Insert into EmployeeBank_Details (Employee_id,employee_name,created_by,created_Date,status)values(@Employee_id,@empname,@CUser,@CDate,@Status)
		  --			update e SET e.account_no=d.Account_NO,e.bank_name=d.Bank_Name,e.Bank_Address=d.Bank_Address,e.IFSC_Code=d.IFSC_Code from EmployeeBank_Details e, @dtbankdetails d where e.employee_id=@employee_id
				--  End
				--else
				--  begin
					update e SET e.account_no=d.Account_NO,e.bank_name=d.Bank_Name,e.Bank_Address=d.Bank_Address,e.IFSC_Code=d.IFSC_Code from EmployeeBank_Details e, @dtbankdetails d where e.employee_id=@employee_id
				  --end
		    End
	else if(@Roles='SuperAdmin')
		    Begin
				--set @count=(SELECT COUNT(id) from EmployeeBank_Details where employee_id=@employee_id)
				--if(@count='0')
				--  Begin
				--	Insert into EmployeeBank_Details (Employee_id,employee_name,created_by,created_Date,status)values(@Employee_id,@empname,@CUser,@CDate,@Status)
		  --			update e SET e.account_no=d.Account_NO,e.bank_name=d.Bank_Name,e.Bank_Address=d.Bank_Address,e.IFSC_Code=d.IFSC_Code from EmployeeBank_Details e, @dtbankdetails d where e.employee_id=@employee_id
				--  End
				--else
				--  begin
update e SET e.account_no=d.Account_NO,e.bank_name=d.Bank_Name,e.Bank_Address=d.Bank_Address,e.IFSC_Code=d.IFSC_Code from EmployeeBank_Details e, @dtbankdetails d where e.employee_id=@employee_id 
				  --end
			End
	else if(@Roles='Chairman Cum Managing Director')
		    Begin
				update e SET e.account_no=d.Account_NO,e.bank_name=d.Bank_Name,e.Bank_Address=d.Bank_Address,e.IFSC_Code=d.IFSC_Code from EmployeeBank_Details e, @dtbankdetails d where e.employee_id=@employee_id
			End
	End			       
		  
		  
	  
	  
	  
	  

commit transaction
end try
begin catch
-- Incase of Exception Rollback the transanction
ROLLBACK TRANSACTION
Select convert(varchar,ERROR_LINE())+' '+ERROR_MESSAGE()
end catch
End

Posted 3-Dec-13 18:19pm

Krish from Hyderabad

Updated 3-Dec-13 18:23pm

_Damian S_

v2

Add a Solution

Comments

m@dhu 4-Dec-13 0:30am

So whats the issue in there?

thatraja 4-Dec-13 5:27am

Hey you, long time no see in GIT.....

thatraja 4-Dec-13 5:27am

what's the error?

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)