How Do I Execute a Stored Procedure From Within SalesLogix and Return the Results to a Recordset?

Question:

I’m trying to execute a stored procedure from within SalesLogix, and return the results to a recordset?  I’m running SalesLogix 7.5 SP3 LAN.  The code on the form is this:

Sub Button1Click(Sender)

Dim objSLXDB

Dim strSQL

Dim objRS

Dim Result

Dim strErr

strSQL = “exec kk ‘A6UJ9A00000N'”

Set objSLXDB = New SLX_DB

Set objRS = objSLXDB.GetNewRecordset()

objRS.Open strSQL, objSLXDB.Connection

If Not (objRS.BOF And objRS.EOF) then

msgbox “Result “ & objRS.Fields(“account”).value

End if

objRS.Close

Set objRS = Nothing

Set objSLXDB = Nothing

End Sub

And the stored procedure inside the database is this:

USE

[SLXDATABASE]

GO

SET

ANSI_NULLSON

GO

SET

QUOTED_IDENTIFIERON

GO

ALTER

PROCEDURE [sysdba].[KK]

@X

varchar(12)

AS

BEGIN

SELECT account, accountid from [sysdba].[account] where accountid =@X

return

END

 

if i execute the stored via SQL manager i get results:

exec kk ‘A6UJ9A00000N’

 

returns

TESTACCOUNT | A6UJ9A00000N

 

What i’m doing wrong?  The error comes on the line of the code:

objRS.Open strSQL, objSLXDB.Connection

Answer:

This is the correct code to achieve those results.

 Dim objRS
  Dim objCmd

  Dim strConnectionString

  Dim oCn
  Dim StrCn

  Dim DataSource
  Dim InitialCatalog
  Dim UserID
  Dim Password
  Dim Resultado

  DataSource = “SERVER”
  InitialCatalog = “DATABASE”
  UserID = “user”
  Password = “pass”
  ‘**************************

  set oCn = CreateObject(“ADODB.Connection”)
  StrCn = “Provider=sqloledb;Data Source=” & DataSource & “;Initial Catalog=” & InitialCatalog & “;User Id=” & UserID & “;Password=” & Password
  oCn.Open StrCn

  If oCn.State = adStateOpen Then

      set objRS = CreateObject(“ADODB.RecordSet”)
      set objCmd = CreateObject(“ADODB.Command”)
      strConnectionString = “exec kk ‘A6UJ9A00000N'”  ‘DNL
      objCmd.CommandText = strConnectionString
      objCmd.CommandType = adCmdText
      objCmd.ActiveConnection = oCn

      Set objRS = objCmd.Execute

      If Not (objRS.BOF And objRS.EOF) then
         ‘TIENE IMPAGOS, CAMBIO EL COLOR
         msgbox “Result = ” & objRS.Fields(“account”).value
      End if

      objRS.Close
      set objRS = Nothing
      set objCmd = Nothing

  end if

  oCn.Close
  set oCn = nothing

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!

You have Successfully Subscribed!