Adds, Updates using a DataSet

[wheels]

I'm in desperate need of the code to Add New records (and modify records) to a Database using the DataSet and DataAdapter objects..

in my old .asp code, I could simply (for a new record) do a:

Recordset.AddNew
Recordset("Field1") = Value1
Recordset("Field2") = Value2
......
....
Recordset.Update


how do I do something similar to this in .aspx with dataset/dataadapters ?


thanks,

[Patoooey]

Originally posted by wheels
I'm in desperate need of the code to Add New records (and modify records) to a Database using the DataSet and DataAdapter objects..
.
.
how do I do something similar to this in .aspx with dataset/dataadapters ?

I just started to use the Update method of the DataAdapter a few days ago. Been splicing together SQL strings up till now.

This was my first attempt at it. Started off with the CreateSqlDataAdapter function from MSDN and added to it. Nothing fancy but should get you started. Just un-comment the areas you want to test out.....Modify, add or delete.

Uses the NorthWind Table that comes with MSDE/SQL 2000.

<%@ 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"] = "JWM";
        //myRow["CompanyName"] = "John William";
        //ds.Tables["Customers"].Rows.Add(myRow);
        //da.Update(ds, "Customers");
    //-----------------------------------------------------
        // find and delete existing row(looks for row added above)
        //
        // Find looks at columns that are primary keys
        //DataRow foundRow;
        //foundRow = ds.Tables["Customers"].Rows.Find("JWM");
        //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" Width="590px" Height="226px" BorderStyle="Double" GridLines="Horizontal" BorderWidth="3px" BorderColor="#336666" BackColor="White" CellPadding="4">
                <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>
            &nbsp;
        </p>
        <p>
            <asp:Label id="Label1" runat="server" Width="426px">Label</asp:Label>
        </p>
        <!-- Insert content here -->
    </form>
</body>
</html>