"Syntax error in ALTER TABLE statement.", again...

A

alexttp

Yeah, yeah, once again about this error...
I saw numerous threads about this error, suggesting there are
limitations in Access 2000 GUI and that I need to use OLEDB rather
than ODBC in order to perform "ALTER TABLE" statements... But it stil
doesn't work.

The problem (uhm... well, this is the reason why I still have this
problem appearing) is that I try to do that from a C# program, and I
couldn't find any sample showing how to do that SUCCESSFULLY...

Here's a snippet from the code:


using System.Data.OleDb;
....

public DataSet ExecSQL(string i_strSQL)
{
DataSet oDs = new DataSet();
try
{
OleDbCommand oCommand = new OleDbCommand(i_strSQL, m_oConnection);
OleDbDataAdapter oAdapter = new OleDbDataAdapter(oCommand);
if(m_oConnection.State != ConnectionState.Open)
{
m_oConnection.Open();
}
oAdapter.Fill(oDs);
}
catch(Exception ex)
{
...
}
return oDs;
}


where m_oConnection is defeined as follows:

protected OleDbConnection m_oConnection = null;

m_oConnection = new OleDbConnection(m_strConnectionString);
m_oADOConnection = new ConnectionClass();
m_strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;User
ID=Admin;Data Source=D:\db.mdb"
m_oADOConnection.Open(m_strConnectionString, string.Empty,
string.Empty, 0);



So I DO use OLEDB, but still keep getting the error when trying to
execute a statement that uses the "DEFAULT" keyword:

"ALTER TABLE Catalog
ADD COLUMN [NewSimpleIntField] int NOT NULL DEFAULT 110 WITH VALUES ;"

Running out of ideas, I even tried using the ADODB library:

using ADODB;

m_oADOConnection = new ConnectionClass();
m_oADOConnection.Open(m_strConnectionString, string.Empty,
string.Empty, 0);

object o = null;
m_oADOConnection.Execute(i_strSQL, out o, 1);

