Problem with Delete from database
This is really perplexing me. I have a dataadapter "mcom1", and perform Update, Insert and Delete commands. I am using access 97. Everything works fine when this is function is a memeber of a class in a windows app. But when I write this function is a member of a web-service class (called from the windows app), any delete command with the syntax as below, fails with the error "System.Argumentexception".
Does anyone know what is going wrong? A parsed version of the delete funciton is written below.
Help much appreciated.
Steve
Function ClientUpdater() As String
'This updates projects table and calls function to subsequently update ClientGrps and Stock
mCom1.DeleteCommand = New OleDbCommand("DELETE FROM Clients WHERE ID = ? ", myConnection)
Dim IDParmD As new OleDbParameter("@ID", OleDbType.Integer)
IDParmD.sourceversion = DataRowVersion.Original
IDParmD.SourceColumn = "ID"
mCom1.DeleteCommand.Parameters.Add(IDParmD)
Try
myConnection.Open()
mCom1.Update(permanentdata.Tables("Clients").Select(Nothing, Nothing, DataViewRowState.Deleted))
' Next process updates.
Catch ex As ArgumentException
ClientUpdater = "Delete Error: " & "/l" & ex.targetsite.ToString
Finally
myConnection.Close()
End Try
End Function
Here is an more extended example fo the code htat is consistently throwing up the same error. As I have said all the updates and inserts work as expected, but hte deletes do not. I have tried reordering, getting rid of the row update handler, changing the syntax etc. I can't think of anything else to do. Might this be a problem with Access 97? The ID column is an autonumber column, so it may be something to do with that.
One recent thing I have tried is tapping the row updating handler to tes to see what arguments are passed. It seems that the datarow does not pass any ID value on teh delete. IE the sourceversion is not properly setting to Original. I ahve tested for original data in the deleted items of the dataset just prior to updating, and they have ID information there. IE there is no stray AcceptChanges floating around in the code.
As I said in my first post, what is most mysterious is that this code works absolutely fine when the same functions are implemented not as Webmethods but in classes as part of the application. This suggests that it is nothing to do with the code itself, but something, rather, to do with access priveledges. But I was not aware of there being any special "delete" priveledges one can set on an access database. Are there any?
Help very much appreciated. I am getting really desparate. Quite enjoying programming .net up until now.
Function ClientUpdater() As String
'This updates projects table and calls function to subsequently update ClientGrps and Stock
mCom1.DeleteCommand = New OleDbCommand("DELETE FROM Clients WHERE ID = ? ", myConnection)
mCom1.UpdateCommand = New OleDbCommand("UPDATE Clients SET ForeName = ? , Surname = ? , Address = ? , Telephone = ? , Fax = ? , Email = ? , Website = ? " & _
"WHERE ID = ?", myConnection)
mCom1.InsertCommand = New OleDbCommand("INSERT INTO Clients ( Forename , Surname , Address , Telephone , Fax, Email , Website) VALUES ( ? , ? , ? , ? , ? , ? , ? )", myConnection)
'Dim IDParmD As OleDbParameter = mCom1.DeleteCommand.Parameters.Add("@ID", OleDbType.Integer)
Dim IDParmD As new OleDbParameter("@ID", OleDbType.Integer)
IDParmD.SourceVersion = DataRowVersion.Original
IDParmD.SourceColumn = "ID"
mCom1.DeleteCommand.Parameters.Add(IDParmD)
Dim ForeNameParm As OleDbParameter = mCom1.UpdateCommand.Parameters.Add("@Forename", OleDbType.VarChar)
Dim ForeNameParmI As OleDbParameter = mCom1.InsertCommand.Parameters.Add("@Forename", OleDbType.VarChar)
ForeNameParm.SourceColumn = "Forename"
ForeNameParmI.SourceColumn = "Forename"
Dim SurNameParm As OleDbParameter = mCom1.UpdateCommand.Parameters.Add("@SurName", OleDbType.VarChar)
Dim SurNameParmI As OleDbParameter = mCom1.InsertCommand.Parameters.Add("@SurName", OleDbType.VarChar)
SurNameParm.SourceColumn = "SurName"
SurNameParmI.SourceColumn = "SurName"
Dim AddressParm As OleDbParameter = mCom1.UpdateCommand.Parameters.Add("@Address", OleDbType.VarChar)
Dim AddressParmI As OleDbParameter = mCom1.InsertCommand.Parameters.Add("@Address", OleDbType.VarChar)
AddressParm.SourceColumn = "Address"
AddressParmI.SourceColumn = "Address"
Dim TelephoneParm As OleDbParameter = mCom1.UpdateCommand.Parameters.Add("@Telephone", OleDbType.VarChar)
Dim TelephoneParmI As OleDbParameter = mCom1.InsertCommand.Parameters.Add("@Telephone", OleDbType.VarChar)
TelephoneParm.SourceColumn = "Telephone"
TelephoneParmI.SourceColumn = "Telephone"
Dim FaxParm As OleDbParameter = mCom1.UpdateCommand.Parameters.Add("@Fax", OleDbType.VarChar)
Dim FaxParmI As OleDbParameter = mCom1.InsertCommand.Parameters.Add("@Fax", OleDbType.VarChar)
FaxParm.SourceColumn = "Fax"
FaxParmI.SourceColumn = "Fax"
Dim EmailParm As OleDbParameter = mCom1.UpdateCommand.Parameters.Add("@Email", OleDbType.VarChar)
Dim EmailParmI As OleDbParameter = mCom1.InsertCommand.Parameters.Add("@Email", OleDbType.VarChar)
EmailParm.SourceColumn = "Email"
EmailParmI.SourceColumn = "Email"
Dim WebsiteParm As OleDbParameter = mCom1.UpdateCommand.Parameters.Add("@Website", OleDbType.VarChar)
Dim WebsiteParmI As OleDbParameter = mCom1.InsertCommand.Parameters.Add("@Website", OleDbType.VarChar)
WebsiteParm.SourceColumn = "Website"
WebsiteParmI.SourceColumn = "Website"
Dim IDParm As OleDbParameter = mCom1.UpdateCommand.Parameters.Add("@ID", OleDbType.Integer)
IDParm.SourceVersion = DataRowVersion.Original
IDParm.SourceColumn = "ID"
'Try
myConnection.Open()
'First Process Deletes
mCom1.Update(permanentdata.Tables("Clients").Select(Nothing, Nothing, DataViewRowState.Deleted))
' Next process updates.
' Catch ex As ArgumentException
' ClientUpdater = "Delete Error: " & "/l!!!!!!" & ex.targetsite.ToString
'Finally
myConnection.Close()
'End Try
AddHandler mCom1.RowUpdated, New OleDbRowUpdatedEventHandler(AddressOf OnClientRowUpdated)
Try
myConnection.Open()
' Next process updates.
mCom1.Update(permanentdata.Tables("Clients").Select(Nothing, Nothing, DataViewRowState.ModifiedCurrent))
' Finally, process inserts.
mCom1.Update(permanentdata.Tables("Clients").Select(Nothing, Nothing, DataViewRowState.Added))
Catch ex As Exception
ClientUpdater = "Client Error: " & "/l!!!!!!" & ex.ToString
Finally
myConnection.Close()
End Try
End Function
I'm sorry -- since I cannot run your code, there's really no way to even begin to determine what's up. If you can get the behavior with a sample we can actually run (for example, with a database everyone has, like Northwind), that would really help people here to help you.
Offhand, I don't personally know what's up. Perhaps someone else might. But you need to help us help you, by providing a sample that demonstrates the problem in a way we can demonstratehere. If possible.
You might try, for fun, the same code against a different version of Jet, or against a SQL Server database, just to verify if it's a Jet problem, or a coding problem, or a bug in ADO.NET.
The problem, for anyone who is interested was that the webservice was passing a dataset to the dataadapter update command. What happened was the webservice contained a public dataset variable, called "permanentdata", and the update function worked like this:
function update(Byval ds as dataset)
permanentdata = ds
'functions which perform deletes, updates etc on individual datatables in teh dataset
Individualtableupdates()
end function
The whole lot worked when this webservice funciton was changed to:
function update(Byval ds as dataset)
permanentdata = ds.copy()
'functions which perform deletes, updates etc on individual datatables in teh dataset
Individualtableupdates()
end function
Thanks for all attempts at help.
Steve
