locked
Possible to update and insert from formview? RRS feed

  • Question

  • User1735635622 posted

    I have a formview where DefaultMode="Insert". Underneath, I have a gridview that displays the information from the inserts. Is there a way to update the information as well as insert? Maybe there is a better way to do what I'm trying to do. Any help is appreciated. Thanks!

    Friday, September 3, 2010 9:25 AM

Answers

  • User-1199946673 posted

    Code is below:

    Did you read the link about the DataKeyNames Property? You need to have a Primary Key field, in the example below I added a ID column (datatype autonumber). Also like for the insert, you need an updatecommand. And you should provide parameters:

            <asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" DataKeyNames="ID" DataSourceID="DetailsViewDataSource" DefaultMode="Insert" oniteminserted="DetailsView1_ItemInserted">
                <Fields>
                    <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" ReadOnly="True" SortExpression="ID" />
                    <asp:BoundField DataField="column1" HeaderText="column1" />
                    <asp:BoundField DataField="column2" HeaderText="column2" />
                    <asp:BoundField DataField="column3" HeaderText="column3" />
                    <asp:BoundField DataField="column4" HeaderText="column4" />
                    <asp:BoundField DataField="column5" HeaderText="column5" />
                    <asp:BoundField DataField="column6" HeaderText="column6" />
                    <asp:CommandField ShowInsertButton="True" />
                </Fields>
            </asp:DetailsView>
            <asp:AccessDataSource ID="DetailsViewDataSource" runat="server" 
                DataFile="~/App_Data/database.mdb" 
                InsertCommand="INSERT INTO table1(column1, column2, column3, column4, column5, column6) VALUES (?, ?, ?, ?, ?, ?)" >
                <InsertParameters>
                    <asp:Parameter Name="column1" />
                    <asp:Parameter Name="column2" />
                    <asp:Parameter Name="column3" />
                    <asp:Parameter Name="column4" />
                    <asp:Parameter Name="column5" />
                    <asp:Parameter Name="column6" />
                </InsertParameters>
            </asp:AccessDataSource>
    
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ID" DataSourceID="GridViewDataSource">
                <Columns>
                    <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
                    <asp:BoundField DataField="column1" HeaderText="column1" />
                    <asp:BoundField DataField="column2" HeaderText="column2" />
                    <asp:BoundField DataField="column3" HeaderText="column3" />
                    <asp:BoundField DataField="column4" HeaderText="column4" />
                    <asp:BoundField DataField="column5" HeaderText="column5" />
                    <asp:BoundField DataField="column6" HeaderText="column6" />
                </Columns>
            </asp:GridView>
            <asp:AccessDataSource ID="GridViewDataSource" runat="server" 
                DataFile="~/App_Data/database.mdb" 
                DeleteCommand="DELETE FROM table1 WHERE (ID = ?)" 
                SelectCommand="SELECT ID, column1, column2, column3, column4, column5, column6 FROM table1" 
                UpdateCommand="UPDATE table1 SET column1 = ?, column2 = ?, column3 = ?, column4 = ?, column5 = ?, column6 = ? WHERE (ID = ?)">
                <UpdateParameters>
                    <asp:Parameter Name="column1" />
                    <asp:Parameter Name="column2" />
                    <asp:Parameter Name="column3" />
                    <asp:Parameter Name="column4" />
                    <asp:Parameter Name="column5" />
                    <asp:Parameter Name="column6" />
                    <asp:Parameter Name="ID" />
                </UpdateParameters>
                <DeleteParameters>
                    <asp:Parameter Name="ID" />
                </DeleteParameters>
            </asp:AccessDataSource>
    


    The only code you need is to refresh the gridview when you inserted a new record in the detailsview:

        protected void DetailsView1_ItemInserted(object sender, DetailsViewInsertedEventArgs e)
        {
            GridView1.DataBind();
        }
    


    You better put the database in the App_Data folder, which will prevent the database form being downloaded. Also, when connecting to a MDB file, use another connectionstring:

    http://www.mikesdotnetting.com/Article/78/AccessDataSource-SqlDataSource-and-connecting-to-Access-databases-in-ASP.NET

    More info on parameter queries, which are used in the (access)datasource controls:

    http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 3, 2010 6:58 PM

