Conversion Failed When Converting the Nvarchar Value ... to Data Type Int

Conversion failed when converting the nvarchar value ... to data type int

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

Conversion failed when converting the nvarchar value '3.5' to data type int with Convert()

Apparently, confirmed_time is a string. When you compare to an integer, it tries to compare as integers. So, make the conversion explicit:

(case when try_convert(float, Confirmed_Time) >= 1 and
           try_convert(float, Confirmed_Time) <= 9
      then '0-9'
 . . .

My recommendation is to never rely on implicit conversion. It introduces really hard to spot errors.

If you want this value as a decimal (which makes more sense to me than an int), then you should change the type in the table. If that is not possible for some reason (say some values are not numbers), you can add a new column:

alter table ReduceDuties add confirmed_time_dec as (try_convert(decimal(10, 2), Confirmed_Time));

You can then use confirmed_time_dec safely in your queries.

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

I should add one more way with XML style:

SELECT * 
FROM mytable
WHERE CAST(id as xml).value('. cast as xs:decimal?','int') BETWEEN 10 AND 15

Convert id to XML, convert the value in xs:decimal and then convert to integer. If there is not numeric value it will be converted into NULL.

Here you can read about XML type casting rules (link).

SQL: Conversion failed when converting the nvarchar value 'XXX' to data type int

If you have OUCode > 999 in your tables, the STR(a.OUCode, 3) will return *** which when trying to convert to int would give you the error

Conversion failed when converting the varchar value '***' to data type int.

From books online : https://learn.microsoft.com/en-us/sql/t-sql/functions/str-transact-sql?view=sql-server-2017

When the expression exceeds the specified length, the string returns ** for the specified length.

The other issue I can see is :
What is the datatype of JobCode in your IdM Table ?. Try this and see if it returns any results :

SELECT JobCode FROM dbo.IdM WHERE ISNUMERIC(JobCode) = 0

How to solve error Conversion failed when converting the nvarchar value to data type integer?

It appears you do not need dynamic SQL at all here.

It's hard to know what logic you are trying to achieve, owing to a lack of sample data and expected output, and your existing query could do with better formatting. But it looks like it's some kind of relational division.

Note that you should not use WITH (NOLOCK) unless you really know what you are doing. It can give wildly incorrect results.

I also strongly suggest you use table references on each column, especially within subqueries

INSERT INTO dbo.TAllData
    (PartID, Code, CodeTypeID, RevisionID, ZPLID, ConCount)
SELECT
  PartID,
  Code,
  Co.CodeTypeID,
  Co.RevisionID,
  Co.ZPLID,
  Count(1) as ConCount
FROM dbo.TPartAttributes PM
INNER JOIN dbo.TCondition Co ON Co.ZfeatureKey = PM.ZfeatureKey
Where EXISTS (SELECT 1
    FROM dbo.TAllfeatures AL
    WHERE AL.ZfeatureKey = Co.ZfeatureKey
      AND AL.IsNumericValues = 0
      AND IIF(Co.ZfeatureType = 'Qualifications', AcceptedValuesOption_Value, Name) = EStrat
)
Group By
  PartID,
  Code,
  Co.CodeTypeID,
  Co.RevisionID,
  Co.ZPLID
Having Count(1) >= (SELECT COUNT(1) FROM TCondition);

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

The parameterIndex for Statement.setString indicates the lexical order of the parameter marker ? in the query. So userNo should be at the end. EG:

    public void handleUpdateAction(ActionEvent event) {
    String sql = "update users set name =?, age = ?, department=?, job=?,  contact = ? where userNo =?" ;
    try {

        String userNo = txt_userNo.getText();
        String name = txt_name.getText();
        double age = Double.valueOf(txt_age.getText());
        String department = txt_department.getText();
        String job = txt_job.getText();
        String contact = txt_contact.getText();
        p_stmt = con.prepareStatement(sql);

        p_stmt.setString(1, name);
        p_stmt.setDouble(2, age);
        p_stmt.setString(3, department);
        p_stmt.setString(4, job);
        p_stmt.setString(5, contact);
        p_stmt.setString(6, userNo);

        int i = p_stmt.executeUpdate();
        if (i == 1)  {
        }System.out.println("Data Updated Successfully");
        loadDataFromDatabase();

    }catch (Exception e) {
        e.printStackTrace();
        throw e;
    }
}

---

Related Topics