c# and mdb files

[Eric Kiernan]

i'm using mdb files for a local database. i have a database called
BudgetSavings, and a table called BudgetSpending. it blows up on
executing an SQL command. below is the code:

string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\C#
u008\\BudgetSavings\\BudgetSavings\\BudgetSavings.mdb";

OleDbConnection objConnection = new OleDbConnection(conStr);
objConnection.ConnectionString = conStr;

//objConnection.Close();
objConnection.Open();

string strSQL = "INSERT INTO BudgetSpending (Year) VALUES
('1964')"; // year is a 4 character text field.

OleDbCommand objCommand = new OleDbCommand();
objCommand.CommandText = strSQL;
objCommand.CommandType = CommandType.Text;
objCommand.Connection = objConnection;
int x = objCommand.ExecuteNonQuery(); / it blows up here
with error:Syntax error in 'INSERT INTO statement"

I'm scratching my head over this. also, if someone knows of a good sql
group, let me now.

 

[Julia M]

             string strSQL = "INSERT INTO BudgetSpending (Year) VALUES
('1964')";   // year is a 4 character text field.

Try and enclose your table and field names in special quotes

e.g.

INSERT INTO `BudgetSpending` (`Year`) VALUES ('1964')

iirc [] instead of `` works, too

 

[Jeff Johnson]

string strSQL = "INSERT INTO BudgetSpending (Year) VALUES
('1964')"; // year is a 4 character text field.

Year is the name of a function. Escape it with brackets:

string strSQL = "INSERT INTO BudgetSpending ([Year]) VALUES ('1964')";

And come out of this with a lesson to avoid using reserved words as column
names.

 

[Eric Kiernan]

Well said!

I put brackets [] around the Years column. No change. i changed the
name of the field to Years, no change. I even put " ' " marks around
the field name. I still gives me a syntax error in INSERT INTO statement.

 

[Jeff Johnson]

Well said!

I put brackets [] around the Years column. No change. i changed the name
of the field to Years, no change. I even put " ' " marks around the
field name. I still gives me a syntax error in INSERT INTO statement.

Set a breakpoint before executing the query and in the Immediate window type

? objCommand.CommandText

Copy and paste the results in your next reply.

For reference, putting apostrophes around the column name is definitely not
the way to go. Access would think you were specifying a constant string, and
that's not allowed in the field list of an INSERT statement. I've never
heard of using backticks ( ` ) around names like Julia suggested, at least
not in Access, and I've worked with Access for a long time.

 

[vanderghast]

Can you do it from Access itself? I mean, in the Immediate (Debug) Window:

CurrentProject.Connection.Execute "INSERT INTO BudgetSpending([Year]) VALUES
('1964')"


it works fine here. Possible problems are that a required field (not
nullable and no default value) is not supplied, an index does not allow
duplicated value, a RDI rule is not satisfied, ...

The [ ] around Year are definitively required.


Vanderghast, Access MVP

Well said!

I put brackets [] around the Years column. No change. i changed the name
of the field to Years, no change. I even put " ' " marks around the
field name. I still gives me a syntax error in INSERT INTO statement.

 

[Eric Kiernan]

Can you do it from Access itself? I mean, in the Immediate (Debug) Window:

CurrentProject.Connection.Execute "INSERT INTO BudgetSpending([Year])
VALUES ('1964')"


it works fine here. Possible problems are that a required field (not
nullable and no default value) is not supplied, an index does not allow
duplicated value, a RDI rule is not satisfied, ...

The [ ] around Year are definitively required.


Vanderghast, Access MVP

And come out of this with a lesson to avoid using reserved words as
column names.

Well said!

I put brackets [] around the Years column. No change. i changed the
name of the field to Years, no change. I even put " ' " marks
around the field name. I still gives me a syntax error in INSERT INTO
statement.

I looked at the locals window, and it was easier for me to see what i
couldn't see on my 22 inch monitor, running on high resolution. When I
meant to write "INSERT INTO BudgetSpend ( Years) etc, I actually typed
in "INSERT INTO BudgetSpend {Years ) ( notice the bracket { instead of
the parenthesis, beginning the Years.) I think what must have happened
is that i first had the problem of not encasing the Year field [Year],
and in fixing it somehow converted the parenthesis to a bracket. Thanks
for all your help. It really pointed me in the right direction. And
I'll have to read up on reserved words, its been awhile for me for
anything other than select and joins for me.

 

[Arne Vajhøj]

And come out of this with a lesson to avoid using reserved words as column
names.

That is very good advice.

Arne

 

[Arne Vajhøj]

string strSQL = "INSERT INTO BudgetSpending (Year) VALUES
('1964')"; // year is a 4 character text field.

Try and enclose your table and field names in special quotes

e.g.

INSERT INTO `BudgetSpending` (`Year`) VALUES ('1964')

iirc [] instead of `` works, too

[] is MS SQLServer and MS Access

`` is MySQL

"" is the standard

MDB file implies MS Access.

Arne

 

[Jeff Johnson]

I looked at the locals window, and it was easier for me to see what i
couldn't see on my 22 inch monitor, running on high resolution.

This is why my text font is set to Lucida Console 12pt at work (1280 x 1024,
19" monitor) and 14pt at home (1680 x 1050, 22" monitor like you). Do your
eyes and your overall health a favor and bump up that font size!

 

[Julia M]

I've never
heard of using backticks ( ` ) around names like Julia suggested

Now you did

It definitely works in Access, too. And since it does I really didn't
bother parting ways with my mysql heritage ;-)

 

[Arne Vajhøj]

Now you did

It definitely works in Access, too. And since it does I really didn't
bother parting ways with my mysql heritage ;-)

Some experimentation shows that it indeed work in MS Access.

Interesting.

But it is not something MS is advertising/supporting.

(not to mention that the concept of using invalid names
is horrible in the first place)

Arne

 

[Jeff Johnson]

Now you did

It definitely works in Access, too. And since it does I really didn't
bother parting ways with my mysql heritage ;-)

Out of curiosity, what version of Access does it work in? Or more
specifically, do you have any idea when it was introduced?