Announcement

Collapse
No announcement yet.

Am I missing something?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Am I missing something?

    I got my installation package for v.9.0 today. I tried it first to connect to am SQL Server database via ADO 2.8. Here is my code:

    Code:
    #COMPILE EXE
    #DIM ALL
    #INCLUDE "win32api.inc"
    #INCLUDE "ADO28.inc"   '<--- you can create it yourself
                               
    FUNCTION PBMAIN () AS LONG
    
        FUNCTION PBMAIN () AS LONG
        LOCAL oConn             AS Int__Connection
        LOCAL oComm             AS Int__Command
        LOCAL oRecordset        AS Int__Recordset
        LOCAL oRecord           AS Int__Record
        LOCAL oError            AS Int_Error
        LOCAL oConnEvents       AS ConnectionEvents
        LOCAL sConnString       AS STRING
     
        oConn = NEWCOM $PROGID_ADODB_Connection
        IF ISFALSE ISOBJECT(oConn) THEN
            MSGBOX "Unable to initialize ADO"
            EXIT FUNCTION
        END IF
        oConnEvents = CLASS "Class_ConnectionEvents"
    
        EVENTS FROM oConn CALL oConnEvents
        sConnString = ""    '<----- try with yours
        MSGBOX "Connecting " & sConnString
        CALL oConn.Open(sConnString)
        IF oConn.Errors.Count = 0 THEN
            IF oConn.State = %adStateOpen THEN
                MSGBOX "Connected"
            END IF
            CALL oConn.Close
            MSGBOX "Disconnected"
        ELSE
            MSGBOX "Failed"
        END IF
    
        EXIT FUNCTION
    
    END FUNCTION
    The code compiles and runs, but no matter which server I tried to connect using what provider I always got 'data source name too long' error (you can see that in ado28.inc in METHOD ConnectComplete as ACODE$(pError.Description). I can connect with the same connection string from VB6.

    I found this Microsoft article http://support.microsoft.com/kb/134723
    This is for the ADO Data Control and per the article the error is caused by a bug in the control that is it is changing semicolon to comma in the connection string. Is PB doing something similar? I am sending semicolons.

    Any idea what am I missing here?


    Thanks,


    Peter Redei
    Last edited by Peter Redei; 24 Aug 2008, 09:38 PM.

  • #2
    sConnString = "" must be sConnString = UCODE$("<connection string>").
    Forum: http://www.jose.it-berater.org/smfforum/index.php

    Comment


    • #3
      Two "FUNCTION PBMAIN () AS LONG" 's ?
      -

      Comment


      • #4
        Thanks

        Thanks Jose, that was it. I should have known that, I already used it many times with your code.

        Peter

        Comment


        • #5
          I strongly recommend you take a peek into the ADO's errorscollection.
          It saved me a lot of searching because it gives good info back.
          Much databases will return the correct errormessage$
          hellobasic

          Comment


          • #6
            Edwin,
            You are right and I did that. The error message was 'data source name too long'. Would you figure out from this message that you need to pass unicode string?
            In general you need to pass all strings in unicode and you need to convert to ANSI all strings you receive.
            For example here is my ExecuteQuery routine:

            Code:
            FUNCTION ExecuteQuery(conn AS Int__Connection, BYVAL sql AS STRING, OPTIONAL BYVAL dbName AS STRING) AS LONG
                DIM retries AS LONG
                DIM dbcn AS Int__Connection
                DIM strConnect AS STRING
                DIM oComm AS Int__Command
            
                IF dbName > "" THEN
                    strConnect = conn.ConnectionString & ";database=" & dbName
                    dbcn = NEWCOM $PROGID_ADODB_Connection
                    dbcn.Provider = UCODE$("MSDASQL.1")  'thanks Jose
                    dbcn.CursorLocation = %adUseServer  'Must use Server side cursor.
                    dbcn.ConnectionString = UCODE$(strConnect)
                    dbcn.Open()
                END IF
            
                oComm = NEWCOM $PROGID_ADODB_Command
            
                ON ERROR GOTO errSub
                IF dbName = "" THEN
                    oComm.ActiveConnection = conn
                ELSE
                    oComm.ActiveConnection =  dbcn
                END IF
                oComm.CommandType = %adCmdText
                [B]oComm.CommandText = UCODE$(sql)[/B]
            tryagain:
                oComm.Execute
                
                ExecuteQuery = %True
                IF dbName > "" THEN dbcn.Close
            
            xerrSub:
                ON ERROR GOTO 0
                dbcn = NOTHING
                oComm = NOTHING
                EXIT FUNCTION
            errSub:
                LOCAL errnum AS LONG
                LOCAL errdesc AS STRING
                errnum = conn.Errors.Item(0).Number
                errdesc = conn.Errors.Item(0).Description
                IF errnum = -2147217865 THEN
                    ' see Q168354 Microsoft article
                    ExecuteQuery = %True
                    RESUME NEXT
                END IF
                ExecuteQuery = %False
                RESUME xerrSub
            END FUNCTION

            Peter Redei
            Last edited by Peter Redei; 26 Aug 2008, 01:22 PM.

            Comment


            • #7
              dbcn.Provider = "MSDASQL.1" must be dbcn.Provider = UCODE$("MSDASQL.1"). If the parameter is a string, you have to use UCODE$; if it is a variant, you don't, because PB converts ansi strings to unicode when you assign them to a variant.
              Forum: http://www.jose.it-berater.org/smfforum/index.php

              Comment


              • #8
                Maybe not from that description but seeing your code.. use all items and make it one error string.
                I often get multiple lines.
                hellobasic

                Comment


                • #9
                  The error message was 'data source name too long'. Would you figure out from this message that you need to pass unicode string?
                  Several WinAPIs requiring unicode give funky error messages when they get ANSI.

                  eg,. ShFileOperation returns "path/file not found" if you give it ANSI, unless the file name length is an integral multiple of the allocation granularity (often 16), in which case it gives you "filename too long."

                  (Not fun to debug at all.)
                  Michael Mattias
                  Tal Systems (retired)
                  Port Washington WI USA
                  [email protected]
                  http://www.talsystems.com

                  Comment

                  Working...
                  X