CODEWITHSUNDEEP
javasqlitejdbctypesmismatch
user7769044
user7769044

Reputation:

[sqlite MISMATCH]: data type mismatch

i'm getting an sqlite mismatch exception while inserting data into database.i m creating an app in netbeans but when i try to update database by selecting textfields by clicking button its showing this exception.

Here's the screenshot

private void calculationDBActionPerformed(java.awt.event.ActionEvent evt) {                                              

      String sql2 = "Insert into Calculation_Info (prodID,item_name,net_amount,tax_rate,Amt_Of_Product,Grand_Total) values(?,?,?,?,?,?)";
      try{

        pst = conn.prepareStatement(sql2);

        pst.setString(1, prodID.getText());
        pst.setString(2, txtPRoduct.getText());
        pst.setString(3, txtTotal.getText());
        pst.setString(4, txtTaxTotal.getText());
        pst.setString(5, txtAmountTotal.getText());
        pst.setString(6, txtGrandTotal.getText());

        pst.execute();
        JOptionPane.showMessageDialog(null, "Gotcha Bro!");
        pst.close();
        rs.close();
      }catch(Exception exc){
          JOptionPane.showMessageDialog(null, exc);
      }
    }

Upvotes: 1

Views: 18632

Answers (2)

Andrew Hyun
Andrew Hyun

Reputation: 61

@YCF_L explains the problem correctly. I believe the issue that you have @Muhammad Quanit is that you are not understanding what your query is asking for and what you're trying to pass into the query. To break it down for you, your query:

"Insert into Calculation_Info (prodID,item_name,net_amount,tax_rate,Amt_Of_Product,Grand_Total) values(?,?,?,?,?,?)"

Takes 6 values that you have to provide (Please NOTE the type of each they take in):

  1. prodID (int)
  2. item_name(String)
  3. net_amount(double)
  4. tax_rate(BigDecimal)
  5. Amt_Of_Product(int)
  6. Grand_Total(BigDecimal)

In your program, you have 6 text boxes that will obtain these values to put in HOWEVER when you call the method: getText() it returns a String type. So when we try to put String prodID = "3" into our SQL query, you would receive the error you originally received: datatype mismatch. Now your current problem is: java.lang.numberFormatException : for input String. This means that you are trying to convert a String value into a number; however it is FAILING. So what could be possible reasons for this? Well...

/* Lets say I have a string with value 4.7*/
    String s = "4.7";
/* Lets try to parse this using parseInt method*/
    Integer.parseInt(s); //NumberFormatException -- because 4.7 cannot be converted into an int
/* How about if my string was a word/phrase?*/
    String s = "foo";
/* Attempt to parse...*/
    Integer.parseInt(s); //NumberFormatException -- because "foo" cannot be converted into integer
    Double.parseDouble(s); //NumberFormatException -- because "foo" cannot be converted into a double

So as you can see, you have to be very careful and keep track of what you are converting and passing into your query (and vice versa).

As @YCF_L mentioned, perhaps your String contains white spaces at the start or end. There is a method called trim() in the String class that will help you with that.

/* Let's call the getText() method to obtain our string */
    String s = item_name.getText(); // s = "    Foo Bar " 
/* NOTE: the white spaces (' ') BEFORE and AFTER the String we want*/
    System.out.println(s.trim()); // Prints out "Foo Bar"

So the String OBJECT has a method called trim() which gets rid of TRAILING and LEADING white spaces, but NOT white spaces in between. So instead of getting " Foo Bar ", we will get "Foo Bar".

I upvoted @YCF_L 's answer -- he deserves the credit. I just wanted you to understand this concept since you were still confused after his explanation followed by receiving the NumberFormatException.

Editted to include the trim() method that was mentioned in the comments.

Upvotes: 1

Youcef LAIDANI
Youcef LAIDANI

Reputation: 59978

You can't set String for an Int of real type, for that you get this type of error, so you have to convert your inputs to the right type, for example :

prodID.getText() should be an int and not a String so you have to use it like so:

pst.setInt(1, Integer.parseInt(prodID.getText()));
//--^^^^^----------^^convert your String to int and change setString to setInt

The same thing for the rest.

Upvotes: 2

Related Questions