Restrictions accessing MDB (through OLEDB) within .NET applications?

[Luis]

Hello,

I am a newbie in .NET and Visual Studio, I come from the Java
environment. I am developing my first .NET app and have found a big
stopper, I hope you can help me out of it.

I have defined an MS Access database with a test table (MS Access 2002
version). From a .NET application I can easily read its data and
display it in a DataGrid. The problem arises when I try to UPDATE that
table: I get an OleDbException (0x80004005): "the operation must use
an updateable query" (the original exception text is in Spanish so the
English message is probably different, but the number should be the
same).

The update command is correct since it works fine when executed from a
(traditional) windows application. When I copy&paste the exact same
code inside a Page_Load method, I get the above exception.

To check if it was a file-write permission restriction, I have also
tested writing a "hello.txt" file to the same directory, and it has
worked fine even from the .NET application. I have also enabled all
read and write permissions from the IIS configuration properties.

So I am lost... Is there some restriction with MDB databases or OLEDB
from within .NET applications? If so, how can I overcome that
restriction? Or, is there some other reason why the exact same code
should work in a windows application and fail in a .NET one?

In case you want to check, this is the code I am using. The table and
column names are in Spanish but that should not be a problem, it is a
simple "persons" data table:


Dim sql As String
Dim insertCmd As OleDbCommand
Dim connStr As String
Dim conn As OleDbConnection

connStr = "Provider=Microsoft.Jet.OLEDB.4.0; "
connStr = connStr + "Data Source=C:\pepe\prueba.mdb;"
conn = New OleDbConnection(connStr)
sql = "insert into [personas] ([nombre], [apellido], [dni]) values
("
sql = sql + "'Pedro','Pérez','333')"
insertCmd = New OleDbCommand(sql, conn)
conn.Open()
insertCmd.ExecuteNonQuery()
conn.Close()

The exception arises when invoking "insertCmd.ExecuteNonQuery()"...

Thank you very much in advance.

Luis.

 

[Uri Dor]

I think the standard procedure is to write only the SELECT yourself and
use a CommandBuilder to generate the INSERT, UPDATE.
Look it up

 

[Cor]

Hi Luis,

Set what i wrote inline extra in your code

Than you will see the problem more easy

watch typos, I typed it in the message without checking

I hope this helps?

Cor

conn.Open() Try
insertCmd.ExecuteNonQuery()

catch eOleDB as OleDb.OleDbException
messagebox.show (eOleDb.toString)
catch ex as exception
messagebox.show (ex.toString)
finally

conn.Close()

End Try

 

[Ron Allen]

Luis,
Is your [dni] field a string? If it is a number it should not be
quoted.
Note that this update code isn't going to work for a DataAdapter. For
that you will need a parameterized query such as
cmd.CommandText = "insert into [personas] ([nombre], [apellido], [dni])
values (?, ?, ?)"
' Parameters must be added in order of appearance in the CommandText
cmd.Paramters.Add("nombre", OleDbType.VarChar, `length of field here`,
"nombre")
cmd.Paramters.Add("apellido", OleDbType.VarChar, `length of field here`,
"apellido")
cmd.Paramters.Add("dni", OleDbType.Int, 0, "dni") ' if this is an integer
field

You may want to follow up in microsoft.public.dotnet.framework.adonet as
that group is dedicated to ADO.NET.

Ron Allen

Hello,

I am a newbie in .NET and Visual Studio, I come from the Java
environment. I am developing my first .NET app and have found a big
stopper, I hope you can help me out of it.

I have defined an MS Access database with a test table (MS Access 2002
version). From a .NET application I can easily read its data and
display it in a DataGrid. The problem arises when I try to UPDATE that
table: I get an OleDbException (0x80004005): "the operation must use
an updateable query" (the original exception text is in Spanish so the
English message is probably different, but the number should be the
same).

The update command is correct since it works fine when executed from a
(traditional) windows application. When I copy&paste the exact same
code inside a Page_Load method, I get the above exception.

To check if it was a file-write permission restriction, I have also
tested writing a "hello.txt" file to the same directory, and it has
worked fine even from the .NET application. I have also enabled all
read and write permissions from the IIS configuration properties.

