Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    May 2007
    Posts
    11

    Unanswered: Syntax error in INSERT INTO statement

    afternoon folks.

    im creating an application in Delphi to access and update an access database

    what i am trying to do is have a doubleclick event on a grid (grid 1 lets call it) that initiates the code below. it then adds certain details to another grid (grid2?)

    it will then go on to save to the database when i actually code it in

    The code i have is as follows that is generating the error:

    (ive added some ... to try and replicate indenting

    var
    sql: string;
    AcadYear: string;
    begin
    AcadYear := '2008';
    sql := 'INSERT INTO StudentOptionChoices ( StudentID, ModuleCode, ...semester, Academic_Year ) ';
    sql := sql + ...DBGStudentsList.datasource.dataset.FieldByName( 'studentID').AsString;
    sql := sql + ...DBGAvailableModules.datasource.dataset.FieldByN ame('ModuleCode').AsString;
    sql := sql + ...DBGAvailableModules.datasource.dataset.FieldbyN ame('semester').AsString;
    sql := sql + quotedstr(AcadYear);

    ...DMStudentRecord.ADOCMOptionsChoices.CommandText := sql;
    ...DMStudentRecord.ADOCMOptionsChoices.Execute;

    ---------------------------
    so you understand my naming:

    DMStudentRecord is a datamodule.
    ADOCMOptionsChoices is a TADOCommand

    ( StudentID, ModuleCode, semester, Academic_Year ) are the fields in the table im inserting into
    ________________________

    THE ERROR I GET:

    Syntax error in INSERT INTO statement

    i cant for the life of me work out why its saying this.

    Anyway. hope this is enough info for you guys and any help will be much appreciated

    cheers

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    T-SQL Syntax:
    Code:
    INSERT
    INTO <tablename> (<field1>, <field2>, <field3> .... <fieldn>)
    VALUES (<value1>, <value2>, <value3> ... <valuen>)
    HTH
    George
    Home | Blog

  3. #3
    Join Date
    May 2007
    Posts
    11
    thanks for the response,

    so therefore creating the following (adding the red bit to my original code:

    sql := 'INSERT INTO StudentOptionChoices ( StudentID, ModuleCode, ...semester, Academic_Year ) ';
    sql := sql + ' VALUES (' ...DBGStudentsList.datasource.dataset.FieldByName( 'studentID').AsString;
    sql := sql + ...DBGAvailableModules.datasource.dataset.FieldByN ame('ModuleCode').AsString;
    sql := sql + ...DBGAvailableModules.datasource.dataset.FieldbyN ame('semester').AsString;
    sql := sql + quotedstr(AcadYear);

    ive tried but get numerous errors regarding semicolons and all sorts (Even though its not related to semicolons)

    or have i misunderstood this?

  4. #4
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    You're trying to insert more than one record at a time aren't you? Hence your use of datasets. You CANNOT do a bulk insert using Delphi like this. Either you have to do a mass import (using an import utility) of for each record do the insert statement. I suspect that latter is going to be the applicable choice.

  5. #5
    Join Date
    May 2007
    Posts
    11
    ive just discovered an issue also:

    the code i am trying to reach inthe SQL statement is on the lines of:

    ('xxx', 'xxx', 'xxx', 'xxx') - notice the ' and , punctuation

    My current code of:

    sql := 'INSERT INTO StudentOptionChoices ( StudentID, ModuleCode, semester, Academic_Year ) ' ;
    sql := sql + ' VALUES (''' + DBGStudentsList.datasource.dataset.FieldByName('st udentID').AsString + ', ';
    sql := sql + DBGAvailableModules.datasource.dataset.FieldByName ('ModuleCode').AsString + ', ';
    sql := sql + DBGAvailableModules.datasource.dataset.FieldbyName ('semester').AsString + ', ';
    sql := sql + quotedstr(AcadYear) + ')';

    gives the layout of:

    ( 'xxx, xxxx, xxx, 'xxx') - notice the punctuation here.

    delphi appears to not like such things as ''' (adding a ' to the line of code)

    any suggestions as to another way to implement this?

    i will also try adding 1 record at a time though im not sure how its done...im thinking multiple INSERT INTO perhaps?

  6. #6
    Join Date
    Mar 2004
    Posts
    480
    Quote Originally Posted by StarStriker
    afternoon folks.

    im creating an application in Delphi to access and update an access database
    Then why post in a mysql forum?

  7. #7
    Join Date
    May 2007
    Posts
    11
    Quote Originally Posted by guelphdad
    Then why post in a mysql forum?
    read my post fully mate. said what the ap is and my problem is with the sql within the ap

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by StarStriker
    read my post fully mate. said what the ap is and my problem is with the sql within the ap
    read the name of this forum fully, mate

    this is MySQL

    totally different forum from the one you should've posted in
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Just a quick attempt:
    Code:
    sql := ""
    sql := sql + "INSERT INTO StudentOptionChoices ( StudentID, ModuleCode, semester, Academic_Year ) " ;
    sql := sql + " VALUES ('" ;
    sql := sql + DBGStudentsList.datasource.dataset.FieldByName('studentID').AsString ;
    sql := sql + "','" ;
    sql := sql + DBGAvailableModules.datasource.dataset.FieldByName('ModuleCode').AsString ;
    sql := sql + "','" ;
    sql := sql + DBGAvailableModules.datasource.dataset.FieldbyName('semester').AsString ;
    sql := sql + "','" ;
    sql := sql + quotedstr(AcadYear) ;
    sql := sql + "')" ;
    Note the use of single quotes and double quote wrapping.
    George
    Home | Blog

  10. #10
    Join Date
    May 2007
    Posts
    11
    Quote Originally Posted by r937
    read the name of this forum fully, mate

    this is MySQL

    totally different forum from the one you should've posted in
    thought i was in the right place...apologies if i inconvenienced you

  11. #11
    Join Date
    May 2007
    Posts
    11
    thanks george

    unfortunatly it appears delphi does not like the use of double quotes and deems in an illegal character?

  12. #12
    Join Date
    May 2007
    Posts
    11
    i hav now come up with the following code which generates only an error saying it needs a semicolon at the end, even though there is one

    AcadYear := '2008';
    sql := 'INSERT INTO StudentOptionChoices ( StudentID, ModuleCode, semester, Academic_Year ) ' ;
    sql := sql + ' VALUES (' + DBGStudentsList.datasource.dataset.FieldByName('st udentID').AsString;
    sql := sql + ', ' + DBGAvailableModules.datasource.dataset.FieldByName ('ModuleCode').AsString;
    sql := sql + ', ' + DBGAvailableModules.datasource.dataset.FieldbyName ('semester').AsString;
    sql := sql + ', ' + quotedstr(AcadYear) + ')' ;
    sql := sql + ' WHERE (' + DBGStudentsList.datasource.dataset.FieldbyName('st udentID').asstring + ');' ;
    memo1.text := sql;
    DMStudentRecord.ADOCMOptionsChoices.CommandText := sql;
    DMStudentRecord.ADOCMOptionsChoices.Execute;
    RefreshData;

    memo1 is just a memo box so i can check what the code is coming out as

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    as it seems to be a Delphi problem rather than an Access problem, Ive moved the thread to the Delphi thread
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by StarStriker
    memo1 is just a memo box so i can check what the code is coming out as
    What does it come out as?
    George
    Home | Blog

  15. #15
    Join Date
    May 2007
    Posts
    11
    the memo box text reads as follows:

    INSERT INTO StudentOptionChoices ( StudentID, ModuleCode, semester, Academic_Year ) VALUES (qrsuyvxw, SO005, 2, '2008') WHERE qrsuyvxw;

    the error states there is a missing semicolon which as far as i can tell, there isnt.

    qrsuyvxw is a student ID selected from a grid

Posting Permissions

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