Call a SQL stored proc with parameter

[AlainG]

Hi all,

I need to call a stored proc in a SQL server DB.

The proc get an input param and send back 3 values.

I wrote a Vbs script and try to do the same with AUtoIt.

The vbs code is

objconn.ConnectionString = "driver={SQL Server};server=ServerName;uid=User;pwd=Pass;database=DB"

objconn.Open

Set objCmd = CreateObject("ADODB.Command")

objCmd.ActiveConnection = objConn

objCmd.CommandText = "Proc_Name"

objCmd.CommandType = 4 'adCmdStoredProc

'Add Input Parameters

objCmd.Parameters.Append objCmd.CreateParameter("@UserLogin", adVarChar, adParamInput, 20, strUserLogin)

' Exécution de la requête

Set objRecordSet = objCmd.Execute

What I wrote in AutoIt

; Instantiate objects

$objConn = ObjCreate("ADODB.Connection")

$objRecordSet = ObjCreate("ADODB.Recordset")

$objConn.Open("driver={SQL Server};server=ServerName;uid=User;pwd=Pass;database=DB")

$strSqlQuery = "ProcName " & $strUserLogin

$objRecordSet.Execute($strSqlQuery,$objConn,$adOpenKeyset,$adLockOptimistic,$adCmdStoredProc)

but the Recordset.execute stops with the following message :

The requested action with this object has failed

I found samples of stored procedure call but without params.

Someone to help me ?

Help me if you can I'm feeling down

And I do appreciate you being round

Edited July 8, 2011 by AlainG

[Zedna]

You missed objCmd = CreateObject("ADODB.Command")

Set objCmd = CreateObject("ADODB.Command")

objCmd.ActiveConnection = objConn

objCmd.CommandText = "Proc_Name"

objCmd.CommandType = 4 'adCmdStoredProc

...

EDIT: Here is my older answer to similar question with links for help

Edited July 9, 2011 by Zedna

[AlainG]

Hereafter the final code

expandcollapse popup

Dim $objConn, $objCmd, $objRecordSet
Dim $strUserLogin, $strPrenomSponsor, $StrNomSponsor, $StrAdrMailSponsor
Dim $strConnexion = "DRIVER={SQL Server};SERVER=serverName;DATABASE=dbName;uid=UserName;pwd=UserPwd"

    $strUserLogin = "User.Login"

    ; Instantiate objects
    $objConn = ObjCreate("ADODB.Connection")
    $objconn.Open($strConnexion)

    $objCmd = ObjCreate("ADODB.Command")
    $objCmd.ActiveConnection = $objConn
    $objCmd.CommandText = "ProcName" 
    $objCmd.CommandType = $adCmdStoredProc

    ; Add Input Parameters
    $objCmd.Parameters.Append ($objCmd.CreateParameter("@UserLogin", $adVarChar, $adParamInput, 20, $strUserLogin))

    ; request execution
    $objRecordSet = ObjCreate("ADODB.Recordset")
    $objRecordSet = $objCmd.Execute

    If $objRecordset.BOF = True OR $objRecordset.EOF = True Then
        MsgBox (0, "Test", "Unexisting user")
    Else
        $strPrenom = $objRecordset.Fields("FirstName").value
    $strNom = $objRecordset.Fields("Name").value
    $StrAdrMail = $objRecordset.Fields("Email").value
    
    If $strPrenom = "" OR $strNom  ="" OR $StrAdrMail = ""  Then
       MsgBox (0, "Test", "Undefined values")
    Else
       MsgBox (0, "Test", $strPrenom)
       MsgBox (0, "Test", $StrNom)
       MsgBox (0, "Test", $StrAdrMail)    
       EndIf
    EndIf

    $objConn.Close

I couldn't manage to make this version using Output parameters work :

; Add Output Parameters
    $objCmd.Parameters.Append ($objCmd.CreateParameter("@Prenom", $adVarChar, $adParamOutput, 20))
    $objCmd.Parameters.Append ($objCmd.CreateParameter("@Nom", $adVarChar, $adParamOutput, 20))
    $objCmd.Parameters.Append ($objCmd.CreateParameter("@eMail", $adVarChar, $adParamOutput, 60))

    $objCmd.Execute
  
    $strPrenom = $objCmd.Parameters("@Prenom").Value
    $strNom = $objCmd.Parameters("@Nom").Value
    $StrAdrMail = $objCmd.Parameters("@eMail").Value

Anyway, thanks for your help.