Update Access DB

[Kajiro]

I am using a Access DB.
Data is displayed in a Listbox.
Have 3 buttons. AddButton,EditButton and DeleteButton.

1 - Problem is that I can only Add, Edit or Delete 1 item in the listbox.

For example:

I delete 1 item in the ListBox. When I want to delete a second item the 2nd item is then replaced by the first deleted item.
Why?
Same problem if I want to Edit!


2 - If the data in DataSet is changed, how can I update the changes to the Access DB?


THX
Kajiro

[Patoooey]

Originally posted by Kajiro
2 - If the data in DataSet is changed, how can I update the changes to the Access DB?

Small sample that shows use of the DataAdapter.CommandBuilder to handle small modifications of a DB. Uses SqlClient but the same applies to OleDb. Remove // from the section of code you wish to test.

John

<%@ Page Language="C#" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">
    
    public static SqlDataAdapter CreateSqlDataAdapter()
    {
        SqlConnection nwindConn = new SqlConnection("server=\'creole\\netsdk\'; trusted_connection=true; Database=\'Northwind\'");
        SqlDataAdapter objDataAdapter = new SqlDataAdapter();
        objDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
    
        objDataAdapter.SelectCommand = new SqlCommand("SELECT CustomerID, CompanyName FROM CUSTOMERS ORDER BY CustomerID", nwindConn);
        objDataAdapter.InsertCommand = new SqlCommand("INSERT INTO Customers (CustomerID, CompanyName) " +
                                                "VALUES (@CustomerID, @CompanyName)", nwindConn);
        objDataAdapter.UpdateCommand = new SqlCommand("UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName " +
                                                "WHERE CustomerID = @oldCustomerID", nwindConn);
        objDataAdapter.DeleteCommand = new SqlCommand("DELETE FROM Customers WHERE CustomerID = @CustomerID", nwindConn);
    
        objDataAdapter.InsertCommand.Parameters.Add("@CustomerID", SqlDbType.Char, 5, "CustomerID");
        objDataAdapter.InsertCommand.Parameters.Add("@CompanyName", SqlDbType.VarChar, 40, "CompanyName");
    
        objDataAdapter.UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.Char, 5, "CustomerID");
        objDataAdapter.UpdateCommand.Parameters.Add("@CompanyName", SqlDbType.VarChar, 40, "CompanyName");
        objDataAdapter.UpdateCommand.Parameters.Add("@oldCustomerID", SqlDbType.Char, 5, "CustomerID").SourceVersion = DataRowVersion.Original;
    
        objDataAdapter.DeleteCommand.Parameters.Add("@CustomerID", SqlDbType.Char, 5, "CustomerID").SourceVersion = DataRowVersion.Original;
        return objDataAdapter;
    }
    
    void Page_Load(Object sender, EventArgs e) {
        SqlDataAdapter da = CreateSqlDataAdapter();
        DataSet ds = new DataSet();
    
        da.Fill(ds, "Customers");
    
        // modify existing data
        //
        //ds.Tables["Customers"].Rows[0][1] = "John William";
        //ds.Tables["Customers"].Rows[1][1] = "Anuer Holdings";
        //da.Update(ds, "Customers");
    //-----------------------------------------------------
        // add new row
        //
        //DataRow myRow;
        //myRow = ds.Tables["Customers"].NewRow();
        //myRow["CustomerID"] = "JW";
        //myRow["CompanyName"] = "John William";
        //ds.Tables["Customers"].Rows.Add(myRow);
        //da.Update(ds, "Customers");
    //-----------------------------------------------------
        // find and delete existing row
        //
        // Find looks at columns that are primary keys
        //DataRow foundRow;
        //foundRow = ds.Tables["Customers"].Rows.Find("JW");
        //if (foundRow != null) {
        //    foundRow.Delete();
        //    da.Update(ds, "Customers");
        //    Label1.Text = "Row found and deleted!";
        //}
        //else {
        //    Label1.Text = "No Row found!";
        //}
    
        DataGrid1.DataSource = ds.Tables["Customers"].DefaultView;
        DataGrid1.DataBind();
        da.Dispose();
    }

</script>
<html>
<head>
</head>
<body>
    <form runat="server">
        <p>
            <asp:DataGrid id="DataGrid1" runat="server" CellPadding="4" BackColor="White" BorderColor="#336666" BorderWidth="3px" GridLines="Horizontal" BorderStyle="Double" Height="226px" Width="590px">
                <FooterStyle forecolor="#333333" backcolor="White"></FooterStyle>
                <HeaderStyle font-bold="True" forecolor="White" backcolor="#336666"></HeaderStyle>
                <PagerStyle horizontalalign="Center" forecolor="White" backcolor="#336666" mode="NumericPages"></PagerStyle>
                <SelectedItemStyle font-bold="True" forecolor="White" backcolor="#339966"></SelectedItemStyle>
                <AlternatingItemStyle backcolor="WhiteSmoke"></AlternatingItemStyle>
                <ItemStyle forecolor="#333333" backcolor="White"></ItemStyle>
            </asp:DataGrid>
        </p>
        <p>
        </p>
        <p>
            <asp:Label id="Label1" runat="server" Width="426px">Label</asp:Label>
        </p>
        <!-- Insert content here -->
    </form>
</body>
</html>