[dba-VB] Can't Get Select Results In Visual Basic

DOH,

Thank you very much for ending my nightmare, can't believe I missed that.

Paul

2009/3/4 Salakhetdinov Shamil <mcp2004 at mail.ru>

>
> Hi Paul,
>
> Try to put
>
> SET NOCOUNT ON
>
> before DECLARE
>
> statement.
>
> HTH
>
> --
> Shamil
>
> -----Original Message-----
> From: Paul Hartland <paul.hartland at googlemail.com>
> To: Access List <accessd at databaseadvisors.com>,VisualBasicList <
> dba-vb at databaseadvisors.com>,SQLServerList <
> dba-sqlserver at databaseadvisors.com>
> Date: Wed, 4 Mar 2009 10:04:54 +0000
> Subject: [dba-VB] Can't Get Select Results In Visual Basic
>
> > To all,
> >
> > Sorry about cross posting this but really having a nightmare, I have the
> > following store procedure in SQL Server 2005 :
> >
> > usp_MyStoredProcedure
> >  (
> >   @nvcPayroll  [nvarchar](50),
> >   @dtiJobDate  [datetime],
> >   @nvcCode  [nvarchar](1)
> >  )
> > AS
> > BEGIN
> >  DECLARE @intUpdateFailed INT
> >  UPDATE dbo.MyTable
> >  SET AvailabilityCode=@nvcCode
> >  WHERE PayrollNo =  @nvcPayroll AND
> >   JobDate  =  @dtiJobDate AND
> >    (
> >   @nvcCode =  'A'  OR
> >   @nvcCode =  'N'
> >    )                AND
> >    (
> >   AvailabilityCode=  'A'  OR
> >   AvailabilityCode=  'N'
> >    )
> >
> >  SET @intUpdateFailed=@@ROWCOUNT
> >  DELETE FROM dbo.MyTable2
> >  WHERE PayrollNo=@nvcPayroll
> >  INSERT INTO MyTable2 (WebResult, PayrollNo)
> >  SELECT CASE WHEN @intUpdateFailed>0 THEN 'Success' ELSE 'Failed' END,
> > @nvcPayroll
> >  SELECT *
> >  FROM MyTable2
> >  WHERE PayrollNo=@nvcPayroll
> > END
> >
> > If I run this in SQL Server seems to work fine, and the SELECT * FROM
> > MyTable2 WHERE PayrollNo=@nvcPayroll returns all the fields.  However I
> have
> > a simple piece of VB code as below:
> >
> > Dim rs As ADODB.Recordset
> > If DE.Conn.Start = adStateClosed Then
> >     DE.Conn.Open
> > Endif
> > Set rs = DE.Conn.Execute(usp_MyStoredProcedure '999999', '" &
> > format(Date,"mm/dd/yy") & "','N'")
> > msgbox rs.fields(0)
> >
> > DE.Conn.Close
> >
> > ' DE.Conn is a DataEnvironment connection which links to the SQL Server
> >
> > The vb code returns Item cannot be found in the collection corresponding
> to
> > the requested name or ordinal.
> >
> > However if I run the SP, then comment out everything except the SELECT
> > statement it returns the resultset.
> >
> > Can anyone tell me what is happening or what I am doing wrong please.
> >
> > Thanks in advance for any help on this....
> >
> >
> >
> >
> >
> >
> > --
> > Paul Hartland
> > paul.hartland at googlemail.com
> > _______________________________________________
> > dba-VB mailing list
> > dba-VB at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-vb
> > http://www.databaseadvisors.com
> >
>
> _______________________________________________
> dba-VB mailing list
> dba-VB at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-vb
> http://www.databaseadvisors.com
>
>


-- 
Paul Hartland
paul.hartland at googlemail.com