All replies

  • User77042963 posted

    If you have a primary key set for your table, you should be able to edit/delete from your formview by configuring the formview.

    Friday, September 3, 2010 10:29 AM
  • User1735635622 posted

    If you have a primary key set for your table, you should be able to edit/delete from your formview by configuring the formview.

    I need to insert and update. I can insert fine, but not sure how to implement update into the formview along with insert.

    Friday, September 3, 2010 10:34 AM
  • User-1199946673 posted

    I need to insert and update. I can insert fine, but not sure how to implement update into the formview along with insert.
     

    It more or less works the same as insert. The one important issue is that you need to set the DataKeyNames Property of the FormView to the Primary Key Field(s) of your table...

    If you show us your formview and datasource you've so far, we can assist you?

    Friday, September 3, 2010 1:57 PM
  • User1735635622 posted

    I need to insert and update. I can insert fine, but not sure how to implement update into the formview along with insert.
     

    It more or less works the same as insert. The one important issue is that you need to set the DataKeyNames Property of the FormView to the Primary Key Field(s) of your table...

    If you show us your formview and datasource you've so far, we can assist you?

    Code is below:

     

    <asp:Calendar ID="calendarInstallDate" runat="server" BackColor="White"
            BorderColor="Black" DayNameFormat="Shortest" Font-Names="Times New Roman"
            Font-Size="10pt" ForeColor="Black" Height="220px" NextPrevFormat="FullMonth"
            TitleFormat="Month" Width="400px"
            onselectionchanged="calendarInstallDate_SelectionChanged" >        
            <DayHeaderStyle BackColor="#CCCCCC" Font-Bold="True" Font-Size="7pt"
                ForeColor="#333333" Height="10pt" />
            <DayStyle Width="14%" />
            <NextPrevStyle Font-Size="8pt" ForeColor="White" />
            <OtherMonthDayStyle ForeColor="#999999" />
            <SelectedDayStyle BackColor="#CC3333" ForeColor="White" />
            <SelectorStyle BackColor="#CCCCCC" Font-Bold="True" Font-Names="Verdana"
                Font-Size="8pt" ForeColor="#333333" Width="1%" />
            <TitleStyle BackColor="Black" Font-Bold="True" Font-Size="13pt"
                ForeColor="White" Height="14pt" />
            <TodayDayStyle BackColor="#CCCC99" />
        </asp:Calendar>
        <asp:Button ID="Button1" runat="server" Text="Button" onclick="Button1_Click" />


    <asp:FormView ID="FormView1" runat="server"  
            DataSourceID="AccessDataSource1" DefaultMode="Insert">
            <EditItemTemplate>
                Label:
                <asp:TextBox ID="txtBox1" runat="server" 
                    Text='<%# Bind("Column1") %>' />
                <br />
                Label:
                <asp:TextBox ID="txtBox2" runat="server" Text='<%# Bind("Column2") %>' />
                <br />
                Label:
                <asp:TextBox ID="txtBox3" runat="server" 
                    Text='<%# Bind("Column3") %>' />
                <br />
                Label:
                <asp:TextBox ID="txtBox4" runat="server" Text='<%# Bind("Column4") %>' />
                <br />
                Label:
                <asp:TextBox ID="txtBox5" runat="server" 
                    Text='<%# Bind("Column5") %>' />
                <br />
                Label:
                <asp:TextBox ID="txtBox6" runat="server" 
                    Text='<%# Bind("Column6") %>' />
                <br />
                <asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" 
                    CommandName="Update" Text="Update" />
                 <asp:LinkButton ID="UpdateCancelButton" runat="server" 
                    CausesValidation="False" CommandName="Cancel" Text="Cancel" />
            </EditItemTemplate>
        <InsertItemTemplate>
            Label:
                <asp:TextBox ID="txtBox1" runat="server" 
                    Text='<%# Bind("Column1") %>' />
                <br />
                Label:
                <asp:TextBox ID="txtBox2" runat="server" Text='<%# Bind("Column2") %>' />
                <br />
                Label:
                <asp:TextBox ID="txtBox3" runat="server" 
                    Text='<%# Bind("Column3") %>' />
                <br />
                Label:
                <asp:TextBox ID="txtBox4" runat="server" Text='<%# Bind("Column4") %>' />
                <br />
                Label:
                <asp:TextBox ID="txtBox5" runat="server" 
                    Text='<%# Bind("Column5") %>' />
                <br />
                Label:
                <asp:TextBox ID="txtBox6" runat="server" 
                    Text='<%# Bind("Column6") %>' />
                <br />
            <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" 
                CommandName="Insert" Text="Insert" />
             <asp:LinkButton ID="InsertCancelButton" runat="server" 
                CausesValidation="False" CommandName="Cancel" Text="Cancel" />
        </InsertItemTemplate>
            <ItemTemplate>
                Label:
                <asp:Label ID="txtBox1" runat="server" 
                    Text='<%# Bind("Column1") %>' />
                <br />
                Label:
                <asp:Label ID="txtBox2" runat="server" Text='<%# Bind("Column2") %>' />
                <br />
                Label:
                <asp:Label ID="txtBox3" runat="server" 
                    Text='<%# Bind("Column3") %>' />
                <br />
                Label:
                <asp:Label ID="txtBox4" runat="server" Text='<%# Bind("Column4") %>' />
                <br />
                Label:
                <asp:Label ID="txtBox5" runat="server" 
                    Text='<%# Bind("Column5") %>' />
                <br />
                Label:
                <asp:Label ID="txtBox6" runat="server" 
                    Text='<%# Bind("Column6") %>' />
                <br />
            </ItemTemplate>
        </asp:FormView>
        <asp:AccessDataSource ID="AccessDataSource1" runat="server" 
            DataFile="C:\Inetpub\wwwroot\Database.mdb"       
            SelectCommand="SELECT [Column1], [Column2], [Column3], [Column4], [Column5], [Column6] FROM [Table1]"
            InsertCommand="INSERT INTO [Table1]([Column1], [Column2], [Column3], [Column4], [Column5], [Column6]) VALUES (Label1, Label2, Label3, Label4, Label5, Label6)">
        </asp:AccessDataSource>
        <table class="style1" runat="server" id="tblSpreadsheet">
            <tr>
                <td>
                    <asp:GridView ID="gvSpreadsheet" runat="server" AutoGenerateColumns="False" 
            BackColor="White" BorderColor="White" BorderStyle="Ridge" BorderWidth="2px" 
            CellPadding="3" CellSpacing="1"  
            GridLines="None" AllowSorting="True" DataSourceID="AccessDataSource2">
            <Columns>
                <asp:BoundField DataField="Column1" HeaderText="Header1" 
                    SortExpression="Column1" />
                <asp:BoundField DataField="Column2" HeaderText="Header2" 
                    SortExpression="Column2" />
                <asp:BoundField DataField="Column3" HeaderText="Header3" 
                    SortExpression="Column3" />
                <asp:BoundField DataField="Column4" HeaderText="Header4" 
                    SortExpression="Column4" />
                <asp:BoundField DataField="Column5" HeaderText="Header5" 
                    SortExpression="Column5" />
                <asp:BoundField DataField="Column6" HeaderText="Header6" 
                    SortExpression="Column6" />
            </Columns>
            <FooterStyle BackColor="#C6C3C6" ForeColor="Black" />
            <HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#E7E7FF" />
            <PagerStyle BackColor="#C6C3C6" ForeColor="Black" HorizontalAlign="Right" />
            <RowStyle BackColor="#DEDFDE" ForeColor="Black" />
            <SelectedRowStyle BackColor="#9471DE" Font-Bold="True" ForeColor="White" />
            <SortedAscendingCellStyle BackColor="#F1F1F1" />
            <SortedAscendingHeaderStyle BackColor="#594B9C" />
            <SortedDescendingCellStyle BackColor="#CAC9C9" />
            <SortedDescendingHeaderStyle BackColor="#33276A" />
        </asp:GridView>
        <asp:AccessDataSource ID="AccessDataSource2" runat="server" 
            DataFile="C:\Inetpub\wwwroot\Database.mdb" 
            SelectCommand="SELECT 

    And the code behind:
    public partial class Checkouts : System.Web.UI.Page
        {
            protected void Page_Load(object sender, EventArgs e)
            {
                     
            }
            
            protected void btnSave_Click(object sender, EventArgs e)
            {
                
            }
    
            protected void calendarInstallDate_SelectionChanged(object sender, EventArgs e)
            {
                
            }
    
            protected void Button1_Click(object sender, EventArgs e)
            {
                this.gvSpreadsheet.DataSource = null;
                this.gvSpreadsheet.DataBind();           
    
                using (OleDbConnection c = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\inetpub\wwwroot\Database.mdb"))
                {
                    c.Open();
    
                    DataTable dt = new DataTable();
                    try
                    {
                        string date = "";
                        date = calendarInstallDate.SelectedDate.ToString("MM/dd/yyyy");
    
                        string sqlStatement = "SELECT [Column1], [Column2], [Column3], [Column4], [Column5], [Column6] FROM Table1 WHERE InstallDate = '" + date + "'";
    
                        OleDbCommand sqlCmd = new OleDbCommand(sqlStatement, c);
    
                        OleDbDataAdapter sqlDa = new OleDbDataAdapter(sqlCmd);
    
                        sqlDa.Fill(dt);
    
                        if (dt.Rows.Count > 0)
                        {
    
                            this.gvSpreadsheet.DataSource = dt;
    
                            this.gvSpreadsheet.DataBind();
    
                        }
    
                    }
    
                    catch (System.Data.SqlClient.SqlException ex)
                    {
    
                        string msg = "Fetch Error:";
    
                        msg += ex.Message;
    
                        throw new Exception(msg);
    
                    }
    
                    finally
                    {
    
                        c.Close();
    
                    }
    
                    this.tblSpreadsheet.Visible = true;
                }
            }
        }

    FROM [Table1]"> </asp:AccessDataSource></td> </tr> <tr> <td> <asp:Button ID="btnSave" runat="server" Text="Save" onclick="btnSave_Click" Visible="False" /> </td> </tr> </table> <br /> <br /> </asp:Content>




    Friday, September 3, 2010 2:38 PM
  • User77042963 posted

    Set up a formview with insert and edit functions is very easy, but you must have a primary key defined for your table. Aer that you can drag and drop on the design surface from VWD/VS in minutes to configure your formview with INSERT/EDIT/DELETE functions without a single line of code. 

    Friday, September 3, 2010 4:29 PM
  • User-1199946673 posted

    Code is below:

    Did you read the link about the DataKeyNames Property? You need to have a Primary Key field, in the example below I added a ID column (datatype autonumber). Also like for the insert, you need an updatecommand. And you should provide parameters:

            <asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" DataKeyNames="ID" DataSourceID="DetailsViewDataSource" DefaultMode="Insert" oniteminserted="DetailsView1_ItemInserted">
                <Fields>
                    <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" ReadOnly="True" SortExpression="ID" />
                    <asp:BoundField DataField="column1" HeaderText="column1" />
                    <asp:BoundField DataField="column2" HeaderText="column2" />
                    <asp:BoundField DataField="column3" HeaderText="column3" />
                    <asp:BoundField DataField="column4" HeaderText="column4" />
                    <asp:BoundField DataField="column5" HeaderText="column5" />
                    <asp:BoundField DataField="column6" HeaderText="column6" />
                    <asp:CommandField ShowInsertButton="True" />
                </Fields>
            </asp:DetailsView>
            <asp:AccessDataSource ID="DetailsViewDataSource" runat="server" 
                DataFile="~/App_Data/database.mdb" 
                InsertCommand="INSERT INTO table1(column1, column2, column3, column4, column5, column6) VALUES (?, ?, ?, ?, ?, ?)" >
                <InsertParameters>
                    <asp:Parameter Name="column1" />
                    <asp:Parameter Name="column2" />
                    <asp:Parameter Name="column3" />
                    <asp:Parameter Name="column4" />
                    <asp:Parameter Name="column5" />
                    <asp:Parameter Name="column6" />
                </InsertParameters>
            </asp:AccessDataSource>
    
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ID" DataSourceID="GridViewDataSource">
                <Columns>
                    <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
                    <asp:BoundField DataField="column1" HeaderText="column1" />
                    <asp:BoundField DataField="column2" HeaderText="column2" />
                    <asp:BoundField DataField="column3" HeaderText="column3" />
                    <asp:BoundField DataField="column4" HeaderText="column4" />
                    <asp:BoundField DataField="column5" HeaderText="column5" />
                    <asp:BoundField DataField="column6" HeaderText="column6" />
                </Columns>
            </asp:GridView>
            <asp:AccessDataSource ID="GridViewDataSource" runat="server" 
                DataFile="~/App_Data/database.mdb" 
                DeleteCommand="DELETE FROM table1 WHERE (ID = ?)" 
                SelectCommand="SELECT ID, column1, column2, column3, column4, column5, column6 FROM table1" 
                UpdateCommand="UPDATE table1 SET column1 = ?, column2 = ?, column3 = ?, column4 = ?, column5 = ?, column6 = ? WHERE (ID = ?)">
                <UpdateParameters>
                    <asp:Parameter Name="column1" />
                    <asp:Parameter Name="column2" />
                    <asp:Parameter Name="column3" />
                    <asp:Parameter Name="column4" />
                    <asp:Parameter Name="column5" />
                    <asp:Parameter Name="column6" />
                    <asp:Parameter Name="ID" />
                </UpdateParameters>
                <DeleteParameters>
                    <asp:Parameter Name="ID" />
                </DeleteParameters>
            </asp:AccessDataSource>
    


    The only code you need is to refresh the gridview when you inserted a new record in the detailsview:

        protected void DetailsView1_ItemInserted(object sender, DetailsViewInsertedEventArgs e)
        {
            GridView1.DataBind();
        }
    


    You better put the database in the App_Data folder, which will prevent the database form being downloaded. Also, when connecting to a MDB file, use another connectionstring:

    http://www.mikesdotnetting.com/Article/78/AccessDataSource-SqlDataSource-and-connecting-to-Access-databases-in-ASP.NET

    More info on parameter queries, which are used in the (access)datasource controls:

    http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 3, 2010 6:58 PM
  • User1735635622 posted

    You are using DetailsView as opposed to thr FOrmView I was using. Will that make any difference?

    Sunday, September 5, 2010 1:22 PM
  • User-1199946673 posted

    You are using DetailsView as opposed to thr FOrmView I was using. Will that make any difference?

     

    Not really, bit since you're using labels and textboxes and then a break, you can just as well use a detailsview.

    Sunday, September 5, 2010 3:22 PM
  • User1735635622 posted

    You are using DetailsView as opposed to thr FOrmView I was using. Will that make any difference?

     

    Not really, bit since you're using labels and textboxes and then a break, you can just as well use a detailsview.


    I got it working through Update and Insert under the FormView, but the GridView always shows data from the entire table. How can I make it only show dates from the SelectedDate from the calendar control?

    Sunday, September 5, 2010 3:34 PM
  • User-1199946673 posted

    How can I make it only show dates from the SelectedDate from the calendar control?
     

            <asp:AccessDataSource ID="GridViewDataSource" runat="server" 
                DataFile="~/App_Data/database.mdb" 
                SelectCommand="SELECT ID, column1, column2, column3, column4, column5, column6 FROM table1 WHERE installdate = ?">
                <SelectParameters>
                    <asp:ControlParameter ControlID="calendarInstallDate" PropertyName="SelectedDate" />
                </SelectParameters>
            </asp:AccessDataSource>


     

     

    Sunday, September 5, 2010 4:04 PM
  • User1735635622 posted

    How can I make it only show dates from the SelectedDate from the calendar control?
     

    1. <asp:AccessDataSource ID="GridViewDataSource" runat="server"   
    2.     DataFile="~/App_Data/database.mdb"   
    3.     SelectCommand="SELECT ID, column1, column2, column3, column4, column5, column6 FROM table1 WHERE installdate = ?">  
    4.     <SelectParameters>  
    5.         <asp:ControlParameter ControlID="calendarInstallDate" PropertyName="SelectedDate" />  
    6.     </SelectParameters>  
    7. </asp:AccessDataSource>  
            <asp:AccessDataSource ID="GridViewDataSource" runat="server" 
                DataFile="~/App_Data/database.mdb" 
                SelectCommand="SELECT ID, column1, column2, column3, column4, column5, column6 FROM table1 WHERE installdate = ?">
                <SelectParameters>
                    <asp:ControlParameter ControlID="calendarInstallDate" PropertyName="SelectedDate" />
                </SelectParameters>
            </asp:AccessDataSource>


     


    When I put that in, it still shows all the data from the table and not just the SelectedDate. Thanks for all the help btw.


    Sunday, September 5, 2010 4:40 PM
  • User796826274 posted

    Youtube video on how to insert data into a database using formview, its quick and uses hardly any code maybe it will help you?

    http://www.youtube.com/watch?v=Ssmq5fZnZw4

    Sunday, September 5, 2010 6:09 PM