Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 35

Thread: ADODB Question

Share/Bookmark
  1. #11
    Dear Eros,

    Everything is good. I'm just so curious.

    Greetings
    Axel

  2. #12
    thinBasic author ErosOlmi's Avatar
    Join Date
    Sep 2004
    Location
    Milan - Italy
    Age
    50
    Posts
    8,115
    Blog Entries
    2
    Rep Power
    10
    Dear Axel,

    in Italy we say "better late than nothing". Hope this is the case in this situation

    Find here attached thinBasic_ADODB.dll in which I have developed a sord of wrapper of internal ADODB errors.
    Please unzip in \thinbasic\Lib\ replacing your current one


    Developed function are here summarized:
          '---To check if there is ana arror, always use connection and Errors.Count method
          if pConn.Errors.Count then
            '---Print info of at least first error. there can be more than one
            printl "Error number..:", pConn.Errors(1).number
            printl "Description...:", pConn.Errors(1).Description
            printl "Source........:", pConn.Errors(1).Source
            printl "SQLState......:", pConn.Errors(1).SQLState
            printl "NativeError...:", pConn.Errors(1).NativeError
            
            '---Clear internal errors collection. Otherwise errors collection will accumulate errors in case execution could continue
            pConn.Errors.Clear
          end if
    
    ...
    
        '---To check if there is ana arror, always use connection and Errors.Count method
        if pConnection.Errors.Count then
          '---Show all errors in one go. 
          '---This function also clear internal errors collection
          printl pConnection.Errors.Msg
        end if
    

    Here below an example on how to use new error functions. Example simulate adding a new record that violate table primary key duplication.

    Uses "Console"
    Uses "ADODB"
    
    
    Dim pConnection   As New ADODB_CONNECTION
    Dim sConn         As String
    
    
      '----------------------------------
      Function Add_New_Record(byval pConn as adodb_connection, byval sISBN as string, byval lYear as long, byval sTitle as string)
      '----------------------------------
        Dim pRS As New ADODB_RECORDSET
        
        pRS.Open "Titles", pConn, %ADOPENKEYSET, %ADLOCKOPTIMISTIC, %ADCMDTABLE 
        if pRS.STATE = %ADSTATEOPEN Then
            pRs.UpdateS("ISBN")           = sISBN '& " " & Timer'"ich 4711" 
            pRs.UpdateN("Year Published") = lYear'2016  
            pRs.UpdateS("Title")          = sTitle'"Mein erster satz"
          pRs.addnew
          'pRs.update
    
    
          'printl AdoDb_GetErrorInfo(pConn)
    
    
          PrintL "Add new" In %CCOLOR_FLIGHTRED
    
    
          '---To check if there is ana arror, always use connection and ErrorsCount method
          if pConn.Errors.Count then
            '---Print info of at least first error. there can be more than one
            printl "Error number..:", pConn.Errors(1).number
            printl "Description...:", pConn.Errors(1).Description
            printl "Source........:", pConn.Errors(1).Source
            printl "SQLState......:", pConn.Errors(1).SQLState
            printl "NativeError...:", pConn.Errors(1).NativeError
            
            '---Clear internal errors collection. Otherwise errors collection will cumulate errors in case execution could continue
            pConn.Errors.Clear
            
          end if
    
    
          printL "  pRecordSet.Close         :", pRS.CLOSE
          
        end if
    
    
      End Function
    
    
      '---------------------------------------------------------------
      ' Connection
      '---------------------------------------------------------------
    
    
        sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & APP_SourcePath & "biblio.mdb"
    
    
        '---Set connection string
        pConnection.ConnectionString = sConn
    
    
        PrintL "-Connection------------------------------------------"
        PrintL "  Opening ..."                    , pConnection.OPEN'(sConn) 
        PrintL "  pConnection.Connectionstring:"  , pConnection.Connectionstring
        PrintL "  pConnection.State           :"  , pConnection.State
        PrintL "  pConnection.Version         :"  , pConnection.Version
    
    
        PrintL "-Press a key to continue------------------------------" In %CCOLOR_FYELLOW
    
    
        '---To check if there is ana arror, always use connection and ErrorsCount method
        if pConnection.Errors.Count then
          '---Show all errors in one go. 
          '---This function also clear internal errors collection
          printl pConnection.Errors.Msg
        end if
    
    
    
    
        If pConnection.STATE = %ADSTATEOPEN Then
      
          Add_New_Record(pConnection, "999999999", 9999, "Eros Test " & date$(2) & " " & time$(0))
          
    
    
          PrintL "-Closing press a key-------------------------------------------" In %CCOLOR_FYELLOW
          'WaitKey
        
          PrintL "  pConnection.Close        :", pConnection.CLOSE
    
    
        Else
          PrintL "-It was not possible to open a connection-" In %CCOLOR_FLIGHTRED
        End If
    
    
        PrintL
        PrintL "-Press a key to finish-------------------------------"
        WaitKey
    
    Attached Files Attached Files
    Last edited by ErosOlmi; 28-12-2016 at 16:55.
    www.thinbasic.com | www.thinbasic.com/community/ | psch.thinbasic.com
    Win10Pro 64bit - 8GB Ram - Intel i7 M620 2.67GHz - NVIDIA Quadro FX1800M 1GB

  3. #13
    thinBasic author ErosOlmi's Avatar
    Join Date
    Sep 2004
    Location
    Milan - Italy
    Age
    50
    Posts
    8,115
    Blog Entries
    2
    Rep Power
    10
    I've updated module and examples in previous post because I changed 3 functions making them more ... OOP like.
    If someone has already downloaded it, please download it again and change the following ADODBConnection.Errors. ... methods


    ------OLD-----------------------------------------NEW----------------------
    
    <ADODBConnection>.ErrorsCount is now -----> <ADODBConnection>.Errors.Count
    <ADODBConnection>.ErrorsClear is now -----> <ADODBConnection>.Errors.Clear
    <ADODBConnection>.ErrorsMsg   is now -----> <ADODBConnection>.Errors.Msg
    
    Last edited by ErosOlmi; 28-12-2016 at 17:01.
    www.thinbasic.com | www.thinbasic.com/community/ | psch.thinbasic.com
    Win10Pro 64bit - 8GB Ram - Intel i7 M620 2.67GHz - NVIDIA Quadro FX1800M 1GB

  4. #14
    Hi Eros,

    great job. My first tests were successful and I would get the expected results.
    In the next few days I'll do more tests and inform about the results.

    Kind Regards,

    Axel

  5. #15
    Hi Eros,

    I have done more tests and what i see let me smile. Insert, update, delete is working and the error checking works also fine.
    But there is one thing I don't anderstand. If there was an error, eg missing value for a mandetory field for a new record, than
    the recordset.close returns 1 an d not 0. at the moment I don't anderstand that.

    my testcode:
    ' Empty GUI script created on 01-03-2017 12:50:16 by  (thinAir)
    Uses "Console"
    Uses "ADODB"
     
     
    Dim pConnection   As New ADODB_CONNECTION
    Dim sConn         As String
    Dim sSql          As String 
     
      '----------------------------------
      Function Add_New_Record(ByVal pConn As ADODB_CONNECTION, ByVal sISBN As String, ByVal lYear As Long, ByVal sTitle As String)
      '----------------------------------
        Dim pRS As New ADODB_RECORDSET
        pRs.CursorLocation = %ADUSECLIENT
        sSql = "select * from Titles where ISBN = " & "'" & sISBN & "';"
        PrintL ssql
        
       'pRS.Open "Titles", pConn, %ADOPENKEYSET, %ADLOCKOPTIMISTIC, %ADCMDTABLE 
       pRs.OPEN sSql, pConn, %ADOPENDYNAMIC, %ADLOCKOPTIMISTIC, %ADCMDTEXT         
    
        If pRS.State = %ADSTATEOPEN Then  
        PrintL pRs.RecordCount
        If pRS.RecordCount = 1 Then
            pRs.UpdateS("ISBN")           = sISBN '& " " & Timer'"ich 4711" 
            pRs.UpdateN("Year Published") = lYear'2016  
            pRs.UpdateS("Title")          = sTitle'"Mein erster satz"
            pRs.update
          '---To check if there is ana arror, always use connection and ErrorsCount method
          If pConn.Errors.Count Then  
            PrintL "--> Fehler nach update" In %CCOLOR_FYELLOW
            PrintL "Error number..:", pConn.Errors(1).Number
            PrintL "Description...:", pConn.Errors(1).Description
            PrintL "Source........:", pConn.Errors(1).Source
            PrintL "SQLState......:", pConn.Errors(1).SQLState
            PrintL "NativeError...:", pConn.Errors(1).NativeError
            pConn.Errors.Clear                     
          End If              
     
          PrintL "update" In %CCOLOR_FLIGHTRED
         End If
     
     If pRS.RecordCount = 0 Then 
            pRs.addnew
            pRs.UpdateS("ISBN")           = sISBN '& " " & Timer'"ich 4711" 
            pRs.UpdateN("Year Published") = lYear'2016  
            pRs.UpdateS("Title")          = sTitle'"Mein erster satz"
            pRs.UpdateS("PubID")          = 69
            pRs.update
          '---To check if there is ana arror, always use connection and ErrorsCount method
          If pConn.Errors.Count Then  
            PrintL "--> Fehler nach insert" In %CCOLOR_FYELLOW
            PrintL "Error number..:", pConn.Errors(1).Number
            PrintL "Description...:", pConn.Errors(1).Description
            PrintL "Source........:", pConn.Errors(1).Source
            PrintL "SQLState......:", pConn.Errors(1).SQLState
            PrintL "NativeError...:", pConn.Errors(1).NativeError
            pConn.Errors.Clear                     
          End If              
     
          PrintL "insert" In %CCOLOR_FLIGHTRED
         End If
     
          
       
     
     
          PrintL "  pRecordSet.Close         :", pRS.CLOSE
           
        End If
     
     
      End Function
     
     
      '---------------------------------------------------------------
      ' Connection
      '---------------------------------------------------------------
     
     
        sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & APP_SourcePath & "biblio.mdb"
     
     
        '---Set connection string
        pConnection.ConnectionString = sConn
     
     
        PrintL "-Connection------------------------------------------"
        PrintL "  Opening ..."                    , pConnection.OPEN'(sConn) 
        PrintL "  pConnection.Connectionstring:"  , pConnection.Connectionstring
        PrintL "  pConnection.State           :"  , pConnection.State
        PrintL "  pConnection.Version         :"  , pConnection.Version
     
     
        PrintL "-Press a key to continue------------------------------" In %CCOLOR_FYELLOW
     
     
        '---To check if there is ana arror, always use connection and ErrorsCount method
        If pConnection.Errors.Count Then
          '---Show all errors in one go. 
          '---This function also clear internal errors collection
          PrintL pConnection.Errors.Msg
        End If
     
     
     
     
        If pConnection.State = %ADSTATEOPEN Then
       
          Add_New_Record(pConnection, "999999900", 9999, "Axels Test " & Date$(2) & " " & Time$(0))
           
     
     
          PrintL "-Closing press a key-------------------------------------------" In %CCOLOR_FYELLOW
          'WaitKey
         
          PrintL "  pConnection.Close        :", pConnection.CLOSE
     
     
        Else
          PrintL "-It was not possible to open a connection-" In %CCOLOR_FLIGHTRED
        End If
     
     
        PrintL
        PrintL "-Press a key to finish-------------------------------"
        WaitKey
    

  6. #16
    thinBasic author ErosOlmi's Avatar
    Join Date
    Sep 2004
    Location
    Milan - Italy
    Age
    50
    Posts
    8,115
    Blog Entries
    2
    Rep Power
    10
    Ok, thanks

    I will check this evening and let you know.
    www.thinbasic.com | www.thinbasic.com/community/ | psch.thinbasic.com
    Win10Pro 64bit - 8GB Ram - Intel i7 M620 2.67GHz - NVIDIA Quadro FX1800M 1GB

  7. #17
    Hi Eros,

    last weekend I have a bit of testing and have found no further problems.
    But I have a little wish list:
    Recordset method .find and
    Connection method .execute

    kind regards,

    Axel

  8. #18
    thinBasic author ErosOlmi's Avatar
    Join Date
    Sep 2004
    Location
    Milan - Italy
    Age
    50
    Posts
    8,115
    Blog Entries
    2
    Rep Power
    10
    Ciao Axel,

    sorry for the delay but I was in bed for a health problem now ok.

    Regarding <Recordset>.Close ... in reality that method does not return any value in standard ADO interface.
    I developed it to return <Recordset>.State after Closing it.
    So internally Close is the following:
          '----------------------------------------------------------------------------
          ' Close
          '----------------------------------------------------------------------------
          Method cADODB_RecordSet_Close() As Dword
            pRecordSet.Close
            Method = pRecordSet.State
          End Method
    
    Possible values are like the following: http://www.w3schools.com/asp/prop_rs_state.asp
    Maybe it's not a great idea to check Close return value.

    Regarding new requests ... I'm already developing them.
    <RecordSet>.Find is already done.
    <Connection>.Execute is quite complex because it returns a <RecordSet> object and I need to understand how to implement this behavior in thinBasic.
    Something like:
    MyRecordsSet = MyConnection.Execute(...)
    
    I will release a new module as soon as done.

    And after that I will work on implementing ADODB_Command interface.
    Last edited by ErosOlmi; 10-01-2017 at 22:22.
    www.thinbasic.com | www.thinbasic.com/community/ | psch.thinbasic.com
    Win10Pro 64bit - 8GB Ram - Intel i7 M620 2.67GHz - NVIDIA Quadro FX1800M 1GB

  9. #19
    Hi Eros,

    if I use the function Add_New_Record a second time with other parameter I have the following error:
    Click image for larger version. 

