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

Question

I'm trying to create an Update Statement for my Table in JavaFx and I'm receiving following Error:

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

I created the tables in SQL Server.In my database only age is from type int, everything else is String.

Here is my Code :

    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, userNo);
        p_stmt.setString(2, name);
        p_stmt.setDouble(3, age);
        p_stmt.setString(4, department);
        p_stmt.setString(5, job);
        p_stmt.setString(6, contact);
        int i = p_stmt.executeUpdate();
        if (i == 1)  {
        }System.out.println("Data Updated Successfully");
        loadDataFromDatabase();

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

Just a guess, but should the parameters not be in the same order that they are in the query? You add the parameter userNo first, but it's the last in your query. Considering that 'Ana' is a name as well, which is the second parameter you add and the parameter for Age is the second in the query, I'm assuming this is a very safe guess. — Larnu

David Browne - Microsoft David Browne - Microsoft · Accepted Answer · 2019-12-27T00:08:00

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

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

1 Answers