Wrox Programmer Forums
Go Back   Wrox Programmer Forums > .NET > Other .NET > ADO.NET
|
ADO.NET For discussion about ADO.NET.  Topics such as question regarding the System.Data namespace are appropriate.  Questions specific to a particular application should be posted in a forum specific to the application .
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ADO.NET section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 30th, 2004, 03:23 AM
Authorized User
 
Join Date: Jul 2004
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default How to delete the dataset records

I want to delete a row in a dataset when I click on the DataGrid Item,the Page_load() code as followed:
-------------------------------------------------------------------
    private void Page_Load(object sender, System.EventArgs e)
        {
            if(!Page.IsPostBack)
            {
                this.sqlDataAdapter1.Fill(this.dataSet21,"Categori es");
                this.DataGrid1.DataSource=this.dataSet21;
                this.DataGrid1.DataBind();

            }
        }
---------------------------------------------------------
when I add the "if(!Page.IsPostBack)" codes to it,it reports not initial instance object,what's wrong??
And another problem is that I want to delete the records in the dataset,the code followed:
----------------------------------------------------
int keys=(int)this.DataGrid1.DataKeys[(int)e.Item.ItemIndex];

dt=this.dataSet21.Tables["Categories"];
 dr=dt.Rows.Find((int)keys);
dr.Delete();
---------------------------
It can delete the row ,but it'll appear when I delete another row?Why?
My English is so poor,but please help me!Thank you!

 
Old July 30th, 2004, 05:18 AM
Friend of Wrox
 
Join Date: Jul 2004
Posts: 623
Thanks: 0
Thanked 1 Time in 1 Post
Default

dear jabby,
first you don't need this.DataGrid1... ,eliminate "this" from your code,
second I didn't see any Connection object and also creation of your
adapter object in your Page_Load(first point that compiler compiles your application),I mean something like below
---------
SqlConnection SqlConnection1 = new SqlConnection(strConnection);
SqlDataAdapter sqlDataAdapter1 = new SqlDataAdapter(strSQLSelect, objConnection);
--------
also for your secnod problem,you delete your row from dataset but not from your database,then when you again retrieve the data from your datasource,your deleted
row is shown again(because it has not be deleted from your datasource), you should now tell me what is your aim?
do you want to delete the row from your datasource and dataset or only from dataset if first use the below
----------
dr.Delete();
SqlCommandBuilder cb = new SqlCommandBuilder(adapter);
objConnection.Open();
SqlDataAdapter1.Update(dataSet21, "Categories");
objConnection.Close();
---------
if second use below code
---------
dr.Remove();
--------
HTH.

--------------------------------------------
Mehdi.
 
Old July 30th, 2004, 06:44 AM
Authorized User
 
Join Date: Jul 2004
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

mehdi62b:
First thank you for your answer!But why should I eliminate "this" from my code?and the SqlDataAdapter ,SqlConnection I had defined in the InitializeComponent() method.I means I want to delete the row from the dataset or not the datasource!

 
Old July 30th, 2004, 08:05 AM
Friend of Wrox
 
Join Date: Jul 2004
Posts: 623
Thanks: 0
Thanked 1 Time in 1 Post
Default

>>the SqlDataAdapter ,SqlConnection I had defined in the InitializeComponent() >>method
if you defined them locally your problem(first) could be due to it.
Can you show me your code?


--------------------------------------------
Mehdi.
 
Old July 30th, 2004, 08:51 AM
Authorized User
 
Join Date: Jul 2004
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you¡¡mehdi62b£¬the code as followed:
--------------------------------------------------
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

namespace WebApplication1
{

    public class WebForm1 : System.Web.UI.Page
    {
        protected System.Data.SqlClient.SqlDataAdapter sqlDataAdapter1;
        protected System.Data.SqlClient.SqlCommand sqlSelectCommand1;
        protected System.Data.SqlClient.SqlCommand sqlInsertCommand1;
        protected System.Data.SqlClient.SqlCommand sqlUpdateCommand1;
        protected System.Data.SqlClient.SqlCommand sqlDeleteCommand1;
        protected System.Data.SqlClient.SqlConnection sqlConnection1;
        protected WebApplication1.DataSet2 dataSet21;
        protected System.Web.UI.WebControls.DataGrid DataGrid1;

        private void Page_Load(object sender, System.EventArgs e)
        {
            if(!Page.IsPostBack)
            {
                sqlDataAdapter1.Fill(dataSet21,"Categories");
                DataGrid1.DataSource=dataSet21.Tables[0];
                DataGrid1.DataBind();

            }
        }

