Albee_J
asked on
ASP.net 2.0 Insert record multiple times based on criteria
I am trying to find the best way to handle this scenerio. I have a web application that is going to be used for our Sales Dept. to enter their Web Ad Insertion Orders. There is a dropdownlist that asks them how this will be billed, the choices are:
Monthly
Quarterly
Semi - Annually
Annually
They want me to have records inserted for the appropriate amount of time each will be billed. For example, Monthly would insert 12 records with a start date for each month of whatever they entered into the startdate text box.
Quarterly would insert 4 records with a start date every 3rd month on the day of their initial startdate entry.
Semi-Annually would have 2 records six months apart with the start date the same as the dayof their initial startdate entry.
Etc etc.
I have attached the code for the Web Insertion Form below along with a diagram of the current table structure.
Monthly
Quarterly
Semi - Annually
Annually
They want me to have records inserted for the appropriate amount of time each will be billed. For example, Monthly would insert 12 records with a start date for each month of whatever they entered into the startdate text box.
Quarterly would insert 4 records with a start date every 3rd month on the day of their initial startdate entry.
Semi-Annually would have 2 records six months apart with the start date the same as the dayof their initial startdate entry.
Etc etc.
I have attached the code for the Web Insertion Form below along with a diagram of the current table structure.
<%@ Page Language="VB" MasterPageFile="~/AdMaster.master" Title="Web Insertion Order Form" %>
<%@ Import Namespace="System.Net.Mail" %>
<script language="VB" runat="server">
Dim myConnection As Data.SqlClient.SqlConnection
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Dim rightNow As DateTime = DateTime.Now
lblDateRequested.Text = String.Format("{0:d}", rightNow)
myConnection = New Data.SqlClient.SqlConnection("Server=FMASQL;database=SalesBilling;User ID=****;Password=*****")
End Sub
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
If txtImage.HasFile Then
txtImage.SaveAs("D:\ASPNetSites\WebAdUploads\" & txtImage.FileName)
Label2.Text = "The following file has been uploaded:" + txtImage.FileName
Else
Label2.Text = "No File Uploaded, when you have the image for this ad send it to Jim Gorzek."
End If
Try
Dim insertProject As New Data.SqlClient.SqlCommand("INSERT INTO AdSales (DateRequested,CompanyID,MsgID,Authorized,Phone,Email,SendBillID,LocationID,AdTypeID,MsgPubCodeID,StartDate,EndDate,BillingID,PONumber,SalesRepID,Percentage,Image) VALUES (@DateRequested,@CompanyID,@MsgID,@Authorized,@Phone,@Email,@SendBillID,@LocationID,@AdTypeID,@MsgPubCodeID,@StartDate,@EndDate,@BillingID,@PONumber,@SalesRepID,@Percentage,@Image);", myConnection)
insertProject.Parameters.AddWithValue("@DateRequested", lblDateRequested.Text)
insertProject.Parameters.AddWithValue("@CompanyID", txtCompanyName.SelectedValue)
insertProject.Parameters.AddWithValue("@MsgID", txtMSGID.Text)
insertProject.Parameters.AddWithValue("@Authorized", txtAuthorized.Text)
insertProject.Parameters.AddWithValue("@Phone", txtPhone.Text)
insertProject.Parameters.AddWithValue("@Email", txtEmail.Text)
insertProject.Parameters.AddWithValue("@SendBillID", SendBill.SelectedValue)
insertProject.Parameters.AddWithValue("@LocationID", txtAdLocation.SelectedValue)
insertProject.Parameters.AddWithValue("@AdTypeID", txtAdType.SelectedValue)
insertProject.Parameters.AddWithValue("@MsgPubCodeID", txtMSGPubCodes.SelectedValue)
insertProject.Parameters.AddWithValue("@StartDate", txtStartDate.Text)
insertProject.Parameters.AddWithValue("@EndDate", txtEndDate.Text)
insertProject.Parameters.AddWithValue("@BillingID", txtBilling.SelectedValue)
insertProject.Parameters.AddWithValue("@PONumber", txtPONumber.Text)
insertProject.Parameters.AddWithValue("@SalesRepID", txtSalesRep.SelectedValue)
insertProject.Parameters.AddWithValue("@Percentage", txtPercentage.Checked)
insertProject.Parameters.AddWithValue("@Image", "D:\ASPNetSites\WebAdUploads\" & txtImage.FileName)
myConnection.Open()
insertProject.ExecuteNonQuery()
myConnection.Close()
Catch ex As Exception
lblResponse.Text = "Error saving: " & ex.Message.ToString
End Try
lblResponse.Text = "**Your Ad Insertion has been submitted successfully"
End Sub
</script>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<p style="text-align: left">
<span style="font-size: 11pt; font-family: Arial"><strong>Insertion Order for
<img src="images/Fabcom2.jpg" />
website</strong></span></p>
<p>
<span style="font-size: 11pt; font-family: Arial"><strong>
<asp:Label ID="lblResponse" runat="server" Font-Bold="False" ForeColor="Red" Font-Size="Small"></asp:Label></strong></span><br />
<asp:Label
ID="Label2" runat="server" Font-Bold="False" ForeColor="Red" Font-Size="Small"></asp:Label> </p>
<p>
<span style="font-size: 11pt; font-family: Arial"><strong></strong></span>
<asp:Label ID="lblDateRequested" runat="server" Font-Bold="True"></asp:Label>
</p>
<p>
<asp:Label ID="lblCompany" runat="server" Font-Bold="True" Text="Company Name:"></asp:Label>
<asp:DropDownList ID="txtCompanyName" runat="server" DataSourceID="IndDirCompanies"
DataTextField="CompanyName" DataValueField="CompanyID">
</asp:DropDownList>
</p>
<p>
<asp:Label ID="lblMSGID" runat="server" Font-Bold="True" Text="MSG ID:"></asp:Label>
<asp:TextBox ID="txtMSGID" runat="server" Width="195px"></asp:TextBox>
</p>
<p>
<asp:Label ID="lblAuthorized" runat="server" Font-Bold="True" Text="Authorized by:"></asp:Label>
<asp:TextBox ID="txtAuthorized" runat="server" Width="195px" Wrap="False"></asp:TextBox></p>
<p>
<asp:Label ID="lblPhone" runat="server" Font-Bold="True" Text="Phone No. :"></asp:Label>
<asp:TextBox ID="txtPhone" runat="server" Width="195px" Wrap="False"></asp:TextBox></p>
<p>
<asp:Label ID="lblEmail" runat="server" Font-Bold="True" Text="Email:"></asp:Label>
<asp:TextBox ID="txtEmail" runat="server" Width="195px" Wrap="False"></asp:TextBox>
<asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server" ControlToValidate="txtEmail"
ErrorMessage="Email format is not valid." SetFocusOnError="True" ValidationExpression="\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"></asp:RegularExpressionValidator></p>
<p>
<asp:Label ID="lblWhoBill" runat="server" Font-Bold="True" Text="Who to bill:"></asp:Label>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="SendBill"
ErrorMessage="You must select who should be billed" SetFocusOnError="True"></asp:RequiredFieldValidator>
<asp:RadioButtonList ID="SendBill" runat="server" DataSourceID="WhoBill"
DataTextField="SendBill" DataValueField="SendBillID">
</asp:RadioButtonList>
</p>
<p>
<asp:Label ID="lblAdLocation" runat="server" Font-Bold="True" Text="Ad Location:"></asp:Label>
<asp:DropDownList ID="txtAdLocation" runat="server" DataSourceID="SqlLocations" DataTextField="Location"
DataValueField="LocationID" AutoPostBack="True">
</asp:DropDownList>
<asp:Label ID="lblAdType" runat="server" Font-Bold="True" Text="Ad Type:"></asp:Label>
<asp:DropDownList ID="txtAdType" runat="server" DataSourceID="SqlLocationType"
DataTextField="AdType" DataValueField="AdTypeID">
</asp:DropDownList>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="txtAdLocation"
ErrorMessage="RequiredFieldValidator" InitialValue="8" SetFocusOnError="True">You must select an Ad Location and Type</asp:RequiredFieldValidator></p>
<p>
<asp:Label ID="lblMSGPubCodes" runat="server" Font-Bold="True" Text="MSG Pub Code:"></asp:Label> <asp:DropDownList
ID="txtMSGPubCodes" runat="server" DataSourceID="MSGPubCodes" DataTextField="MSGPubCodeDescription"
DataValueField="MSGPubCodeID" Width="205px">
</asp:DropDownList>
<asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="txtMSGPubCodes"
ErrorMessage="You must select a MSG Pub Code" InitialValue="9" SetFocusOnError="True"></asp:RequiredFieldValidator></p>
<p>
<asp:Label ID="lblStartDate" runat="server" Font-Bold="True" Text="Start Date:"></asp:Label>
<asp:TextBox ID="txtStartDate" runat="server" Width="130px"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server" ControlToValidate="txtStartDate"
ErrorMessage="You must enter a start date" SetFocusOnError="True"></asp:RequiredFieldValidator><asp:RegularExpressionValidator
ID="RegularExpressionValidator2" runat="server" ControlToValidate="txtStartDate"
ErrorMessage="Date must be formatted as : xx-xx-xxxx" SetFocusOnError="True"
ValidationExpression="(0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])[- /.](19|20)\d\d"></asp:RegularExpressionValidator>
</p>
<p>
<asp:Label ID="lblEndDate" runat="server" Font-Bold="True" Text="End Date:"></asp:Label>
<asp:TextBox ID="txtEndDate" runat="server" Width="130px"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator5" runat="server" ControlToValidate="txtEndDate"
ErrorMessage="You must enter an end date" SetFocusOnError="True"></asp:RequiredFieldValidator>
<asp:RegularExpressionValidator ID="RegularExpressionValidator3" runat="server" ControlToValidate="txtEndDate"
ErrorMessage="Date must be formatted as: xx-xx-xxxx" SetFocusOnError="True" ValidationExpression="(0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])[- /.](19|20)\d\d"></asp:RegularExpressionValidator></p>
<p>
<asp:Label ID="Label1" runat="server" Font-Bold="True" Text="Billing:"></asp:Label>
<asp:DropDownList
ID="txtBilling" runat="server" DataSourceID="BillingTypes" DataTextField="BillingType"
DataValueField="BillingID" Width="130px">
</asp:DropDownList>
<asp:RequiredFieldValidator ID="RequiredFieldValidator6" runat="server" ControlToValidate="txtBilling"
ErrorMessage="You must choose how this should be billed" InitialValue="6" SetFocusOnError="True"></asp:RequiredFieldValidator></p>
<p>
<asp:Label ID="lblPO" runat="server" Font-Bold="True" Text="PO Number:"></asp:Label>
<asp:TextBox ID="txtPONumber" runat="server" Width="130px"></asp:TextBox></p>
<p>
<asp:Label ID="lblSalesRep" runat="server" Font-Bold="True" Text="Sales Rep:"></asp:Label>
<asp:DropDownList ID="txtSalesRep" runat="server" DataSourceID="SalesReps" DataTextField="SalesRep"
DataValueField="SalesRepID" Width="168px">
</asp:DropDownList>
<asp:RequiredFieldValidator ID="RequiredFieldValidator7" runat="server" ControlToValidate="txtSalesRep"
ErrorMessage="Please select Sales Rep for this order" InitialValue="11" SetFocusOnError="True"></asp:RequiredFieldValidator></p>
<p>
<asp:Label ID="lblPercentage" runat="server" Font-Bold="True" Text="15%"></asp:Label> <asp:CheckBox
ID="txtPercentage" runat="server" /></p>
<p>
<asp:Label ID="lblImage" runat="server" Font-Bold="True" Text="Upload Image:"></asp:Label> <asp:FileUpload
ID="txtImage" runat="server" />
<asp:RegularExpressionValidator
id="FileUpLoadValidator" runat="server"
ErrorMessage="Upload Jpegs and Gifs only."
ValidationExpression="^(([a-zA-Z]:)|(\\{2}\w+)\$?)(\\(\w[\w].*))(.jpg|.JPG|.gif|.GIF)$"
ControlToValidate="txtImage" SetFocusOnError="True"></asp:RegularExpressionValidator></p>
<p>
<asp:Label ID="lblTextImage" runat="server" Text=" "></asp:Label> </p>
<p>
<strong><span style="font-size: 14pt; font-family: Arial">
<asp:Button ID="btnSubmit" runat="server" OnClick="Button1_Click" Text="Submit Order" />
<asp:SqlDataSource ID="WhoBill" runat="server" ConnectionString="<%$ ConnectionStrings:SalesBillingConnectionString %>"
SelectCommand="SELECT [SendBillID], [SendBill] FROM [SendBill]"></asp:SqlDataSource>
<asp:SqlDataSource ID="SalesReps" runat="server" ConnectionString="<%$ ConnectionStrings:SalesBillingConnectionString %>"
SelectCommand="SELECT [SalesRepID], [SalesRepFirstName] + ' ' + [SalesRepLastName] AS SalesRep FROM [tSalesReps] ORDER BY [SalesRepFirstName]">
</asp:SqlDataSource>
<asp:SqlDataSource ID="BillingTypes" runat="server" ConnectionString="<%$ ConnectionStrings:SalesBillingConnectionString %>"
SelectCommand="SELECT [BillingID], [BillingType] FROM [BillingTypes] ORDER BY [BillingType]">
</asp:SqlDataSource>
<asp:SqlDataSource ID="MSGPubCodes" runat="server" ConnectionString="<%$ ConnectionStrings:SalesBillingConnectionString %>"
SelectCommand="SELECT MSGPubCodeID, MSPPubCode, MSGPubCodeDescription FROM MSGPubCodes ORDER BY MSPPubCode">
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlLocations" runat="server" ConnectionString="<%$ ConnectionStrings:SalesBillingConnectionString %>"
SelectCommand="SELECT [LocationID], [Location] FROM [AdLocations] ORDER BY [LocationID] DESC"></asp:SqlDataSource>
<asp:SqlDataSource ID="IndDirCompanies" runat="server" ConnectionString="<%$ ConnectionStrings:FMAConnectionStringMaywood %>"
SelectCommand="SELECT [CompanyID], [CompanyName] FROM [INDDIR_Companies] ORDER BY [CompanyName]"></asp:SqlDataSource>
<asp:SqlDataSource ID="SqlLocationType" runat="server" ConnectionString="<%$ ConnectionStrings:SalesBillingConnectionString %>"
SelectCommand="SELECT dbo.AdLocations.LocationID, dbo.AdLocations.Location, dbo.AdType.AdTypeID, dbo.AdType.AdType FROM dbo.AdLocations INNER JOIN dbo.AdType ON dbo.AdLocations.LocationID = dbo.AdType.LocationID WHERE (dbo.AdLocations.LocationID = @LocationID )">
<SelectParameters>
<asp:ControlParameter ControlID="txtAdLocation" Name="LocationID" PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>
</span></strong> </p>
</asp:Content>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you sasapopovic, I'm going to work on getting your code implemented and testing it. Thank you!
ASKER
Ah I see that is in C# I'm going to have to look up how to translate that. I'm coding in vb.net
ASKER
Ok I think this is converted right.
Dim numberOfIterations As Integer
Dim monthRepeater As Integer
Select Case Convert.ToInt32(txtBilling.SelectedValue)
Case 1
numberOfIterations = 12
monthRepeater = 1
Case 2
numberOfIterations = 4
monthRepeater = 3
'TODO: define for other types
End Select
Dim startingDate As DateTime = DateTime.Parse(txtStartDate.Text)
Dim currentAdSalesIndex As Integer
For currentAdSalesIndex = 0 To numberOfIterations- 1 Step currentAdSalesIndex + 1
' InsertAdSales(lblDateRequested.Text, txtCompanyName.SelectedValue, TODO: set all other input parameters */
)
startingDate = startingDate.AddMonths(monthRepeater)
startingDate = startingDate.AddDays(startingDate.Days - 1)'Set it to first day in month
Next
'----------------------------------------------------------------
' Converted from C# to VB .NET using CSharpToVBConverter(1.2).
' Developed by: Kamal Patel (http://www.KamalPatel.net)
'----------------------------------------------------------------
Yes, looks good.
ASKER
Thank you sooo much sasapopovic!
You are welcome :-)
ASKER
SalesBillingDiagram.pdf