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

T-SQL Syntax:
HTH

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?

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.

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?

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

read the name of this forum fully, mate

this is MySQL

totally different forum from the one you should've posted in

Just a quick attempt:
Note the use of single quotes and double quote wrapping.

thought i was in the right place...apologies if i inconvenienced you

thanks george

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

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

as it seems to be a Delphi problem rather than an Access problem, Ive moved the thread to the Delphi thread

What does it come out as?

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