VB call stored procedure

tackey

CREATE PROCEDURE sp_compare AS
 IF (select name from sysobjects where name = 'TAB_RESULT_FINACE') <> ''
      DROP TABLE TAB_RESULT_FINACE
 
      SELECT outbound category, outbound order number, [SO No #], customer code, customer name, customer PO number, person in charge,
          [Invoice Date], currency, amount, status INTO #TAB_ACCOUNT
      FROM TAB_ACCOUNT WHERE (Status = N'Not Invoiced ') OR (Status = N'Invoicing')

     SELECT TAB_INVOICE. [Customer number], TAB_INVOICE. [Customer name],
      TAB_INVOICE. [(Ending balance) local currency] AS advance balance, sum (case when the amount is null then 0 else amount end)
      AS Billing Amount, (sum (((Ending Balance) Local Currency)) + sum (case when amount is null then 0 else amount end)) as Prepayment balance
      INTO TAB_RESULT_FINACE
      FROM TAB_INVOICE LEFT OUTER JOIN #TAB_ACCOUNT ON
      TAB_INVOICE. [Customer number] = #TAB_ACCOUNT. Customer number
      GROUP BY TAB_INVOICE. [Customer number], TAB_INVOICE. [Customer name], TAB_INVOICE. [(Ending balance) local currency]

      SELECT TAB_RESULT_FINACE. [Customer number], TAB_RESULT_FINACE. [Customer name],
      TAB_RESULT_FINACE. Prepayment balance AS 'U8 Prepayment balance', SUM (TAB_RESULT. Prepayment balance) AS 'CS Prepayment balance',
      (CASE WHEN TAB_RESULT_FINACE. Prepayment balance = SUM (TAB_RESULT. Prepayment balance) THEN 'TRUE' ELSE 'FALSE' END) AS 'Comparison value'
      FROM TAB_RESULT_FINACE LEFT OUTER JOIN
      TAB_RESULT ON TAB_RESULT_FINACE. [Customer number] = TAB_RESULT. [Customer number]
      GROUP BY TAB_RESULT_FINACE. [Customer number], TAB_RESULT_FINACE. [Customer name], TAB_RESULT_FINACE. Advance payment balance
GO

My stored procedure is like this, I now find that there is a problem in executing the stored procedure in VB, the stored procedure itself is no problem, I have called it in the query analyzer, but calling in VB is not enough, please help me look at it what is the problem!

Set oRst = New ADODB.Recordset
 cn1.CursorLocation = adUseClient
 Set oRst = cn1.Execute ("exec sp_compare")
    spdDetail.MaxRows = 0
    Do While Not oRst.EOF
        spdDetail.MaxRows = spdDetail.MaxRows + 1
        spdDetail.Row = spdDetail.MaxRows
        spdDetail.SetText 1, spdDetail.Row, oRst.Fields (0)
        spdDetail.SetText 2, spdDetail.Row, oRst.Fields (1)
        spdDetail.SetText 3, spdDetail.Row, oRst.Fields (2)
        spdDetail.SetText 4, spdDetail.Row, oRst.Fields (3)
        spdDetail.SetText 5, spdDetail.Row, oRst.Fields (4)
        oRst.MoveNext
    Loop

I have called the stored procedure, and I want to read the data of the stored procedure SELECT into the table control, but always said that the data set object ORST is not open?

尉迟普贤

up

尉迟普贤

orst to define ... come dim oRst as ADODB.Recordset

四不像君

Try the following two sentences on the stored procedure,
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

And this stored procedure is too complicated, it is best to just return a simple data set.

821105

There is a problem with the response time between the program and the SQL server
You can write statements that call stored procedures like this

    Dim adocomm As New ADODB.Command
    Set adocomm.ActiveConnection = ConnSrv
    adocomm.CommandText = "sp_compare"
    adocomm.CommandType = adCmdStoredProc
    adocomm.Execute
    ReturnValue = adocomm.Parameters (0)
    If ReturnValue = 0 Then
      'carried out
    Else
      'drop out
    End If
    Set adocomm = Nothing