Cannot Alter Database Data

Please help I'm pulling my hair out.

[chris_f]

I'm getting balder by the minute (I look like Homer Simpson before long if I can't get this working).

Here's what I'm doing. I am simply trying to connect to a Microsoft Access 2000 database using ASP.net/VB.net. If I do a select command I can get data out of the database with ease.

If I do a response.write of the edit command I am sending to my database it displays the following:

update shippers set companyname='Speedy Express' where shipperid=1

This looks ok to me and updates the database if I use it directly in the database query analyser. However, if I activate the following command using:

Cmd.ExecuteNonQuery()

where cmd is the name of my command I get the following error:

[big]Operation must use an updateable query[/big]

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Operation must use an updateable query.

Has anyone got any ideas what this error message is referring to and even better how to fix it?

Chris

p.s. my connection string looks like this:

"PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & server.mappath("/sites/dbcode_test/NWIND_test.mdb") & ";"

[JuDDer]

It may have something to do with the permissions on the database.

Have you checked that the actual access database has the correct permissions for IUSER_machinename?

<added> Make sure you don't have Access open while you're tying to run the update as well</added>

[chris_f]

Thanks for the speedy reply JuDDer,

Access is not open run the code. Also, I am guessing the permissions are correct as I can do a select query. Is this not the case? I am running windows XP so I don't have a security tab when I right click on the file. Also, I have put no security on the database within Access.

Chris.

[JuDDer]

Not having the right security will allow exactly what you are describing, ie it will allow you to select but not update.

I don't run XP myself so I dont know about how it handles security.

Do you get any sort of security options if you right click on the folder which contains the access file rather than the access file itself?

[chris_f]

I've looked in IIS and I can see that both the application and the database file has an option called Anonymous access checked so I'm guessing the security is not an issue. Can anyone confirm this? JuDDer have you (or anyone else) got any more ideas?

Chris.

[chris_f]

In IIS under the file tab I just checked script source access and write (read and log visits were already ticked). No Change!

Chris

[JuDDer]

Hmmm.
I still think it's a permissions issue, but I really don't know where to go from here as I'm not an XP user.

Did you check to see if there were any security options when right clicking the folder that the database is in?

How about putting the database in the root of your site to see if it makes any difference?

The "Operation must use an updateable query" was something I used to get when I first learned 'classic' ASP and I'm sure it was always a security/permissions issue.

I wonder if your database is locked? Is there an .ldb file in the same directory as your database? If so, delete it.

I'll keep thinking...

[chris_f]

Did you check to see if there were any security options when right clicking the folder that the database is in? There is no security option on the folder either.

How about putting the database in the root of your site to see if it makes any difference? It is in the root.

I wonder if your database is locked? Is there an .ldb file in the same directory as your database? If so, delete it. There's no .ldb file. I'm going to reboot just in case. I will respond in five minutes.

Many Thanks again JuDDer. This is greatly appreciated.

Chris.

[chris_f]

Nope. Still not working

Chris.

[JuDDer]

Can you post the rest of your code for the update statement just in case we can spot something there that's the cause of the problem.

Have you tried connecting to the database via a DSN instead of your existing connection string?

<added>
Does your table have a unique key in it?
</added>

Anything's worth a shot but I still say it's the permissions ;)

[JuDDer]

How about right-clicking on the folder that has the database in it and enabling file sharing?

Have a look at this article about file permissions on XP [support.microsoft.com] to see if it helps.

<added> This article [dotnet247.com] is worth reading too.
</added>

[edited by: JuDDer at 6:51 pm (utc) on July 2, 2002]

[chris_f]

2edit.aspx

<%@ Page Language="vb" Debug="True" %>
<%@ Import Namespace="System.Data.OLEDB" %>
<html>
<head>
<title>TEST Edit</title>
</head>
<script language="VB" runat="server">
Sub Page_Load(Sender As Object, E As EventArgs)
Dim strConn as string = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & server.mappath("/sites/dbcode_test/NWIND_test.mdb") & ";"
Dim strSQL2 as string = "select * from shippers where shipperid=1"
Dim Conn2 as New OLEDBConnection(strConn)
Dim Cmd2 as New OLEDBCommand(strSQL2,Conn2)
Conn2.Open()
DataGrid1.DataSource = Cmd2.ExecuteReader(system.data.CommandBehavior.CloseConnection)
DataGrid1.DataBind()
End Sub

Sub Update_Submit(Sender As Object, E As EventArgs)
Dim newname As String
newname = newshippername.Text

Response.Redirect("2DoEdit.aspx?name=" & newname)

