• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

Stored procedure in VB

I have a stored procedure that I want to call from my VB program. I have two ways of programming it and I want to know the difference. Mostly it is thew way the parameters are set.

**************** Method 1 String Parameters*********  

cmd.CommandText = "AlltestProc " & b & ", '', '', " & f & ", ''"
cmd.CommandType = adCmdStoredProc


+++++++++++++ Method 2 Using Command object ++++++++++++

  cmd.CommandText = "AlltestProc " & b & ", '', '', " & f & ", ''"
  cmd.CommandType = adCmdStoredProc
'  cmd.Parameters.Append cmd.CreateParameter("pint", adInteger, adParamInput, , 1)
'  cmd.Parameters.Append cmd.CreateParameter("pVarchar", adVarChar, adParamInput, 4, "This")
'  cmd.Parameters.Append cmd.CreateParameter("pChar", adChar, adParamInput, 3, "OKY")
'  cmd.Parameters.Append cmd.CreateParameter("pTinyInt", adTinyInt, adParamInput, , 3)
'  cmd.Parameters.Append cmd.CreateParameter("pReturn", adVarChar, adParamOutput, 30, "")
  Set rs = cmd.Execute


The first method seams to be easier.

1 Solution
The second method is the best way of doing this. You are specifying the correct types to pass to the Stored Procedure so there will be no ambiguity when they are passed into it.

You can also use SQL Execute to call your procedure, like

Set rs = con.Execute("EXECUTE sp_name, paramnumber, 'paramtext', moreparams")

This returns a forward-only cursor and may not be applicable for all situations but it's quick and easy.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now