Am I missing something?

[Peter Redei]

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:

#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

[José Roca]

sConnString = "" must be sConnString = UCODE$("<connection string>").

[Wayne Diamond]

Two "FUNCTION PBMAIN () AS LONG" 's ?

[Peter Redei]

Thanks

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

Peter

[Edwin Knoppert]

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$

[Peter Redei]

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:

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

[José Roca]

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.

[Edwin Knoppert]

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

[Michael Mattias]

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.)