Click here to Skip to main content
15,885,920 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
APP.config code:
XML
<connectionStrings>
    <add name="sqlconfig" connectionString="Data Source=AP134;Initial Catalog=Agilepoint;Integrated Security=SSPI"/>
  </connectionStrings>


C#
private void btnadd_Click(object sender, EventArgs e)
       {

           string gn = string.Empty;
           string ck = string.Empty;
           string connectionstring = ConfigurationManager.ConnectionStrings["sqlconfig"].ConnectionString;
           SqlConnection cn = new SqlConnection(connectionstring);

           SqlCommand cmd = new SqlCommand("empcrudoperations", cn);
           cmd.CommandType = CommandType.StoredProcedure;
           SqlParameter p1 = new SqlParameter("@Action", SqlDbType.VarChar,40);
           p1.Value = "insert";
           SqlParameter p = new SqlParameter("@eid", SqlDbType.Int);
           SqlParameter p2 = new SqlParameter("@Firstname", SqlDbType.VarChar, 40);
           SqlParameter p3 = new SqlParameter("@Lastname", SqlDbType.VarChar, 40);
           var rdbtn = ggbbox.Controls.OfType<RadioButton>();
           SqlParameter p4 = new SqlParameter("@Gender", SqlDbType.VarChar, 40);
           foreach (RadioButton rb in rdbtn)
           {
               if (rdmale.Checked)
               {
                   gn = rdmale.Text;

               }
               else
               {
                   gn = rdfemale.Text;
               }
           }

           SqlParameter p5 = new SqlParameter("@Contactno", SqlDbType.VarChar,40);
           SqlParameter p6 = new SqlParameter("@Email", SqlDbType.VarChar, 40);
           SqlParameter p7 = new SqlParameter("@Country", SqlDbType.VarChar, 40);
           SqlParameter p8 = new SqlParameter("@State", SqlDbType.VarChar, 40);
           SqlParameter p9 = new SqlParameter("@Address", SqlDbType.VarChar, 40);
           SqlParameter p10 = new SqlParameter("@Role", SqlDbType.VarChar, 40);
           var chkbox = gbrole.Controls.OfType<CheckBox>();
           foreach (CheckBox ch in chkbox)
           {
               if (trdev.Checked)
               {
                   ck = trdev.Text;
               }
               else if (seng.Checked)
               {
                   ck = seng.Text;
               }
               else if (chr.Checked)
               {
                   ck = chr.Text;
               }
               else
                   ck = cadmin.Text;
           }
           SqlParameter p11 = new SqlParameter("@Experiance", SqlDbType.Int);
           SqlParameter p12 = new SqlParameter("@Salary", SqlDbType.Int);
           p.Value = Convert.ToInt32(textBox1.Text);
           p2.Value = tbname.Text;
           p3.Value = tblname.Text;
           p4.Value = gn;
           p5.Value = tbcontact.Text;
           p6.Value = tbemail.Text;
           System.Text.RegularExpressions.Regex Emailval = new System.Text.RegularExpressions.Regex(@"^[a-zA-Z][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$");

           if (!Emailval.IsMatch(tbemail.Text))
           {
               MessageBox.Show("entered email id is Invaid");
           }
           p7.Value = cntry.Text;
           p8.Value = cstate.Text;
           p9.Value = tbaddress.Text;
           p10.Value = ck;
           p11.Value = tbexp.Text;
           p12.Value = tbsalary.Text;
           cmd.Parameters.Add(p1);
           cmd.Parameters.Add(p);
           cmd.Parameters.Add(p2);
           cmd.Parameters.Add(p3);
           cmd.Parameters.Add(p4);
           cmd.Parameters.Add(p5);
           cmd.Parameters.Add(p6);
           cmd.Parameters.Add(p7);
           cmd.Parameters.Add(p8);
           cmd.Parameters.Add(p9);
           cmd.Parameters.Add(p10);
           cmd.Parameters.Add(p11);
           cmd.Parameters.Add(p12);
           cn.Open();
           
           int count =cmd.ExecuteNonQuery();
           tbname.Text = p2.Value.ToString();
           tblname.Text = p3.Value.ToString();
           tbcontact.Text = p5.Value.ToString();
           tbemail.Text = p6.Value.ToString();
           cntry.Text = p7.Value.ToString();
           cstate.Text = p8.Value.ToString();
           tbaddress.Text = p9.Value.ToString();
           tbexp.Text = p10.Value.ToString();

           if (count > 0)
           {
               MessageBox.Show("new employee added successfully");
           }

           cn.Close();

       }



