none
Update dataset from database after the database filled the primary key with an AutoNumber RRS feed

  • Question

  •  

    I add a new row to an Access table. The database then fills the primary key with an AutoNumber for that row. I need to retrieve the value of the primary key just created.

    Is it possible update back the dataset?

    I need this value to write it in the related tables.

     

    Thanks

    Thursday, September 6, 2007 3:35 PM

Answers

  • well you refill it like you should be doing before to get the results.

     

    C#

     

    Code Snippet

    OleDbCommand command = new OleDbCommand("SELECT * FROM [table]", new OleDbConnection(connectionString));

    OleDbDataAdapter adapter = new OleDbDataAdapter(command);

    DataSet ds = new DataSet();

    command.Connection.Open();

    adapter.Fill(ds);

    command.Connection.Close();

     

    //get the last row of data:

     

    DataRow lastRow = ds.Tables[0].Rows[ds.Tables[0].Rows.Count - 1];

    //get the value of the field you are after

     

    string fieldValue = lastRow["columnName"].ToString();

     

     

     

    VB.NET:

     

    Code Snippet

    Dim command as new OleDbCommand("SELECT * FROM [table]", new OleDbConnection(connectionString))

    Dim adapter as new OleDbDataAdapter(command)

    Dim ds as new DataSet()

    command.Connection.Open()

    adapter.Fill(ds)

    command.Connection.Close()

     

    Dim lastRow as DataRow = ds.Tables(0).Rows(ds.Tables(0).Rows.Count - 1)

    Dim fieldValue as string = lastRow("columnName").ToString()

     

     

     

     

     

    Thursday, September 6, 2007 6:55 PM

All replies

  • not sure what you mean, can you explain further by what you mean by "Is it possible to update back the dataset?" Do you mean to get the new product ID? If so, you would need to refill that dataset.

     

     

    Thursday, September 6, 2007 3:53 PM
  • I mean, I need to update the DataSet with the value of the primary key just created by the database after I just saved the DataSet. If I use fill to fill again the dataset who can I make sure I identify exactly the same row of data that I just saved.

     

    Thursday, September 6, 2007 5:02 PM
  • because you would need to get the last record which would be the new record you just inserted, or search the datatable for the row using whatever data you entered for that row by iterating through it or by using a DataView to give it a filter, which will return back the results in a DataTable.

     

    Thursday, September 6, 2007 5:51 PM
  •  

    If I search the datable for the data I just added, I may encounter a row with the same data. Is there another way of identifying the row I just added, or the value that the database generated for the primary key?
    Thursday, September 6, 2007 6:03 PM
  • the other way would be to manually insert data and get the SCOPE_IDENTITY() Value returned back from your insert statement but since you are using DataSet's, I don't believe there is another way unless you refill the dataset and get the last row, which would contain your last record you inserted

     

    Thursday, September 6, 2007 6:15 PM
  •  

    Ok, I see, but can you explain how to refill the dataset and get the last row?
    Thursday, September 6, 2007 6:39 PM
  • well you refill it like you should be doing before to get the results.

     

    C#

     

    Code Snippet

    OleDbCommand command = new OleDbCommand("SELECT * FROM [table]", new OleDbConnection(connectionString));

    OleDbDataAdapter adapter = new OleDbDataAdapter(command);

    DataSet ds = new DataSet();

    command.Connection.Open();

    adapter.Fill(ds);

    command.Connection.Close();

     

    //get the last row of data:

     

    DataRow lastRow = ds.Tables[0].Rows[ds.Tables[0].Rows.Count - 1];

    //get the value of the field you are after

     

    string fieldValue = lastRow["columnName"].ToString();

     

     

     

    VB.NET:

     

    Code Snippet

    Dim command as new OleDbCommand("SELECT * FROM [table]", new OleDbConnection(connectionString))

    Dim adapter as new OleDbDataAdapter(command)

    Dim ds as new DataSet()

    command.Connection.Open()

    adapter.Fill(ds)

    command.Connection.Close()

     

    Dim lastRow as DataRow = ds.Tables(0).Rows(ds.Tables(0).Rows.Count - 1)

    Dim fieldValue as string = lastRow("columnName").ToString()

     

     

     

     

     

    Thursday, September 6, 2007 6:55 PM
  •  

    Excellent, the code runs well. Thanks.

     

    Can you tell me if this is a good way to maintain the relationship between tables?

     

    Also, what would be the solution if the database is available to more than 1 user at the time, meaning it’s impossible to be sure the last row was created by my code?
    Thursday, September 6, 2007 8:39 PM
  •  

    well depends what you mean by maintaining a relationship between tables. It's probably the way of doing it if you are wanting to have a "local" copy of the database in memory really and to keep the constraints/relationships consistant.

     

    Well, the last row would be whoever inserted the last row of data in SQL/Access, there really isn't a way to see

    who inserted what row AFAIK.

    Thursday, September 6, 2007 8:48 PM
  •  

    By maintaining the relationship between tables I mean I have a relational database with 1 to many relations between tables. When I add new rows of data I want to make sure the data from one table relates to the date in another. For example: some orders belong to a certain client. That’s way I thought I need the primary key just created to save its value in related tables.  I’m asking if there is another way to add rows in a relational database because I’m surprised other post do not seem to talk about this. As if there are other ways to do this.
    Thursday, September 6, 2007 8:59 PM
  • Well yes, that is true about the relationship you are describing. I'm not sure if there is another approach the way you are asking, especially if you are using say a DataAdapter to do what you are doing but the approach does make it easier using DataAdapter's, you can easily create relationships with the data you have fetched from SQL, which will of course be filled in x amount of tables in the dataset and easily update the tables/relationships with data.

    Thursday, September 6, 2007 9:12 PM