dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 8 of 8

Thread: Delete a row using Dataset

  1. #1
    Join Date
    Jul 2003
    Location
    Dallas, Texas
    Posts
    9

    Delete a row using Dataset

    deleting a row using Dataset seems not working. The following is the code I am using to try to delete a row from a table, but it is not working. No exceptions are throwed. Any idea?

    Your help is appreciated.


    const string TBL_NAME = "EmployeeTitles";
    SqlConnection sqlconn = new SqlConnection("server=(local);database=pubs;Trusted_Connection=yes");
    //SqlConnection sqlconn = new SqlConnection("server=Peru;database=pubs;user id = sa;password=insource");
    //SqlDataAdapter sqlAdap = new SqlDataAdapter("select * from EmployeeTitles WHERE TitleID < 100",sqlconn);
    SqlDataAdapter sqlAdap = new SqlDataAdapter();
    sqlAdap.SelectCommand = new SqlCommand("select * from EmployeeTitles WHERE TitleID < 100", sqlconn);
    DataSet ds = new DataSet();
    sqlAdap.Fill(ds,TBL_NAME);
    SqlCommandBuilder comBuilder = new SqlCommandBuilder(sqlAdap);

    DataRow rowDel=ds.Tables[TBL_NAME].Rows[2];;
    ds.Tables[TBL_NAME].Rows.Remove(rowDel);
    sqlAdap.Update(ds.Tables[TBL_NAME]);

  2. #2
    Andy Tacker is offline More than "Just Another Member"
    Join Date
    Jun 2001
    Location
    55°50' N 37°39' E
    Posts
    1,503

    simple!

    try this!

    DataRow rowDel=ds.Tables[TBL_NAME].Rows[2];
    rowDel.delete();

    ds.Tables[TBL_NAME]. AcceptChanges();

    sqlAdap.Update(ds.Tables[TBL_NAME]);
    If you think you CAN, you can, If you think you CAN'T, you are probably right.

    Have some nice Idea to share? Write an Article Online or Email to us and You may WIN a Technical Book from CG.

  3. #3
    Join Date
    Jul 2003
    Location
    Dallas, Texas
    Posts
    9

    Re: simple!

    Originally posted by andy_tacker
    try this!

    DataRow rowDel=ds.Tables[TBL_NAME].Rows[2];
    rowDel.delete();

    ds.Tables[TBL_NAME]. AcceptChanges();

    sqlAdap.Update(ds.Tables[TBL_NAME]);
    Thanks for the help, but I tried the code given here. It still did not work for me. Any ideas?

    Thanks again.

  4. #4
    Andy Tacker is offline More than "Just Another Member"
    Join Date
    Jun 2001
    Location
    55°50' N 37°39' E
    Posts
    1,503

    ok

    I will try to create a sample project and try deleterow. then i will tell you the solution. deal?
    If you think you CAN, you can, If you think you CAN'T, you are probably right.

    Have some nice Idea to share? Write an Article Online or Email to us and You may WIN a Technical Book from CG.

  5. #5
    Join Date
    Aug 2003
    Posts
    102
    DataRow rowDel=ds.Tables[TBL_NAME].Rows[2];;
    ds.Tables[TBL_NAME].Rows.Remove(rowDel);
    sqlAdap.Update(ds.Tables[TBL_NAME]);


    Your code can delete the row in the DataSet but not on the actual database right?????????????
    This is because a DataAdapter automatically call a Delete, Update, Insert command when you update it.............
    From your code seems you forgot the Delete Command for the DataAdapter......... thats why the record is not deleted on the actual database itself...........

    try this....

    Dim strDelete As String = "DELETE FROM TABLENAME WHERE colname = 2"
    Dim cmdDelete As New OleDbCommand(strDelete, cnn)
    DataAdapter.DeleteCommand = cmdDelete

    DataAdapter.Update(DataSet.Tables("TableName"))

    Hope this works.................

  6. #6
    Join Date
    Jul 2003
    Location
    Dallas, Texas
    Posts
    9
    Originally posted by riscoh
    DataRow rowDel=ds.Tables[TBL_NAME].Rows[2];;
    ds.Tables[TBL_NAME].Rows.Remove(rowDel);
    sqlAdap.Update(ds.Tables[TBL_NAME]);


    Your code can delete the row in the DataSet but not on the actual database right?????????????
    This is because a DataAdapter automatically call a Delete, Update, Insert command when you update it.............
    From your code seems you forgot the Delete Command for the DataAdapter......... thats why the record is not deleted on the actual database itself...........

    try this....

    Dim strDelete As String = "DELETE FROM TABLENAME WHERE colname = 2"
    Dim cmdDelete As New OleDbCommand(strDelete, cnn)
    DataAdapter.DeleteCommand = cmdDelete

    DataAdapter.Update(DataSet.Tables("TableName"))

    Hope this works.................
    Thank you for the information. This works, but it still assume that I know how to write a SQL statement.

    Thanks again.

  7. #7
    Andy Tacker is offline More than "Just Another Member"
    Join Date
    Jun 2001
    Location
    55°50' N 37°39' E
    Posts
    1,503

    Delete command

    Yes, it assumes so that you know how to create SQL statements.
    other way is to use command builder to create a delete command for you and use it. but trust me, if you use command builder, you will be tired of assigning parameters. so, i think declaring a delete commnad yourself is better.
    but I haven't yet got time to work with dataset row deletion. as soon as i get some, i will write in here.
    If you think you CAN, you can, If you think you CAN'T, you are probably right.

    Have some nice Idea to share? Write an Article Online or Email to us and You may WIN a Technical Book from CG.

  8. #8
    Join Date
    Sep 2003
    Location
    Brazil
    Posts
    5

    donīt use acceptchanges

    I think u should comment the AcceptChanges line of code.
    If u use it, the dataset is marked as "unchanged" and the dataadapter wonīt use the update command against it..

    good luck!

    --------
    Originally posted by andy_tacker
    try this!

    DataRow rowDel=ds.Tables[TBL_NAME].Rows[2];
    rowDel.delete();

    //ds.Tables[TBL_NAME]. AcceptChanges();

    sqlAdap.Update(ds.Tables[TBL_NAME]);

    -------------------

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)