Sysntax Error Inserting into a table with an OleDbCommand

[Oluwole Kolawole]

Hi all,

I need another set of eyes to look at a problem I'm having. I'm trying to
insert data into a Micrsoft Access 2002 table using the following SQL
statement (all fields are Text):

"Insert Into Customers (UserName, Password, FirstName, LastName, Address,
PostCode, PhoneDay, PhoneEve, PhoneMob) Values ('Jack', 'beanstalk', 'Jack',
'Beanstalk', 'Nowhere', 'Nothing', '00000000', ' ', ' ')"

As far as I can tell, there is absolutely nothing wrong with this Insert
statement but ADO.Net thinks different and I keep getting a "Syntax error in
INSERT INTO statement" error. The code I'm using is as follows:

bool AllOk = true;
RowsAffected = 0;
try
{
OleDbConnection cn = new OleDbConnection(cnString());
cn.Open();
OleDbCommand cmd = new OleDbCommand(SQLstmt, cn);
RowsAffected = cmd.ExecuteNonQuery(); <----THIS DOESN'T WORK
cn.Close();
cn.Dispose();
cn = null;
cmd.Dispose();
cmd = null;
}
catch (Exception e)
{AllOk = false;}

I'm getting desperate, can anyone tell me what I'm doing wrong.

Regards.



Wole
London.
--



===============================
Life is not fair, get used to it!
God is fair, get Him!

 

[William Ryan eMVP]

You are using a Reserved word (Password) so either replace it (and probably
username too to be safe) or wrap it in [ReservedWord] here. All in all, I'd
recommend getting rid of the reserved word all together b/c you or someone
else may forget to do this in the future.

HTH,

Bill

 

[Oluwole Kolawole]

Tx Bill,

That has gotten rid of that error, but now I'm stuck with another one. I
started getting the error "operation must use an updateable query" which I
found out means that there was a problem with permissions to the Access
database or directory (or both). After fooling around with permissions and
rights, etc, I'm now getting the error "unspecified error" when I try to
open the connection (cn.open()...and it's driving me up the wall. Any
ideas will be gratefuly received.

Regards.



Wole.
--



===============================
Life is not fair, get used to it!
God is fair, get Him!

You are using a Reserved word (Password) so either replace it (and probably
username too to be safe) or wrap it in [ReservedWord] here. All in all, I'd
recommend getting rid of the reserved word all together b/c you or someone
else may forget to do this in the future.

HTH,

Bill

Hi all,

I need another set of eyes to look at a problem I'm having. I'm trying to
insert data into a Micrsoft Access 2002 table using the following SQL
statement (all fields are Text):

"Insert Into Customers (UserName, Password, FirstName, LastName, Address,
PostCode, PhoneDay, PhoneEve, PhoneMob) Values ('Jack', 'beanstalk', 'Jack',
'Beanstalk', 'Nowhere', 'Nothing', '00000000', ' ', ' ')"

As far as I can tell, there is absolutely nothing wrong with this Insert
statement but ADO.Net thinks different and I keep getting a "Syntax

error

in
INSERT INTO statement" error. The code I'm using is as follows:

bool AllOk = true;
RowsAffected = 0;
try
{
OleDbConnection cn = new OleDbConnection(cnString());
cn.Open();
OleDbCommand cmd = new OleDbCommand(SQLstmt, cn);
RowsAffected = cmd.ExecuteNonQuery(); <----THIS DOESN'T WORK
cn.Close();
cn.Dispose();
cn = null;
cmd.Dispose();
cmd = null;
}
catch (Exception e)
{AllOk = false;}

I'm getting desperate, can anyone tell me what I'm doing wrong.

Regards.



Wole
London.
--



===============================
Life is not fair, get used to it!
God is fair, get Him!

 

[Oluwole Kolawole]

Ok, I've sorted out my other problem as well. This site
(http://support.microsoft.com/default.aspx?scid=kb;en-us;827190) explains it
all.

Regards.



Wole.

--



===============================
Life is not fair, get used to it!
God is fair, get Him!

Tx Bill,

That has gotten rid of that error, but now I'm stuck with another one. I
started getting the error "operation must use an updateable query" which I
found out means that there was a problem with permissions to the Access
database or directory (or both). After fooling around with permissions and
rights, etc, I'm now getting the error "unspecified error" when I try to
open the connection (cn.open()...and it's driving me up the wall. Any
ideas will be gratefuly received.

Regards.



Wole.
--



===============================
Life is not fair, get used to it!
God is fair, get Him!

You are using a Reserved word (Password) so either replace it (and probably
username too to be safe) or wrap it in [ReservedWord] here. All in all, I'd
recommend getting rid of the reserved word all together b/c you or someone
else may forget to do this in the future.

HTH,

Bill

Hi all,

I need another set of eyes to look at a problem I'm having. I'm trying to
insert data into a Micrsoft Access 2002 table using the following SQL
statement (all fields are Text):

"Insert Into Customers (UserName, Password, FirstName, LastName, Address,
PostCode, PhoneDay, PhoneEve, PhoneMob) Values ('Jack', 'beanstalk', 'Jack',
'Beanstalk', 'Nowhere', 'Nothing', '00000000', ' ', ' ')"

As far as I can tell, there is absolutely nothing wrong with this Insert
statement but ADO.Net thinks different and I keep getting a "Syntax

error

in
INSERT INTO statement" error. The code I'm using is as follows:

bool AllOk = true;
RowsAffected = 0;
try
{
OleDbConnection cn = new OleDbConnection(cnString());
cn.Open();
OleDbCommand cmd = new OleDbCommand(SQLstmt, cn);
RowsAffected = cmd.ExecuteNonQuery(); <----THIS DOESN'T WORK
cn.Close();
cn.Dispose();
cn = null;
cmd.Dispose();
cmd = null;
}
catch (Exception e)
{AllOk = false;}

I'm getting desperate, can anyone tell me what I'm doing wrong.

Regards.



Wole
London.
--



===============================
Life is not fair, get used to it!
God is fair, get Him!