ODBC Stored Procedure Problem

  • Thread starter Thread starter Jason MacKenzie
  • Start date Start date
J

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
 
First off, you need to set the CommandType property of the CommandObject to
storedprocedure,

second if you'r working against a SqlServer you can use Sql Profiler to
trace what the client sends to the Sql Server

third, why use odbc if your runing against a Sql box? SqlClient is direct
and don't go through the extra layers of ODBC / OLE DB to communicate with
the Sql net library.

--
Patrik Löwendahl
cshrp.net - " Elegant code by witty programmers "
cornerstone.se - " IT Training for professionals "

Jason MacKenzie said:
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
Click to expand...
 
No exception is thrown


Patrice Scribe said:
What if you don't catch the exception ?

Patrice

--

"Jason MacKenzie" <[email protected]> a écrit dans le message de
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,
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
 
And, I might add, don't prefix your stored procedure names with "sp_". It
tags them as "system" procedures so the engine looks in Master first. I also
expect that the SP might be returning more than one resultset. Try adding
"SET NOCOUNT ON" to the SP to eliminate the extra non-rowset-returning
resultsets.

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Patrik Löwendahl said:
First off, you need to set the CommandType property of the CommandObject to
storedprocedure,

second if you'r working against a SqlServer you can use Sql Profiler to
trace what the client sends to the Sql Server

third, why use odbc if your runing against a Sql box? SqlClient is direct
and don't go through the extra layers of ODBC / OLE DB to communicate with
the Sql net library.

--
Patrik Löwendahl
cshrp.net - " Elegant code by witty programmers "
cornerstone.se - " IT Training for professionals "

Jason MacKenzie said:
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,
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
 
Back
Top