Current Recordset does not support updating - HELP needed!

[Hexman]

I've come up with an error which the solution eludes me. I get the
error:

An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in HRTest.exe

Additional information: Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

It occurs when I attempt to add a new record. I've stripped out much
of the code, leaving the pertinent (I hope) info. I'm using MS Access
2003 as the database.

The error appears to be quite self-explanatary, but I've read about
the locktype an it seems correct. The "Current Recordset" I'm unsure
of. I just want to find out if the record is on file and if not, add
it, otherwise I'll update it.

Sounds simple, but I need help.

Thanks,

Hexman

---------------------------------------------------------------------------------------------------
Imports System.Data.OleDb
'
'
'
Public Class Form1

Inherits System.Windows.Forms.Form
'
'
'
Public db As ADODB.Connection
Public C As ADODB.Command
Public POrstData As ADODB.Recordset

Private Sub DoUpdate()

'make connection and open database
db = New ADODB.Connection

db.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & txtDatabaseDir & "HRMDB.MDB" &
";Persist Security Info=False"

db.Open()

C = New ADODB.Command
C.ActiveConnection = db
C.CommandType = ADODB.CommandTypeEnum.adCmdText

'create and open record set

POrstData = New ADODB.Recordset
POrstData.Open("PurOrd", db,
ADODB.CursorTypeEnum.adOpenkeyset,
ADODB.LockTypeEnum.adLockOptimistic, True)

End Sub

Private Sub UpdateProcess()

C.CommandText = "Select PODate, POLoc, POItem FROM PurOrd " & _
"where PODate = #" & SelectedDate & "# and POLoc = '" & strLoc & "' "
& _
"AND POItem = " & CInt(strItem)

POrstData = C.Execute

If POrstData.EOF = True Then
POrstData.AddNew() <======= Fails with Error =====
POrstData.Fields.Item("PODate").Value = "#" & strDate & "#"
POrstData.Fields.Item("POLoc").Value = strLoc
POrstData.Fields.Item("POItem").Value = CInt(strItem)
POrstData.Update()
Else
MsgBox("Record Found: #" & SelectedDate & "# " & strLoc & " "
& CInt(strItem))
End If

End Sub

 

[Chris]

I've come up with an error which the solution eludes me. I get the
error:




It occurs when I attempt to add a new record. I've stripped out much
of the code, leaving the pertinent (I hope) info. I'm using MS Access
2003 as the database.

The error appears to be quite self-explanatary, but I've read about
the locktype an it seems correct. The "Current Recordset" I'm unsure
of. I just want to find out if the record is on file and if not, add
it, otherwise I'll update it.

Sounds simple, but I need help.

Thanks,

Hexman

---------------------------------------------------------------------------------------------------
Imports System.Data.OleDb
'
'
'
Public Class Form1

Inherits System.Windows.Forms.Form
'
'
'
Public db As ADODB.Connection
Public C As ADODB.Command
Public POrstData As ADODB.Recordset

Private Sub DoUpdate()

'make connection and open database
db = New ADODB.Connection

db.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & txtDatabaseDir & "HRMDB.MDB" &
";Persist Security Info=False"

db.Open()

C = New ADODB.Command
C.ActiveConnection = db
C.CommandType = ADODB.CommandTypeEnum.adCmdText

'create and open record set

POrstData = New ADODB.Recordset
POrstData.Open("PurOrd", db,
ADODB.CursorTypeEnum.adOpenkeyset,
ADODB.LockTypeEnum.adLockOptimistic, True)

End Sub

Private Sub UpdateProcess()

C.CommandText = "Select PODate, POLoc, POItem FROM PurOrd " & _
"where PODate = #" & SelectedDate & "# and POLoc = '" & strLoc & "' "
& _
"AND POItem = " & CInt(strItem)

POrstData = C.Execute

If POrstData.EOF = True Then
POrstData.AddNew() <======= Fails with Error =====
POrstData.Fields.Item("PODate").Value = "#" & strDate & "#"
POrstData.Fields.Item("POLoc").Value = strLoc
POrstData.Fields.Item("POItem").Value = CInt(strItem)
POrstData.Update()
Else
MsgBox("Record Found: #" & SelectedDate & "# " & strLoc & " "
& CInt(strItem))
End If

End Sub

Why are you using old ADO and not ADO.NET?

Chris

 

[Hexman]

Why are you using old ADO and not ADO.NET?

Chris

Its what I've had examples of. Please point me in the ADO.NET
direction. I need examples (add, delete, query, update, etc.)

Thanks,

Hexman

 

[Guest]

Look in the .net Framework SDK... it has several examples of ado.net

http://msdn.microsoft.com/netframework/

 

[Hexman]

OK,

I looked at the examples in the SDK and other internet sources --- and
now I'm even more confused. I wish there was an example of someone
reading a transaction file and updating a table (add, update & delete
based on the transaction type). I've seen ASP code, C code & VB code
with ADO classic and ADO.NET, some with SQL, OleDB, ODBC, .....

Don't know which way to turn - just trying to get this task finished
(along with being guided down the correct path of learning about
this).

I've seen so much it really bluring. I thought my approach in the
code below was a good solution, but apparently not.

I need more help.

Can someone guide me closer to the objective?

Thanks,

Hexman

 

[Tiny Tim]

OK,

I looked at the examples in the SDK and other internet sources --- and
now I'm even more confused. I wish there was an example of someone
reading a transaction file and updating a table (add, update & delete
based on the transaction type). I've seen ASP code, C code & VB code
with ADO classic and ADO.NET, some with SQL, OleDB, ODBC, .....

Don't know which way to turn - just trying to get this task finished
(along with being guided down the correct path of learning about
this).

I've seen so much it really bluring. I thought my approach in the
code below was a good solution, but apparently not.

I need more help.

Can someone guide me closer to the objective?

Thanks,

Hexman