Looks like regno is a nvarchar data type in your table and you have passed an int via your your procedure, either use a cast and convert @regno to an nvarchar or change the regno data type to an integer in the table.

DECLARE @regnocast NVARCHAR(15)

SET @regnocast = CAST(@regno AS NVARCHAR)

Then in your SELECT, INSERT and WHERE clauses use @regnocast rather than @regno

You are trying to concatenate a string and an integer.
You need to cast @ID as a string.
try:

SET @sql=@sql+' AND Emp_Id_Pk=' + CAST(@ID AS NVARCHAR(10))

My best guess would be that your problem is in this line:

dm.AddParameters(4, "@boxtype", Convert.ToString(BoxType(lines.GetValue(I).ToString())));

in the procedure @boxtype is declared as an int but I believe this overload of AddParameters will make your parameter of type varchar. I don't know what your data is but one or other seems wrong. I suspect the above line probably should be

dm.AddParameters(4, "@boxtype", Convert.ToInt32(BoxType(lines.GetValue(I).ToString())));

It depends on exactly what BoxType(lines.GetValue(I).ToString())) returns. If it is a string representation of an int then use the above. If it is an int then you don't need the convert at all:

dm.AddParameters(4, "@boxtype", BoxType(lines.GetValue(I).ToString()));

Why I came to this conclusion

I thought I'd add a note on how I debugged this error so that you can understand for future use.

The error message "Error converting data type nvarchar to int" tells us that somewhere is expecting an int but receiving an nvarchar. Nvarchars are only a type in sql so we know that there must be either something in the sql or something in the call to the database.

If it was a problem in the procedure itself you would have noticed it when you were testing the procedure away from the rest of the app. This means it must have been down to the way the procedure is being called.

At this point we look at the parameter list and find that there is one parameter that is an int. We then check that against the code and hey presto, there's our suspicious looking line...

@Gwashoppa, thx for taking time to answer my question. To my understanding return values are not to be mixed with OUTPUT parameters. But in any case I found the solution - data type and length needs to be set explicitly:

cmd.Parameters["@mobile"].SqlDbType = SqlDbType.NVarChar;
cmd.Parameters["@mobile"].Size = 50;

I thought that happened in the AddWithValue function, but apparently not.

In the LEFT JOIN condition you need to make sure that both columns are nvarchar so you need to do cast([v_FullCollectionMembership].CollectionID as nvarchar(30)) = cast([v_Collections].CollectionID as nvarchar(30)) The query will be slower with that, but should work.

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();

Assuming your first table is named A with a column named MyIntColumn of type int and your second table is named B with a column named MyNVarcharColumn of type nvarchar, you could do

From A
Inner Join B On A,MyIntColumn = Try_Cast(B.MyNVarcharColumn As int) 

Tom