End Sub
</script>
<body>
<br><br>
<font face="Verdana"><h3>Update</h3></font>
<form id="update_form" runat="server">
<font face="Verdana" size="2" color="#000000">Name:</font> <asp:TextBox ID="newshippername" Runat="server" ForeColor="maroon" Font-Name="verdana" Font-Size="10" Font-Bold="True" Width="400" Height="20" /><br>
<asp:Button ID="Form_Submit" Runat="server" Text="Submit Form!" OnClick="Update_Submit" />
</form>
<br><br>
Speedy Express
<br><br>
<font face="Verdana"><h3>Suppliers Data</h3></font>
<ASP:DataGrid id="Datagrid1" runat="server"
Width="100%"
BackColor="white"
BorderColor="black"
ShowFooter="false"
CellPadding=3
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
Headerstyle-BackColor="lightblue"
Headerstyle-Font-Size="10pt"
Font-Bold="True"
/>
</body>
</html>

2DoEdit.aspx

<%@ Page Language="vb" Debug="True" %>
<%@ Import Namespace="System.Data.OLEDB" %>
<html>
<head>
<title>TEST DoEdit</title>
</head>
<script language="VB" runat="server">
Sub Page_Load(Sender As Object, E As EventArgs)
Dim passedname As String

passedname = Trim(Request.QueryString("name"))

response.write("Passed Name = <b>" & passedname & "</b><br>")

txtbox.text = passedname

'passedname = "'" & passedname & "'"
'response.write("Passed Name has been changed to <b>" & passedname & "</b>")

Dim strConn as string = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & server.mappath("/sites/dbcode_test/NWIND_test.mdb") & ";"
Dim strSQL as string = "update shippers set companyname='" & txtbox.Text.Replace("'","''") & "' where shipperid=1"

'ProductNameTextBox.Text.Replace("'","''")

Dim Conn as New OLEDBConnection(strConn)
Dim Cmd as New OLEDBCommand(strSQL,Conn)
Conn.Open()
Cmd.ExecuteNonQuery()
Response.Write(strSQL)
Conn.Close()

End Sub
</script>
<body>
<br><br><br>
<form name="test" runat="server">
<asp:TextBox ID="txtbox" Runat="server" />
</form>
<br><br>Updated?!?!
<br><br>DONE!
</body>
</html>

You'll notice some code is commented out as I've tried different things. I can't seem to get DNS to work.

Chris.

[JuDDer]

I take it you didn't have any luck with the url's that I listed??

[chris_f]

Just read the URL's. I wont have time to have a go tonight. I've have a play tomorrow night.

Chris

[Grumpus]

"How about putting the database in the root of your site to see if it makes any difference?"
It is in the root.

Not sure on XP, but I'm pretty sure that you can't give your root directory, write access. (Though you can give files write access - see below, though). You can give it read and execute access, but not write. Put your database in a subdirectory (give the folder a random name while you're at it to give some added security for those who want to "guess" the location of the database) and make sure the whole directory (not just the database) has write access. (Microsoft likes to create temp files when things are open, so if you only give permissions to the database itself and not the folder, the temp files will fail).

Good Luck (And don't you hate it when it's something as simple as this - happens to me three times a day!)

G.

[chris_f]

Ok. Here's the state of play.

[big]IIS Security[/big]
------------

Directory Security
Anonymous Access = checked
Account used for anonymous access = IUSR_MAX (Max = Computer name)
Allow IIS to control password = checked

Directory
Execute Permissions = Scripts Only
Application Protection = Low (IIS Process)
Script Source Access = checked
Read = checked
Write = checked
Directory Browsing = checked
Log visits = checked
Index this resource = checked

[big]XP Security[/big]
-----------
There is no security tab in the folder or file properties.
I've turned 'use simple file sharing' off as it has some security attached.

Thanks for the help you are giving me guys. It's greatly appreciated. Hopefully I've missed something obvious that you can point out to me.

Chris.

[chris_f]

JuDDer,

Did you have a chance to look at my code and see if there were any silly errors? I have looked high and low. Even the almighty Google can not answer my question.

AAAAAAHHHHHHHHH

Chris.

[JuDDer]

Chris,

I'm at a total loss on this one now I'm afraid :(

I still say it's a permissions problem, but I have no idea about what to suggest as the next move.

I don't have a copy of XP handly so I can't play with it to work out how it deals with file security.

I'm sure this has got to be an easy fix - if only we knew how!

I'll keep thinking about it and will let you know if I can think of anything else.
If you manage to get it working in the meantime, let us know.

[tomasz]

You need to change permission of your mdb file to write.
Hope this will help

[chris_f]

[6]!!!Celebrations!!![/6]

I have finally found the solution.

The reason the security tab didn't appear (even after I disabled simple file sharing) was a security patch I had installed after helping McAfee develop an anti-viral definition to Klez.

Thanks for all you help guys (and gals). I hope I can repay the favour sometime.

Chris.