CODEWITHSUNDEEP
asp.netdebuggingstored-proceduressqldatasource
TheTechGuy
TheTechGuy

Reputation: 17374

Changed stored procedure not recognized in ASP.NET - SQLDataSource

I am working with ASP.NET and StoredProcedure. I change the stored procedure so now it returns 10 field rather than 9. I have added corresponding textbox in asp.net which gets the 10th field.

<asp:TextBox ID="ID" runat="server" Text='<%# Eval("ID") %>' Width="80px" ReadOnly="true"></asp:TextBox>

But it fails, saying

DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'ID'.

I run the stored procedure in SQL Management studio and indeed it does not return the newly modified field. I stop and start the SQL engine and now it does spits out the new value but in my ASP.net application, it still does not recognized the new value. What is the best way to troubleshoot this? It happened to me before as well.

Adding the code where SP is called:

SqlConnection aConn = new 
SqlConnection(ConfigurationManager.ConnectionStrings["DBConn"].ConnectionString);
SqlCommand aCommand = new SqlCommand("GetCustomer", aConn);
aCommand.CommandType = CommandType.StoredProcedure;
aCommand.Parameters.Add("@ID", SqlDbType.Int).Value = aID;
SqlDataAdapter adapter = new SqlDataAdapter(aCommand);
DataSet aCustomerDS = new DataSet();
adapter.Fill(aCustomerDS);

My Stored Procedure is(there is nothing strange here):

CREATE PROCEDURE [dbo].[GetCustomer]
    @ID int
AS
BEGIN
    SET NOCOUNT ON;

IF @ID = 0
BEGIN    
SELECT  ID,
    [this field] as thisfield,
       [that field] as thatfield,
       [new field] as newfield
FROM MyDB.dbo.customers
ORDER BY ID DESC
END
ELSE
BEGIN
SELECT  ID,
    [this field] as thisfield,
       [that field] as thatfield,
       [new field] as newfield
FROM MyDB.dbo.Customers
WHERE ID = @ID
ORDER BY ID DESC
END
END

FormView is used to bind data to sdsCustomer

<asp:FormView ID="fvCustomer" runat="server" AllowPaging="True" DataSourceID="sdsCustomer"
            DataKeyNames="ID" OnPreRender="OnfvCustomer_PreRender">

Upvotes: 0

Views: 775

Answers (3)

TheTechGuy
TheTechGuy

Reputation: 17374

My answer : It was complicated.

The FormView1 was bound to two storedprocedure: 1) usuing SQLDataSource 2) using SQLDataSource on the fly

If I would change the one that is created on the fly, ASP would give me error that the new field is not recognized by the formview. To get rid of this error, I modify the original SQLDataSource that was bound to formview and added that field, this get rid of the error. So basically this is Microsoft bug. It looks look at a different stored procecedure and I am fillwing the formview with a different stored procedure, if the field in the two stored procedure do not match I get a very misleading error because ID is indeed in the stored procedure but not in the one that is tied with the formview. Below is a diagram of the bug.

enter image description here

Upvotes: 0

3Dave
3Dave

Reputation: 29051

What is the best way to troubleshoot this?

What's the source for your stored proc look like?

Set a breakpoint on the line that runs the query. Examine the results that are returned and make sure that your ID column is, in fact, present.

Alternatively, fire up the SQL Profiler and ensure that:

  1. The ID column is present in the result set when the procedure is called with the same parameter values that the application is using,
  2. Your proc is only returning one result set.

  3. Make sure you've included the line

    set nocount on;

in your procedure.

If the procedure is unintentionally returning multiple result sets (which will happen if you leave out the set nocount statement) your data source may not contain what you're expecting.

Upvotes: 0

The Evil Greebo
The Evil Greebo

Reputation: 7138

You need to update the data source itself to reflect the results coming out.

Upvotes: 1

Related Questions