        override protected void OnInit(EventArgs e)
        {
            InitializeComponent();
            base.OnInit(e);
        }

        private void InitializeComponent()
        {
            this.sqlDataAdapter1 = new System.Data.SqlClient.SqlDataAdapter();
            this.sqlDeleteCommand1 = new System.Data.SqlClient.SqlCommand();
            this.sqlConnection1 = new System.Data.SqlClient.SqlConnection();
            this.sqlInsertCommand1 = new System.Data.SqlClient.SqlCommand();
            this.sqlSelectCommand1 = new System.Data.SqlClient.SqlCommand();
            this.sqlUpdateCommand1 = new System.Data.SqlClient.SqlCommand();
            this.dataSet21 = new WebApplication1.DataSet2();
            ((System.ComponentModel.ISupportInitialize)(this.d ataSet21)).BeginInit();
            this.DataGrid1.DeleteCommand += new System.Web.UI.WebControls.DataGridCommandEventHand ler(this.DataGrid1_Delete);
            //
            // sqlDataAdapter1
            //
            this.sqlDataAdapter1.DeleteCommand = this.sqlDeleteCommand1;
            this.sqlDataAdapter1.InsertCommand = this.sqlInsertCommand1;
            this.sqlDataAdapter1.SelectCommand = this.sqlSelectCommand1;
            this.sqlDataAdapter1.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
                                                                                 new System.Data.Common.DataTableMapping("Table", "Categories", new System.Data.Common.DataColumnMapping[] {
                                                                                new System.Data.Common.DataColumnMapping("CategoryID", "CategoryID"),
                                        new System.Data.Common.DataColumnMapping("CategoryName ", "CategoryName"),
                                        new System.Data.Common.DataColumnMapping("Description" , "Description"),
                                            new System.Data.Common.DataColumnMapping("Picture", "Picture")})});
            this.sqlDataAdapter1.UpdateCommand = this.sqlUpdateCommand1;
            //
            // sqlDeleteCommand1
            //
            this.sqlDeleteCommand1.CommandText = "DELETE FROM Categories WHERE (CategoryID = @Original_CategoryID) AND (CategoryNam" +
                "e = @Original_CategoryName)";
            this.sqlDeleteCommand1.Connection = this.sqlConnection1;
            this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_Cate goryID", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "CategoryID", System.Data.DataRowVersion.Original, null));
            this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_Cate goryName", System.Data.SqlDbType.NVarChar, 15, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "CategoryName", System.Data.DataRowVersion.Original, null));
            //
            // sqlConnection1
            //
            this.sqlConnection1.ConnectionString = "workstation id=JABBY;packet size=4096;integrated security=SSPI;data source=JABBY;" +
                "persist security info=False;initial catalog=Northwind";
            //
            // sqlInsertCommand1
            //
            this.sqlInsertCommand1.CommandText = "INSERT INTO Categories(CategoryName, Description, Picture) VALUES (@CategoryName," +
                " @Description, @Picture); SELECT CategoryID, CategoryName, Description, Picture " +
                "FROM Categories WHERE (CategoryID = @@IDENTITY)";
            this.sqlInsertCommand1.Connection = this.sqlConnection1;
            this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CategoryName" , System.Data.SqlDbType.NVarChar, 15, "CategoryName"));
            this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Description", System.Data.SqlDbType.NVarChar, 1073741823, "Description"));
            this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Picture", System.Data.SqlDbType.VarBinary, 2147483647, "Picture"));
            //
            // sqlSelectCommand1
            //
            this.sqlSelectCommand1.CommandText = "SELECT CategoryID, CategoryName, Description, Picture FROM Categories";
            this.sqlSelectCommand1.Connection = this.sqlConnection1;
            //
            // sqlUpdateCommand1
            //
            this.sqlUpdateCommand1.CommandText = @"UPDATE Categories SET CategoryName = @CategoryName, Description = @Description, Picture = @Picture WHERE (CategoryID = @Original_CategoryID) AND (CategoryName = @Original_CategoryName); SELECT CategoryID, CategoryName, Description, Picture FROM Categories WHERE (CategoryID = @CategoryID)";
            this.sqlUpdateCommand1.Connection = this.sqlConnection1;
            this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CategoryName" , System.Data.SqlDbType.NVarChar, 15, "CategoryName"));
            this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Description", System.Data.SqlDbType.NVarChar, 1073741823, "Description"));
            this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Picture", System.Data.SqlDbType.VarBinary, 2147483647, "Picture"));
            this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_Cate goryID", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "CategoryID", System.Data.DataRowVersion.Original, null));
            this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_Cate goryName", System.Data.SqlDbType.NVarChar, 15, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "CategoryName", System.Data.DataRowVersion.Original, null));
            this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CategoryID", System.Data.SqlDbType.Int, 4, "CategoryID"));
            //
            // dataSet21
            //
            this.dataSet21.DataSetName = "DataSet2";
            this.dataSet21.Locale = new System.Globalization.CultureInfo("zh-CN");
            this.Load += new System.EventHandler(this.Page_Load);
            ((System.ComponentModel.ISupportInitialize)(this.d ataSet21)).EndInit();

        }
        #endregion

        protected void DataGrid1_Delete(Object sender, DataGridCommandEventArgs e)
        {

            DataTable dt;
            //DataRow dr;
            try
            {
                dt=dataSet21.Tables[0];
            //int keys=(int)DataGrid1.DataKeys[(inte.Item.ItemIndex];
            //dt.PrimaryKey = new DataColumn[]{dt.Columns["CategoryID"]};
            DataRowCollection dr=dataSet21.Tables["Categories"].Rows;
                if(dr.Equals(""))
                    Response.Write("dr is null");
                else
                    Response.Write("dr is not null");
                //dr.Delete();
                //dr.AcceptChanges();
                dr.RemoveAt(e.Item.ItemIndex);
                DataGrid1.DataBind();
                }
            catch(Exception ep)
            {
    Response.Write(ep.Message+"<br/>"+ep.Source+"<br/>"+ep.StackTrace+"<br/>");
        }
    }

    }
}
-----------------------------------------
I had bind the delete event

 
Old July 30th, 2004, 09:31 AM
Friend of Wrox
 
