Post reply

Query runs in SSMS but not in VB6

  • December 23, 2013 at 12:07 pm

    #287636

    I'm doing some maintenance work on a client's VB6 system. I've written a new stored proc as follows...

    DECLARE @p_dt smalldatetime

    DECLARE @p_AcctNo char(13)

    IF EXISTS (SELECT * FROM tblHist WHERE AcctNo = @p_AcctNo And Indic = 'Y' And @p_dt BETWEEN StartDate AND EndDate)

    SELECT -1

    ELSE

    SELECT 0

    If I furnish a value for the two parameters and execute it, it always returns the correct result -- either 0 or -1. This is thanks to help I got on this forum on a post last night.

    Here is the VB6 code that calls this proc.

    Public Function getValue(Acctno As String, dt As String) As Boolean

    Dim rs As ADODB.Recordset

    Dim ssql As String

    ssql = "prc_select_value '" & Acctno & "', 2, '" & dt & "'"

    Set rs = New ADODB.Recordset

    rs.CursorLocation = adUseClient

    rs.Open ssql, MDIForm1.dcnn

    getValue = rs(0).Value

    rs.Close

    Set = Nothing

    End Function

    This always receives a value of zero in rs(0) even though the sql proc returns -1 for the same input account number and date.

    I will post this on a VB6 forum elsewhere but I was wondering if anyone could see anything wrong with the sql.

    Thanks.

  • December 23, 2013 at 5:46 pm

    #1675708

    It could be something to do with declaring the VB variable "dt" as a string and not a date, causing a bad conversion of the dt string to a datetime within SQL.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply