Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    12

    Unanswered: HELP! insert record

    I'm doing a program by using Java to insert record into SQL file. it's a registration program. but i'm not familiar with SQL, i got an error msg "An attempt was made to insert a null value into a column that does not accept nulls. " and i cant solve it. my SQL file contains

    connect 'jdbc:rmi://localhost:1099/jdbc:cloudscape:customers;create=true'
    ;

    drop table custProducts
    ;
    drop table products
    ;
    drop table custinfor
    ;

    create table custinfor (
    userName varchar (20) NOT NULL,
    firstName varchar (20) NOT NULL,
    lastName varchar (30) NOT NULL,
    email varchar (30) NOT NULL,
    password varchar (20) NOT NULL,
    verify varchar (20) NOT NULL,
    constraint pk_custinfor primary key (userName)
    )
    ;
    create table products (
    productID int DEFAULT AUTOINCREMENT,
    productName varchar (100) NOT NULL,
    description varchar (100) NOT NULL,
    price real NOT NULL,
    imageFile varchar (50) NOT NULL,
    constraint pk_products primary key (productID)
    )
    ;
    create table custProducts (
    userName varchar (20) NOT NULL,
    productID int NOT NULL,
    constraint fk_custProducts_1 foreign key (userName)
    references custinfor (userName),
    constraint fk_custProducts_2 foreign key (productID)
    references products (productID)
    )
    ;
    insert into custinfor (userName,firstName,lastName,email,password,verify
    ) values ('jSmith','John','Smith','sjohn@hotmail.com','jsmi th123','jsmith123')
    ;
    insert into custinfor (userName,firstName,lastName,email,password,verify
    ) values ('jess88','Jess','Ling','jess88@yahoo.com','jess45 6','jess456')
    ;

    insert into products (productName,description,price,imageFile) values ('Desktop-1','Intel Pentium 4 Processor 1.8Gb,256MB RAM',2998.00,'pentium4 processor.jpg')
    ;
    insert into products (productName,description,price,imageFile) values ('Desktop-2','Intel Celeron Processor,256MB RAM',2260.00,'pentium4 processor.jpg')
    ;

    insert into custProducts (userName,productID) values ('jSmith',1)
    ;
    insert into custProducts (userName,productID) values ('jess88',2)
    ;

    can someone tell me what's the mistake that i made?

  2. #2
    Join Date
    Oct 2003
    Posts
    7

    Re: HELP! insert record

    Hi as the error describe
    msg "An attempt was made to insert a null value into a column that does not accept nulls. " and i cant solve it. my SQL file contains

    You are trying to ignore/skip all the fields which requires input while doing an insertion. What u can do is try to create a table with fields the accept null i.e

    create table custinfor (
    userName varchar (20),
    firstName varchar (20) ,
    lastName varchar (30) ,
    email varchar (30) NOT NULL,
    password varchar (20) NOT NULL,
    verify varchar (20) ,
    .....

    or in the insert statement, insert all the input for all the respective fields.

    hope this help

  3. #3
    Join Date
    Sep 2003
    Location
    POLAND/WARSAW/ZIELONKA
    Posts
    5
    Are you sure that this is all you trying to put into database ? As far as I'm concerned it looks GOOD. There's nothing wrong with the sets of statements that you showed. Hmmm, I'm confused ... try saying more.

  4. #4
    Join Date
    Sep 2003
    Location
    POLAND/WARSAW/ZIELONKA
    Posts
    5
    to prove it i've coded your statements into my mysql database and ... everything works fine:

    mysql> source c:/dbf.sql
    Query OK, 0 rows affected (0.00 sec)

    Query OK, 0 rows affected (0.00 sec)

    Query OK, 0 rows affected (0.00 sec)

    Query OK, 0 rows affected (0.00 sec)

    Query OK, 0 rows affected (0.00 sec)

    Query OK, 0 rows affected (0.00 sec)

    Query OK, 1 row affected (0.00 sec)

    Query OK, 1 row affected (0.00 sec)

    Query OK, 1 row affected (0.00 sec)

    Query OK, 1 row affected (0.00 sec)

    Query OK, 1 row affected (0.00 sec)

    Query OK, 1 row affected (0.00 sec)

    mysql> select * from custinfor; select * from custproducts;select * from product
    s;
    +----------+-----------+----------+-------------------+-----------+-----------+
    | userName | firstName | lastName | email | password | verify |
    +----------+-----------+----------+-------------------+-----------+-----------+
    | jSmith | John | Smith | sjohn@hotmail.com | jsmith123 | jsmith123 |
    | jess88 | Jess | Ling | jess88@yahoo.com | jess456 | jess456 |
    +----------+-----------+----------+-------------------+-----------+-----------+
    2 rows in set (0.00 sec)

    +----------+-----------+
    | userName | productID |
    +----------+-----------+
    | jSmith | 1 |
    | jess88 | 2 |
    +----------+-----------+
    2 rows in set (0.00 sec)

    +-----------+-------------+-------------------------------------------+-------+-
    -----------------------+
    | productID | productName | description | price |
    imageFile |
    +-----------+-------------+-------------------------------------------+-------+-
    -----------------------+
    | 1 | Desktop-1 | Intel Pentium 4 Processor 1.8Gb,256MB RAM | 2998 |
    pentium4 processor.jpg |
    | 2 | Desktop-2 | Intel Celeron Processor,256MB RAM | 2260 |
    pentium4 processor.jpg |
    +-----------+-------------+-------------------------------------------+-------+-
    -----------------------+
    2 rows in set (0.00 sec)

  5. #5
    Join Date
    Sep 2003
    Posts
    12
    thanks mgrula and rexcel. i cancelled NOT NULL and it works. mgrula, i mean i want to insert record into db, not displaying it.
    thanks.

Posting Permissions

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