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 dbo.OrgChartTemp FROM vw_OrgChartReportsTo WHERE DepartmentID
= @Department
DECLARE @DepartmentLeaderCount int
SET @DepartmentLeaderCount = (SELECT COUNT(*) FROM dbo.OrgChartTemp 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 dbo.OrgChartTemp WHERE JobCode IN (' + @JobCodes + ')')
GO