Michael Zens 31.Aug.11 09:14 AM a Web browser LC LSXAll ReleasesWindows
Hopefully someone can help me. Basically we are connecting to a SQL server and executing a stored procedure which returns two fields. We are able to connect and execute the procedure but the resultset returned is empty and count is -1. The SQL db is trying to throw back data IE: This is the record set trying to be returned by the procedure:
Create_TIA: N
Err_Message: Error - Invalid sales outlet code
This data is not getting to the Domino database.
Running code via Action in view.
With other code in database we are getting results back, however, that code is doing basic getting column data and nothing to do with stored procedures. Below is my code which is pretty basic.
(Options)
Option Declare
Uselsx "*lsxlc"
Function validateTIA(vdoc As NotesDocument) As Boolean
Dim subTitle As String
subTitle = MODULENAME + " / function " + Cstr(Getthreadinfo(1))
On Error Goto Error_Trap
'assume failure
validateTIA = False
Dim luVu As notesView
Dim qryDoc As NotesDocument
Dim lcSess As New LCSession
Dim lcConn As LCConnection
Dim Parms As New LCFieldList 'to be sent to SQL server
Dim Result As New LCFieldList 'returned from SQL server
Dim Parm As LCField
Dim queryName As String
Dim dbName As String
Dim dbEngine As String
Dim dbUser As String
Dim dbPswd As String
Dim svr As String
Dim serverIP As String
Dim procName As String
Dim dbFieldNames() As Variant
Dim domFieldNames() As Variant
Dim x As Integer, count As Integer
Dim dateVal As String
Dim msg As String
'need to connect to SQL server
queryName = "qry_CRS_TIADataValidation"
Set luVU = db.GetView("DbQueries")
Set qryDoc = luVu.GetDocumentByKey(queryName, True)
'set the parameters to be sent to the external database
x=0 'initialize x
Forall fldNames In qryDoc.dbFields 'external db columns
Redim Preserve dbFieldNames(x)
dbFieldNames(x) = fldNames
x = x+1
End Forall
x=0 're-initialize x
Forall fldNames In qryDoc.domFields 'domino field names
Redim Preserve domFieldNames(x)
domFieldNames(x) = fldNames
x=x+1
End Forall
'need to get the parameter to send (parms)
Set Parm = Parms.Append("CRS_SO_CD",LCTYPE_TEXT)
Parm.Text = concessiondoc.DealerCode(0)
Set Parm = Parms.Append("CRS_INVC_PRT_DT",LCTYPE_TEXT) 'LCTYPE_DATETIME) 'YYYY-MM-DD format
dateVal = vdoc.dateInvoice(0)
Dim InvDT As New notesDateTime(dateVal)
Stop
Parm.Text = convertDate(invDT.dateOnly) 'this will convert it to a yyyy-mm-dd format
Set Parm = Parms.Append("CRS_PROD_FAM_CD",LCTYPE_TEXT)
parm.Text = "F"
Set Parm = Parms.Append("CRS_VEH_SER_NO",LCTYPE_TEXT)
Parm.Text = vdoc.vehicleSerial(0)
'connect to external server
Select Case dbEngine
Case "SQL"
Set lcConn = New LCConnection("oledb")
With lcConn
.provider = dbEngine+"OLEDB" ' SQLOLEDB
.server = serverIP
.database = dbName
.UserID = dbUser
.password = dbPswd
.Procedure = procName
.connect
End With
Case "DB2"
Set lcConn = New LCConnection("DB2")
With lcConn
.Database = dbEngine
.Userid = dbUser
.Password = dbPswd
.NoJournal = True
.Procedure = procName
.Connect
End With
End Select
If lcConn.IsConnected Then
Msgbox "Connected to " & dbEngine & " server."
Else
Msgbox "Did Not Connect to " & dbEngine & " server. Major Error in processing."
End If
count = lcConn.Call(Parms,1,Result)
If count = 0 Then
'we have an issue, need to deal with it
Print "No results were generated from the procedure call."
Msgbox "No results were generated from the procedure call."
Elseif count = LCCOUNT_UNKNOWN Then
Print "A result set was generated but the number of results is unknown"
Msgbox "A result set was generated but the number of results is unknown"
Else
Msgbox "SUCCESS - THE STORED PROCEDURE RETURNED " & Cstr(count )& " records..."
End If
Dim CREATE_TIA As String
Dim ERROR_MSG As String
CREATE_TIA = result.CREATE_TIA(0)
ERROR_MSG = result.ERROR_MSG(0)
If ERROR_MSG <> "" And CREATE_TIA = "N" Then
msg = "The SQL server returned an error when trying to validate TIA and are unable to process charge back for " _
& vDoc.SerialNumber(0) &". The error message returned by the SQL server was:" _
& Chr(10) & Chr(10) & ERROR_MSG
Msgbox "Concession Recovery",16,msg
End If
Msgbox "CREATE_TIA = " & CREATE_TIA
Msgbox "ERROR_MSG = " & ERROR_MSG
If CREATE_TIA = "Y" Then validateTIA = True
Return to top
Resultset is empty (Michael Zens 31.Aug.11)
. . 
Print this page
