RE:
-
Is it possible to call this stored procedure from VB 6.0 ?
-
Eh? Was this supposed to be part of another thread? If not: "Which stored procedure?"
If your question is just: "Can stored procedures be called from VB6?", the answer is yes. Just send the exec [procname(,,)] statement in the same way as you would any other SQL statement.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
This page came just when i clicked the 'discuss about this article' after reading the article 'Using Parameters with Stored Procedures' today.
The stored procedure has output parameters and eg. was given to call it from query analyzer.But i wanted to know,if this can be called in VB6.
The stored procedure is
CREATE PROC usp_AddTwoIntegers @FirstNumber int = 5,
@SecondNumber int, @Answer varchar(30) OUTPUT as
Declare @sum int
Set @sum = @FirstNumber + @SecondNumber
Set @Answer = 'The answer is ' + convert(varchar,@sum)
Return @sum
-
It would be something like this :
Private Function exec_usp_AddTwoIntegers(ByVal FirstNumber As Integer, ByVal SecondNumber As Integer, ByRef Answer As String, Optional ByRef ReturnValue As Integer) As Integer
On Error GoTo Gestion
Dim MyCmd As ADODB.Command
Set MyCmd = New ADODB.Command
MyCmd.CommandText = "dbo.usp_AddTwoIntegers"
MyCmd.CommandType = adCmdStoredProc
Dim MyParam As ADODB.Parameter
Set MyParam = New ADODB.Parameter
MyParam.Direction = adParamReturnValue
MyParam.Name = "@Return"
MyParam.Type = adInteger
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@FirstNumber"
MyParam.Value = FirstNumber
MyParam.Size = 4
MyParam.Direction = adParamInput
MyParam.Type = adInteger
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SecondNumber"
MyParam.Value = SecondNumber
MyParam.Size = 4
MyParam.Direction = adParamInput
MyParam.Type = adInteger
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@Answer"
MyParam.Value = Answer
MyParam.Size = 30
MyParam.Direction = adParamInputOutput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
MyCn.Open
MyCmd.ActiveConnection = MyCn
MyCmd.Execute exec_usp_AddTwoIntegers
MyCn.Close
ReturnValue = CInt(MyCmd.Parameters("@Return").Value)
Answer = MyCmd.Parameters("@Answer").Value
Set MyParam = Nothing
Set MyCmd = Nothing
Exit Function
Gestion:
ErrHandler ModuleName, Me.Name, "exec_usp_AddTwoIntegers", Err
MsgBox Err.Description & " : " & Err.Number
End Function
-
Thank u Remi, this worked out.
But imagine if i wanted to pass 25 parameters to my stored procedure and wanted an output parameter,then how long will be my calling the stored proc code? Is there any other way?
-
We you can shorten to create param code to 1 line, but in any case you have to set all 25 parameters. I have no problems with that anymore as I created a program to generate that code so it takes less than 1 minute to recreate the code
.You can always ship the sp to me so that I generate the code for ya.
-
Thank u for ur help.I will surely get ur help for my future sps.
Although we generate code , will that not be too many lines of code Remi?
Rohini
-
I don't really care, it's the same copy/paste
.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply