11 Replies - 851 Views - Last Post: 29 May 2016 - 06:03 AM Rate Topic: -----

#1 asimkh   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 24-May 16

Error message when adding DVD (Oracle SQL)

Posted 29 May 2016 - 03:52 AM

Sample code:

txtTitle.setBackground(Color.WHITE);
        txtYear.setBackground(Color.WHITE);
        String sql = "";
        try
        {
            String title = txtTitle.getText();
            String sanitisedTitle = title.replaceAll("'", "\\\\'");
            int year = Integer.parseInt(txtYear.getText());
            if(DVD.checkTitle(title))
                if(DVD.checkYear(year))
                {
                    sql = "INSERT INTO dvds VALUES (null, '"+ sanitisedTitle +
                            "'," + year + 0 +")";
                    PreparedStatement pst = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                int count  = pst.executeUpdate();
                if(count == 1)
                {
                    ResultSet rs = pst.getGeneratedKeys();
                    if(rs.next())
                    {
                        int ID = rs.getInt(1);
                        
                        DVD dvd = new DVD(ID, title, year,true);
                        dvds.add(dvd);
                        txtTitle.setText(title+"");
                        JOptionPane.showMessageDialog(this,"DVD "+ title + " added");
                        parent.loadData();
                    }
                    else
                        JOptionPane.showMessageDialog(this,"Application error, dvd added but ID unknown");
                }
                else
                    JOptionPane.showMessageDialog(this,"Application error, dvd not added please contact admin");
            }


The error message i get when i try to add a dvd object from my gui is :
INSERT INTO dvds VALUES(null,'KHAN',2015)Column count doesn't match value count at row 121S011136

Is This A Good Question/Topic? 0
  • +

Replies To: Error message when adding DVD (Oracle SQL)

#2 andrewsw   User is online

  • Entwickler
  • member icon

Reputation: 6605
  • View blog
  • Posts: 26,918
  • Joined: 12-December 12

Re: Error message when adding DVD (Oracle SQL)

Posted 29 May 2016 - 03:55 AM

How many columns are in your dvds table? Without listing the fields in your statement you need to insert a value for every field, and in the correct order.
Was This Post Helpful? 0
  • +
  • -

#3 asimkh   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 24-May 16

Re: Error message when adding DVD (Oracle SQL)

Posted 29 May 2016 - 04:01 AM

Thanks i just figured out i was missing a column which is a boolean value
Its working now
sql = "INSERT INTO dvds VALUES (null, '"+ sanitisedTitle +
                            "'," + year + ","+cbxFavorite.isSelected()+")";

Was This Post Helpful? 0
  • +
  • -

#4 asimkh   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 24-May 16

Re: Error message when adding DVD (Oracle SQL)

Posted 29 May 2016 - 04:31 AM

Now i am having trouble with editing gui to update the data

sql = "UPDATE dvds SET title = '"+sanitisedTitle ="', year =" + year + "," + "favourite ="+cbxFavorite.isSelected() +" WHERE id = "+dvd.getID();


The error i get is
unexpected type
expecting: variable
found: value
Was This Post Helpful? 0
  • +
  • -

#5 andrewsw   User is online

  • Entwickler
  • member icon

Reputation: 6605
  • View blog
  • Posts: 26,918
  • Joined: 12-December 12

Re: Error message when adding DVD (Oracle SQL)

Posted 29 May 2016 - 04:38 AM

Is that the line that causes the error, and which part of it?

If it is the id part then you could test by substituting a known value, then print out the value of dvd.getID().
Was This Post Helpful? 0
  • +
  • -

#6 Ryano121   User is offline

  • D.I.C Lover
  • member icon

Reputation: 1461
  • View blog
  • Posts: 3,289
  • Joined: 30-January 11

Re: Error message when adding DVD (Oracle SQL)

Posted 29 May 2016 - 04:39 AM

Concatenating strings to create SQL is a bad idea. Read up about SQL Injection and using Prepared Statements.
Was This Post Helpful? 2
  • +
  • -

#7 andrewsw   User is online

  • Entwickler
  • member icon

Reputation: 6605
  • View blog
  • Posts: 26,918
  • Joined: 12-December 12

Re: Error message when adding DVD (Oracle SQL)

Posted 29 May 2016 - 04:40 AM

If the id is a text value then it would also need to be surrounded with single quotes or, preferably, use parameterized queries Edit: aka prepared statements.
Was This Post Helpful? 0
  • +
  • -

#8 asimkh   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 24-May 16

Re: Error message when adding DVD (Oracle SQL)

Posted 29 May 2016 - 04:43 AM

Id is an int value...
Was This Post Helpful? 0
  • +
  • -

#9 andrewsw   User is online

  • Entwickler
  • member icon

Reputation: 6605
  • View blog
  • Posts: 26,918
  • Joined: 12-December 12

Re: Error message when adding DVD (Oracle SQL)

Posted 29 May 2016 - 04:45 AM

Okay, so what part of the statement is causing a problem? If you don't know then break it down, update one column at a time until you discover the problematic one.
Was This Post Helpful? 1
  • +
  • -

#10 asimkh   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 24-May 16

Re: Error message when adding DVD (Oracle SQL)

Posted 29 May 2016 - 04:55 AM

That works
Thanks!
Was This Post Helpful? 0
  • +
  • -

#11 andrewsw   User is online

  • Entwickler
  • member icon

Reputation: 6605
  • View blog
  • Posts: 26,918
  • Joined: 12-December 12

Re: Error message when adding DVD (Oracle SQL)

Posted 29 May 2016 - 04:57 AM

What was the problem, and solution?
Was This Post Helpful? 0
  • +
  • -

#12 asimkh   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 24-May 16

Re: Error message when adding DVD (Oracle SQL)

Posted 29 May 2016 - 06:03 AM

Just some syntax error in the update statement...

Then i had to make sure my checking methods were right and basically had to fiddle around with some of the code...
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1