ObjectDataSource Not Binding Properly To GridView

Question

This is probably something simple, just need some fresh eyes. It's very long, but I wanted to give full context. I'm trying to show a GridView that uses an ObjectDataSource. The ODS returns a DataTable. The DT is constructed from the query result of an MSSQL stored procedure. The problem is that my ASP BoundField is complaining at me for wanting a field that isn't in the data source...but it definitely is. When I leave out the aforementioned field, the grid shows fine, but when I press "Delete", an id of 0 is sent to my ODS, not the one that actually deletes the records, 70. I haven't tried updating yet, but I imagine that doesn't work if the other two don't. What am I doing wrong?

GridView:

ObjectDataSource (.ASPX):

ObjectDataSource (.CS):

public DataTable CtGetExcludeRules(
    string ruleKey // Length: 255
)
{
    SqlConnection dbConn = new SqlConnection(_connectString);
    SqlCommand command =
        new SqlCommand
        {
            CommandText = "CT_Get_ExcludeRules",
            CommandType = CommandType.StoredProcedure,
            Connection = dbConn
        };

    command.Parameters.Add(
        "@RuleKey", SqlDbType.VarChar, 255
    ).Value = ruleKey ?? "";

    dbConn.Open();

    DataTable dt = new DataTable();
    SqlDataAdapter da = new SqlDataAdapter(command);
    da.Fill(dt);

    dbConn.Close();
    da.Dispose();

    return dt.Rows.Count > 0 ? dt : new DataTable{Columns = { "ruleKey", "field" }, Rows = { "No results" }};
}

public bool CtDeleteExcludeRules(
    int id,
    string userId // Length: 50
)
{
    using (var dbConn = new SqlConnection(_connectString))
    {
        using (var command = new SqlCommand(
            "CT_Delete_ExcludeRules", dbConn
        ))
        {
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(
                "@Id", SqlDbType.Int
            ).Value = id;

            command.Parameters.Add(
                "@UserId", SqlDbType.VarChar, 50
            ).Value = userId ?? "";

            dbConn.Open();

            return command.ExecuteNonQuery() > 0;
        }
    }
}

public bool CtUpdateExcludeRules(
    int id,
    string field, // Length: 50
    string userId // Length: 50
)
{
    using (var dbConn = new SqlConnection(_connectString))
    {
        using (var command = new SqlCommand(
            "CT_Update_ExcludeRules", dbConn
        ))
        {
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(
                "@Id", SqlDbType.Int
            ).Value = id;

            command.Parameters.Add(
                "@Field", SqlDbType.VarChar, 50
            ).Value = field;

            command.Parameters.Add(
                "@UserId", SqlDbType.VarChar, 50
            ).Value = System.Web.HttpContext.Current.User.Identity.Name
                      ?? "";

            dbConn.Open();

            return command.ExecuteNonQuery() > 0;
        }
    }
}

Stored procedures:

[dbo].[CT_Get_ExcludeRules]
@RuleKey varchar(255) = ''
AS
SELECT * FROM [Interface].[dbo].[CT_ExcludeRules] WHERE RuleKey = @RuleKey;


[dbo].[CT_Delete_ExcludeRules]
@Id int,
@UserId varchar(50)
as

BEGIN TRANSACTION;  
BEGIN TRY  

    insert into CT_ExcludeRules_History
    select Id,RuleKey,Field,@UserId,'Delete',getdate()
    from CT_ExcludeRules
    where Id = @Id

    delete from [dbo].[CT_ExcludeRules]
    where Id = @Id

    Commit
END TRY 


[CT_Update_ExcludeRules]
@Id int,
@Field varchar(50),
@UserId varchar(50)
as

BEGIN TRANSACTION;  
BEGIN TRY  
    Update [dbo].[CT_ExcludeRules]
    set Field = @Field
    where Id = @Id

    insert into CT_ExcludeRules_History
    select Id,RuleKey,Field,@UserId,'Update',getdate()
    from CT_ExcludeRules
    where Id = @Id

    Commit
END TRY

Table design:

GET stored procedure example:

DataTable Rows Results View (also the same array that is retrieved by the data source's OnSelected event):

Values passed to data source after clicking "Delete":

Trying to add an Id column to the GridView gives me this error message:

A field or property with the name 'Id' was not found on the selected data source.

I don't need an id column, I just want to delete and update like a normal GridView!

RESOLUTION:

Thanks to user "sea-charp" below for seeing what was missing. I needed to replace { "ruleKey", "field" } with { "id", "ruleKey", "field" } in order for my data source to recognize the "Id" property. Once that was recognized, I was able to add "Id" in the DataKeyNames attribute of my GridView, which fully enabled my deletion method via the proper Id of 70 instead of 0.

Sea Charp · Accepted Answer

Instead of this :

command.Parameters.Add(
        "@RuleKey", SqlDbType.VarChar, 255
    ).Value = ruleKey ?? "";

Try this

command.Parameters.Add("@RuleKey", SqlDbType.VarChar, 255);
command.Parameters("@RuleKey").Value = ruleKey ?? "";

Looks like you're getting the error because you have this as part of your return syntax:

DataTable{Columns = { "ruleKey", "field" }, Rows = { "No results" }};

... which returns a table with no ID when there are no results - possibly because of the command parameter syntax you're using.

ObjectDataSource Not Binding Properly To GridView

Answers (1)

Related Questions