Adodb converting to Sqlite issue-VBForums
Results 1 to 2 of 2

Thread: Adodb converting to Sqlite issue

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Feb 2009
    Location
    Uk
    Posts
    156

    Adodb converting to Sqlite issue

    I'm moving my database from access to sqlite - however i'm having issues with converting the following:

    Code:
                  Dim connectStr = (Strings.DBConnectstrings)
            Dim strm As New ADODB.Stream
            Dim con As New ADODB.Connection
            Dim rs As New ADODB.Recordset
            Dim stmt As String = New String("")
            stmt = "SELECT *  FROM exam WHERE [Section] = '" & section & "'"
    
            Try
                con.Open(connectStr)
                '  rs = con.Execute(stmt)
                rs.Open(stmt, con, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, &H1)
                ' con.Close()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
    I dont know what sqlite statements to use to replace the ADODB recordset

    So far:
    Code:
                  Dim con As New SQLiteConnection(Strings.DBConnectstrings.ToString)
            Dim rs As New ADODB.Recordset
            Dim sql As String
            Sql = "SELECT *  FROM exam WHERE [Section] = '" & section & "'"
            Dim adapter As New SQLiteDataAdapter(sql, con)
            Dim dt As New DataTable("exam")
            Try
                adapter.Fill(dt)
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
    However i get an error
    System.Runtime.InteropServices.COMException (0x800A0E78): Operation is not allowed when the object is closed.
    at ADODB.RecordsetClass.Move(Int32 NumRecords, Object Start)
    Can anyone point me in the right direction

    Full sub:
    Code:
        Private Sub getDbRecord(ByVal section As String)
    
            Dim connectStr = (Strings.DBConnectstrings)
            Dim strm As New ADODB.Stream
            Dim con As New ADODB.Connection
            Dim rs As New ADODB.Recordset
            Dim stmt As String = New String("")
            stmt = "SELECT *  FROM exam WHERE [Section] = '" & section & "'"
    
            Try
                con.Open(connectStr)
                '  rs = con.Execute(stmt)
                rs.Open(stmt, con, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, &H1)
                ' con.Close()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
    
            Dim i
            Dim randList = getRandList(QnosEachSection)
            ' MsgBox(randList.Count)
            For i = 0 To randList.count - 1
                Dim x = randList.Item(i)
    
                rs.Move(x)
                Dim question As Question = New Question
                question.section = rs.Fields().Item(1).Value
                question.question = rs.Fields().Item(2).Value
                question.option1 = rs.Fields().Item(3).Value
                question.option2 = rs.Fields().Item(4).Value
                question.option3 = rs.Fields().Item(5).Value
                question.option4 = rs.Fields().Item(6).Value
                question.answer = rs.Fields().Item(7).Value
                'Added for regs
                question.regulation = rs.Fields().Item(9).Value
                'added for attachment
                If rs.Fields(8).ActualSize > 0 Then
                    question.attachment = rs.Fields(8).Value
                    question.hasAttachment = True
                Else
                    question.hasAttachment = False
                End If
    
                qList.Add(question)
                '   MsgBox(question.ToString)
                rs.MoveFirst()
            Next
    
        End Sub
    Last edited by experience; Dec 10th, 2012 at 06:36 PM.

  2. #2
    PowerPoster Nightwalker83's Avatar
    Join Date
    Dec 2001
    Location
    Adelaide, Australia
    Posts
    13,095

    Re: Adodb converting to Sqlite issue

    My guess is that close the connection which in-turn terminate the recordset!

    Try it like so:

    Move those to the top of the form/module
    vb.net Code:
    1. Dim i
    2.         Dim x = randList.Item(i)
    3.         Dim randList = getRandList(QnosEachSection)

    vb.net Code:
    1. Try
    2.             con.Open(connectStr)
    3.             '  rs = con.Execute(stmt)
    4.             rs.Open(stmt, con, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, &H1)
    5.  
    6.         ' MsgBox(randList.Count)
    7.         For i = 0 To randList.count - 1
    8.            
    9.  
    10.             rs.Move(x)
    11.             Dim question As Question = New Question
    12.             question.section = rs.Fields().Item(1).Value
    13.             question.question = rs.Fields().Item(2).Value
    14.             question.option1 = rs.Fields().Item(3).Value
    15.             question.option2 = rs.Fields().Item(4).Value
    16.             question.option3 = rs.Fields().Item(5).Value
    17.             question.option4 = rs.Fields().Item(6).Value
    18.             question.answer = rs.Fields().Item(7).Value
    19.             'Added for regs
    20.             question.regulation = rs.Fields().Item(9).Value
    21.             'added for attachment
    22.             If rs.Fields(8).ActualSize > 0 Then
    23.                 question.attachment = rs.Fields(8).Value
    24.                 question.hasAttachment = True
    25.             Else
    26.                 question.hasAttachment = False
    27.             End If
    28.  
    29.             qList.Add(question)
    30.             '   MsgBox(question.ToString)
    31.             rs.MoveFirst()
    32.         Next
    33.             ' con.Close()
    34.         Catch ex As Exception
    35.             MsgBox(ex.Message)
    36.         End Try
    Last edited by Nightwalker83; Dec 10th, 2012 at 09:31 PM. Reason: fixed spelling!
    when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
    If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
    Please consider giving me some rep points if I help you a lot.
    Links to my code examples can now be found on my website: My websites
    Please rate my post if you find it helpful!
    Technology is a dangerous thing in the hands of an idiot! I am that idiot.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.