Jump to content
Sign in to follow this  
AlainG

Call a SQL stored proc with parameter

Recommended Posts

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 by AlainG

Share this post


Link to post
Share on other sites
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 by Zedna

Share this post


Link to post
Share on other sites
AlainG

Hereafter the final code

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.