RE:

Question

RE:

rohini-145252

Old Hand

Points: 328
More actions
August 8, 2005 at 10:03 pm

#60162

Is it possible to call this stored procedure from VB 6.0 ?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246988 More actions · Answer 1

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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

rohini-145252 Old Hand Points: 328 More actions · Answer 2

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

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

rohini-145252 Old Hand Points: 328 More actions · Answer 4

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?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

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.

rohini-145252 Old Hand Points: 328 More actions · Answer 6

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Ninja's_RGR'us

SSC Guru

Points: 294069

August 10, 2005 at 10:27 pm

#581273

I don't really care, it's the same copy/paste .