We help IT Professionals succeed at work.

Calling Stored procedure

jc64 asked
I want an easy way of calling stored procedure from VB.

Here is my first attempt

Private Sub Form_Load()
Dim rs As New ADODB.Record
Dim objCon As New ADODB.Connection
Dim cmd As New ADODB.Command

objCon.Open "DSN=xapidsn;UID=sa;PWD="

cmd.ActiveConnection = objCon
cmd.CommandText = "dbo.testproc syslogs" 'the stored pro and the paramter
cmd.CommandType = adCmdStoredProc
MsgBox cmd("id")

End Sub

here is my stored procedure and it takes one parameter.

create procedure dbo.testproc(
@objectName varchar(50)
select * from Sysobjects where name = @objectName

I know how to build parameters using cmd.Paramters.Append for each paramter but I don't want to build all the paramters as I want to call stored procedure with a lot of paramters for testing.

Thank you
Watch Question

This is the way I do it

Param2="Order by Date"
objCon.Execute "dbo.testproc '" & Param1 & "','" & Param2 & "'"


I used the following line to get the recoreds.
rs.Open "dbo.testproc syslogs", objCon, adOpenForwardOnly, adLockReadOnly, adCmdStoredProc

I just included the parameter values after the stoted proc name But thanks for your comment.

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