Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2013
    Posts
    5

    Question Unanswered: Can Anyone help me with insert command to add data into table

    This is the query for table creation

    CREATE TABLE RESERVATION
    ( ResNum AUTOINCREMENT(100, 1),
    HotelNo CHAR(4) NOT NULL,
    RoomNo CHAR(4) NOT NULL,
    GuestNo CHAR(4) NOT NULL,
    ArrivalDate DATE,
    DepartureDate DATE,
    NumPersons CHAR(2),
    CONSTRAINT RESERVATIONPKEY PRIMARY KEY (ResNum),
    CONSTRAINT RESERVATIONFKEY1 FOREIGN KEY (HotelNo, RoomNo) REFERENCES ROOM,
    CONSTRAINT RESERVATIONFKEY2 FOREIGN KEY (GuestNo) REFERENCES GUEST (GuestNo))



    This is the insert statement i wrote but i'm keep getting error

    INSERT INTO RESERVATION (HotelNo, RoomNo, GuestNo, ArrivalDate, DepartureDate, NumPersons)
    VALUES ('1111', '1002', 'GG10', #4/11/2013#, #4/13/2013#, '2');



    This is the error i'm getting

    Microsoft Access set 0 field(S) to Null due to a type conversion failure, and it didn't add 1 record(s) to the table due to key violation, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violation.


    Just Make sure you write in access, same as i did in insert statement above.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    date literals need to be delimted with "# or '#

    however I'd suggest you use appropriate datatypes for your data. use numeric datatypes for numbers, text for alhpanumeric date for date/time values. if I see a column suffixed with 'No' then I expect that column to be numeric. numpersons should be numeric. its arguable that the the hotel, roomno could be text, but that depends on your design standards
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Microsoft Access set 0 field(S) to Null due to a type conversion failure, and it didn't add 1 record(s) to the table due to key violation, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violation.

    The problem isn't in the code that you posted, it ran as it should. Review your keys.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

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