Access SQL Server

[Nick]

I'm trying to access the SwyxCallDetails table on our SQL Server.

The connection does work but the query at the end doesn't and I don't know why.

 

I'm positive that I've missed something but u don't know what. 

 

The End Goal is to get the DB entry for the correspondent CakllID into the "Query" Variable 

 

Function EMAIL(CalledID)

    PBXScript.OutputTrace "--------->EMAIL() Called"

    Dim Connection
    Set Connection = CreateObject("ADODB.Connection")
    

    Dim ConString 
    ConString = "Provider=SQLOLEDB;Server=##;database=SwyxCallDetails;User ID=IpPbxCDR_User;Password=##"

    PBXScript.OutputTrace "----------->" & ConString

    Connection.Open ConString


    if Connection.State = 1 Then
        PBXScript.OutputTrace "-------->Connected"
    Else
        PBXScript.OutputTrace "-------->Not Connected"
    End if


    Dim SqlString
    SqlString = "SELECT CalledNumber FROM dbo.IpPbxCDR WHERE CallId = '" & CallId & "'"

    PBXScript.OutputTrace "--------->" & SqlString


    Dim Query 
    Query = CreateObject("ADODB.Recordset")
    PBXScript.OutputTrace "---------> Query object created"
    Query.Open SqlString, Connection
    PBXScript.OutputTrace "--------> Query Connected"


    EMAIL = Query

    Query.Close
    Connection.Close

End Function

 

[Tom Wellige]

There is a "Set" missig in front of

 

Query = CreateObject("ADODB.Recordset")

 

Also, please take a look into the CheckCallerInDatabase example from the Function Collection to see how you can do error tracing and error handling.

 

When dealing with external data sources (e.g. files or databases) this is sort of mandatory.

 

 

Your function returns at the end a closed recordset which you will not be able to access anymore. You should do all the needed data handling within your function, so you will be able to close the recordset in the end properly without anything else fails afterwards because of this.