Can't Insert/Update/Delete in DetailsView

[satchi]

Hi, I am having some trouble doing the usual insert, update, delete on my sql database. I have a Master/Details setting where the selected GridView item is shown on a DetailsView.

When I select an item in GridView, that item shows up on my DetailsView. The Insert, Edit, and Delete command links are then set on the details view. The problem I'm getting is, nothing updates when I click on the Update link, Delete link, or the Insert. When clicking on those, it' just postbacks to the same page w/ the editing mode open.

I've seen many examples regarding creating sqldatasources and creating commands and parameters in .aspx form, but I'm having trouble finding samples of script/in-code form.

Am I missing something? ...

Also, I'm a little clueless as to how the event methods _ItemInsert _ItemUpdate _ItemDelete works. Could someone explain to me how these links update the database? My current understanding is that the dataadpter's insertcommand, updatecommand, and editcommand are responsible for most of the work? where when those links are pressed, that's where it's actually updating/inserting/deleting, and the event methods are there for extra code if necessary?

.aspx code:

<asp:GridView ID="gvProducts" runat="server"
  AutoGenerateRows="False"
  DataKeyNames="pid">
    <Columns>
        <asp:BoundField DataField="pid" HeaderText="pID" InsertVisible="False" ReadOnly="True" SortExpression="pid" />
        <asp:BoundField DataField="cid" HeaderText="cID" SortExpression="cid" />
        <asp:BoundField DataField="name" HeaderText="Name" SortExpression="name" />
        <asp:CommandField ShowSelectButton="true" />
    </Columns>
</asp:GridView>
<asp:DetailsView ID="dvProducts" runat="server"
  AutoGenerateRows="False"                                 
  OnModeChanging="dvProducts_ModeChanging" 
  OnItemUpdating="dvProducts_ItemUpdating" 
  OnItemDeleting="dvProducts_ItemDeleting"  
  OnItemInserting="dvProducts_ItemInserting">
    <Fields>
        <asp:BoundField DataField="pid" HeaderText="ProductID" ReadOnly="True" InsertVisible="False" />
        <asp:BoundField DataField="cid" HeaderText="CategoryID" />
        <asp:BoundField DataField="name" HeaderText="Name" />
        <asp:BoundField DataField="desc" HeaderText="Description" />
        <asp:BoundField DataField="rprice" HeaderText="Reg. Price" />
        <asp:BoundField DataField="sprice" HeaderText="Sale Price"  />                                    
        <asp:BoundField DataField="thumbnail" HeaderText="Thumbnail" />
        <asp:BoundField DataField="image" HeaderText="Image" />
        
        <asp:CommandField ShowInsertButton="true" ShowEditButton="true" ShowDeleteButton="true" />
        
    </Fields>
</asp:DetailsView>

.aspx.cs code:

protected void BindGridView()
{
    SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString:Products"].ConnectionString);
    string strSql = "SELECT * FROM [Product]";

    SqlDataAdapter dataAdapter = new SqlDataAdapter(strSql, sqlConn);
    DataSet dataset = new DataSet();

    dataAdapter.Fill(dataset, "Product");

    gvProducts.DataSource = dataset.Tables["Product"].DefaultView;
    gvProducts.DataBind();
}

