CODEWITHSUNDEEP
asp.net-mvcc#-4.0sql-server-2008-r2
davids
davids

Reputation: 5577

SQL Results Not Returned to C#

SETUP:

I have a procedure to get a list of messages to send. I want to update the results records so I cannot accidentally pull the same records twice. Regardless of how I approach it, SSMS produces the correct list of messages. However, if I update the records in the same procedure, C# does not get any results.

I have tried using a DataAdapter like this: 

var da = new SqlDataAdapter(cmd);
da.Fill(table);

and DataReader like this:

var dataReader = cmd.ExecuteReader();
table.Load(dataReader);
dataReader.Close();

to populate my DataTable, neither seems to affect the results.

My procedure looks like this:

ALTER PROCEDURE [dbo].[MnxNoticeGetMessages] 
@sessionId uniqueidentifier = null
AS
BEGIN
SET NOCOUNT ON;

DECLARE @Output TABLE (messageid uniqueidentifier)

INSERT INTO @Output
SELECT messageid FROM MnxTriggerEmails WHERE dateSent is null 

SELECT * FROM MnxTriggerEmails WHERE messageid IN (SELECT messageid FROM @Output)

UPDATE MnxTriggerEmails SET dateSent=GETDATE() 
    WHERE messageid IN (SELECT * FROM @Output)
END

This produces results in SSMS but not in C#.

If I comment out the UPDATE like this (no other changes):

--UPDATE MnxTriggerEmails SET dateSent=GETDATE() 
--    WHERE messageid IN (SELECT * FROM @Output)

I get the expected results in both SSMS and C#.

What am I missing? What would cause this? How do I update the records AND get the results at the same time?

Upvotes: 1

Views: 122

Answers (1)

user2639740
user2639740

Reputation: 1215

Your code looks fine. It should work. It almost sounds like you are calling the sp twice. The first time changes the dates and the second returns no results. This would cause ssms to produce the desired results and c# to be empty.

Check the code you haven't posted and see if you make that call again before populating the DataTable.

Upvotes: 1

Related Questions