Calling stored procedure from sql

[collib]

I have created a stored proceure in SQL called sp_add_client, it has 4 parameters (Surname, Given, Middle, Birthday). I have connected to the SQl db using ADO,

adoConn.ConnectionString = "driver={SQL Server};" & _
"server=qaserver\sql2000;uid=sa;pwd=me4wsql;database=chris"
adoConn.Open

how do I now call this stored procedure and pass arguments into it so I can add a new client to db.

Thanks in advance

 

[TomKane]

This is what we do:

strSQL = "USP_GET_ACCOUNT_FOR_ACTION" _
& " @INFIXEDDATE='" & dtFixedEndDate & "'," _
& " @INDATE='" & dtLocalWorkDate & "'," _
& " @INTIMEPERIODCODE=" & intLocalTimePeriodId & "," _
& " @INFROMWORKPROCESSCODE=" & lngLocalSelectedWorkProcessFromCode & "," _
& " @INTOWORKPROCESSCODE=" & lngLocalSelectedWorkProcessToCode & "," _
& " @INACCTYPE='" & strAccountTermType & "'," _
& " @INJOBLIST='" & strLocalListJobRoles & "'," _
& " @INFROMJOBTYPECODE='" & strLocalSelectedJobTypeFromCode & "'," _
& " @INTOJOBTYPECODE='" & strLocalSelectedJobTypeToCode & "'"
Set adorsLocal = AccessADO.Execute(strSQL)

Hope it helps

 

[collib]

Thanks one last question, can you explain this line to me

Set adorsLocal = AccessADO.Execute(strSQL)

I assume adorsLocal is the recordsest, but what is AccessAdo.

 

[BobRodes]

Tom's way works just fine, using the SQL end of things. To answer your last question, AccessADO looks like a Command object to me.

I don't do it his way. I use a Command object, set its CommandType property to adcmdstoredproc, use the createparameter method to add any necessary parameters to the command object's parameters collection, and execute the command, setting a recordset equal to the result of said execution. Here's some code, freshly kyped from MSDN, that illustrates this in detail:

***
Public Sub AppendX()

Dim cnn1 As ADODB.Connection
Dim cmdByRoyalty As ADODB.Command
Dim prmByRoyalty As ADODB.Parameter
Dim rstByRoyalty As ADODB.Recordset
Dim rstAuthors As ADODB.Recordset
Dim intRoyalty As Integer
Dim strAuthorID As String
Dim strCnn As String

' Open connection.
* Set cnn1 = New ADODB.Connection
* strCnn = "Provider=sqloledb;" & _
* "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
* cnn1.Open strCnn
cnn1.CursorLocation = adUseClient

' Open command object with one parameter.
* Set cmdByRoyalty = New ADODB.Command
* cmdByRoyalty.CommandText = "byroyalty" 'name of stored proc
* cmdByRoyalty.CommandType = adCmdStoredProc

