none
need help updating ms access!! row data always marked as new RRS feed

  • Question

  • Hi all
    I have aroblem: I want to save is ms access, datatable, but all lines are marked as new, and all the sta is re insert to the db!
    when I debug I see the row state is added!(even if it is not true!!)
    what can I do??
    Monday, October 5, 2009 12:46 PM

Answers

  • Do you receive any errors? I see 2 potential issues in your code: 
     
    1. When you create parameters in your code then their names must math parameter names inside of your SQL statement. For example, for the @name parameter in your SQL statement, you should create @name parameter,not "name", when you call GetNewParameter. Also keep in mind that named parameters will work ONLY with the SQL Server database, but not with the Access database 
     
    2. Add square brackets around all the column names and table, since you are using reserved words as column names

    INSERT INTO [task]s ([Name],[Date],[Note],[DurationSt]) VALUES (@Name,@Date,@Note,@DurationSt)


    Val Mazur (MVP) http://www.xporttools.net
    • Marked as answer by Yichun_Feng Monday, October 12, 2009 1:01 AM
    Wednesday, October 7, 2009 10:28 AM
    Moderator
  • Your current code should insert values properly as long as you fix all those SQL formatting issues and pass parameters properly. But if you need, you could manipulate state of the rows using your own code. For example, you could reset state of all rows to unchanged if you call AcceptChanges method for the DataTable. It will reset state of the rows and will not change anything in a database. Then you could explicitly set state of each row to Modified, Deleted or Added (again) using SetModified, Delete or SetAdded methods of the row. Is this what you need?  


    Val Mazur (MVP) http://www.xporttools.net
    • Marked as answer by Yichun_Feng Monday, October 12, 2009 1:02 AM
    Thursday, October 8, 2009 10:24 AM
    Moderator

All replies

  • Could you please be more specific? Based on your description it's hard to identify what is the actual problem. Maybe you could describe your scenario or post some part of your code and point to the issue. From your post I believe you need to insert the rows and the code does it for you.
    Val Mazur (MVP) http://www.xporttools.net
    Tuesday, October 6, 2009 10:42 AM
    Moderator
  • the problem is that  Row.RowState is always "added".

    what I wannt to do is to take ds with all the data from another part of the application and update changes in the db

    (add new rows, change what is need to be changeD)

    void UpdateAdapterMannualy(DataSet ds)

    {

     

     

    DataTable categoryTable = ds.Tables["Tasks"];

    adapter.SelectCommand.Connection = cn;

    adapter.Fill(categoryTable);

     

     

     

    foreach (DataRow Row in ds.Tables["Tasks"].Rows)

    {

     

     

    switch (Row.RowState)

    {

     

     

    case DataRowState.Added :

    dapter.InsertCommand =

    new OleDbCommand(

     

     

    "INSERT INTO tasks (Name,Date,Note,DurationSt) " +

     

     

    "VALUES (@Name,@Date,@Note,@DurationSt)");

    adapter.InsertCommand.Parameters.Clear();

     

     

    adapter.InsertCommand.Parameters.Add(GetNewParameter("Name", Row["Name"]));

    adapter.InsertCommand.Parameters.Add(GetNewParameter(

     

    "Date", Row["Start"].ToString()));

    adapter.InsertCommand.Parameters.Add(GetNewParameter(

     

    "Note", Row["Notes"]));

    adapter.InsertCommand.Parameters.Add(GetNewParameter(

     

    "DurationSt", Row["Duration"].ToString()));

     

     

     

    this.Open();

    adapter.InsertCommand.Connection = cn;

    adapter.InsertCommand.ExecuteNonQuery();

     

     

    this.Close();

     

     

     

    break
    case DataRowState.Deleted :

     

     

    break;

     

     

    case DataRowState.Modified :

     

     

    break;

    }

    }

    }

    Tuesday, October 6, 2009 12:20 PM
  • Do you receive any errors? I see 2 potential issues in your code: 
     
    1. When you create parameters in your code then their names must math parameter names inside of your SQL statement. For example, for the @name parameter in your SQL statement, you should create @name parameter,not "name", when you call GetNewParameter. Also keep in mind that named parameters will work ONLY with the SQL Server database, but not with the Access database 
     
    2. Add square brackets around all the column names and table, since you are using reserved words as column names

    INSERT INTO [task]s ([Name],[Date],[Note],[DurationSt]) VALUES (@Name,@Date,@Note,@DurationSt)


    Val Mazur (MVP) http://www.xporttools.net
    • Marked as answer by Yichun_Feng Monday, October 12, 2009 1:01 AM
    Wednesday, October 7, 2009 10:28 AM
    Moderator
  • I will check that. any idea what to do with the other problem?that the rowstate in  ds is always "added"?
    tnx
    Wednesday, October 7, 2009 11:07 AM
  • Your current code should insert values properly as long as you fix all those SQL formatting issues and pass parameters properly. But if you need, you could manipulate state of the rows using your own code. For example, you could reset state of all rows to unchanged if you call AcceptChanges method for the DataTable. It will reset state of the rows and will not change anything in a database. Then you could explicitly set state of each row to Modified, Deleted or Added (again) using SetModified, Delete or SetAdded methods of the row. Is this what you need?  


    Val Mazur (MVP) http://www.xporttools.net
    • Marked as answer by Yichun_Feng Monday, October 12, 2009 1:02 AM
    Thursday, October 8, 2009 10:24 AM
    Moderator