Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    49

    Unanswered: ADO doesn't retrieve value from this sp..why?

    Hi, all.
    I tried to get result of sp.
    Dim rst As Recordset
    Set rst = New ADODB.Recordset
    rst.Open sp, cnn

    doesn't get result.
    when i call rst.EOF, it thows error: Can do this since rst is closed...

    I found it's problem of sp which is little complex.
    But, still I think it should work!
    My question is how can I get the returned value from following sp in VB?


    --- return list of tables that needed to update
    --- list is one string separated by '&' delimeter
    --- @listOfUpdateTime: [TableName=UpdateTime]&[...] eg) tblDeptList=12/25/2004&tblHoliday=12/24/2004&...
    CREATE procedure spGetListOfTableToDownLoad
    @listOfUpdateTime varchar(500)
    as
    SET NOCOUNT ON
    Declare @listOfTable varchar(300), @item varchar(300)
    Declare @tbl varchar(50), @uptime datetime
    Declare @list varchar(500)
    Declare @sep varchar(1)
    SET @list = ''
    SET @sep = '&'
    DECLARE cur CURSOR FAST_FORWARD FOR
    SELECT * FROM fnSplit(@listOfUpdateTime, @sep)

    OPEN cur

    FETCH NEXT
    FROM cur
    INTO @item

    Declare @re bit, @tp varchar(50)
    WHILE @@FETCH_STATUS = 0
    BEGIN

    -- get tablename, update time
    Declare cur2 CURSOR FAST_FORWARD FOR
    SELECT * FROM fnSplit(@item, '=')
    OPEN cur2
    FETCH NEXT FROM cur2 INTO @tbl
    Print @tbl
    FETCH NEXT FROM cur2 INTO @tp
    print 'tp:' + @tp
    SET @uptime = CAST(@tp as datetime)
    print @uptime
    -- @re =1: true, 0: false
    EXEC spIsUpdate @tbl, @uptime, @re output
    IF @re = 1
    SET @list = @list + @tbl + @sep
    CLOSE cur2
    DEALLOCATE cur2

    FETCH NEXT
    FROM cur
    INTO @item
    END
    if LEN(@list) > 0
    SET @list = LEFT(@list, Len(@list)-Len(@sep))
    CLOSE cur
    DEALLOCATE cur

    SELECT @list as Result

    SET NOCOUNT OFF


    GO
    __________________
    --- PARAM:: @tbl: table name,
    --- @uptime : update time (passed from local db) that will be compared on HQ table
    --- return 1 if Max(UpdateTime) of @table > @uptime
    --- otherwise return 0
    CREATE Procedure spIsUpdate
    @tbl varchar(50), @uptime datetime, @result bit output
    as
    BEGIN
    Declare @bit bit

    DECLARE @SQLString NVARCHAR(500)
    DECLARE @ParmDefinition NVARCHAR(500)
    declare @uptimeHQ datetime
    /* Build the SQL string once.*/
    SET @SQLString = N'SELECT @tp = MAX(UpdateTime) FROM ' + @tbl
    SET @ParmDefinition = N'@tp datetime OUTPUT'
    EXECUTE sp_executesql @SQLString, @ParmDefinition
    ,@uptimeHQ OUTPUT

    If @uptimeHQ > @uptime
    SET @result = 1
    ELSE
    SET @result = 0
    -- RETURN @bit
    END


    GO
    MCSD .NET, SCJP, SCJWD

  2. #2
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    You posted two SPs... does this happen on both?
    That which does not kill me postpones the inevitable.

  3. #3
    Join Date
    Apr 2004
    Posts
    49
    first calls second..
    MCSD .NET, SCJP, SCJWD

  4. #4
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Have you tried running this from Query Analyzer and seeing what results you get?
    That which does not kill me postpones the inevitable.

  5. #5
    Join Date
    Apr 2004
    Posts
    49
    yes, it returns a recordset with one record one col as varchar
    MCSD .NET, SCJP, SCJWD

  6. #6
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Ok, this is VB right? Try using this to create your Recordset:

    Dim rst
    Set rst = CreateObject("ADODB.Recordset")
    (may also want to do your connection object the same way)

    If that doesn't work, can you provide all the code for your connection to the DB and executing the query? I notice in your first post it's kind of there, but not complete.
    That which does not kill me postpones the inevitable.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •