Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Problems using ADODB

  1. #1
    Junior Member
    Join Date
    Jan 2018
    Location
    Surrey, UK
    Posts
    8
    Rep Power
    7

    Problems using ADODB

    Hi,

    I have recently started using ThinBasic and have been attempting to use the ADODB module to read data from ACCESS and ORACLE database tables. I am able to connect to the databases and run some SQL and using the RecordSet ObjectCount I can see that rows are being returned but as soon as I try and access the data using the Fields(x).Name and Fields(x).Value a pop-up appears saying ‘thinbasic.exe has stopped working’ and if I then select Debug ‘An unhandled WIN32 exception occurred in thinbasic.exe (10040)’. It is running on Windows 7 32 bit.

    An example script below – this gets to the line ‘Console_WriteLine(objRecordSet.Fields.Count)’ and then the pop-up appears.

    Has anyone else had this issue?

    Uses "ADODB"
    Uses "ADO"
    Uses "Console"

    Dim objConnection As ADODB_CONNECTION
    Dim objRecordSet As ADODB_RECORDSET
    Dim ConnectionDetails, SQL As String
    Dim SegmentNames(9999) As String
    Dim SegmentCt As Integer
    Dim FieldCt As Integer

    ConnectionDetails = "Provider = Microsoft.ACE.OLEDB.12.0; " & _
    "Data Source = h:\My Documents\Access\Table Space History.accdb"

    objConnection = New ADODB_CONNECTION
    objRecordSet = New ADODB_RECORDSET

    objConnection.Open(ConnectionDetails)

    SQL = "Select * from SegmentNames"

    objRecordSet.Open SQL, objConnection, %ADOPENSTATIC, %ADLOCKOPTIMISTIC, %ADCMDTEXT

    If objRecordSet.RecordCount > 0 Then
    Console_WriteLine("Records Extracted = " & objRecordSet.RecordCount)
    Console_WriteLine(objRecordSet.Fields.Count)

    objRecordSet.MoveFirst

    Console_WriteLine(objRecordSet.Fields(1).Value)

    SegmentCt = 0
    Do Until objRecordSet.EOF
    SegmentCt = SegmentCt + 1
    Console_WriteLine(SegmentCt)
    SegmentNames(SegmentCt) = objRecordSet.Fields(2).Value
    objRecordSet.MoveNext
    Loop
    Else
    Console_WriteLine("No records extracted")
    End If

    objRecordSet.Close
    objConnection.Close


    Thanks

    Adam

  2. #2
    thinBasic author ErosOlmi's Avatar
    Join Date
    Sep 2004
    Location
    Milan - Italy
    Age
    57
    Posts
    8,777
    Rep Power
    10
    Ciao Adam,

    what thinBasic version are you using? I've recently changed a lot ADODB module, so please download and install thinBasic 1.10.4 from here: http://www.thinbasic.com/community/s...inBasic-1-10-x

    I've tryed to simulate your script and here it seems to work fine with few little changes:
    Uses "ADODB"Uses "Console"
    
    
    Dim objConnection As ADODB_CONNECTION
    Dim objRecordSet As ADODB_RECORDSET
    Dim ConnectionDetails, SQL As String
    Dim SegmentNames(9999) As String
    Dim SegmentCt As Integer
    Dim FieldCt As Integer
    
    
    ConnectionDetails = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " & app_sourcepath & "Table Space History.accdb"
    
    
    objConnection = New ADODB_CONNECTION
    objRecordSet = New ADODB_RECORDSET
    
    
    objConnection.Open(ConnectionDetails)
    
    
    '---If connection is open then Executes a command and print some data
    If objConnection.State = %ADSTATEOPEN Then
    
    
      SQL = "Select * from SegmentNames"
    
    
      objRecordSet.Open SQL, objConnection, %ADOPENSTATIC, %ADLOCKOPTIMISTIC, %ADCMDTEXT
    
    
      If objRecordSet.RecordCount > 0 Then
        Console_WriteLine("Records Extracted = " & objRecordSet.RecordCount)
        Console_WriteLine(objRecordSet.Fields.Count)
    
    
        objRecordSet.MoveFirst
    
    
        Console_WriteLine(objRecordSet.Fields(1).Value)
    
    
        SegmentCt = 0
        while Not objRecordSet.EOF
          SegmentCt = SegmentCt + 1
          printl SegmentCt
          SegmentNames(SegmentCt) = objRecordSet.Fields(2).Value
          for FieldCt = 1 to objRecordSet.Fields.Count
            printl objRecordSet.Fields(FieldCt).Name , "=", objRecordSet.Fields(FieldCt).Value
          Next
          objRecordSet.MoveNext
        Wend
      Else
        Console_WriteLine("No records extracted")
      End If
    
    
      objRecordSet.Close
      objConnection.Close
    Else
    
    
      long nError
      If objConnection.Errors.Count Then 
        PrintL "--> Error happened:" In %CCOLOR_FLIGHTRED
        For nError = 1 to objConnection.Errors.Count
          PrintL "Error number..:", objConnection.Errors(nError).Number
          PrintL "Description...:", objConnection.Errors(nError).Description
          PrintL "Source........:", objConnection.Errors(nError).Source
          PrintL "SQLState......:", objConnection.Errors(nError).SQLState
          PrintL "NativeError...:", objConnection.Errors(nError).NativeError
        Next
        '---Clear all errors so new errors will restart to fill Errors collection
        objConnection.Errors.Clear
      End If
    
    
    end If
    
    
    printl "---Press a key to end---"
    WaitKey
    
    Attached the script and an access DB I've tried to replicate to simulate your script

    Let me know.
    Eros
    Attached Images Attached Images
    Attached Files Attached Files
    www.thinbasic.com | www.thinbasic.com/community/ | help.thinbasic.com
    Windows 10 Pro for Workstations 64bit - 32 GB - Intel(R) Xeon(R) W-10855M CPU @ 2.80GHz - NVIDIA Quadro RTX 3000

  3. #3
    Junior Member
    Join Date
    Jan 2018
    Location
    Surrey, UK
    Posts
    8
    Rep Power
    7
    Hi Eros,

    Thanks for that - yes it does look like I had downloaded an old(ish) version of ThinBasic (1.9.16.17).

    I've now downloaded 1.10.4.0 and the ADODB File(x).Name and File(x).Value now work.

    I do have one other query now though:

    The value of <recordset>.EOF is set to 0 whilst there are records to be read but returns a value of 4294967295 at end of file so when I try and use 'Do Until objRecordSet.EOF = %TRUE' this is never triggered. Is this correct?


    Thanks

    Adam

  4. #4
    thinBasic author ErosOlmi's Avatar
    Join Date
    Sep 2004
    Location
    Milan - Italy
    Age
    57
    Posts
    8,777
    Rep Power
    10
    Try using

    While Not rs.EOF
    ...
    Wend
    
    Like in my Example and see if it works.
    www.thinbasic.com | www.thinbasic.com/community/ | help.thinbasic.com
    Windows 10 Pro for Workstations 64bit - 32 GB - Intel(R) Xeon(R) W-10855M CPU @ 2.80GHz - NVIDIA Quadro RTX 3000

  5. #5
    Junior Member
    Join Date
    Jan 2018
    Location
    Surrey, UK
    Posts
    8
    Rep Power
    7
    Yup that has done the trick.

    Thanks for your help.


    Adam

  6. #6
    thinBasic author ErosOlmi's Avatar
    Join Date
    Sep 2004
    Location
    Milan - Italy
    Age
    57
    Posts
    8,777
    Rep Power
    10
    Good.

    If you want to use DO/LOOP use DO While ...

    Do While Not objRecordSet.EOF
    Loop
    
    I will check why Do Until objRecordSet.EOF is not working.
    I suppose objRecordSet.EOF is not returning real TRUE/FALSE
    www.thinbasic.com | www.thinbasic.com/community/ | help.thinbasic.com
    Windows 10 Pro for Workstations 64bit - 32 GB - Intel(R) Xeon(R) W-10855M CPU @ 2.80GHz - NVIDIA Quadro RTX 3000

  7. #7
    Junior Member
    Join Date
    Jan 2018
    Location
    Surrey, UK
    Posts
    8
    Rep Power
    7

    More ADODB oddness

    Hello Again.

    I have been attempting to write some code to access an Access database using ThinBasic but have been receiving a strange error.

    The program was attempting to open a table (recordset) run some SQL and retrieve the results and then using a different selection run the same query but this second attempt at running the SQL causes the error 'Assignment type not supported. Something is wrong with variable assignment.' to be shown (below).

    AssignmentError.JPG

    This is the script I ended up with to test the connection - just a simple loop but the second time 'objRecordSet.Open' is issued the error is shown.

    Uses "UI"
    Uses "ADODB"

    Dim objConnection As ADODB_CONNECTION
    Dim objRecordSet As ADODB_RECORDSET
    Dim a As Integer
    Dim ConnectionDetails, SQL As String
    Dim segct As Integer
    Dim segmentname(9999) As String
    Dim segmentid(9999) As Integer

    ConnectionDetails = "Provider = Microsoft.ACE.OLEDB.12.0; " & _
    "Data Source = h:\My Documents\Access\ATCORE Table Space History.accdb"

    objConnection = New ADODB_CONNECTION
    objRecordSet = New ADODB_RECORDSET

    objConnection.Open(ConnectionDetails)

    For a = 1 To 3

    SQL = "Select * from segmentnames"

    objRecordSet.Open SQL, objConnection, %ADOPENSTATIC, %ADLOCKOPTIMISTIC, %ADCMDTEXT

    If objRecordSet.RecordCount > 0 Then
    SegCt = 0
    objRecordSet.MoveFirst
    While Not objRecordSet.EOF
    Incr SegCt

    SegmentName(SegCt) = objRecordSet.Fields(2).Value
    SegmentID(SegCt) = objRecordSet.Fields(1).Value

    objRecordSet.MoveNext
    Wend
    MsgBox "Loop " & a
    End If

    objRecordSet.Close

    Next

    Is there something I should be doing?

    This on Version 1.10.4.0 (Windows 7 64 bit).


    Thanks

    Adam Counsell

  8. #8
    thinBasic author ErosOlmi's Avatar
    Join Date
    Sep 2004
    Location
    Milan - Italy
    Age
    57
    Posts
    8,777
    Rep Power
    10
    Checking ...
    www.thinbasic.com | www.thinbasic.com/community/ | help.thinbasic.com
    Windows 10 Pro for Workstations 64bit - 32 GB - Intel(R) Xeon(R) W-10855M CPU @ 2.80GHz - NVIDIA Quadro RTX 3000

  9. #9
    thinBasic author ErosOlmi's Avatar
    Join Date
    Sep 2004
    Location
    Milan - Italy
    Age
    57
    Posts
    8,777
    Rep Power
    10
    Ciao Adam,

    to solve, please add open and close parentheses to objRecordSet.Open in this way:
    objRecordSet.Open(SQL, objConnection, %ADOPENSTATIC, %ADLOCKOPTIMISTIC, %ADCMDTEXT)
    
    I still need to understand why first time loop works and from second time on it gives error.
    I suppose it is an optimization step I do the first time I parse the source code that is not correct.

    Let me know if it works for you too.
    Eros
    Last edited by ErosOlmi; 11-05-2018 at 14:13.
    www.thinbasic.com | www.thinbasic.com/community/ | help.thinbasic.com
    Windows 10 Pro for Workstations 64bit - 32 GB - Intel(R) Xeon(R) W-10855M CPU @ 2.80GHz - NVIDIA Quadro RTX 3000

  10. #10
    Junior Member
    Join Date
    Jan 2018
    Location
    Surrey, UK
    Posts
    8
    Rep Power
    7
    Hi Eros,

    Yes that is now working.

    Thanks for your help.


    Adam

Page 1 of 2 12 LastLast

Similar Threads

  1. ADODB Question
    By axelmoe in forum thinBasic General
    Replies: 34
    Last Post: 24-04-2017, 12:46
  2. Problems / ThinAir
    By Petr Schreiber in forum Preview testing
    Replies: 7
    Last Post: 15-09-2008, 10:39
  3. Forum Problems
    By matthew in forum General
    Replies: 4
    Last Post: 29-06-2007, 15:01

Members who have read this thread: 0

There are no members to list at the moment.

Posting Permissions

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