So I am lost... Is there some restriction with MDB databases or OLEDB
from within .NET applications? If so, how can I overcome that
restriction? Or, is there some other reason why the exact same code
should work in a windows application and fail in a .NET one?

In case you want to check, this is the code I am using. The table and
column names are in Spanish but that should not be a problem, it is a
simple "persons" data table:


Dim sql As String
Dim insertCmd As OleDbCommand
Dim connStr As String
Dim conn As OleDbConnection

connStr = "Provider=Microsoft.Jet.OLEDB.4.0; "
connStr = connStr + "Data Source=C:\pepe\prueba.mdb;"
conn = New OleDbConnection(connStr)
sql = "insert into [personas] ([nombre], [apellido], [dni]) values
("
sql = sql + "'Pedro','Pérez','333')"
insertCmd = New OleDbCommand(sql, conn)
conn.Open()
insertCmd.ExecuteNonQuery()
conn.Close()

The exception arises when invoking "insertCmd.ExecuteNonQuery()"...

Thank you very much in advance.

Luis.

 

[Luis]

Ron, thank you for the pointer to
"microsoft.public.dotnet.framework.adonet".

My update must be correct since, as I explained below, it does work OK
as long as I don't run it inside ASP.NET, for example, from a "windows
application" project (and yes, DNI is a string).

Therefore the problem must have to do with some difference between the
ASP.NET environment and the windows/client-based one.

Regards,
Luis.

Luis,
Is your [dni] field a string? If it is a number it should not be
quoted.
Note that this update code isn't going to work for a DataAdapter. For
that you will need a parameterized query such as
cmd.CommandText = "insert into [personas] ([nombre], [apellido], [dni])
values (?, ?, ?)"
' Parameters must be added in order of appearance in the CommandText
cmd.Paramters.Add("nombre", OleDbType.VarChar, `length of field here`,
"nombre")
cmd.Paramters.Add("apellido", OleDbType.VarChar, `length of field here`,
"apellido")
cmd.Paramters.Add("dni", OleDbType.Int, 0, "dni") ' if this is an integer
field

You may want to follow up in microsoft.public.dotnet.framework.adonet as
that group is dedicated to ADO.NET.

Ron Allen

Hello,

I am a newbie in .NET and Visual Studio, I come from the Java
environment. I am developing my first .NET app and have found a big
stopper, I hope you can help me out of it.

I have defined an MS Access database with a test table (MS Access 2002
version). From a .NET application I can easily read its data and
display it in a DataGrid. The problem arises when I try to UPDATE that
table: I get an OleDbException (0x80004005): "the operation must use
an updateable query" (the original exception text is in Spanish so the
English message is probably different, but the number should be the
same).

The update command is correct since it works fine when executed from a
(traditional) windows application. When I copy&paste the exact same
code inside a Page_Load method, I get the above exception.

To check if it was a file-write permission restriction, I have also
tested writing a "hello.txt" file to the same directory, and it has
worked fine even from the .NET application. I have also enabled all
read and write permissions from the IIS configuration properties.

So I am lost... Is there some restriction with MDB databases or OLEDB
from within .NET applications? If so, how can I overcome that
restriction? Or, is there some other reason why the exact same code
should work in a windows application and fail in a .NET one?

In case you want to check, this is the code I am using. The table and
column names are in Spanish but that should not be a problem, it is a
simple "persons" data table:


Dim sql As String
Dim insertCmd As OleDbCommand
Dim connStr As String
Dim conn As OleDbConnection

connStr = "Provider=Microsoft.Jet.OLEDB.4.0; "
connStr = connStr + "Data Source=C:\pepe\prueba.mdb;"
conn = New OleDbConnection(connStr)
sql = "insert into [personas] ([nombre], [apellido], [dni]) values
("
sql = sql + "'Pedro','Pérez','333')"
insertCmd = New OleDbCommand(sql, conn)
conn.Open()
insertCmd.ExecuteNonQuery()
conn.Close()

The exception arises when invoking "insertCmd.ExecuteNonQuery()"...

Thank you very much in advance.

Luis.