problem o fc# accessing stored procedure at sql server 2k

  • Thread starter Thread starter Mullin Yu
  • Start date Start date
M

Mullin Yu

i have a stored procedure at sql server 2k. which will update records and
select result from temp table.

if i use SqlConnection class, and i do both. but, if i use OleDbConnection
class, i can have only records updated, but no result come out.

how can i debug, and what's the error?

thanks!
 
it's my program.

really strange!!

Ole -> 0

Sql -> 1

Do I need to set something else?

thanks!

private void button1_Click(object sender, System.EventArgs e)

{

try

{

//string dbstring = "Provider=SQLOLEDB.1;User ID=sa;Password=ccbswsd;Data
Source=CCBS-EDMSDB4;Use Procedure for Prepare=1;Auto Translate=True;Packet
Size=4096;Workstation ID=MULLIN-YU;Use Encryption for Data=False;Tag with
column collation when possible=False;database=CCBSAPP";

string dbstring = "Provider=SQLOLEDB.1;User ID=sa;Password=ccbswsd;Data
Source=CCBS-EDMSDB4;Packet Size=4096;database=CCBSAPP";

OleDbConnection conn = new OleDbConnection(dbstring);


OleDbCommand oleCommand = new OleDbCommand();

oleCommand.CommandText = "GetJobItems";

oleCommand.CommandType = CommandType.StoredProcedure;

oleCommand.Connection = conn;

DataTable results = new DataTable("OutboundQueueItem");

OleDbDataAdapter oleDataAdapter = new OleDbDataAdapter(oleCommand);


//string valMachine = "localhost";

//int valOutboundType = 2;

string valMachine = "aaa";

int valOutboundType = 2;

OleDbParameter paramMachine = new OleDbParameter("@Machine",
OleDbType.VarChar, 20, ParameterDirection.Input, false, 0, 0,
"MachineLocked", DataRowVersion.Current, valMachine);

OleDbParameter paramOutboundType = new OleDbParameter("@OutboundType",
OleDbType.Integer, 4, ParameterDirection.Input, false, 0, 0, "OutboundType",
DataRowVersion.Current, valOutboundType);

oleCommand.Parameters.Add(paramMachine);

oleCommand.Parameters.Add(paramOutboundType);


oleDataAdapter.Fill(results);



MessageBox.Show("count: " + results.Rows.Count);


}

catch(OleDbException oleex)

{

Console.WriteLine(oleex.Message);

}

catch(Exception ex)

{

throw ex;

}

}

private void button2_Click(object sender, System.EventArgs e)

{

string dbstring = "User ID=sa;Password=ccbswsd;Data
Source=CCBS-EDMSDB4;Packet Size=4096;Workstation
ID=MULLIN-YU;database=CCBSAPP";

SqlConnection conn = new SqlConnection(dbstring);

SqlCommand sqlCommand = new SqlCommand();

sqlCommand.CommandText = "GetJobItems";

sqlCommand.CommandType = CommandType.StoredProcedure;

sqlCommand.Connection = conn;

DataTable results = new DataTable("OutboundQueueItem");

SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);


//string valMachine = "localhost";

//int valOutboundType = 2;

string valMachine = "aaa";

int valOutboundType = 2;



SqlParameter paramMachine = new SqlParameter("@Machine", SqlDbType.VarChar,
20, ParameterDirection.Input, false, 0, 0, "MachineLocked",
DataRowVersion.Current, valMachine);

SqlParameter paramOutboundType = new SqlParameter("@OutboundType",
SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "OutboundType",
DataRowVersion.Current, valOutboundType);

sqlCommand.Parameters.Add(paramMachine);

sqlCommand.Parameters.Add(paramOutboundType);


//sqlCommand.Connection.Open();

sqlDataAdapter.Fill(results);

conn.Close();

conn.Dispose();


MessageBox.Show("count: " + results.Rows.Count);

}
 
It may be in your Stored Procedure code. Please note that I haven't tested
this theory , but just something to think about. Have you set Nocount on in
the SP. I don't know if it effects the SQLClient, but having the record
counts on in Classic ADO would cause problems in the resultsets returned. I
assume the OLEDB classes would probably have the same problems. Just a
thought.

HTH
Chris Torgerson
 
I've set it, but still error.

I tested with a simple query at the sp like
select * from Table1. I got resultset from both OleDbConnection and
SqlConnection.

It means there's problem when running my sp with OleDbConnection.

Here's my sp:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO



ALTER PROC GetJobItems @Machine as varchar(20), @OutboundType as int as
declare @JobID as bigint
declare @JobItemID as bigint
BEGIN

-- Select one JobItem from OutboundQueue
select top 1 @JobID=JobID from OutboundQueueItem where isLocked = 0 and
AddToProcessing = 1 and OutboundType = @OutboundType order by SubmissionDate
asc

--
*************************************************************
-- select JobItems within that JobID just got
-- Using Cursor
-- *************************************************************

-- Declare the variables to store the values returned by FETCH.
DECLARE @tmpJobItemID as bigint

-- Declare the variable to store the concat value
DECLARE @tmpString as varchar(200)

-- Initialize
SET @tmpString = ''

DECLARE SampleCrsr CURSOR FOR
SELECT JobItemID FROM OutboundQueueItem where JobID = @JobID and IsLocked
= 0 and AddToProcessing = 1 and OutboundType = @OutboundType


-- Create temp Table for storing JobItemIDs to be processed
CREATE TABLE #tmpJobItemID(tJobItemID bigint)

OPEN SampleCrsr

-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.

FETCH NEXT FROM SampleCrsr INTO @tmpJobItemID

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

--SET @tmpString = @tmpString + Convert(varchar, @tmpJobItemID) + ', '
INSERT INTO #tmpJobItemID (tJobItemID) Values (@tmpJobItemID)

-- Concatenate and display the current values in the variables.
--PRINT 'JobItemID: ' + Convert(varchar, @tmpJobItemID)

-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM SampleCrsr INTO @tmpJobItemID
END

--PRINT 'tmpString: ' + @tmpString + '1'

CLOSE SampleCrsr
DEALLOCATE SampleCrsr

-- ***************************************************************
-- End Of using cursor
-- ***************************************************************

-- Lock the Job at OutboundQueue, and update the status to "Working"
-- update OutboundQueueItem set isLocked = 1, MachineLocked = 'Mullin-Yu',
Status = 2 where JobItemID = @JobItemID
update OutboundQueueItem set isLocked = 1, MachineLocked = @Machine,
Status = 2 where JobItemID in (select tJobItemID from #tmpJobItemID)

-- return recordset
-- select * from OutboundQueueItem where JobItemID =
@JobItemID
select * from OutboundQueueItem where JobItemID in (select tJobItemID from
#tmpJobItemID)

END




GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
 
Back
Top