Insert multiple records

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

Answer 1

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?

Answer 2

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

Answer 3

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]

Answer 4

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

Answer 5

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.

Answer 6

User-2146101396 posted

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

Answer 7

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]

Answer 8

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)

Answer 9

User-330204900 posted

Sorry not sure what you mean.

Answer 10

User-2146101396 posted

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

thanxs