You Declare Phone No as Numeric Field So You need to Convert Your Text into Integer

 [cPhoneNo] NUMERIC (18)   NOT NULL
 [cFax]     NUMERIC (18)   NOT NULL

Use like this:

com.Parameters.AddWithValue("@phoneno", Convert.ToInt32(contactnumberTB.Text));
com.Parameters.AddWithValue("@fax", Convert.ToInt32(faxTB.Text));

STOP USING ADD WITH VALUES

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...

The problem is being caused by passing values to @MobileNo parameter which you have declared as integer in stored procedure. You need to have validation in code behind before passing value from txtMobileNo.Text to @MobileNo parameter which validates that value entered in textbox is valid integer.

See Int32.TryParse method to validate text is valid integer.

Otherwise declare @MobileNo parameter in stored proc to VARCHAR eg

create proc spRegisterUser  
@UserName nvarchar(100),  
@Password nvarchar(200),  
@Email nvarchar(200),  
@MobileNo VARCHAR(15)

--Rest of code

After looking for other people answer I find this solution.

string sqlquery = "INSERT INTO Runner (firstName, lastName, amount, categoryID, cityID, charityID, routeID) VALUES (@firstName, @lastName, @amount, @category, @city, @charity, @route)";
cmd = new SqlCommand(sqlquery, conn);
conn.Open();
cmd.Parameters.Add("@firstName", SqlDbType.VarChar, 50).Value =  txtFirstName.Text;
cmd.Parameters.Add("@lastName", SqlDbType.VarChar, 50).Value = txtLastName.Text;
cmd.Parameters.Add("@amount", SqlDbType.Int).Value = txtMoney.Text;
cmd.Parameters.Add("@category", SqlDbType.Int).Value = cbBoxCategory.SelectedValue;
cmd.Parameters.Add("@city", SqlDbType.Int).Value = cbBoxCity.SelectedValue;
cmd.Parameters.Add("@charity", SqlDbType.Int).Value = cbBoxCharity.SelectedValue;
cmd.Parameters.Add("@route", SqlDbType.Int).Value = cbBoxRoute.SelectedValue;
cmd.ExecuteNonQuery();
MessageBox.Show("New Runner has been registered!");
conn.Close();

By using Convert.ToInt32(cbBoxRoute.SelectedValue)

Here is the link where I find my solution:

Insert ID into SQL from C# Combo box

@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.

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

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

Conversion failed when converting the nvarchar value '%' to data type int

Is pretty clear that you have a datatype issue. And the wildcard of '%' is certainly not a integer. You are probably having the issue because the column in where [column] LIKE @param is probably an integer. So even though you identify the parameter as string it is still trying to do an integer to string comparison.

So you are comparing like WHERE Integer LIKE String and that throws a datatype conversion error because SQL will automatically try to convert your string to an integer.

To solve if you want to search for a number as a string which doesn't seem like a good idea you would do something like:

WHERE CAST([column] AS VARCHAR(10)) LIKE @param.

Setup a try...catch and then iterate over each column so you can identify the culprit. For example:

while ( reader.HasRows )    
{

    for( int index = 0; index < reader.FieldCount; index ++ )
    {
        fields[ reader.GetName( index ) ] = reader.GetString( index );
    }    
}

Usually it's not a problem to pass a string to a parameter that's numeric, as long as the SQL Server is able to convert the content of the string to a numeric value itself. If that doesn't work, you get this error.

For example: Passing "Hello" to a parameter that's numeric, you get an error. Passing "1234" you don't. Please note that an empty string or a string containing whitespace can not be converted to a numeric value!

However, it should be said that it is not good style to do that. You should make sure that the types you use in your application match the types in the database to avoid problems. Maybe some further detail on why you need to have string types in your application could help.

EDIT 1
To make a parameter optional for the query, the way to go would be the following:

1. Change your SQL statement to allow optional parameters like WHERE RAUMKLASSE_ID = ISNULL(@Raumklasse_ID, RAUMKLASSE_ID).
2. Do not add the @Raumklasse_ID parameter if it should be optional or add the value DBNull.Value

You should really consider changing your string properties to nullable types like int?.

EDIT 2
This is how your code could look implementing the changes I suggested in Edit 1:

using (SqlCommand cmd = new SqlCommand(@"SELECT r.BEZEICHNUNG AS BEZEICHNUNG, r.ID AS ID FROM RAUM r WHERE RAUMKLASSE_ID = ISNULL(@Raumklasse_ID, RAUMKLASSE_ID) OR STADT_ID = ISNULL(@Stadt_ID, STADT_ID) OR GEBAEUDE_ID = ISNULL(@Gebaeude_ID, GEBAEUDE_ID) OR REGION_ID = ISNULL(@Region_ID, REGION_ID)", con)) 
{
    con.Open();
    if (!String.IsNullOrWhitespace(RAUMKLASSE_ID))
        cmd.Parameters.AddWithValue("@Raumklasse_ID", RAUMKLASSE_ID);
    else
        cmd.Parameters.AddWithValue("@Raumklasse_ID", DBNull.Value);
    if (!String.IsNullOrWhitespace(STADT_ID))
        cmd.Parameters.AddWithValue("@Stadt_ID", STADT_ID);
    else
        cmd.Parameters.AddWithValue("@Stadt_ID", DBNull.Value);
    if (!String.IsNullOrWhitespace(GEBAEUDE_ID))
        cmd.Parameters.AddWithValue("@Gebaeude_ID", GEBAEUDE_ID);
    else
        cmd.Parameters.AddWithValue("@Gebaeude_ID", DBNull.Value);
    if (!String.IsNullOrWhitespace(REGION_ID))
        cmd.Parameters.AddWithValue("@Region_ID", REGION_ID);
    else
        cmd.Parameters.AddWithValue("@Region_ID", DBNull.Value);
    ...
}

I advice you to use the SqlDbType and put for each parameter to the corresponding type in the database like this :

cmd.Parameters.Add (new SqlParameter("@unitPrice", SqlDbType.Float).Value = txtUnitPrice.Text ;

or

cmd.Parameters.AddWithValue("@unitPrice", SqlDbType.Float).Value = txtUnitPrice.Text;

I meet the same exception, it throws message like "Database 'YYYY' cannot be opened. It is in the middle of a restore." This is expected behavior for me, the sql server is actually in the middle of restore.

The error number is 927. For all the error numbers, refer to https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/cc645603(v=sql.105)?redirectedfrom=MSDN

how to catch this type of sql exception:

try
{
    await db.ExecuteProcAsync(...);
}
catch (SqlException e) when (e.Number == 927)
{
    ...
}