We help IT Professionals succeed at work.

Stored procedure not returning any rows to C# .net program, but does return rows in SQL debugger

1,047 Views
Last Modified: 2011-04-14
Stored procedure looks like this:

CREATE    PROCEDURE [dbo].[uspw_updatenotes]
@notetext varchar(8000),
@meeting_id int,
@project_note_type_id int,
@participant_id int,
@reviseddate varchar(30)
AS
begin tran
declare @newreviseddate varchar(30)
select @newreviseddate = convert(varchar(30), getdate(), 109)
update meeting..meeting_notes set note = @notetext, reviseddate = @newreviseddate where meeting_id = @meeting_id and project_note_type_id = @project_note_type_id and participant_id = @participant_id and convert(char(30), reviseddate, 109)=  convert(char(30), @reviseddate, 109)
commit tran
select  distinct @newreviseddate  as NewRevisedDate from meeting..meeting_notes where meeting_id = @meeting_id and project_note_type_id = @project_note_type_id and participant_id = @participant_id


Code like this:

int intProjectNoteTypeID, intParticipant_ID;
                        string strRevisedDate;
                        string strTotalQueryString = Request.QueryString["k"];
                        strParameters = strTotalQueryString.Split('|');
                        intProjectNoteTypeID = Int32.Parse(strParameters[0].ToString());
                        intParticipant_ID = Int32.Parse(Session["Participant_ID"].ToString());
                        strRevisedDate = strParameters[2].ToString();
                        oData = new Utilities.DataAccess("Meeting_AZ");
                        oCmd = new SqlCommand();
                        oCmd.CommandType = CommandType.StoredProcedure;
                        oCmd.CommandText = "uspw_updatenotes";
                        oData.AddParam(ref oCmd, "@notetext", SqlDbType.VarChar, 8000, txtNotes.Text, true);
                        oData.AddParam(ref oCmd, "@meeting_id", SqlDbType.Int, 4, Session["validmeeting"], true);
                        oData.AddParam(ref oCmd, "@project_note_type_id", SqlDbType.Int, 4, intProjectNoteTypeID, true);
                        oData.AddParam(ref oCmd, "@participant_id", SqlDbType.Int, 4, intParticipant_ID, true);
                        oData.AddParam(ref oCmd, "@reviseddate", SqlDbType.VarChar, 30, strRevisedDate, true);
                        
                        oData.Execute(oCmd);
                        
                        oData.Query(oCmd, ref oRead);
                        oData.RemoveParam(ref oCmd);
                        DataTable dt = new DataTable();
                        dt.Columns.Add("NewRevisedDate", typeof(string));

                        if(oRead.HasRows)
                        {
                              while(oRead.Read())
                              {
                                    DataRow dr = dt.NewRow();
                                    dr["NewRevisedDate"] =  oRead["NewRevisedDate"].ToString();
                              }
                        }

                        oRead.Close();
                        if (dt.Rows.Count > 0)
                        {
                              txtNewRevisedDate.Text = dt.Rows[0].ItemArray[0].ToString();
                        }

dt.Rows.Count never has any rows even though in the debugger I see rows returned.  What I need from the stored procedure is the date that's being updated in the stored proc.
Comment
Watch Question

Lukasz Zielinskisoftware developer
CERTIFIED EXPERT
Top Expert 2008

Commented:
oData.Execute(oCmd);
i think Execute should be called on queries not returning records, try .Open instead

ziolko.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
CREATE    PROCEDURE [dbo].[uspw_updatenotes]
@notetext varchar(8000),
@meeting_id int,
@project_note_type_id int,
@participant_id int,
@reviseddate varchar(30)
AS
SET NOCOUNT ON  ---- please add this line ----
begin tran
declare @newreviseddate varchar(30)
select @newreviseddate = convert(varchar(30), getdate(), 109)
update meeting..meeting_notes set note = @notetext, reviseddate = @newreviseddate where meeting_id = @meeting_id and project_note_type_id = @project_note_type_id and participant_id = @participant_id and convert(char(30), reviseddate, 109)=  convert(char(30), @reviseddate, 109)
commit tran
select  distinct @newreviseddate  as NewRevisedDate from meeting..meeting_notes where meeting_id = @meeting_id and project_note_type_id = @project_note_type_id and participant_id = @participant_id

Author

Commented:
There is no open command for a stored proc.  Using set nocounton still returns 0 records.
Dirk HaestProject manager
CERTIFIED EXPERT

Commented:
oData = new Utilities.DataAccess("Meeting_AZ");
--> what type is oData ?

Retrieving Data Using the DataReader
http://msdn2.microsoft.com/en-us/library/haa3afyz(VS.71).aspx
software developer
CERTIFIED EXPERT
Top Expert 2008
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I changed to using execute.scalar and placing the results in a string variable.  That worked.  Thanks.
Dirk HaestProject manager
CERTIFIED EXPERT

Commented:
If you use execute.scalar you won't need the oRead.hasrows, because it only returns the first column of the first row

Some more info:
You will get the result of one row and one column that is one single value using execute scalar. suppose you write the store procedure for inserting, updating and deleting the rows. that time you need to get the result to front end that contains the result of the operations. that time its b est for execute scalar.

Execute reader is a connected architecture of ado.net. it needs the connection until close the operation. its like recordset in asp. its forward only cursor so you can use one time only. typically we use when fill the data into dropdownbox.

Commented:
Move
declare @newreviseddate varchar(30)
select @newreviseddate = convert(varchar(30), getdate(), 109)

above your BEGIN TRAN

Change your Update to this...
update meeting..meeting_notes set note = @notetext, reviseddate = @newreviseddate where meeting_id = @meeting_id and project_note_type_id = @project_note_type_id and participant_id = @participant_id and rtrim(reviseddate)=  @reviseddate

The CONVERT statement is not necessary here because the conversion of Varchar to CHAR is implicit (the extra spaces at the end of the CHAR field are removed by the trim()) and the 109 is only necessary when converting dates (you are using it to convert char & varchar).

Change this line...

select  distinct @newreviseddate  as NewRevisedDate from meeting..meeting_notes where meeting_id = @meeting_id and project_note_type_id = @project_note_type_id and participant_id = @participant_id

to

select  @newreviseddate  as NewRevisedDate

Since you set @newreviseddate above and it is not coming from a table and you do not require the DISTINCT because @newreviseddate is a single variable.   This may not fix your problem, but it will make your code more effecient.

Author

Commented:
NBSO_ISS:

I actually had that in my code originally but I think I was getting an error on this...many permutations and combinations of the stored procedure were tried.

Author

Commented:
I use the format 109 to keep milliseconds...reviseddate is part of the table key so milliseconds can be important.

Commented:
The milliseconds should not be lost as the fields in question are CHAR and VARCHAR.  If you were converting a DATE field to CHAR or VARCHAR, then the 109 would be required, but as you are converting (in one case CHAR to CHAR -- which is unnecessary) CHAR and VARCHAR fields to CHAR data type, the 109 is not necessary.  109 is only necessary when working with Date/time data types.

Just wondering which part was throwing the error?
"I actually had that in my code originally but I think I was getting an error on this...many permutations and combinations of the stored procedure were tried"
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.