Name:	2017-03-30 13_23_59-thinBasic __ Script RunTime error!.png 
Views:	10 
Size:	12.1 KB 
ID:	9664.

    Any idear???

    Brds,

    Axel

  10. #20
    thinBasic author ErosOlmi's Avatar
    Join Date
    Sep 2004
    Location
    Milan - Italy
    Age
    50
    Posts
    8,115
    Blog Entries
    2
    Rep Power
    10
    I have to check.
    I will try this evening and let your know.
    www.thinbasic.com | www.thinbasic.com/community/ | psch.thinbasic.com
    Win10Pro 64bit - 8GB Ram - Intel i7 M620 2.67GHz - NVIDIA Quadro FX1800M 1GB

Page 2 of 4 FirstFirst 1234 LastLast

Similar Threads

  1. Question of the day ;)
    By Petr Schreiber in forum thinBasic General
    Replies: 8
    Last Post: 23-08-2010, 19:58
  2. C to TB question
    By Michael Clease in forum Other languages
    Replies: 2
    Last Post: 03-06-2010, 12:11
  3. gdi question
    By Lionheart008 in forum UI (User Interface)
    Replies: 6
    Last Post: 07-12-2009, 19:31
  4. UDT question
    By sandyrepope in forum thinBasic General
    Replies: 3
    Last Post: 18-02-2008, 22:33
  5. m15 question
    By kryton9 in forum M15 file format
    Replies: 4
    Last Post: 20-06-2007, 20:18

Posting Permissions

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