"Error converting data type nvarchar to int." executing stored procedure

Question

I have a stored procedure and I am getting this error when executing it from C# code.

Error converting data type nvarchar to int.

My stored procedure:

CREATE PROCEDURE [dbo].[donateBloodProc] 
  @donorName varchar(50),
  @gender varchar(20),
  @email varchar(50),
  @bloodGroup varchar(50),
  @contact_number int, @L_D_D date,
  @city varchar(50),
  @arid_number varchar(50),
  @DepId int 
  AS
  INSERT INTO
        tblDonors(Dname, gender, bloodGroup, email, contact_number,
        L_D_D,city,arid_number,DeptID,dateDonated) 
    VALUES 
       (@donorName,@gender,@bloodGroup,@email,@contact_number,
       @L_D_D,@city,@arid_number,@DepId,GETDATE());

And this is my C# code:

SqlCommand commandForSP = new SqlCommand("donateBloodProc",DALobj.openConnection());
commandForSP.CommandType = System.Data.CommandType.StoredProcedure;

commandForSP.Parameters.AddWithValue("@donorName", TextBox1.Text);
commandForSP.Parameters.AddWithValue("@gender", RadioButtonList1.SelectedValue);
commandForSP.Parameters.AddWithValue("@email", TextBox5.Text);
commandForSP.Parameters.AddWithValue("@bloodGroup", DropDownList1.SelectedValue);
commandForSP.Parameters.AddWithValue("@contact_number", TextBox2.Text);
commandForSP.Parameters.AddWithValue("@L_D_D", TextBox3.Text);
commandForSP.Parameters.AddWithValue("@city", DropDownList2.SelectedValue);
commandForSP.Parameters.AddWithValue("@arid_number", TextBox1.Text);
commandForSP.Parameters.AddWithValue("@DepId", TextBox4.Text);

commandForSP.ExecuteNonQuery();

All the data types of the columns in my stored procedure are same as in my database table tblDonors

Answer 1

as @bejger said it is because you are sending string instead of int to the contact and depid parameters.

but i think sending the String/VARCHAR value for INT columns is acceptable and it does not throw the exception if it is a valid integer.

it throws Exception when provided String can not be converted into Integer. hence i would suggest you to use Int32.TryParse() method to perform the parsing before sending the value to the parameter.

Int32.TryParse() method returns true if the parsing is successfull otherwise returns false.

Complete Code:

if(!Int32.TryParse(TextBox2.Text,out var contact))
{
  //Error here you can return or display some warning
  return;
}

if(!Int32.TryParse(TextBox4.Text,out var depid))
{
  //Error here you can return or display some warning
  return;
}

SqlCommand commandForSP = new SqlCommand("donateBloodProc",DALobj.openConnection());
commandForSP.CommandType = System.Data.CommandType.StoredProcedure;

commandForSP.Parameters.AddWithValue("@donorName", TextBox1.Text);
commandForSP.Parameters.AddWithValue("@gender", RadioButtonList1.SelectedValue);
commandForSP.Parameters.AddWithValue("@email", TextBox5.Text);
commandForSP.Parameters.AddWithValue("@bloodGroup", DropDownList1.SelectedValue);
commandForSP.Parameters.AddWithValue("@contact_number",contact);
commandForSP.Parameters.AddWithValue("@L_D_D", TextBox3.Text);
commandForSP.Parameters.AddWithValue("@city", DropDownList2.SelectedValue);
commandForSP.Parameters.AddWithValue("@arid_number", TextBox1.Text);
commandForSP.Parameters.AddWithValue("@DepId", depid);

commandForSP.ExecuteNonQuery();

Answer 2

The problem is with these lines:

commandForSP.Parameters.AddWithValue("@contact_number", TextBox2.Text);
commandForSP.Parameters.AddWithValue("@DepId", TextBox4.Text);

Because you try to pass string values, whereas your columns are of integer type. You should try to do this in such a way:

commandForSP.Parameters.AddWithValue("@contact_number", int.Parse(TextBox2.Text));
commandForSP.Parameters.AddWithValue("@DepId", int.Parse(TextBox4.Text));

Although be aware that this is not fully error-proof (so if there is no value in the textbox it will throw an exception).

To summarize, your code will look like this:

SqlCommand commandForSP = new SqlCommand("donateBloodProc",DALobj.openConnection());
commandForSP.CommandType = System.Data.CommandType.StoredProcedure;
commandForSP.Parameters.AddWithValue("@donorName",TextBox1.Text);
commandForSP.Parameters.AddWithValue("@gender", RadioButtonList1.SelectedValue);
commandForSP.Parameters.AddWithValue("@email", TextBox5.Text);
commandForSP.Parameters.AddWithValue("@bloodGroup",DropDownList1.SelectedValue);
commandForSP.Parameters.AddWithValue("@contact_number", int.Parse(TextBox2.Text));
commandForSP.Parameters.AddWithValue("@L_D_D",TextBox3.Text);
commandForSP.Parameters.AddWithValue("@city", DropDownList2.SelectedValue);
commandForSP.Parameters.AddWithValue("@arid_number", TextBox1.Text);
commandForSP.Parameters.AddWithValue("@DepId", int.Parse(TextBox4.Text));
commandForSP.ExecuteNonQuery();