stored procedure:

C#
create table Employee
(
Eid int primary key,
Firstname varchar(40),
Lastname varchar(40),
Gender varchar(40),
Contactno int,
Email varchar(40),
Country varchar(40),
State varchar(40),
Address varchar(40),
Role varchar(40),
Experience int,
Salary int
)
select * from Employee
set identity_insert Agilepoint.dbo.EmployeeDetails on

insert into Employee values(1,'jyothi','R','female',75234355,'jyothi.jyothi.joy@gmail.com','India','bangalore','#04 2ndfloor hoysalanagr','trainee dev.',0,8000)
insert into Employee values(2,'renu','R','female',76234355,'renu@gmail.com','USA','XXX','#04 2ndfloor hoysalanagr','software dev.',0,18000)
insert into Employee values(3,'arjun','R','male',2364355,'arjun@gmail.com','India','mangalore','#04 2ndfloor vijaynagar','sen. dev.',0,48000)
insert into Employee values(5,'amruth','R','male',9234355,'amruth@gmail.com','India','bangalore','#04 2ndfloor bommanahalli','admin',0,38000)
insert into Employee values(6,'rathna','R','female',85234355,'rathna.joy@gmail.com','Australia','XXX','#04 2ndfloor hoysalanagr','trainee dev.',0,28000)
select * from Employee

create proc empcrudoperations
(
@Action VARCHAR(10)='',
@eid int=0,
@Firstname varchar(40)='',
@Lastname varchar(40)='',
@gender varchar(40)='',
@Contactno int=0,
@Email varchar(40)='',
@Country varchar(40)='',
@state varchar(40)='',
@Address varchar(40)='',
@Role varchar(40)='',
@Experiance int=0,
@salary int=0

)
as
begin
SET NOCOUNT ON;
if @Action='select'
begin
select * from Employee
end
else if @Action='insert'
begin 
insert into Employee(Eid,Firstname,Lastname,Gender,Contactno,Email,Country,State,Address,Role,Experience,Salary) values(@eid,@Firstname,@Lastname,@gender,@Contactno,@Email,@Country,@state,@Address,@Role,@Experiance,@salary)
end
else if @Action='update'
begin
--declare @eid int;
update Employee set Firstname=@Firstname,Lastname=@Lastname,gender=@gender,Contactno=@Contactno,Email=@Email,Country=@Country,State=@state,
       Address=@Address,Role=@Role,Experience=@Experiance,Salary=@salary where eid=@eid
end
else if @Action='Delete'
begin

 delete from Employee where eid=@eid;
end
else
  begin
  select * from Employee
  end
end
Posted
Comments
Krunal Rohit 22-Nov-15 23:34pm    
What's exception? Post the error details as well.

-KR
jyothi joy 23-Nov-15 0:04am    
An unhandled exception of type 'System.FormatException' occurred in System.Data.dll




Additional information: Failed to convert parameter value from a String to a Int32.

at executenonquery method part


after inserting data into textboxes in screenshots and click on add button on screenshot iam getting this exception

there is no error

1 solution

In database you have ContactNo defined as int but your parameter is
C#
SqlParameter p5 = new SqlParameter("@Contactno", SqlDbType.VarChar,40);

Change it to-
C#
SqlParameter p5 = new SqlParameter("@Contactno", SqlDbType.SqlDbType.Int);


Note: I don't think datatype int can store contact nos. Changing it to varchar is suggested unless you really need any arithmatic operation to be done on this field(seems impractical).

Hope, it helps :)
 
Share this answer
 
Comments
jyothi joy 23-Nov-15 2:55am    
I got my mistake....thank u....
Suvendu Shekhar Giri 23-Nov-15 3:19am    
Glad that it helped :)

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