We help IT Professionals succeed at work.

how to call a SQL Server stored procedure in VB using ADO

Bob Butcher
Bob Butcher asked
Could someone please provide a snippet of code on how I would call a stored procedure in SQL Server using VB and the ADO command object. A bried comment of each step would be very, very helpful.

Not sure how to tie everything together.

Thanks in advance.

Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Here is one example.

 Dim objConn, objCmd, objParam
set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DSN=macdb;uid=test;pwd=test"
Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.CommandText = "sp_sel_no_authors"
objCmd.CommandType = adCmdStoredProc
objcmd.ActiveConnection = objConn
Set objParam = objCmd.CreateParameter ("@count_authors",adInteger,adParamOutput,4)
objCmd.Parameters.Append objParam

you can also take the full details about this issue from below link.

Look at the code snippet
'ConnectionObject is the connection object to database
    Set commnd = New ADODB.Command
    commnd.ActiveConnection = ConnectionObject
    commnd.CommandType = 4
    commnd.CommandTimeout = 180
    commnd.CommandText = "Insertvalues"
'@EMPID is the SP parameter name
'adInteger is the Type of @EMPID (Int)
'adParamInput is to say that this is input parameter
'4 is the length for the type
'var_EmpID is the varaible in my vb code which contains value to be passed
    commnd.Parameters.Append commnd.CreateParameter("@EmpID", adInteger, adParamInput, 4, var_EmpID)
    commnd.Parameters.Append commnd.CreateParameter("@Name", adVarChar, adParamInput, 50, var_Name)
    commnd.Parameters.Append commnd.CreateParameter("@FirstName", adVarChar, adParamInput, 100, var_FirstName)

Open in new window

Explore More ContentExplore courses, solutions, and other research materials related to this topic.