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.

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

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

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.

error converting data type nvarchar to int.

Msg 8114 Level 16 State 5 Line 1 Error converting data ...

Created the following cursor to cause the query to execute one row at a time which allowed me to identify the problem data row SET ARITHABORT OFF SET ARITHIGNORE ON SET ANSI_WARNINGS OFF DECLARE @msg VARCHAR(4096) BEGIN TRY DECLARE @itemid AS NVARCHAR(255); DECLARE C CURSOR FAST_FORWARD FOR SELECT ItemID AS itemid FROM dbo.qryOtherFieldDataVerifySource; OPEN C; FETCH NEXT FROM C INTO @itemid ...

Error converting varchar value to data type int - Stack Overflow

SQL is trying to convert your phrase to int probably because its part of CASE statement. It uses the first route to determine the output type. In your case- you put NULL as the first route option in your CASE so it is determined as int. try putting instead of it this CAST(NULL AS VARCHAR(10))

c# - ErrorSystem.Data.SqlClient.SqlException (0x80131904 ...

System.Data.SqlClient.SqlException Conversion failed when converting the nvarchar value to data type int. Hot Network Questions Why are there two mechanisms to remove/hide disks in Linux?

Getting Error Error converting data type nvarchar to ...

The problem might be that youre not giving anything to infer the data type from as youre not setting the value of the parameter. Try setting the parameters using the Add method as follows cmd.Parameters.Add (@Raumklasse_ID SqlDbType.Int).Value = RAUMKLASSE_ID; That way if you dont want to set the value you can still define the parameter.

Error converting data type varchar to int (stored procedure ...

One possible reason Your MBID parameter is varchar (20) but the MBID column in Members is int. Change the parameter to be int and see if it fixes the problem. Youll also need to change this line in C# cmd.Parameters.Add (@MBID SqlDbType.VarChar).Value = (txtSearch.Text.Trim ()); to.

SQL Error (245) Conversion failed when converting the ...

Firstly check if any values are bad (which I dont doubt there will be because youre storing numerical data as a nvarchar) SELECT YourIdColumn Pro_PCT FROM dbo.YourTable WHERE TRY_CONVERT(intPro_PCT) IS NULL AND Pro_PCT IS NOT NULL; That will give you a dataset back of all the bad data. Youll need to fix all of those values.

type conversion - Why does SQL Server say it cant convert ...

Query 1 worked because when you removed the N (which is used to convert a string to nvarchar) SQL Server didnt need to perform an implicit convertion of sales.type from varchar to nvarchar. In this case sales.pid has an implicit convertion on both queries since its being compared to products.idn which has a different data type.

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;

Write your query this way. It includes a better test for integers and dumps ISNUMERIC that returns 1 for '-.', for example.

SELECT
    zip,
    coordinates
FROM (
    SELECT
        CASE WHEN ZIP5>'' AND NOT ZIP5 LIKE '%[^0-9]%' THEN
            CONVERT(int, [ZIP5]) END zip,
        CONVERT(varchar(max), geom) AS coordinates
    FROM 
        [SpatialData].[dbo].[zip5]
    WHERE
        ZIP5>'' AND NOT ZIP5 LIKE '%[^0-9]%'
) AS t1
WHERE
    zip >= 85000 AND
    zip < 86000

See this Connect item

SQL Server is free to evaluate the WHERE/SELECT clause in the order that it decides is optimized. A view or derived table is not materialized can easily be expanded into, from the outer query.

What SQL Server is compiling is really a query that looks like this

SELECT
    CONVERT(int, [ZIP5]) AS zip,
    CONVERT(varchar(max), geom) AS coordinates
FROM 
    [SpatialData].[dbo].[zip5]
WHERE
    ISNUMERIC([ZIP5]) = 1
    AND CONVERT(int, [ZIP5]) >= 85000
    AND CONVERT(int, [ZIP5]) < 86000

You can inspect the query plan of your original, but my guess from looking at the structure is that the WHERE clause uses the expression CONVERT(int, [ZIP5]) twice, so it makes sense to streamline the resolution (calculation to a result) of the expression in the process of retrieving data from the table. This puts the processing of the SELECT clause before the WHERE, so your ISNUMERIC() = 1 never got a chance to filter the bad eggs.

It is by-design.