asked on

call a stored procedure in VB6

I have a stored procedure that returns (outputs) data. I need the procedure to 1) accept input date from VB front end and 2) return the results into a flexgrid. Here is the stored procedure:

CREATE PROCEDURE [DBO].[CHKREC] AS

DECLARE @VDD1 CHAR(1)
DECLARE @VDD0 CHAR(1)
SET @VDD0 = ''
SET @VDD1 = 'C'
SELECT
CASE WHEN SRCDOCTYP = '6' THEN '00' END AS RC,
CASE WHEN RECONUM = '0' THEN 'NOT CLEARED' END AS NOT_CLEARED,
CASE WHEN CHEKBKID like 'KEY BANK FLA' THEN ('00000'+'XXXXXXXXXX') END AS AcctNum,
RTRIM('000'+ CMTRXNUM) as CheckNum,
CONVERT(varchar(8),POSTEDDT,112)as IssueDate,
Right('0000000000' + Replace(Cast(TRXamnt as varchar), '.', ''), 10) AS AMOUNT,
CASE WHEN VOIDED = '1' THEN @VDD1 ELSE @VDD0 END AS VOIDED
FROM CM20200
WHERE SRCDOCTYP =6 AND CHEKBKID = 'KEY BANK FLA' AND POSTEDDT >= '08/25/05' and reconum = '0' and CMTRXNUM LIKE '0%'
order by CMTRXNUM

GO

I just created this procedure (first time) and the syntax checks out. How do I test to make sure the procedure works?

Pioneermfg

ASKER

I just figured out how to test the stored procedure, so please disregard last question.

Dabas

If you want to delete this question, post a zero-point question in https://www.experts-exchange.com/Community_Support/

Subject: Moderator Please Delete
Body: Please delete this question:
https://www.experts-exchange.com/questions/21541796/call-a-stored-procedure-in-VB6.html

Naveen Swamy

Public Function ExecuteSP(ByVal sProcName As String, ParamArray aParams()) As ADODB.Recordset

Dim cmd As ADODB.Command
Dim cmdParam As New ADODB.Parameter

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = gConn
cmd.CommandText = sProcName
cmd.CommandType = adCmdStoredProc

Set ExecuteSP = New ADODB.Recordset
ExecuteSP.CursorLocation = adUseClient
ExecuteSP.Properties("Initial Fetch Size") = 5000
ExecuteSP.Properties("Background Fetch Size") = 5000

If UBound(aParams) < 0 Then
' if NO parameters to execute the sp
Set ExecuteSP = cmd.Execute
Else
' if we use parameters to execute the sp
Set ExecuteSP = cmd.Execute(, aParams, adAsyncFetchNonBlocking)
End If

Set cmd = Nothing
' Exit Function
'errH:
' MsgBox "Error: " & vbCrLf & "Reason: " & Err.Description, vbCritical + vbSystemModal, gAppTitle
'' Resume
' Exit Function
End Function

grsTemp.Open ExecuteSP("your stored procedure name", and its parameter)
e.g
grsTemp.Open ExecuteSP("SP_Lister", strMainId, sCurrentTableName, iPrevID, sPrevFldName, sFldOrderVal)

Pioneermfg

ASKER

I guess I don't understand. I can call the stored procedure, but I can't figure out how to pass the parameter to the stored procedure. I want to pass the textbox input (date) into the where clause (posteddt >= "my textbox". Do I need to change the stored procedure? If so, what do I do to make that happen?

WHERE SRCDOCTYP =6 AND CHEKBKID = 'KEY BANK FLA' AND POSTEDDT >= '08/25/05' and reconum = '0' and CMTRXNUM LIKE '0%'

ASKER CERTIFIED SOLUTION

Dabas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pioneermfg

ASKER

This part of the stored procedure I have done, but I need to pass in the parameter from the VB front end (textbox formated as a date (08/31/05). I am having an issue with this right now, but I will let you know as soon as I get it to work.

Dabas

Pioneermfg:
> I am having an issue with this right now,
Maybe if you post the code that is raising the issue, I might be able to help

Dabas

Pioneermfg

ASKER

It turned out the problem is in the stored procedure and how I setup the parameter. It's fixed now.

Dabas

Pioneermfg:
Just a little friendly advice.
You have allocated 5 B grades in the last 10 of your questions.
This appears on YOUR profile, not on mine.
Quite a lot or experts will not even bother to answer your questions when they feel that they have a 50% of a chance not to be appreciated.

Dabas