locked
Insert multiple records RRS feed

  • Question

  • User-2146101396 posted

    In visual studio 2005 asp net v2  i managed to insert multiple records in an sql server table by using a form view and the following code:

     Formview:

     

    <asp:FormView ID="ConsessionFormView" runat="server" DataKeyNames="SK" AllowPaging="True" CellPadding="4" ForeColor="#333333" Width="583px" DefaultMode="Insert" style="left: 30px; ; top: 0px" OnItemInserting="ConsessionFormView_ItemInserting" oniteminserted="ConsessionFormView_ItemInserted" onmodechanged="ConsessionFormView_ModeChanged"

    >

    <EditItemTemplate>

    SK:

    <asp:Label ID="SKLabel1" runat="server" Text='<%# Eval("SK") %>'></asp:Label><br />

    vendorno:

    <br />

    <asp:DropDownList DataSourceID="SqlDataSource2" DataTextField="vendorno" DataValueField="vendorno" ID="DropDownList1" Runat="server" selectedvalue='<%# Bind("vendorno") %>' Width="167px">

     

    </asp:DropDownList><br />

    ItemCategory:

    <asp:TextBox ID="ItemCategoryTextBox" runat="server" Text='<%# Bind("ItemCategory") %>'>

    </asp:TextBox><br />

    StoreName:

    <br />

    <asp:DropDownList DataSourceID="SqlDataSource1" DataTextField="storename" DataValueField="storename" ID="DropDownList2" Runat="server" selectedvalue='<%# Bind("Storename") %>' Width="162px">

    </asp:DropDownList><br />

    Commission:

    <asp:TextBox ID="CommissionTextBox" runat="server" Text='<%# Bind("Commission") %>'>

    </asp:TextBox><br />

    <asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" CommandName="Update"

    Text="Update">

    </asp:LinkButton>

    <asp:LinkButton ID="UpdateCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"

    Text="Cancel">

    </asp:LinkButton>

    </EditItemTemplate>

    <InsertItemTemplate>

    vendorno:

    <asp:TextBox ID="vendornoTextBox" runat="server" Text='<%# Bind("vendorno") %>'>

    </asp:TextBox>

    <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="vendornoTextBox"

    ErrorMessage="Please enter a Vendor Number"></asp:RequiredFieldValidator><br />

    ItemCategory:

    <asp:TextBox ID="ItemCategoryTextBox" runat="server" Text='<%# Bind("ItemCategory") %>'>

    </asp:TextBox>

    <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="ItemCategoryTextBox"

    ErrorMessage="Please enter an Item Category"></asp:RequiredFieldValidator><br />

    <br />

    StoreName:&nbsp;<br />

    <asp:ListBox ID="ListBox1" runat="server" DataSourceID="SqlDataSource1" DataTextField="StoreName" DataValueField="StoreName" SelectionMode="Multiple" style="left: 85px; ; top: -15px" AppendDataBoundItems="True" Height="130px" Width="197px" selectedvalue='<%# Bind("Storename") %>'>

    <asp:ListItem Selected="true">-Please select the stores-</asp:ListItem>

    </asp:ListBox><br />

    <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="ListBox1"

    ErrorMessage="Please Select at least a store name" InitialValue="-Please select the stores-" style="left: 287px; ; top: -34px"></asp:RequiredFieldValidator><br />

    Commission:

    <asp:TextBox ID="CommissionTextBox" runat="server" Text='<%# Bind("Commission") %>'>

    </asp:TextBox>

    <asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server" ControlToValidate="CommissionTextBox"

    ErrorMessage="Please enter a commission"></asp:RequiredFieldValidator>

    <asp:CompareValidator ID="CompareValidator1" runat="server" ControlToValidate="CommissionTextBox"

    ErrorMessage="Only numeric Values" Operator="DataTypeCheck" Type="Currency" style="left: -170px; ; top: 16px"></asp:CompareValidator><br />

    <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert"

    Text="Insert" >

    </asp:LinkButton>

    </InsertItemTemplate>

    <ItemTemplate>

    SK:

    <asp:Label ID="SKLabel" runat="server" Text='<%# Eval("SK") %>'></asp:Label><br />

    vendorno:

    <asp:Label ID="vendornoLabel" runat="server" Text='<%# Bind("vendorno") %>'>

    </asp:Label><br />

    ItemCategory:

    <asp:Label ID="ItemCategoryLabel" runat="server" Text='<%# Bind("ItemCategory") %>'>

    </asp:Label><br />

    StoreName:

    <asp:Label ID="StoreNameLabel" runat="server" Text='<%# Bind("StoreName") %>'></asp:Label><br />

    Commission:

    <asp:Label ID="CommissionLabel" runat="server" Text='<%# Bind("Commission") %>'>

    </asp:Label><br />

    <asp:LinkButton ID="EditButton" runat="server" CausesValidation="False" CommandName="Edit"

    Text="Edit">

    </asp:LinkButton>

    </ItemTemplate>

    <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />

    <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />

    <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />

    <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />

    </asp:FormView>

    Code:

    Protected Sub ConsessionFormView_ItemInserting(ByVal sender As Object, ByVal e As FormViewInsertEventArgs)

    Dim box1 As TextBox = CType(Me.ConsessionFormView.FindControl("vendornotextbox"), TextBox)

    Dim box2 As TextBox = CType(Me.ConsessionFormView.FindControl("itemcategorytextbox"), TextBox)

    Dim lst As ListBox = CType(Me.ConsessionFormView.FindControl("ListBox1"), ListBox)

    Dim box3 As TextBox = CType(Me.ConsessionFormView.FindControl("commissiontextbox"), TextBox)

    Dim i As Integer = 0

    Dim objConnection As SqlConnection

    Dim objCmd As SqlCommand

    Dim strConnection As String = ConfigurationManager.ConnectionStrings("ermesSAConnectionString").ConnectionStringobjConnection = New SqlConnection(strConnection)

    objConnection.Open()

    Do While (i < lst.Items.Count)

    Dim item As String = ""

    If lst.Items(i).Selected Then

    item = lst.Items(i).Value

    End If

     

    If item <> "" Then

     

    Dim sql As String = "INSERT INTO [FactConcessionMap_100] ([vendorno], [ItemCategory], [StoreName], [Commission]) VALUES ('" + box1.Text + "'," + box2.Text + ", '" + item + "', " + box3.Text + ")"

    'use ado.net to insert records into db

     

    objCmd =
    New SqlCommand(sql, objConnection)

     

    ' execute the command

    i = (i + 1)

    objCmd.ExecuteNonQuery()

    Else

    i = (i + 1)

    End If

    Loop

    e.Cancel = True

     

    objCmd.Connection.Close()

    messagelabel.Text = "Record inserted successfully."

    box1.Text = ""

    box2.Text = ""

    box3.Text = ""

    lst.SelectedIndex = 0

    End Sub

     How can i implement the above functionality using the dynamic data site web application from the insert.aspx template? At the moment i can insert an individual record to my required table.

    Thanxs in advance

    Tuesday, September 16, 2008 7:21 AM

