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
