use stored procedure from inside program

I have a very long sql query that i made into a stored procedure.
ii don't know how to call the stored procedure. i need to pass 3 parameters to it.

this is how the query was run from the program:

        strSql = ""
        strSql = strSql & " " & _
                 "   SELECT    1 level , " & _
                 "             ord.order_id , " & _
                 "             ord.client , " & _
                 "             ord.line_no , " & _
                 "             ' ' dispatch_date , " & _
                 "             -1 item_owner , " & _
                 "             ' ' stock_location , " & _
                 "             ' ' return_action , " & _
                 "             ' ' item_class " & _
                 "     FROM    asodetail ord " & _
                 "    WHERE    ord.order_id = '" & m_strOrderNumber & "' " & _
                 "      AND    ord.client = '" & m_strClient & "' " & _
                 "      AND    ord.order_id NOT IN ( SELECT order_id FROM bretheader WHERE client = '" & m_strClient & "' ) "
        strSql = strSql & " UNION ALL " & _
                 "   SELECT    2 level , " & _
                 "             ord.order_id , " & _
                 "             ord.client , " & _
                 "             ord.line_no , " & _
             
                 "             ' ' return_action , " & _
                 "             ' ' item_class " & _
                 "     FROM    asodetail ord LEFT JOIN algrelvalue fly ON ( ord.article_id = fly.article_id " & _
                 "                                                      AND ord.client = fly.client " & _
                 "                                                      AND fly.rel_attr_id = 'V7' ) " & _
                 "                           LEFT JOIN bhjpackageviewhighjump hj ON ( ord.order_id = hj.order_id " & _
                 "                                                             AND ord.client = hj.client " & _
                 "                                                             AND hj.job_id = " & m_lngHighJumpToAgressoJobId & ") "
        strSql = strSql & " " & _
                 "                           LEFT JOIN algudispatch dis ON ( ord.order_id = dis.order_id " & _
                 "                                                       AND ord.article_id = dis.article_id " & _
                 "                                                       AND dis.status = 4 ) " & _
                 "    WHERE    (    (     COALESCE ( CASE WHEN fly.rel_value = ' ' THEN 'NA' ELSE fly.rel_value END , 'NA' ) = 'NA' " & _
                 "                    AND ord.kit_type = '2' ) " & _
                 "               OR ( ord.kit_type = '0' ) ) " & _
                 "      AND    ord.order_id = '" & m_strOrderNumber & "' " & _
                 "      AND    ord.client = '" & m_strClient & "' "
             
   
     Set rstSelect = m_cnnAgresso.Execute(strSql)



the name of the procedure and its parameters is:

proc_get_order_details ( @order_id int, @client char(2),  @job_id int)

itortuAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nike_golfCommented:
I'm currently using the following to run a stored procedure on Mysql:

'Execute stored procedures on MYSQL DB to populate smaller table sizes for faster loading
 sql = "Call cardnos('" & Format(Date - 14, "yyyy-mm-dd") & "')"

in your case I'm guessing it might look like the following:

...
sql = "Call proc_get_order_details ('" & order_id & "','" &  client & "','" & job_id & "')"
conn.Execute sql, , adCmdText
...

NG,
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
itortuAuthor Commented:
i am doing it this way


strSql = ""
strSql = "Call proc_get_order_details ('" & m_strOrderNumber & "','" & m_strClient & "','" & m_lngHighJumpToAgressoJobId & "')"
Set rstSelect = m_cnnAgresso.Execute(strSql)

does it look correct to you?


                         
0
VBRocksCommented:
Here's how you can do it with VB6, this will return a recordset for you to work with, let me know if you have VB.NET:

-Note that you have to add a reference to Microsoft ActiveX Data Objects Library:

Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command

Dim param1 as new ADODB.Parameter
Dim param2 as new ADODB.Parameter
Dim param3 as new ADODB.Parameter

Dim numRecs As Integer  'number of records affected

cn.ConnectionString = "Connection String"
cn.Open

cmd.ActiveConnection = cn
cmd.CommandText = "proc_get_order_details"
cmd.CommandType = adCmdStoredProc

'Create parameters for the stored procedure
Set param1= cmd.CreateParameter("@OrderNumber", adVarChar, adParamInput, 10, m_strOrderNumber)
Set param2 = cmd.CreateParameter("@Client", adVarChar, adParamInput, 50, m_strClient)
Set param3 = cmd.CreateParameter("@HiJump2Aggresso", adVarChar, adParamInput, 20, m_lngHighJumpToAgressoJobId)
     
'Add parameters to cmd object
cmd.Parameters.Append param1
cmd.Parameters.Append param2
cmd.Parameters.Append param3

     
'Execute the command and return a recordset
set rst = cmd.Execute(numRecs)

   If numRecs > 0 Then
      'process with your recordset (rst)...

   End If
     
'Close the connection
cn.Close
     
'Cleanup
Set cmd = Nothing

Hope that helps!
0
itortuAuthor Commented:
I got it to work doing it like this:

strSql = "proc_get_order_details '" & m_strOrderNumber & "', '" & m_strClient & "', " & m_lngHighJumpToAgressoJobId
Set rstSelect = m_cnnAgresso.Execute(strSql)


I was formating the string incorrectly.
0
nike_golfCommented:
Glad it worked.

NG,
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

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.