Click here to Skip to main content
15,893,722 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
i want to add record in the database using C#. but when i click to "ADD" button message box appears with message that
Employee Record could not be added!!!!"


What I have tried:

<pre> //methode
string qry = "execute insertemplab @Employee_id,@Emplyee_name,@Emplyee_phone,@Employee_address,@Employee_type,@clothmanufacture";
            
            SqlConnection connection = getconnection();
            SqlCommand command = new SqlCommand(qry, connection);
            command.CommandType = CommandType.StoredProcedure;
          command.Parameters.AddWithValue("@Employee_ID", lab.Employee_ID);
            command.Parameters.AddWithValue("Emplyee_Name", lab.EmployeeName);
            command.Parameters.AddWithValue("@Emplyee_phone", lab.PhoneNumber);
            command.Parameters.AddWithValue("@Employee_Address", lab.EmployeeAddress);
            command.Parameters.AddWithValue("@Employee_Type", lab.EmployeType);
            command.Parameters.AddWithValue("@clothmanufacture", lab.ClothManufactured);
            
            try{
                command.ExecuteNonQuery();
                
                MessageBox.Show("Employee Record Added Successfully");
            }
            catch (SqlException ex)
            {
                MessageBox.Show("Employee Record could not be added!!!!");
            }



        }

//when click to add button
LaboureClass lab = new LaboureClass(txtlaboureID.Text, txtname.Text, txtphone.Text, txtaddress.Text, txttype.Text, int.Parse(txtmanufacture.Text));
            
           
            DBconnect.addEmployerecord(lab);

//this is my procedure in SQL server
create proc insertemplab @Employee_id varchar(50), @Emplyee_name varchar(50), 
 @Emplyee_phone varchar(50),  @Employee_address varchar(50),  @Employee_type varchar(50),@clothmanufacture varchar(50)
as
begin 
insert into Employee_tbl(Employee_id,Emplyee_name,Emplyee_phone,Employee_address,Employee_type) values(@Employee_id, @Emplyee_name,  @Emplyee_phone,  @Employee_address,  @Employee_type)
insert into Labour_tbl(L_Employee_id,clothmanufacture) values(@Employee_id,@clothmanufacture)

end 
Posted
Updated 28-Jan-20 4:55am

Well unfortunately, your code catches the error and disposes of it, so in the catch block your message box would be better returning ex.Message.

But, the way you are calling your proc is unusual. Normally, the SQL would be just the name of the proc, with Command.CommmandType set to SP as you have done.

What happens when you do this:
string qry = "insertemplab";


I presume your parameter names match those of the stored proc.

EDIT: You might want to think about disposing your connection and command too, depending on what that getConnection() delivers.
 
Share this answer
 
v2
Start with the debugger: put a breakpoint on the line:
C#
MessageBox.Show("Employee Record could not be added!!!!");

Run the app again, and when it stops, look at the Exception object ex to find out what the reported problem is. Then use the debugger to look at the data you are passing and use the error to help you work out what it is.

We can't do any of that for you: we don't have access to your code while it's running, we don't have your data in the textboxes, we don't have your LaboureClass, and we don't have your database to try it on.
Give it a try, and see how much information you can find out!
 
Share this answer
 
Comments
LuQman Asif 28-Jan-20 11:40am    
Could not find stored procedure 'execute insertemplab @Employee_id,@Emplyee_name,@Emplyee_phone,@Employee_address,@Employee_type,@clothmanufacture'.
this message appears when i debugg by applying break
OriginalGriff 28-Jan-20 11:50am    
And that tells you everything you need to know...
When you want to your a stored procedure, it defines the parameters, not yoru C# code.
Replace this:

string qry = "execute insertemplab @Employee_id,@Emplyee_name,@Emplyee_phone,@Employee_address,@Employee_type,@clothmanufacture";

With this:

string qry = "insertemplab";

And try again.
LuQman Asif 28-Jan-20 11:58am    
Procedure or function 'insertemplab' expects parameter '@Employee_type', which was not supplied.

now this appears on debbuging.
OriginalGriff 28-Jan-20 12:02pm    
So check your lab object and see what is in lab.EmployeType
At a guess, it's null - probably because you have two objects with similar names and the one you are using is misspelled...
LuQman Asif 28-Jan-20 12:10pm    
yes when i debug it is null. now tell my how can i solve it.
The actual way though to figure out what is going is to utilize the debugger and look and see what is going on that throws the SqlException; you are assigning the exception off to the ex variable but you are not actually doing anything with it.
So put a breakpoint in athe the catch line and see what the actual SqlException is.

There are 2 possibilities off the bat:
1. EXECUTE is assumed when your SqlCommand is of the type CommandText.StoredProcedure

2. Data Types. All of the variables in the SP are defined as VARCHAR(50), and the "clothmanufacture" value being passed in is an INTeger. Do the data types in the table schema match with the SP variable declarations?
 
Share this answer
 
v2
Comments
LuQman Asif 28-Jan-20 11:33am    
all the datatype in SP are varchar(50) but varchar can accept any type of value. is not it true?
MadMyche 28-Jan-20 11:57am    
Yes and no. Your types should match all the way through from C# to SP to table schema.
And after further review.. I think it is the SqlCommand itself; it should only be the SP name, no parameters or execute should be in there.
LuQman Asif 28-Jan-20 12:59pm    
you are right i have changed that. i debug it and now this error appears
Procedure or function 'insertemplab' expects parameter '@Employee_type', which was not supplied.
MadMyche 28-Jan-20 17:11pm    
If the value you are attempting to pass in has not been defined, the parameter will not be attached. It would need to be either (1) explicitly defined as dbNull or (2) a default would need to be defined in the SP.
But it looks like you already got this taken care of

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900