Home / Questions / 98618
Accepted
Lucas. D
Asked: 2020-08-31 10:12:58 +0800 CST 2020-08-31 10:12:58 +0800 CST

Result set has no current row

  • 772

I am doing a program of Librosand Autoreswhere one autorhas many libros.

enter image description here

When inserting a new one, LibroI need to obtain the iddel autorto save it as fk_dniin the table Libros, so I have the following code which loads me comboboxwith those Autoresthat are saved in my database.

public void insertarLibro()
    {
        String url = "jdbc:sqlserver://localhost:1433;databaseName=Biblioteca";
        String user = "mi_usuario";
        String password = "mi_contraseña";
        String fk_dni = "SELECT dni FROM Autores WHERE nombre = '?'";


        try
        {
            Connection cn = DriverManager.getConnection(url, user, password);
            PreparedStatement ps = cn.prepareStatement(fk_dni);
            ResultSet rs = ps.executeQuery();
            String nombre = (String)cboAutor.getSelectedItem();

            while(rs.next())
            {
                ps.setString(1, nombre);
            }

            System.out.println("DNI : " + rs.getString(1));
        }
        catch(SQLException e)
        {
            System.out.println("ERROR : ");
            e.printStackTrace(System.out);
        }

    }

But my program returns the following error

com.microsoft.sqlserver.jdbc.SQLServerException: Result set has no current row

So I deduce that my program is not entering my loop while. In case I remove the single quotes in my query fk_dni, the error it throws me is:

The value is not set for parameter number 1.

The System.out.println("DNI : " + rs.getString(1));is temporary since I only use it to verify that the data is displayed correctly.

java

2 Answers

  1. As @A.Cedano already said, the query does not have single quotes around the question mark:

    String fk_dni = "SELECT dni FROM Autores WHERE nombre = ?";

    Also, you have to pass the value to the query and then execute it

     public void insertarLibro()
    {
    String url = "jdbc:sqlserver://localhost:1433;databaseName=Biblioteca";
    String user = "mi_usuario";
    String password = "mi_contraseña";
    String fk_dni = "SELECT dni FROM Autores WHERE nombre = ?";


    try
    {
        Connection cn = DriverManager.getConnection(url, user, password);
        PreparedStatement ps = cn.prepareStatement(fk_dni);
        String nombre = (String)cboAutor.getSelectedItem();
        ps.setString(1, nombre);
        ResultSet rs = ps.executeQuery();
        while(rs.next())
           System.out.println("DNI : " + rs.getString(1));
    }
    catch(SQLException e)
    {
        System.out.println("ERROR : ");
        e.printStackTrace(System.out);
    }

}
    • 5
  2. Best Answer

    There are some errors in the code:

    • Misspelled prepared query. The parameter(s) to be passed in the prepared statement, indicated by ? should not be surrounded by single quotes like you have now. Should be:

      String fk_dni = "SELECT dni FROM Autores WHERE nombre = ?";

    • You cannot do the executeQuerybefore setting the parameter nombre:

      try
{
    String nombre = (String)cboAutor.getSelectedItem();
    Connection cn = DriverManager.getConnection(url, user, password);
    PreparedStatement ps = cn.prepareStatement(fk_dni);
    ps.setString(1, nombre);
    ResultSet rs = ps.executeQuery();

    if(rs.next())
    {
       System.out.println("DNI : " + rs.getString(1));
    }

}
catch(SQLException e)
{
    System.out.println("ERROR : ");
    e.printStackTrace(System.out);
}

    Note: Searching for a DNI only by name can give you wrong data, it may be that José from Egypt is not the same as José from Nazareth.

    • 3