Solved

Correct Syntax for SQL statement

Posted on 2011-03-09
9
561 Views
Last Modified: 2012-05-11
I am trying to put a stored procedure in my code in VBA , but for some reason I can't figure the proper syntax to return the value.
I am able to get the result when I run in SQL but I can' t run it in my VBA code.

this is what I have in my sql server query:
DECLARE @I_sCompanyID smallint, @O_mNoteIndex numeric(19,5), @O_iErrorState int;
select @I_sCompanyID = CMPANYID from DYNAMICS..SY01500 where INTERID = 'TEST4';
exec DYNAMICS.[dbo].[smGetNextNoteIndex] @I_sCompanyID, 1, @O_mNoteIndex output,
@O_iErrorState output; SELECT @O_mNoteIndex

and my vba code is below , but when I try to get my value , it always return 1 instead of
711.00000

what am I missing?

thanks
David
Private Sub ButSave_Click()
Dim intReturn As Integer

    'Retrieve an ADO Connection for the current user
    Set cn = UserInfoGet.CreateADOConnection
    'Set the current Database company
    cn.DefaultDatabase = UserInfoGet.IntercompanyID
    cmd.ActiveConnection = cn

'Get next note id and then insert batch record
strsql = "DECLARE @I_sCompanyID smallint, @O_mNoteIndex numeric(19,5), @O_iErrorState int; " & _
         "select @I_sCompanyID = CMPANYID from DYNAMICS..SY01500 where INTERID = 'TEST4'; " & _
         "exec DYNAMICS.[dbo].[smGetNextNoteIndex] @I_sCompanyID, 1, @O_mNoteIndex output, " & _
         "@O_iErrorState output; SELECT @O_mNoteIndex"

cmd.CommandText = strsql
cmd.Execute intReturn
MsgBox (intReturn)





End Sub

Open in new window

SQLScript.JPG
0
Comment
Question by:taverny
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 17

Expert Comment

by:Daniel Reynolds
ID: 35089068
Why not just call the stored procedure?

strsql = "exec myStoredProcedure 'param1','param2'"
 
0
 

Author Comment

by:taverny
ID: 35090190
i thought that was what I was doing ,
I am actually trying to follow the info from an other question that I have open , and one expert told me to do it this way.
it seems to work in SQL but I guess I don't know how to store the value returned from this SQL statment
here is the original post:
http://www.experts-exchange.com/Microsoft/Applications/Microsoft_Dynamics/Q_26843202.html
0
 

Author Comment

by:taverny
ID: 35118761
can someone help me on getting the value back from the sql statment?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Expert Comment

by:judgeking
ID: 35140422
I've attached an example of how I call a SP and get the output.  Basically, you have to define the SP parms, and whether it's an input or output.

But using your code above, you may be able to get the result by changing the code to this:

'(change the last bit of your SQL to SELECT @O_mNoteIndex AS NoteIndex
Dim oRs 'returned recordset

Set oRs = cmd.Execute(intReturn) 'intReturn only tells us how many rows were affected
On error resume next
MsgBox oRs(0) 'Not sure if index starts at 0 or 1
MsgBox oRs(1) 'Not sure if index starts at 0 or 1
MsgBox oRs("NoteIndex") 'May be able to use the output name if you make the change above


