updating a access Database Record

[Fred Geurtsen]

Question about updating a access Database Record!
Normally i'm working with SQLserver, but i have to do this with access and it's not working.

My error is: Operation must use an updateable query.

This is my code:
Dim objCommand As OleDbCommand
Dim strSQLQuery As String

' Our insert command
strSQLQuery = "Update Regios SET Regio=@TextRegio WHERE RegioID=@RegioID "

' Create new command object passing it our SQL insert
' and telling it which connection to use.

objCommand = New OleDbCommand(strSQLQuery, conStoreAdmin)

' Add parameters that our SQL command takes:
objCommand.Parameters.Add(New OleDbParameter("@TextRegio", OleDbType.VarChar, 500))
objCommand.Parameters.Add(New OleDbParameter("@RegioID", OleDbType.Integer))

' Set the values of these parameters:
objCommand.Parameters("@TextRegio").Value = strKAT
objCommand.Parameters("@RegioID").Value = intID

' Open the connection, execute the command, and close the connection.
conStoreAdmin.Open()
objCommand.ExecuteNonQuery()
conStoreAdmin.Close()

Thx

 

[Paul Clement]

¤ Question about updating a access Database Record!
¤ Normally i'm working with SQLserver, but i have to do this with access and it's not working.
¤
¤ My error is: Operation must use an updateable query.
¤
¤ This is my code:
¤ Dim objCommand As OleDbCommand
¤ Dim strSQLQuery As String
¤
¤ ' Our insert command
¤ strSQLQuery = "Update Regios SET Regio=@TextRegio WHERE RegioID=@RegioID "
¤
¤ ' Create new command object passing it our SQL insert
¤ ' and telling it which connection to use.
¤
¤ objCommand = New OleDbCommand(strSQLQuery, conStoreAdmin)
¤
¤ ' Add parameters that our SQL command takes:
¤ objCommand.Parameters.Add(New OleDbParameter("@TextRegio", OleDbType.VarChar, 500))
¤ objCommand.Parameters.Add(New OleDbParameter("@RegioID", OleDbType.Integer))
¤
¤ ' Set the values of these parameters:
¤ objCommand.Parameters("@TextRegio").Value = strKAT
¤ objCommand.Parameters("@RegioID").Value = intID
¤
¤ ' Open the connection, execute the command, and close the connection.
¤ conStoreAdmin.Open()
¤ objCommand.ExecuteNonQuery()
¤ conStoreAdmin.Close()

Could be permissions issue on the folder or database file. Is this an ASP.NET application?


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Fred Geurtsen]

Yes, It is an ASP.NET application, so my access database file can't be
written. Do I need to share and allow changing the folder where my
database is?

 

[Andy O'Neill]

Yes, It is an ASP.NET application, so my access database file can't be
written. Do I need to share and allow changing the folder where my
database is?

You also need a primary key.
If you have one then the next thing to try is just executing a hard coded
string and check that doesn't work.

When you write to an access database that user has to create an ldb file in
the same folder as the mdb and be able to write the user locks away to that.
The last user disconnecting also deletes the ldb with recent versions of
access.
There's no database service.

 

[C CORDON]

If it is an ASP.NET app you may even need to edit the machine.config file.

Did you make it work?

 

[slonocode]

Question about updating a access Database Record!
Normally i'm working with SQLserver, but i have to do this with access
and it's not working.

My error is: *Operation must use an updateable query.*
**
This is my code:

Dim objCommand As OleDbCommand
Dim strSQLQuery As String

' Our insert command
strSQLQuery = "Update Regios SET Regio=@TextRegio WHERE RegioID=@RegioID "

' Create new command object passing it our SQL insert
' and telling it which connection to use.

objCommand = New OleDbCommand(strSQLQuery, conStoreAdmin)

' Add parameters that our SQL command takes:
objCommand.Parameters.Add(New OleDbParameter("@TextRegio",
OleDbType.VarChar, 500))
objCommand.Parameters.Add(New OleDbParameter("@RegioID", OleDbType.Integer))

' Set the values of these parameters:
objCommand.Parameters("@TextRegio").Value = strKAT
objCommand.Parameters("@RegioID").Value = intID

' Open the connection, execute the command, and close the connection.
conStoreAdmin.Open()
objCommand.ExecuteNonQuery()
conStoreAdmin.Close()

Thx

I believe when using Access DB you need to use a question mark in the
select query to indicate a parameter.

 

[Paul Clement]

¤ Yes, It is an ASP.NET application, so my access database file can't be
¤ written. Do I need to share and allow changing the folder where my
¤ database is?

If you're not using impersonation for your web application (configured via the web.config) then you
need to provide full file access to the folder where the Access database is located for the ASPNET
account. This is the default identity under which your ASP.NET web application executes if you are
not implementing impersonation, and it is a somewhat restricted account.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)