Answers

  • User-330204900 posted

    Yep got you you want a FieldTemplate or standard control that allows a user to select many items and then when the insert is clicked you then insert n records; yes?

    protected void DetailsView1_ItemInserting(object sender, DetailsViewInsertEventArgs e)
    {
    	// I think  you could implement your logic to submit more than one record here
    	
    	// items here is IOrdersDictionary of key/.values pairs
    	var items = e.Values;
    	
    	// you coulds then iterate through you many items control Listbox CheckBoxList
    	// and then insert each with the Insert method of the LinqDataSource
    	DetailsDataSource.Insert(items);
    }

    Hope this helps [:D]

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 16, 2008 4:47 PM
  • User-330204900 posted

    Download the DD wizard and have a look at the markup generated when you choose to create a custom page.

     Download Dynamic Data Wizard

    Hope this helps [:D]

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 17, 2008 5:02 AM

All replies

  • User-330204900 posted

    How do you mean "insert multiple records" are you allowing the user to add several records at once or is it like a gridview? could you explain or show a screen shot?

    Tuesday, September 16, 2008 11:02 AM
  • User-2146101396 posted

    Lets say i have table A with the fields  vendorno,storename,commision and category. From the detailsview (insert.aspx template) i want the user to be able to enter

    data in vendorno,commision and category textboxes and select one ore more data from a listbox that will contain some storenames devired from another table.

    Example:     Vendor no: 123

                       Category:32

                        Storename: Store1,Store2 Store4(selected from a multiple listbox)

                       Commission:23.00

    After hitting the insert button i want 3 records to be inserted to the table with the same data entered in vendorno,category and commsion together with the differend selected store names.

    I hope you can understand now!

    Thanxs

    Tuesday, September 16, 2008 1:58 PM
  • User-330204900 posted

    Yep got you you want a FieldTemplate or standard control that allows a user to select many items and then when the insert is clicked you then insert n records; yes?

    protected void DetailsView1_ItemInserting(object sender, DetailsViewInsertEventArgs e)
    {
    	// I think  you could implement your logic to submit more than one record here
    	
    	// items here is IOrdersDictionary of key/.values pairs
    	var items = e.Values;
    	
    	// you coulds then iterate through you many items control Listbox CheckBoxList
    	// and then insert each with the Insert method of the LinqDataSource
    	DetailsDataSource.Insert(items);
    }

    Hope this helps [:D]

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 16, 2008 4:47 PM
  • User-2146101396 posted

    I did it with the following code:

    Protected Sub DetailsView1_ItemInserting(ByVal sender As Object, ByVal e As DetailsViewInsertEventArgs)

    Dim box1 As TextBox = CType(Me.DetailsView1.FindControl("vendornotextbox"), TextBox)

    Dim box2 As TextBox = CType(Me.DetailsView1.FindControl("itemcategorytextbox"), TextBox)

    Dim lst As ListBox = CType(Me.DetailsView1.FindControl("ListBox1"), ListBox)

    Dim box3 As TextBox = CType(Me.DetailsView1.FindControl("commissiontextbox"), TextBox)

    Dim i As Integer = 0

    Dim objConnection As SqlConnection

    Dim objCmd As SqlCommand

    Dim strConnection As String = ConfigurationManager.ConnectionStrings("ermesSAConnectionString").ConnectionStringobjConnection = New SqlConnection(strConnection)

    objConnection.Open()

    Do While (i < lst.Items.Count)

    Dim item As String = ""

    If lst.Items(i).Selected Then

    item = lst.Items(i).Value

    End If

    If item <> "" Then

    Dim sql As String = "INSERT INTO [FactConcessionMap_100] ([vendorno], [ItemCategory], [StoreName], [Commission]) VALUES ('" + box1.Text + "'," + box2.Text + ", '" + item + "', " + box3.Text + ")"

    'use ado.net to insert records into db

    objCmd = New SqlCommand(sql, objConnection)

    ' execute the command

    i = (i + 1)

    objCmd.ExecuteNonQuery()

    Else

    i = (i + 1)

    End If

    Loop

    e.Cancel = True

    objCmd.Connection.Close()

    Response.Redirect(table.ListActionPath)

    end sub

     

     It works but the validation i implemented before using a partial class is not working anymore? Why is that?

     

     Imports System.Web.DynamicData

    Imports System.ComponentModel.DataAnnotations

    <MetadataType(GetType(concessionmetadata))> Partial Public Class FactConcessionMap_100

    End ClassPublic Class concessionmetadata

    <Required()> _

    Public VendorNo As Object

    <Required()> _

    Public ItemCategory As Object

    <Required()> _

    Public StoreName As Object

    <Required()> _

    Public Commission As Object

    End Class

    Wednesday, September 17, 2008 2:08 AM
  • User-330204900 posted

    Is the aspx page using DynamicField or DynamicControl, if not you will not get any of the Dynamic Data validation as this is built into the FieldTemplate used by DynamicControl and DynamicField.

    Wednesday, September 17, 2008 3:57 AM
  • User-2146101396 posted

    No i am using plain textboxes and a listbox. How can i use the controls you are saying?

    Wednesday, September 17, 2008 4:40 AM
  • User-330204900 posted

    Download the DD wizard and have a look at the markup generated when you choose to create a custom page.

     Download Dynamic Data Wizard

    Hope this helps [:D]

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 17, 2008 5:02 AM
  • User-2146101396 posted

    How can i state the validation group after clicking on the insert event of the details view?

    Can i include it in

    Protected Sub DetailsView1_ItemInserting(ByVal sender As Object, ByVal e As DetailsViewInsertEventArgs)

    Wednesday, September 17, 2008 5:34 AM
  • User-330204900 posted

    Sorry not sure what you mean.

    Wednesday, September 17, 2008 5:48 AM
  • User-2146101396 posted

    Nevermind i used fieldvalidators instead of the dynamic. the wizard though helped me a lot.

    thanxs

    Wednesday, September 17, 2008 6:50 AM