MS Access LDB file (lock) and INSERT Failure

[dale zhang]

Hi,

I am writing a subscription page in C#. I first check if username is unique by

"SELECT UserName FROM Logins WHERE Username ='dadada'"

Then if unique, I create the user by

"INSERT INTO Logins
(UserName,UserType,Password,StartDate,Status,Email ,Phone,Home) VALUES
('dadada','u','dadadada',#8/25/2005 5:22:33
PM#,'trial','[e-mail]','1231231231','10 brechk st., bridgewater, nj
08807')"

Somehow this always returns exception after running dbCmd.ExecuteNonQuery()
as below

"Syntax error in INSERT INTO statement."

I checked the syntax many times, could not find anything wrong.

When user logins in, I update the DB successfully as below:

"INSERT INTO Visits (Email,LoginDate,ReferPage,LoginOK,LoginPass,IP) VALUES
('admina',#8/25/2005 5:25:16
PM#,'/passwordProtectCSharp/privatePage.aspx',1,'adminabc','127.0.0.1')"

So access to DB is OK. I suspect my first reading to Logins table caused ldb
lock. So I can not insert afterwards. After removing the check of uniqueness,
I still have the same error.

Any suggestions? Thanks. -Dale
PS. ldb file sometimes stays there for a long time, sometimes it disappears
soon after code is stopped. can not delete it. any way to control it?

[Cathal Connolly [VB MVP]]

Access automatically creates an ldb (lock file) when you open a connection
to it, and will only remove it after you close the connection, you never
need to delete it. Your sql statement will have a problem, either because
it's using a reserved word i.e. I think password is reserved, so if you have
a field called that wrap it in square brackets [password], so Access knows
it's a field, or else you're not meeting a field requirement e.g. your phone
number is 10 characters and you've only allowed for 8.

Please note you should always wrap database access code in either try-catch
or Using blocks to ensure that if there is an error the connection is
correctly closed.

Cathal
"dale zhang" <da*******@discussions.microsoft.com> wrote in message
news:48**********************************@microsof t.com...

Hi,

I am writing a subscription page in C#. I first check if username is unique by
"SELECT UserName FROM Logins WHERE Username ='dadada'"

Then if unique, I create the user by

"INSERT INTO Logins
(UserName,UserType,Password,StartDate,Status,Email ,Phone,Home) VALUES
('dadada','u','dadadada',#8/25/2005 5:22:33
PM#,'trial','[e-mail]','1231231231','10 brechk st., bridgewater, nj
08807')"

Somehow this always returns exception after running dbCmd.ExecuteNonQuery() as below

"Syntax error in INSERT INTO statement."

I checked the syntax many times, could not find anything wrong.

When user logins in, I update the DB successfully as below:

"INSERT INTO Visits (Email,LoginDate,ReferPage,LoginOK,LoginPass,IP) VALUES ('admina',#8/25/2005 5:25:16
PM#,'/passwordProtectCSharp/privatePage.aspx',1,'adminabc','127.0.0.1')"

So access to DB is OK. I suspect my first reading to Logins table caused ldb lock. So I can not insert afterwards. After removing the check of uniqueness, I still have the same error.

Any suggestions? Thanks. -Dale
PS. ldb file sometimes stays there for a long time, sometimes it disappears soon after code is stopped. can not delete it. any way to control it?

[dale zhang]

Cathal,

Thank you very much. You are exactly right. Some words are reserved. I added
some of them with 1 at end. It is work fine now.

-Dale

"Cathal Connolly [VB MVP]" wrote:

Access automatically creates an ldb (lock file) when you open a connection
to it, and will only remove it after you close the connection, you never
need to delete it. Your sql statement will have a problem, either because
it's using a reserved word i.e. I think password is reserved, so if you have
a field called that wrap it in square brackets [password], so Access knows
it's a field, or else you're not meeting a field requirement e.g. your phone
number is 10 characters and you've only allowed for 8.

Please note you should always wrap database access code in either try-catch
or Using blocks to ensure that if there is an error the connection is
correctly closed.

Cathal
"dale zhang" <da*******@discussions.microsoft.com> wrote in message
news:48**********************************@microsof t.com...

Hi,

I am writing a subscription page in C#. I first check if username is

unique by

"SELECT UserName FROM Logins WHERE Username ='dadada'"

Then if unique, I create the user by

"INSERT INTO Logins
(UserName,UserType,Password,StartDate,Status,Email ,Phone,Home) VALUES
('dadada','u','dadadada',#8/25/2005 5:22:33
PM#,'trial','[e-mail]','1231231231','10 brechk st., bridgewater, nj
08807')"

Somehow this always returns exception after running

dbCmd.ExecuteNonQuery()

as below

"Syntax error in INSERT INTO statement."

I checked the syntax many times, could not find anything wrong.

When user logins in, I update the DB successfully as below:

"INSERT INTO Visits (Email,LoginDate,ReferPage,LoginOK,LoginPass,IP)

VALUES

('admina',#8/25/2005 5:25:16
PM#,'/passwordProtectCSharp/privatePage.aspx',1,'adminabc','127.0.0.1')"

So access to DB is OK. I suspect my first reading to Logins table caused

ldb

lock. So I can not insert afterwards. After removing the check of

uniqueness,

I still have the same error.

Any suggestions? Thanks. -Dale
PS. ldb file sometimes stays there for a long time, sometimes it

disappears

soon after code is stopped. can not delete it. any way to control it?