Basic insert works in database but not in java class

Question

I am using Microsoft Azure's cloud database for the first time and all SQL (SELECT, UPDATE and DELETE) are working except for insert. When I try to insert into a table it gives me this exception: com.microsoft.sqlserver.jdbc.SQLServerException: A result set was generated for the update.

Keep in mind all the SQL works when done in the database itself.

I noticed it only gives it to me when using stat.executeUpdate() when I change it to stat.executeQuery() there are no exceptions and is "successful" but it does not insert anything into the table.

I have updated all software and still, nothing has changed.

I found out that one INSERT works but not the other.

Edit: The other question is returning a comment while my method is a void and is not using stat.executUpdate() to get the comment.

public static void main(String[] args)
{
    ConnectDB();
    insertHistory("5364", 10, "5005", "353", "6", 10, "admin");
}

This is the broken INSERT:

    public static void insertHistory(String sInt, int iBase, String P1, String P2, String P3, int pBase, String Email)
    {
        try
        {
            stat = con.prepareStatement("INSERT INTO dbo.tblHistory (Num, BaseForNum, P1, P2, P3, BaseForP, UserID) VALUES (?, ?, ?, ?, ?, ?, ?)");
            stat.setString(1, sInt);
            stat.setInt(2, iBase);
            stat.setString(3, P1);
            stat.setString(4, P2);
            stat.setString(5, P3);
            stat.setInt(6, pBase);
            stat.setInt(7, getUserID(Email));
            stat.executeUpdate();
        }
        catch(SQLException e)
        {
            System.out.println(e);
            JOptionPane.showMessageDialog(null, "Could not save to tblHistory.");
        }
    }

This is the working INSERT:

    public static void insertAccount(String Email, String Password)
    {
        try
        {
            stat = con.prepareStatement("INSERT INTO dbo.tblUser (Email, Password) VALUES (?, ?)");
            stat.setString(1, Email);
            stat.setString(2, Password);
            stat.executeUpdate();
            JOptionPane.showMessageDialog(null, "Account created.");
        }
        catch(SQLException e)
        {
            JOptionPane.showMessageDialog(null, e);
        }
    }

I expect it to have no exception and insert into the database but I get an SQL exception.

So for some reason the format on my code disappeared, not sure how to fix it. — Chris, Jun 25 '19 at 12:23
Could it be that one of the `string` parameters of `insertHistory` is `NULL` — GuidoG, Jun 25 '19 at 12:29
Possible duplicate of [SQLServerException: A result set was generated for update](https://stackoverflow.com/questions/22360730/sqlserverexception-a-result-set-was-generated-for-update) — racraman, Jun 25 '19 at 12:30
What is this function doing? `getUserID(Email)` Is it returning `NULL`, which could be causing the problem? — SS_DBA, Jun 25 '19 at 12:37
getUserID(Email) is getting the primary key that is in the same row as Email, right now it's value is 1 — Chris, Jun 25 '19 at 12:39
So `getUserID` also accesses the database? It could be throwing that exception. — RealSkeptic, Jun 25 '19 at 12:40
Basically this: INSERT INTO dbo.tblHistory (Num, BaseForNum, P1, P2, P3, BaseForP, UserID) VALUES ('5364', 10, '5005', '353', '6', 10, 1); — Chris, Jun 25 '19 at 12:41
Yes that was the problem, I tried stat.setInt(7, 1); and it worked, thanks. — Chris, Jun 25 '19 at 12:43

score 0 · Answer 1 · edited Jun 25 '19 at 14:40

0

I changed stat.setInt(7, getUserID(Email)) to stat.setInt(7, 1). RealSkeptic said it's because it accesses the database.

edited Jun 25 '19 at 14:40

answered Jun 25 '19 at 12:47

Chris

1
2

Basic insert works in database but not in java class

1 Answers1