Const adCmdStoredProc   = 4
  Const adParamInput      = 1
  Const adParamOutput     = 2
  Const adInteger         = 3
  Const adDate            = 7
  Const adVarChar         = 200

  Set oCommand = CreateObject("ADODB.Command")
  
  oCommand.ActiveConnection = Conn
  oCommand.CommandTimeout = 600
  oCommand.CommandType = adCmdStoredProc
  oCommand.CommandText = "spAddReprintTransaction"
  oCommand.Parameters.Append oCommand.CreateParameter("@EntryID", adInteger, adParamInput, , EntryID)
  oCommand.Parameters.Append oCommand.CreateParameter("@DistribType", adVarChar, adParamInput, 50, DistribType)
  oCommand.Parameters.Append oCommand.CreateParameter("@FaxNumber", adVarChar, adParamInput, 50, FaxNumber)
  oCommand.Parameters.Append oCommand.CreateParameter("@UserName", adVarChar, adParamInput, 50, UserName)
  oCommand.Parameters.Append oCommand.CreateParameter("@FailureID", adInteger, adParamInput, , FailureID)
  oCommand.Parameters.Append oCommand.CreateParameter("@TransID", adInteger, adParamOutput)
  oCommand.Execute
  lTransID = oCommand.Parameters("@TransID")

Open in new window

0
 

Author Comment

by:taverny
ID: 35142666
Thank you for your reponse . I am gonna try that now.
0
 

Author Comment

by:taverny
ID: 35150727
at the same time that you reponded another expert also responded to my other questions. I will post his answer in here.
but since you seem to understand very well stored procedure , may be you can explain me a little more your code so I can try to do it on my own the next time it happens again.
this is what I understand so far if I look at your code:
line 1 thru 6 create constant with the value specified on the right
line 8 thru 11  we create a connection to the server

then that's where I am not following , line 12 13 , I am not sure if it is a defined name or it is a stored procedure.
then the following append the new constant to the stored procedure, how do you know what the stored procedure expect from the code.

thanks for your helpl, but I am very new to store procedure
0
 
LVL 6

Accepted Solution

by:
judgeking earned 500 total points
ID: 35172205
Yes, spAddReprintTransaction is the name of a SP in my DB.  The next lines set up the parms for the SP (with values if input parms), with TransID being an output parm, that's how I retrieve the value on line 21.  When calling any SP, you have to know what parms it expects and what types of values they are (int, char, datetime, etc) or else the call will fail.

The code below might work for you, but you need to find out the name of the second parm, I've just put the name @Flag for now:
Const adCmdStoredProc   = 4
  Const adParamInput      = 1
  Const adParamOutput     = 2
  Const adSmallInt        = 2
  Const adInteger         = 3
  Const adDate            = 7
  Const adVarChar         = 200
  Const adNumeric         = 131

  Dim oCommand
  Dim iCompanyID, iNoteIndex
  
  Set oCommand = CreateObject("ADODB.Command")  
  Set oRs = cn.Execute("select CMPANYID from DYNAMICS..SY01500 where INTERID = 'TEST4'")
  iCompanyID = oRs("CMPANYID")
  oCommand.ActiveConnection = cn
  oCommand.CommandTimeout = 600
  oCommand.CommandType = adCmdStoredProc
  oCommand.CommandText = "smGetNextNoteIndex"
  oCommand.Parameters.Append oCommand.CreateParameter("@I_sCompanyID", adSmallInt, adParamInput, , iCompanyID)
  oCommand.Parameters.Append oCommand.CreateParameter("@Flag", adInteger, adParamInput, , 1)
  oCommand.Parameters.Append oCommand.CreateParameter("@O_mNoteIndex", adNumeric, adParamOutput)
  oCommand.Parameters.Append oCommand.CreateParameter("@O_iErrorState", adInteger, adParamOutput)
  oCommand.Execute
  iNoteIndex = oCommand.Parameters("@O_mNoteIndex")

Open in new window

0
 

Author Comment

by:taverny
ID: 35234816
Thanks for the response.
I will probably need your help in the near future regarding stored procedure , but thanks for your help already.
0
 
LVL 6

Expert Comment

by:judgeking
ID: 35235265
No problem, thanks for the 'accept'.  
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
On Sep 22nd 2014 Microsoft released Update Rollup 1 for Microsoft Dynamics CRM 2013 Service Pack 1 and back in July Update Rollup 3 was released.  So we now have:   Update Rollup 1Update Rollup 2Update Rollup 3Service Pack 1Update Rollup 1 for S…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question