.... but this resulted in the same error, eventually. :-(

Any help would be GREATLY appreciated, since I'm really frustrated...

Alex
 
M

MGFoster

alexttp said:
Yeah, yeah, once again about this error...
I saw numerous threads about this error, suggesting there are
limitations in Access 2000 GUI and that I need to use OLEDB rather
than ODBC in order to perform "ALTER TABLE" statements... But it stil
doesn't work.
Click to expand...
So I DO use OLEDB, but still keep getting the error when trying to
execute a statement that uses the "DEFAULT" keyword:

"ALTER TABLE Catalog
ADD COLUMN [NewSimpleIntField] int NOT NULL DEFAULT 110 WITH VALUES ;"
Click to expand...
< SNIP >

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

No matter the language used to send DDL commands to the db, the DDL
syntax has to fit the db's DDL syntax. In Access (JET) dbs some
versions will recognize the DEFAULT <value> and others will not.

If you have access to an Access interface that can open the db you want
to alter, in Access XP (JET 4.0) you can change the ANSI recognition to
ANSI-92. On the main menu look at: Tools > Options > Tables/Queries
tab: SQL Server Compatible Syntax (ANSI-92) > check box: This Database
should be checked. ANSI-92 will allow recognition of the DEFAULT
<value>.

DBs that don't have the ANSI-92 option turned on will not accept the
"DEFAULT" keyword. My tests show even w/ the ANSI-92 option turned on,
JET will not accept the "WITH VALUES" phrase. You will have to add the
column & then load it w/ an UPDATE command.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQHriQoechKqOuFEgEQI9NQCghP/zUN1jKoJEXo7uLZ+DnKqHAWUAoJcR
9k4fVzMfzJ6Ikf0ksLlUYwOh
=4IXO
-----END PGP SIGNATURE-----
 
G

Gary Walter

Just for the record (and meaning no offense)....

In Access 2000 (with an Access db), the following
DDL stmts work:

currentproject().Connection.Execute "ALTER TABLE tbl1 ADD COLUMN f3 int NOT NULL;",
dbFailOnError
currentproject().Connection.Execute "ALTER TABLE tbl1 ALTER COLUMN f3 SET DEFAULT 5",
dbFailOnError

You will get an error if you try to "SET DEFAULT"
in "ADD COLUMN" stmt.

I think it was Michel who pointed out that a ";"
at end of second stmt will cause it to error out
(another workaround was to wrap default value
in single quotes).

I , too, am not familiar with "WITH VALUES"
in Access DDL.

Again, not meaning any offense...

Gary Walter

MGFoster said:
alexttp said:
Yeah, yeah, once again about this error...
I saw numerous threads about this error, suggesting there are
limitations in Access 2000 GUI and that I need to use OLEDB rather
than ODBC in order to perform "ALTER TABLE" statements... But it stil
doesn't work.
Click to expand...
So I DO use OLEDB, but still keep getting the error when trying to
execute a statement that uses the "DEFAULT" keyword:

"ALTER TABLE Catalog
ADD COLUMN [NewSimpleIntField] int NOT NULL DEFAULT 110 WITH VALUES ;"
Click to expand...
< SNIP >

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

No matter the language used to send DDL commands to the db, the DDL
syntax has to fit the db's DDL syntax. In Access (JET) dbs some
versions will recognize the DEFAULT <value> and others will not.

If you have access to an Access interface that can open the db you want
to alter, in Access XP (JET 4.0) you can change the ANSI recognition to
ANSI-92. On the main menu look at: Tools > Options > Tables/Queries
tab: SQL Server Compatible Syntax (ANSI-92) > check box: This Database
should be checked. ANSI-92 will allow recognition of the DEFAULT
<value>.

DBs that don't have the ANSI-92 option turned on will not accept the
"DEFAULT" keyword. My tests show even w/ the ANSI-92 option turned on,
JET will not accept the "WITH VALUES" phrase. You will have to add the
column & then load it w/ an UPDATE command.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQHriQoechKqOuFEgEQI9NQCghP/zUN1jKoJEXo7uLZ+DnKqHAWUAoJcR
9k4fVzMfzJ6Ikf0ksLlUYwOh
=4IXO
-----END PGP SIGNATURE-----
Click to expand...
 
A

alexttp

MGFoster said:
If you have access to an Access interface that can open the db you want
to alter, in Access XP (JET 4.0) you can change the ANSI recognition to
ANSI-92. On the main menu look at: Tools > Options > Tables/Queries
tab: SQL Server Compatible Syntax (ANSI-92) > check box: This Database
should be checked. ANSI-92 will allow recognition of the DEFAULT
<value>.

DBs that don't have the ANSI-92 option turned on will not accept the
"DEFAULT" keyword. My tests show even w/ the ANSI-92 option turned on,
JET will not accept the "WITH VALUES" phrase. You will have to add the
column & then load it w/ an UPDATE command.
Click to expand...

Thanks for that answer.
Well, sure I understand the language doesn't matter - it starts to
matter only when trying to look for specific code examples... There
are lots of VB6 sample code snippets (which cannot be fitted to C#,
alas), but not a single .NET one... :-(

I'll see if I can get Access XP (right now I use 2000 - and there is
no such checkbox...).

Meanwhile - is there any possibility to do the same programmatically
(say, as one of the connection's properties)?
After all, as far as I understand, the setting is stored in some way
in the DB itself, right? Otherwise the same query would not execute on
the clone of this DB on another computer, on which this checkbox was
not set... Or is this the case, actually?
Or, maybe there's something in the Registry to be played with, so that
the setting may be applied per computer?

Thanks,

Alex
 
A

alexttp

Unbelievable!
After all, the problem seems to be in the "[]" signs around the table
name!
I mean, the statement
"ALTER TABLE [Catalog] ADD COLUMN NewSimpleIntField int NOT NULL
DEFAULT 110"
works perfectly, while
"ALTER TABLE Catalog ADD COLUMN NewSimpleIntField int NOT NULL DEFAULT
110" fails with syntax error!...

Another issue, indeed, is that there cannot be ";" at the end of the
statement when using DEFAULT. Well, who needs it... ;-)

And, as you both postulated, "WITH VALUES" is indeed NOT supported.

Anyway, thanks for you replies, they gave me some clues.

Alex

PS All my findings are on the Access2000 MDB via JET (in the Access
GUI itself, both versions - with & without the "[]" - are valid; "WITH
VALUES" is invalid there too).
 
G

Gary Walter

I give up.....:cool:

Yesterday the following erred out,
today it works.

currentproject().Connection.Execute "ALTER TABLE tbl1 ADD COLUMN f3 int NOT NULL
DEFAULT 5", dbfailonerror

It still errors out in Query Designer GUI,
whether I wrap tbl1 in brackets or not
(as it also does using CurrentDb.Execute)

I don't know...if you had success...great.

But it sure seems too flaky to depend on.

I have never had trouble splitting into 2 SQL's.

Maybe I have just been lucky.

Gary Walter
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

"Unrecognized database format" 2007 1
Querying Excel document with OleDb 0
Excel 2003 cell is not allowing to enter more than 255 charecters in a cell. 3
Multiple series using OWC10, C#, ASP.NET 0
Info: ALTER TABLE DEFAULT error 1
Retrieving Data from Excel Pivot tables into Datatable 0

Top