ODBC Stored Procedure Problem

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
 
P

Patrik Löwendahl

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...
 
J

Jason MacKenzie

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...
 
W

William \(Bill\) Vaughn

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Exception has been thrown by the target of an invocation 2
select stored procedure 4
Returning parms to vb.net from as400 stored procedure 1
error msg help needed on stored procedure 4
stored procedure takes too much time to execute 7
Dynamic Stored Procedure 6
Stored procedure syntax problem 2
Populate a DataGrid from Multiple Databases - Order Problem 3

Top