MS Access LDB file (lock) and INSERT Failure

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 address removed)','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?
 
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
 
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 said:
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 said:
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 address removed)','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?
 

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


Back
Top