We help IT Professionals succeed at work.

Calling Stored procedure

jc64
jc64 asked
on
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
cmd.Execute
 
MsgBox cmd("id")

End Sub


here is my stored procedure and it takes one parameter.



create procedure dbo.testproc(
@objectName varchar(50)
)
as
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
Comment
Watch Question

Commented:
This is the way I do it

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

Author

Commented:
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.