move previouse record in VB.net

[khwo]

Hi All,

i facing problem here, the following is my coding for move previouse recored.

'Open connection
ConnCust = New ADODB.Connection
Cust_String = "Provider=Microsoft.JET.OLEDB.4.0;data source=\\AAA\ABC.mdb"
ConnCust.Open(Cust_String)
'Open recordset from Customer List
RstCust = New ADODB.Recordset
Last_SQL = "SELECT * FROM CustomerList WHERE CustomerID <= " & lblCustID.Text & " ORDER BY CustomerID"
RstCust = ConnCust.Execute(Last_SQL)
With RstCust
If Not .EOF And Not .BOF Then
If RstCust("CustomerID").Value = lblCustID.Text Then
.MovePrevious()
If .BOF Then
MsgBox("Begining of record list!", vbExclamation, "Customer Entry Record")
.MoveFirst()
End If
End If
Call Retrieve_Data()
Else
.Close()
ConnCust.Close()
'load first record
Call Load_First_Saved_Record()
Exit Sub
End If
Call Record_Exist()
'Close recordset
.Close()
End With
'Close connection
ConnCust.Close()
'Dereference recordset and connection
RstCust = Nothing
ConnCust = Nothing

when i try to running the program there have error in
.MovePrevious() line. The error message are: -

An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in ABC.exe
Additional information: Operation is not allowed in this context.

can anyone help me in this? thanks.

[williamrojas78]

Hi

Why don't you try something different: evaluate the number of records instead of identifying if ou are at the beginning or at the end of the recordset.

another thing is that the recordst will start at the beginning, so if the first action is move to the previous record, you will get an error.

If there are records, then move to the previous one, but make sure you go to the last one first.

'Open connection
        ConnCust = New ADODB.Connection
        Cust_String = "Provider=Microsoft.JET.OLEDB.4.0;data source=\\AAA\ABC.mdb"
        ConnCust.Open(Cust_String)
        'Open recordset from Customer List
        RstCust = New ADODB.Recordset
        Last_SQL = "SELECT * FROM CustomerList WHERE CustomerID <= " & lblCustID.Text & " ORDER BY CustomerID"
        RstCust = ConnCust.Execute(Last_SQL)

With RstCust

if RstCust.RecordCount = 0 then
.Close()
ConnCust.Close()
'load first record
Call Load_First_Saved_Record()
Exit Sub
end if

If RstCust("CustomerID").Value = lblCustID.Text Then
if not .BOF            
.Movelast()
.MovePrevious()
else
MsgBox("Begining of record list!", vbExclamation, "Customer Entry Record")
End If
End If
Call Retrieve_Data()
end if
end with

i think that will do it.

regards

[khwo]

hi williamrojas78,

I have use your code but still cannot work. still error there. do u have any code that to call the previouse record? if yes can u give me some example? thanks.

the error are:-

when i try to running the program there have error in .MoveLast() and .MovePrevious() line. The error message are: -

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

Additional information: Rowset does not support fetching backward.

can give some advice?

[williamrojas78]

I just tried the code here and it works. I don't understand.

The ADO component that you added to your references: is it a .NET type or a Com type?

I thinks that is your problem, maybe the COM component does not support the methods. Try selecting the .NET component instead.

mine is located at:
C:\Program Files\Microsoft.NET\Primary Interop Assemblies\adodb.dll

regards

[khwo]

hi williamrojas78,

i try to add the .NET component which located at:
C:\Program Files\Microsoft.NET\Primary Interop Assemblies\adodb.dll but still cannot work.

inside my project i have the following reference:-
1) adodb1
2) CrystallDecisions.CrystallReports.Engine
3) CrystallDecisions.ReportSOurce
4) CrystallDecisions.Shared
5) System
6) System.Data
7) System.Drawing
8) System.Windows.Forms
9) System.XML

and inside my form i have imports the following thing:-
1) Imports System.Data
2) Imports System.Data.OleDb
3) Imports ADODB

and i have 2 variable which are:-
Dim ConnCust As ADODB.Connection
Dim RstCust As ADODB.Recordset

which mistake i have done? pls give some advice. thanks.

[williamrojas78]

Hi

Try using this connection instead:

'define this variable where connection and recordset variables are defined
dim cmdCommand As New ADODB.Command



'---------------------------------------------------------------
ConnCust = New ADODB.Connection

ConnCust.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\AAA\ABC.mdb;Mode=Read|Write"
ConnCust.CursorLocation = ADODB.CursorLocationEnum.adUseClient
ConnCust.Open()


With cmdCommand
            .let_ActiveConnection(conConnectionVh)
            .CommandText = "SELECT * FROM CustomerList ;" 
            .CommandType = ADODB.CommandTypeEnum.adCmdText
End With

With RstCust
            .CursorType = ADODB.CursorTypeEnum.adOpenStatic
            .CursorLocation = ADODB.CursorLocationEnum.adUseClient
            .LockType = ADODB.LockTypeEnum.adLockOptimistic
            .Open(cmdCommand)
End With

I think the commandtext for your connection is blocking this option.
Other than that i don't know.

Hope it helps.