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

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
>