Unable to INSERT record in MS Access

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

Guest

Dear Newsgroup:
I am trying to insert records into an access database
using VS.NET (ASP.NET) however I continuely receive the
error "Operation must use an updateable query" when I
execute the objCmd.ExecuteNonQuery() command.

The stored procedure is right out of Chris Payne's book
ASP.NET in 21 days: "INSERT INTO tblUsers (UserName,
Password, FirstName...) VALUES (@username, @Password,
@Firstname ...)
This I put in an access query saved as spAddUser. When I
run it in Access it works fine. When I let ASP.NET run
it w/ the following code it continuely crashes. Any help
would greatly be appreciated.
Thanks,
Dave Corwith
(e-mail address removed)


Public Sub AddUser(ByVal objUser As UserDetails)
Dim intID As Integer
Dim objReader As OleDbDataReader
Dim objCmdID As New OleDbCommand("SELECT MAX
(UserID) FROM tblUsers", objConn)
Dim objCmd As New OleDbCommand("spAddUser",
objConn)
objCmd.CommandType =
CommandType.StoredProcedure

Dim objParam As New OleDbParameter
("@username", OleDbType.Char)
objParam.Value = objUser.UserName
objCmd.Parameters.Add(objParam)


Try
objConn.Open()
objCmd.ExecuteNonQuery()
objUser.UserID = CType
(objCmdID.ExecuteScalar, Integer)
Catch e As Exception
Throw e
End Try

..
 
Hi,

The only thing that comes in my mind is that aspnet local user account has
not enough privileges on access file to write to it.
Check out NTFS security.
 
Dave:

(first, make sure you obscure your email address when you post it here...add
in nospam or something so spammers can't pick it up.)

Can you update a record or delete one? I take it that the SELECT statement
is working fine?
 
Hi,

I think it happens because some of your field names are reserved words
(Password at least). Provider will complain if it finds any reserved words.
In this case you need to wrap names of these fields into square brackets

"INSERT INTO tblUsers (UserName, [Password], FirstName...) VALUES
(@UserName, @Password,
@Firstname ...)
 
(first, make sure you obscure your email address when you post it here...add
in nospam or something so spammers can't pick it up.)
Click to expand...

Too late, I've already send him p3nis enlargment pills and viagra
commercials in 100 copies ;-)
 
ROFLMAO! my p3nis has got a lot of people wanting to help him out too and
refininance the house in the process.
 
Val's right, it's probably the reserved word and they can cause nasty and
subtle problems like this...IMHO, they aren't worth maintaining because
they'll keep cropping up.
Val Mazur said:
Hi,

I think it happens because some of your field names are reserved words
(Password at least). Provider will complain if it finds any reserved words.
In this case you need to wrap names of these fields into square brackets

"INSERT INTO tblUsers (UserName, [Password], FirstName...) VALUES
(@UserName, @Password,
@Firstname ...)


--
Val Mazur
Microsoft MVP
Check Virus Alert, stay updated
http://www.microsoft.com/security/incident/blast.asp

Dear Newsgroup:
I am trying to insert records into an access database
using VS.NET (ASP.NET) however I continuely receive the
error "Operation must use an updateable query" when I
execute the objCmd.ExecuteNonQuery() command.

The stored procedure is right out of Chris Payne's book
ASP.NET in 21 days: "INSERT INTO tblUsers (UserName,
Password, FirstName...) VALUES (@UserName, @Password,
@Firstname ...)
This I put in an access query saved as spAddUser. When I
run it in Access it works fine. When I let ASP.NET run
it w/ the following code it continuely crashes. Any help
would greatly be appreciated.
Thanks,
Dave Corwith
(e-mail address removed)


Public Sub AddUser(ByVal objUser As UserDetails)
Dim intID As Integer
Dim objReader As OleDbDataReader
Dim objCmdID As New OleDbCommand("SELECT MAX
(UserID) FROM tblUsers", objConn)
Dim objCmd As New OleDbCommand("spAddUser",
objConn)
objCmd.CommandType =
CommandType.StoredProcedure

Dim objParam As New OleDbParameter
("@Username", OleDbType.Char)
objParam.Value = objUser.UserName
objCmd.Parameters.Add(objParam)


Try
objConn.Open()
objCmd.ExecuteNonQuery()
objUser.UserID = CType
(objCmdID.ExecuteScalar, Integer)
Catch e As Exception
Throw e
End Try

.
Click to expand...
Click to expand...
 
Hi William,


William Ryan said:
Val's right, it's probably the reserved word and they can cause nasty and
subtle problems like this...IMHO, they aren't worth maintaining because
they'll keep cropping up.
Click to expand...

I am not so sure since Dave is saying that it works within Access...
 
Thanks, I found the problem. The group EVERYONE MUST
HAVE FULL ACCESS RIGHTS at least at the file level. See
MS knowledge base 175168.
 
William,
Thanks for the spam advice.
-----Original Message-----
Dave:

(first, make sure you obscure your email address when you post it here...add
in nospam or something so spammers can't pick it up.)

Can you update a record or delete one? I take it that the SELECT statement
is working fine?



.
Click to expand...
 
Miha,
You got it. The group EVERYONE must have Full access
rights at least at the file level. This resolved it.
See KB 175168 and Access error 3073. Thanks for your
help and the viagra pills will come in handy...
 
Hi Miha,

Yes, it will work within Access, but when you execute statement from the
application, then there is a provider between database and client and this
provider checks for the reserved words
 
Back
Top