Join Date: Jul 2004
Posts: 623
Thanks: 0
Thanked 1 Time in 1 Post
Default

in this line
protected WebApplication1.DataSet2 dataSet21;
where is the reference?(I mean WebApplication1.DataSet2)
did you correctly put it in your namespace("WebApplication1")
if not ,change it to
protected System.Data.DataSet dataSet21;
also
change this.dataSet21 = new WebApplication1.DataSet2();
to this.dataSet21 = new System.Data.DataSet();

--------------------------------------------
Mehdi.
 
Old July 30th, 2004, 12:42 PM
Authorized User
 
Join Date: Jul 2004
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

can you give me the code of delete a row from a datagird,not delete from datasource but the dataset!

 
Old July 30th, 2004, 09:23 PM
planoie's Avatar
Friend of Wrox
 
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

There is no method for removing a data grid item. You must remove the item from the datagrid's data source. In a DataSet, this would be a row in one of the set's DataTables. You can do this with ther DataTable.Remove() or DataTable.RemoveAt() methods.
 
Old July 31st, 2004, 07:04 AM
Authorized User
 
Join Date: Jul 2004
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

No,no,I only want to delete the row from the dataset or not the data source,for example in a shopping cart.
Can't i invoke the datarow.delete() and the datarow.acceptchanges()?Isn't it the same as the remove()?

 
Old July 31st, 2004, 07:26 AM
Friend of Wrox
 
Join Date: Jul 2004
Posts: 623
Thanks: 0
Thanked 1 Time in 1 Post
Default

DataRow.Delet() and then DataRow.acceptChanges() is not the same as
DataRow.remove() at all.first method deletes your dataRow from your datasource (and also DataSet),
but second method Deletes your DataRow only from DataSet(not datasource)
(even if you call Update() method it doesn't delete it from datasource it is because of RowState property,i.e in first method RowState is Deleted but in second method RowState is Detached,Have a look at MSDN for more information)
Hope it was clear.

--------------------------------------------
Mehdi.





Similar Threads
Thread Thread Starter Forum Replies Last Post
How to delete all the records in a table Kai Lai Access VBA 2 March 15th, 2016 07:20 AM
Delete Duplicate Records prasanta2expert SQL Server 2000 9 December 15th, 2006 10:44 PM
How to delete one of two duplicate records aarkaycee SQL Server 2000 0 August 20th, 2006 07:27 PM
delete records in database shoakat Classic ASP Databases 9 October 26th, 2004 08:31 PM
How to use the DELETE method in a DataSet thomaz C# 2 January 10th, 2004 02:28 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.