' Get parameter value and append parameter.
intRoyalty = Trim(InputBox("Enter royalty:&quot)
* Set prmByRoyalty = cmdByRoyalty.CreateParameter("percentage", _
adInteger, adParamInput)
* cmdByRoyalty.Parameters.Append prmByRoyalty 'can pass intRoyalty as argument here
** prmByRoyalty.Value = intRoyalty

' Create recordset by executing the command.
** Set cmdByRoyalty.ActiveConnection = cnn1
* Set rstByRoyalty = cmdByRoyalty.Execute 'can pass cnn1 as argument here

' Open the Authors table to get author names for display.
Set rstAuthors = New ADODB.Recordset
rstAuthors.Open "authors", cnn1, , , adCmdTable

' Print current data in the recordset, adding
' author names from Authors table.
Debug.Print "Authors with " & intRoyalty & " percent royalty"
Do While Not rstByRoyalty.EOF
strAuthorID = rstByRoyalty!au_id
Debug.Print " " & rstByRoyalty!au_id & ", ";
rstAuthors.Filter = "au_id = '" & strAuthorID & "'"
Debug.Print rstAuthors!au_fname & " " & rstAuthors!au_lname
rstByRoyalty.MoveNext
Loop

rstByRoyalty.Close
rstAuthors.Close
cnn1.Close

End Sub

***

I put an asterisk in front of the lines illustrating the most important concepts.

Where I put **, the property value is a bit superfluous, in that the value can be supplied as an argument to one of the methods that open the object, e. g. the value can be supplied as an argument to the CreateParameter method. In fact, it should be done this way when possible, since persisting state unnecessarily between method calls represents a performance hit.

Bob Rodes

 

[collib]

Thanks Bob, still having problems

I have a SP in SQL called sp_add_client it has 4 parameters
@Surname varchar(70),
@Given varchar(70),
@Middle varchar(70),
@Birthday varchar(70)

I connect to db using this:

Public mconHS As ADODB.Connection
Public mcmdHS As ADODB.Command

Public Function DBConnect(strConnect As String)
Set mconHS = New ADODB.Connection
mconHS.Mode = adModeShareDenyNone
mconHS.CursorLocation = adUseServer
mconHS.ConnectionString = strConnect
mconHS.ConnectionTimeout = 30
mconHS.Open

' Find out if the attempt to connect worked.
If mconHS.State = adStateOpen Then
MsgBox "Connected to DB was established!"
Else
MsgBox "No connection to DB wasn't established."
End If

End Function

I then have a form where I add in surname, given, middle and birthdate. I then call

strSP = "sp_add_client"
Call Stored_Procedure(strSP)

Public Sub Stored_Procedure(spName As String)

Dim prmSP As ADODB.Parameter
Dim rstHS As ADODB.Recordset
Dim strSurname As String
Set mcmdHS = New ADODB.Command


' Open command object with one parameter.
mcmdHS.CommandText = spName
mcmdHS.CommandType = adCmdStoredProc
mcmdHS.CommandTimeout = 15


' Get parameter value and append parameter.
Set prmSP = mcmdHS.CreateParameter("@Surname", adVarChar, adParamOutput, 70)

mcmdHS.Parameters.Append prmSP
prmSP.Value = gSurname

' Create recordset by executing the command.
Set mcmdHS.ActiveConnection = mconHS
Set rstHS = mcmdHS.Execute 'can pass cnn1 as argument here

' Open the Authors table to get author names for display.
Set rstHS = New ADODB.Recordset
rstHS.Open "client", mconHS, , , adCmdTable

' Print current data in the recordset, adding
' author names from Authors table.
Debug.Print "Authors with " & gSurname & " percent royalty"
Do While Not rstHS.EOF
strSurname = rstHS!Client_id
Debug.Print " " & rstHS!Client_id & ", ";
'rstHS.Filter = "client_id = '" & strAuthorID & "'"
'Debug.Print rsths! & " " & rstAuthors!au_lname
rstHS.MoveNext
Loop

rstHS.Close
'rstAuthors.Close
mconHS.Close

End Sub

What am I missing. What do I do if I want to pass multiple arguments to a stored procedure.

Thanks in advance

 

[sdraper]

It appears as if you have created @SurName as an output parameter. Aren't the parameters you mention at the top input parameters to be passed to the stored proc? If so you need to create all of them as you have except adParamInput instead of output.
Set prmSP = mcmdHS.CreateParameter("@Surname", adVarChar, adParamInput, 70,"Sam&quot where Sam is the input value
You also have the lines;
Set rstHS = mcmdHS.Execute 'can pass cnn1 as argument here

' Open the Authors table to get author names for display.
Set rstHS = New ADODB.Recordset
rstHS.Open "client", mconHS, , , adCmdTable
when the first line executes, set rstHS = mcmdHS.Execute the recordset is created. You don't need
Set rstHS = New ADODB.Recordset
rstHS.Open "client", mconHS, , , adCmdTable

There may be other problems but I noticed these immediately.
Hope this helps,:->


Sam

 

[BobRodes]

Ok.

Sam's quite right. Now, the point about parameters is that the parameters collection that you create has to exactly match the expected parameters in the stored proc. So, you only create the first one, and you make it an output instead of an input as well. To pass multiple arguments, you create them and append them to the parameters collection, as you correctly did with one of them. Create the other parameters and put appropriate values in them as well.

A good way to figure out what parameters you need is to call the command.parameters.refresh method and use debug to evaluate the resulting contents. Refresh will go find the right parameters and populate the collection correctly. I don't recommend using this in production, however: it requires an extra round trip to the database and those trips are expensive in terms of performance.

HTH

Bob Rodes

 

[sdraper]

Wow, Bob you took the words right out of my mouth and made them more understandable <g> I guess that's why it says instructor next to your name and programmer next to mine. Hang in there collib! ADO is fabulous once you get a handle on it.

Sam

 

[collib]

Thanks for your help guys I was able to get the stored procedure to work.