Results 1 to 6 of 6

Thread: Problems using ADODB

  1. #1

    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
    50
    Posts
    8,281
    Blog Entries
    2
    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 Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Cattura.JPG 
Views:	6 
Size:	104.6 KB 
ID:	9824  
    Attached Files Attached Files
    www.thinbasic.com | www.thinbasic.com/community/ | psch.thinbasic.com
    Win10Pro 64bit - 8GB Ram - Intel i7 M620 2.67GHz - NVIDIA Quadro FX1800M 1GB

  3. #3
    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
    50
    Posts
    8,281
    Blog Entries
    2
    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/ | psch.thinbasic.com
    Win10Pro 64bit - 8GB Ram - Intel i7 M620 2.67GHz - NVIDIA Quadro FX1800M 1GB

  5. #5
    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
    50
    Posts
    8,281
    Blog Entries
    2
    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/ | psch.thinbasic.com
    Win10Pro 64bit - 8GB Ram - Intel i7 M620 2.67GHz - NVIDIA Quadro FX1800M 1GB

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

Posting Permissions

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