asked on

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

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.

Lukasz Zielinski

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

ziolko.

Guy Hengel [angelIII / a3]

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

Kaporch

ASKER

There is no open command for a stored proc. Using set nocounton still returns 0 records.

Dirk Haest

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

ASKER CERTIFIED SOLUTION

Lukasz Zielinski

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kaporch

ASKER

I changed to using execute.scalar and placing the results in a string variable. That worked. Thanks.

Dirk Haest

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.

NBSO_ISS

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.

Kaporch

ASKER

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.

Kaporch

ASKER

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

NBSO_ISS

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"