No Results in Datatable

[Jason MacKenzie]

If have a stored procedure that seems to work fine through query analyzer
but if I attempt the same thing via code, no exceptions are thrown but no
results are ever returned. I'm using the ODBC.NET dataprovider.

The string that I cut and paste when I debug is:

sp_OrgChart 1900,
'''ANALYS'',''ARLDR'',''DLDR'',''ENG'',''PROG'',''STU'',''DLDR'''

and again, this works great in query analyzer.

However, the following code never returns any results. The SQL statement
argument would look like the string above. I've left out the error handling
etc. It works great for regular SQL statements though.

Public Function ReturnDataTable(ByVal SQLStatement As String, ByVal
InsertRowAtIndex0 As Boolean) As DataTable

Dim myConnection As OdbcConnection
Dim myCommand As OdbcCommand
Dim myDA As OdbcDataAdapter

myConnection = New OdbcConnection(strConnectionString)
myCommand = New OdbcCommand(SQLStatement, myConnection)
myDA = New OdbcDataAdapter


myDA.SelectCommand = myCommand

Dim myDT As New DataTable

Try
m_GeneralError = ""
myDA.Fill(myDT)

If InsertRowAtIndex0 Then
Dim BlankRow As System.Data.DataRow = myDT.NewRow()
myDT.Rows.InsertAt(BlankRow, 0)
End If


Return myDT

catch Ex as Exception

End try

End Function


And here is my stored procedure:


CREATE PROCEDURE sp_OrgChart

@Department int,
@JobCodes nvarchar(500)

AS


if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[OrgChartTemp]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[OrgChartTemp]



declare @TableName as nvarchar(50)
declare @TempQuery as nchar(2500)


SELECT * INTO dbrgChartTemp FROM vw_OrgChartReportsTo WHERE DepartmentID
= @Department


DECLARE @DepartmentLeaderCount int
SET @DepartmentLeaderCount = (SELECT COUNT(*) FROM dbrgChartTemp WHERE
JobCode = 'DLDR')


if @DepartmentLeaderCount = 0

begin
INSERT INTO OrgChartTemp

SELECT * FROM vw_OrgChartReportsTo WHERE JobCode = 'DLDR' AND LastName IN
(SELECT SUBSTRING(ReportsTo, 1, CHARINDEX(',', ReportsTo) - 1) FROM
OrgChartTemp)

UPDATE OrgChartTemp SET DepartmentID = (SELECT TOP 1 DepartmentID FROM
vw_OrgChartReportsTo WHERE DepartmentID = @Department), Department = (SELECT
TOP 1 Department FROM vw_OrgChartReportsTo WHERE DepartmentID = @Department)
end

exec('SELECT eeEENum, Name, ReportsTo, ImagePath, Department, Position,
Location FROM dbrgChartTemp WHERE JobCode IN (' + @JobCodes + ')')
GO