Results 1 to 7 of 7
  1. #1
    jmorris is offline Member
    Join Date
    Nov 2008
    Posts
    12
    Rep Power
    0

    Default Help with sql insert

    I am trying to insert data into my database, but I get the "data type mismatch in criteria expression" error. Everything in the database is stored as text EXCEPT the autonumber field id. It is the first field in the database.

    This is my insert:
    Java Code:
    String query = "INSERT INTO Customer VALUES ('','" + fname + "','"+ lname + "','" + address + "','"+ city + "','"+ state + "','"+ zipcode + "','"+ phone + "')";
    I tried to insert an empty string into the id field and that's what gives me the error.
    I just need to know how to deal with the autonumber field...somebody pls help...and thx :)

  2. #2
    serjant's Avatar
    serjant is offline Senior Member
    Join Date
    Jun 2008
    Location
    Ukraine,Zaporozhye
    Posts
    487
    Rep Power
    6

    Default

    Most elegant to insert into sql tables is in this way:

    Java Code:
    INSERT INTO table_name (column1, column2, column3,...)
    VALUES (value1, value2, value3,...)

  3. #3
    jmorris is offline Member
    Join Date
    Nov 2008
    Posts
    12
    Rep Power
    0

    Default

    Ok so now my insert is as follows:

    Java Code:
    INSERT INTO Customer (fName, lName, address, city, state, zipcode, phone)
    VALUES ('" + fname + "','"+ lname + "','" + address + "','"+ city + "','"+ state + "','"+ zipcode + "','"+ phone + "')


    but I still need to know how to deal with the autonumber field...

  4. #4
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    8

    Default

    Are your table columns actually named "fName" and so? I doubt it.

    Also use a PreparedStatement, cobbling together an SQL Statement like this is error-prone and just begging for problems (both of an innocent nature and as SQL injection attacks).

    To handle an automatically generated value field, just simply leave that field off the column and value list (i.e. don't include it).

  5. #5
    gustio is offline Member
    Join Date
    Jun 2008
    Posts
    14
    Rep Power
    0

    Default

    Autonumber field is generated by DBMS.
    If you need only for that field, try with less field:
    INSERT INTO Customer (fName)
    VALUES ('" + fname + "')
    It will insert a row on your db, only id and fName have value, others null

  6. #6
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    8

    Default

    Quote Originally Posted by gustio View Post
    Autonumber field is generated by DBMS.
    If you need only for that field, try with less field:
    INSERT INTO Customer (fName)
    VALUES ('" + fname + "')
    Like I said, cobbling together an SQL String this way is not the way anyone should want to do this. It is error prone (and outright dangerous).

    It will insert a row on your db, only id and fName have value, others null
    Only if those columns are defined to allow null values.

  7. #7
    jmorris is offline Member
    Join Date
    Nov 2008
    Posts
    12
    Rep Power
    0

    Default

    ok I think I got it now. Thanks for the help. I redid my query using prepared statements.

Similar Threads

  1. How can i insert a char into a string
    By Jamie in forum New To Java
    Replies: 8
    Last Post: 02-17-2011, 08:59 PM
  2. Cannot insert duplicate key row in object
    By losintikfos in forum New To Java
    Replies: 3
    Last Post: 05-07-2009, 09:43 AM
  3. SQL Insert Help!!!!
    By shaungoater in forum New To Java
    Replies: 1
    Last Post: 06-14-2008, 03:14 AM
  4. How to insert graph in java
    By valery in forum Advanced Java
    Replies: 1
    Last Post: 08-06-2007, 08:38 PM
  5. How do insert a Graphic
    By carl in forum New To Java
    Replies: 1
    Last Post: 08-01-2007, 05:30 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •