syntax error in INSERT INTO statement

Hi all,

I am trying to create, and write data into microsoft access file using
CDaoDatabase and CDaoRecordset in Microsoft visual studio 8.0. I am new
to working with databases.

It is actually a example code from a website. The code is,

CDaoDatabase database;

CDaoRecordset recordset(&database);
CString lpszFile = "C:\\Database.mdb";
database.Create(lpszFile);
//database.Open(lpszFile);
_ASSERTE( _CrtCheckMemory( ) );
CString SqlCmd ="CREATE TABLE MINECOORDINATES (X VARCHAR(10)"
", Y VARCHAR(10)"
", Z VARCHAR(10))" ;
database.Execute(SqlCmd);
recordset.Open(AFX_DAO_USE_DEFAULT_TYPE,
"SELECT * FROM MineCoordinates", 0);
database.Execute("INSERT INTO MineCoordinates(X);");
database.Execute("INSERT INTO MineCoordinates(Y);");
database.Execute("INSERT INTO MineCoordinates(Z);");

recordset.AddNew();
recordset.SetFieldValue("X","10");
recordset.SetFieldValue("Y","13");
recordset.SetFieldValue("Z","13");
recordset.Update();
recordset.MoveNext();
recordset.AddNew();
recordset.SetFieldValue("X","11");
recordset.SetFieldValue("Y","1");
recordset.SetFieldValue("Z","3");
recordset.Update(); */
COleVariant olevar;
while(!recordset.IsEOF());
{
olevar.ChangeType(VT_BSTR, NULL);
recordset.GetFieldValue("X",olevar);
CString strData = (LPCSTR)olevar.pbstrVal;
// Code for inserting data into a listbox, for example
recordset.MoveNext();
}

At the run time i get an error stating, syntax error in INSERT INTO
statement. Could anyone please let me know, what is the error here?

Thank you,

Priya

Mark
Tue Oct 24 14:59:55 CDT 2006

hariharan.priya@gmail.com> wrote:
> database.Execute("INSERT INTO MineCoordinates(X);");
> database.Execute("INSERT INTO MineCoordinates(Y);");
> database.Execute("INSERT INTO MineCoordinates(Z);");

Lack of VALUES(...)?

--
- Mark Randall
http://www.temporal-solutions.co.uk
http://www.awportals.com

serenseven
Wed Oct 25 08:54:02 CDT 2006

"hariharan.priya@gmail.com" wrote:

> CDaoDatabase database;
>
> CDaoRecordset recordset(&database);
> CString lpszFile = "C:\\Database.mdb";
> database.Create(lpszFile);
> //database.Open(lpszFile);
> _ASSERTE( _CrtCheckMemory( ) );
> CString SqlCmd ="CREATE TABLE MINECOORDINATES (X VARCHAR(10)"
> ", Y VARCHAR(10)"
> ", Z VARCHAR(10))" ;
> database.Execute(SqlCmd);
> recordset.Open(AFX_DAO_USE_DEFAULT_TYPE,
> "SELECT * FROM MineCoordinates", 0);
> database.Execute("INSERT INTO MineCoordinates(X);");
> database.Execute("INSERT INTO MineCoordinates(Y);");
> database.Execute("INSERT INTO MineCoordinates(Z);");

I don't think you need these 3 Execute statements. See below.

> recordset.AddNew();
> recordset.SetFieldValue("X","10");
> recordset.SetFieldValue("Y","13");
> recordset.SetFieldValue("Z","13");
> recordset.Update();
> recordset.MoveNext();
> recordset.AddNew();
> recordset.SetFieldValue("X","11");
> recordset.SetFieldValue("Y","1");
> recordset.SetFieldValue("Z","3");
> recordset.Update(); */

I am not terribly familar with DAO database access, but I think the
AddNew/SetFieldValue/Update set of methods takes care of the SQL INSERT
operation for you.

But while we're here, some other pointers:

If you are going to store integer data in the X, Y, and Z columns of your
MineCoordinates table, don't declare the columns as type varchar(10). That's
equivalent to string data. Declare the columns as integers, like so:

CREATE TABLE MineCoordinates (X int, Y int, Z int)

Also, for reference, if you want to insert the values X = 11, Y = 1 and Z =
3 into a new row in the database directly with an insert statement, the
correct way of doing so is:

INSERT INTO MineCoordinates (X, Y, Z) VALUES (11, 1, 3)

Hope this helps,
Sean

Tim
Wed Oct 25 23:56:03 CDT 2006

hariharan.priya@gmail.com wrote:
>
>I am trying to create, and write data into microsoft access file using
>CDaoDatabase and CDaoRecordset in Microsoft visual studio 8.0. I am new
>to working with databases.
>
>It is actually a example code from a website. The code is,

Sean's answer is correct; AddNew/Update are a programmatic way of
implementing the INSERT INTO statement. You use one OR the other.

Can you tell me which web site you got this from? I'd like to see what it
actually said.

>CString SqlCmd ="CREATE TABLE MINECOORDINATES (X VARCHAR(10)"
> ", Y VARCHAR(10)"
> ", Z VARCHAR(10))" ;
>database.Execute(SqlCmd);
>recordset.Open(AFX_DAO_USE_DEFAULT_TYPE,
> "SELECT * FROM MineCoordinates", 0);
>database.Execute("INSERT INTO MineCoordinates(X);");
>database.Execute("INSERT INTO MineCoordinates(Y);");
>database.Execute("INSERT INTO MineCoordinates(Z);");
>
>recordset.AddNew();
>recordset.SetFieldValue("X","10");
>recordset.SetFieldValue("Y","13");
>recordset.SetFieldValue("Z","13");
>recordset.Update();
>recordset.MoveNext();

For future reference, those last six lines are the equivalent of this:
database.Execute("INSERT INTO MineCoordinates VALUES ('10','13','13')");
--
Tim Roberts, timr@probo.com
Providenza & Boekelheide, Inc.