Problem with access on Server 2003

[zubie]

Hey all

 

I have an ASP.net 1.1 project running on Server 2003

 

Problem:

1) First time I run page on the server it is fine. (eg add a user)

2) If I try and run something else (eg add/amend user) I recieve an error message saying "operation must use an updateable query"

(It seems to have kept a lock on the DB)

 

The DB has all the required permissions.

The app runs perfectly on the development machine with no locking problems

 

anyone every experience this?

any help gladly recieved

 

ZuBiE

[zubie]

Ahhh ......

 

Well maybe I dont have all the correct permissions.

What permission do I have to give for the DB

 

The big problem is that this works once fine (reads/writes to the DB) and then gives the error!!!!!

[Arch4ngel]

Did you correctly disconnected after your operations ?

Keeping a connection alive might result in permissions problem.

 

Give us a little code to see how you make your connection, add and disconnection

[zubie]

OK

 

Here is a update SQL

 

Public strConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=C:\Inetpub\wwwroot\MART\db\MART.mdb"

 

strSql = "update ReportDetails set DateFrom = '" & strDateFrom & _

"', DateTo = '" & strDateTo & "', DaysSuspended = " & _

intDaysSuspended & ", DaysAbsent = " & intDaysAbsent

 

ExecuteSql(strSql)

 

This call the function ExecuteSql

 

Friend Function ExecuteSql(ByVal strSql As String)

'Use this function for updates, inserts and deletes

Dim clsConnection As New Connection()

Dim dbconConnection As System.Data.IDbConnection = New OleDbConnection(strConnectionString)

Dim cmdCommand As New OleDbCommand(strSql, dbconConnection)

 

Try

'Open connection

dbconConnection.Open()

 

'Execute the Sql

cmdCommand.ExecuteNonQuery()

 

Catch ex As Exception

Throw ex

Finally

dbconConnection.Close()

End Try

 

End Function

[Arch4ngel]

Here is what I used in C# :

 

[size=2]
System.Data.OleDb.OleDbConnection con = [/size][size=2][color=#0000ff]new[/color][/size][size=2] System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-1; Data Source=" + @"C:\Inetpub\wwwroot\test.mdb"); 
con.Open(); 

System.Data.OleDb.OleDbCommand cmd = con.CreateCommand(); 
cmd.CommandText = "INSERT INTO test (Nom) VALUES (\****\")"; 
cmd.ExecuteNonQuery(); 
Response.Write(con.State.ToString()); 
[/size]

 

The translation in VB.NET :

[size=2]
Dim con as System.Data.OleDb.OleDbConnection = new [/size][size=2]System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-1; Data Source=C:\Inetpub\wwwroot\test.mdb") 
con.Open()

Dim cmd as System.Data.OleDb.OleDbCommand = con.CreateCommand() 

cmd.CommandText = "INSERT INTO test (Nom) VALUES ('****')" 'Don't know how to make "  
cmd.ExecuteNonQuery() 
Response.Write(con.State.ToString()) 
[/size]

 

What seems to be causing trouble is your Connection Object. IDBConnection caused me a lot of trouble... try switching to OleDbConnection. It might work better. I managed to insert a lot of scrap in a testing db.

 

It might be what causing you so much trouble.

[zubie]

OK I'll give it a shot and get back to ya

[zubie]

  Quote

OK I'll give it a shot and get back to ya

Nope ...

It worked fine the first time through

 

then tried to run the same report again and it bombed out at the .ExecuteNonQuery

[Arch4ngel]

Can you send me the WHOLE error message ?

It might be an error in your sql code in use with your Table designed... like RI (referential integrity) problem or something like this.

[zubie]

Here is the full error.

As I said before this is using the EXACT same code as the first successful run ..

*shrug*

 

System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.OleDb.OleDbException: Operation must use an updateable query. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at MARTWS.wsGlobal.ExecuteSql(String strSql) at MARTWS.Report.ReportParameters(String strDateFrom, String strDateTo, Int32 intDaysSuspended, Int32 intDaysAbsent) at MARTWS.wsReports.WriteReportParameters(String strDateFrom, String strDateTo, Int32 intDaysSuspended, Int32 intDaysAbsent) --- End of inner exception stack trace ---

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.Web.Services.Protocols.SoapException: System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.OleDb.OleDbException: Operation must use an updateable query. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at MARTWS.wsGlobal.ExecuteSql(String strSql) at MARTWS.Report.ReportParameters(String strDateFrom, String strDateTo, Int32 intDaysSuspended, Int32 intDaysAbsent) at MARTWS.wsReports.WriteReportParameters(String strDateFrom, String strDateTo, Int32 intDaysSuspended, Int32 intDaysAbsent) --- End of inner exception stack trace ---

 

Source Error:

 

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

 

Stack Trace:

 

 

[soapException: System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.OleDb.OleDbException: Operation must use an updateable query.

at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)

at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)

at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)

at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)

at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)

at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()

at MARTWS.wsGlobal.ExecuteSql(String strSql)

at MARTWS.Report.ReportParameters(String strDateFrom, String strDateTo, Int32 intDaysSuspended, Int32 intDaysAbsent)

at MARTWS.wsReports.WriteReportParameters(String strDateFrom, String strDateTo, Int32 intDaysSuspended, Int32 intDaysAbsent)

--- End of inner exception stack trace ---]

System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall) +1489

System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) +218

ProjectMART.wrReports.wsReports.WriteReportParameters(String strDateFrom, String strDateTo, Int32 intDaysSuspended, Int32 intDaysAbsent) +130

ProjectMART.ReportChoice.btnViewReport_Click(Object sender, EventArgs e) +645

System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108

System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57

System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18

System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33

System.Web.UI.Page.ProcessRequestMain() +1277

 

 

 

 

--------------------------------------------------------------------------------

Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573

[Arch4ngel]

http://www.xlinesoft.com/asprunner/docs/error_operation_must_use_an_updateable_query.htm

At this address... they say that it's a permission problem.

IUSR shall have read/write access.

 

http://support.microsoft.com/default.aspx?scid=kb;en-us;q175168

This is the error from Microsoft. Might find your solution in there.

 

As I know... there's much more chance that it's a permission problem.

 

As Microsoft say... ADO makes .ldb file when it write... so make sure permission are also to the right directory where temp file are created.

[zubie]

Cheers ... I'll look into that

 

thanks for the help

 

ZuBiE

[Arch4ngel]

it's nothing...

 

Pleasure to help .NET friends :p