ado SP problem in SQL Server 7.0 and 2000
thanks Alejandro:
there's something similar but more complicated in another post. you use
createparameter() where the other way is to set the .name, .type,
.direction, ... and then append the param.
your way's easier. Thanks. I've learned a few things xtra that will come in
handy some day.
I found another way which works for this app and may or may not be of use to
you. in this snip, i'm using the code for the actual product, not the test
proc:
With cmdQry
.ActiveConnection = CNN1
.CommandText = "proc_AppendAdage_2_DR_ItemDetail"
.CommandType = adCmdStoredProc
.CommandTimeout = 120
.Parameters.Refresh
.Parameters.Item(1).Value = i
.Execute
Returned"
Else
End If
End With
Debug.Print cmdQry.Parameters(2).Value
Debug.Print cmdQry.Parameters(0).Value
Debug.Print cmdQry.Parameters(0).Name
thanks again,
mike
Quote:
> 1 - Use SET NOCOUNT ON/OFF in your sp to avoid that sql server returns the
> message (xxx rows affected) as recordset to the client.
> 2 - Use ADO command object from VB to execute an sp with parameters.
> return 0 as return value), you have to process the recordset, close it
and
> assign nothing to it before accessing the parameters.
> ...
> set objConn = new adodb.connection
> set objCmd = new adodb.command
> set objRs = new adodb.recordset
> with objConn
> ...
> .errors.clear
> .open
> end with
> with objCmd
> .commandtext = "proc_test"
> .commandtype = adCmdStoredProc
> adParamReturnValue)
> 10)
adParamOutput,
> 20)
> set .activeconnection = objConn
> end with
> with objRs
> .cursorlocation = adUseClient
> .cursortype = adOpenStatic
> .locktype = adLockReadOnly
> .open objCmd
> ' PUT HERE CODE TO PROCESS THE RECORDSET
> ...
> .close
> end with
> set objRs = nothing
> ...
> Greetings,
> Alejandro Mesa
> > Hi:
> > The stored proc below (furthur below) works ok unless i uncomment the
line
> > which selects from DR_BusUnit_Master. i've been using
> > Set recordset = connection.Execute(sql, dwRecs, adCmdText) and the
> > recordset.Open methods to test it. Here's the sql:
> > it seems that the query on DR_BusUnit_Master (see proc below) which
> returns
> > a few rows with 3 fields is blocking the final select statement. so that
i
> > get an error from the statement:
> > if recordset!Ret = -1 then --or if I try to access recordset!szOut
> > that says the "Item cannot be found in the collection corresponding to
the
> > requested name or ordinal." (i.e. so it's not there.)
> > recordset!BusUnit does evaluate, however.
> > how can I get the recordset to drop it's previous rows and do what i
want?
> > unless there's another way. here's the proc:
> > create proc proc_test
> > as
> > select BusUnit from DR_BusUnit_Master
> > return 0
> > go
> > here's the sql that shows in the immediate window and works fine in
Query
> > Analyzer:
> > Query Analyzer returns the BusUnit records (3 of 'em), and 0 and "duh
> duh"
> > for Ret and OutMsg - none of the other selects inside or outside the SP
> get
> > returned.
> > this is not good. i'm trying to run an SP that when run in QA reports
> 6300+
> > records affected, then an aggregate of 40 and finally the one record
which
> > is a happy (or not so happy ) error string along with the return value
of
> 0
> > or -1.
> > what's up here. what's the right way to do this in VB? Any help will be
> > greatly appreciated.
> > thanks in advance,
> > mike