protected void BindDetailsView()
{
    SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString:Products"].ConnectionString);
    string strSql = "SELECT * FROM [Product] WHERE ([pid] = @pid)";
    SqlDataAdapter dataAdapter = new SqlDataAdapter(strSql, sqlConn);
    DataSet dataset = new DataSet();

    dataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@pid", SqlDbType.Int, 0));
    dataAdapter.SelectCommand.Parameters["@pid"].SqlValue = gvProducts.SelectedValue;

    dataAdapter.Fill(dataset);

    dvProducts.DataSource = dataset;

    // build insert command
    string insertString = "INSERT INTO [Product] ([cid], [name], [desc], [rprice], [sprice], [thumbnail], [image]) VALUES (@cid, @name, 

@desc, @rprice, @sprice, @thumbnail, @image)";
    SqlCommand insertCmd = new SqlCommand(insertString, sqlConn);
    insertCmd.Parameters.Add("@cid", SqlDbType.Int, 4, "cid");
    insertCmd.Parameters.Add("@name", SqlDbType.VarChar, 4, "name");
    insertCmd.Parameters.Add("@desc", SqlDbType.VarChar , 4, "desc");
    insertCmd.Parameters.Add("@rprice", SqlDbType.Money, 4, "rprice");
    insertCmd.Parameters.Add("@sprice", SqlDbType.Money, 4, "sprice");
    insertCmd.Parameters.Add("@thumbnail", SqlDbType.VarChar, 4, "thumbnail");
    insertCmd.Parameters.Add("@image", SqlDbType.VarChar, 4, "image");
    dataAdapter.InsertCommand = insertCmd;

    // build update command
    string updateString = "UPDATE [Product] SET [cid]=@cid, [name]=@name, [desc]=@desc, [rprice]=@rprice, [sprice]=@sprice, [thumbnail]

=@thumbnail, [image]=@image WHERE [pid]=@pid";
    SqlCommand updateCmd = new SqlCommand(updateString, sqlConn);
    updateCmd.Parameters.Add("@cid", SqlDbType.Int);
    updateCmd.Parameters.Add("@name", SqlDbType.VarChar);
    updateCmd.Parameters.Add("@desc", SqlDbType.VarChar);
    updateCmd.Parameters.Add("@rprice", SqlDbType.Money);
    updateCmd.Parameters.Add("@sprice", SqlDbType.Money);
    updateCmd.Parameters.Add("@thumbnail", SqlDbType.VarChar);
    updateCmd.Parameters.Add("@image", SqlDbType.VarChar);
    updateCmd.Parameters.Add("@pid", SqlDbType.Int);
    dataAdapter.UpdateCommand = updateCmd;

    //dataAdapter.Update(dataset, "Products");

    // build delete command
    SqlCommand deleteCmd = new SqlCommand("DELETE FROM [Product] WHERE [pid] = @pid", sqlConn);
    deleteCmd.Parameters.Add("@pid", SqlDbType.Int, 0);
    deleteCmd.Parameters["@pid"].Value = 1;  //for testing purpose
    dataAdapter.DeleteCommand = deleteCmd;
    
    dvProducts.DataBind();
    sqlConn.Close();
}

protected void btnSearch_Click(object sender, EventArgs e)
{
    BindGridView();
    //dvProducts.Visible = false;
}
protected void gvProducts_SelectedIndexChanged(object sender, EventArgs e)
{
    BindDetailsView();
    //dvProducts.Visible = true;
}

protected void dvProducts_ModeChanging(object sender, DetailsViewModeEventArgs e)
{
    dvProducts.ChangeMode(e.NewMode);
    BindDetailsView();
}

protected void dvProducts_ItemUpdating(object sender, DetailsViewUpdateEventArgs e)
{
    dvProducts.DataBind();
}

protected void dvProducts_ItemDeleting(object sender, DetailsViewDeleteEventArgs e)
{
    dvProducts.DataBind();
}

protected void dvProducts_ItemInserting(object sender, DetailsViewInsertEventArgs e)
{
    dvProducts.DataBind();
}

 

[satchi]

Ok, I've done a little more digging on the issue and it seems to be related to the SqlDataAdapter's capability to doing updates on the database?

Basically I was following this article below:

http://www.codeproject.com/cs/database/sqldawithoutsqlcb.asp#__comments

..to try doing insert/del/update commands on my database. I was reading the below response on that article and it appears some ppl can't get it to update either.

So... has anyone done updating with the SqlDataAdapter before that can provide some sample code that I can look at? I'm reluctant to get into SqlCommandBuilder because I really want to code this by hand first (to understand it more). Any other suggestions as to how updating the db? Thanks in adv.