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.

<%@ 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>&nbsp;</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>&nbsp;
        <asp:DropDownList ID="txtCompanyName" runat="server" DataSourceID="IndDirCompanies"
            DataTextField="CompanyName" DataValueField="CompanyID">
        </asp:DropDownList>&nbsp;
    </p>
    <p>
        <asp:Label ID="lblMSGID" runat="server" Font-Bold="True" Text="MSG ID:"></asp:Label>
        &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;
        <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>
        &nbsp;&nbsp;&nbsp;&nbsp;
        <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>
        &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;
        <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>
        &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;
        <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>&nbsp;
        <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>
        &nbsp; &nbsp; &nbsp;&nbsp;</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>&nbsp;<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>
        &nbsp; &nbsp; &nbsp; &nbsp;
      
        <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>
        &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;
        <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>&nbsp;&nbsp;
        &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;
       <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>
        &nbsp; &nbsp;&nbsp;
        <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>
        &nbsp; &nbsp; &nbsp; &nbsp;<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>&nbsp;<asp:CheckBox
            ID="txtPercentage" runat="server" /></p>
    <p>
        <asp:Label ID="lblImage" runat="server" Font-Bold="True" Text="Upload Image:"></asp:Label>&nbsp;<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>&nbsp;</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&#13;&#10;FROM         dbo.AdLocations INNER JOIN&#13;&#10;                      dbo.AdType ON dbo.AdLocations.LocationID = dbo.AdType.LocationID&#13;&#10;WHERE (dbo.AdLocations.LocationID  = @LocationID  )">
                <SelectParameters>
                    <asp:ControlParameter ControlID="txtAdLocation" Name="LocationID" PropertyName="SelectedValue" />
                </SelectParameters>
            </asp:SqlDataSource>
            &nbsp; </span></strong>&nbsp;</p>
</asp:Content>

Open in new window

Albee_JAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Albee_JAuthor Commented:
0
sasapopovicCommented:
Hi,

I would do it like this:
int numberOfIterations;
int monthRepeater;
switch(Convert.ToInt32(txtBilling.SelectedValue))
{
   case 1: //monthly
   {
      numberOfIterations = 12;
      monthRepeater = 1;
   }
   case 2: //quarterly
   {
      numberOfIterations = 4;
      monthRepeater = 3;
   }
//TODO: define for other types

}

DateTime startingDate = DateTime.Parse(txtStartDate.Text);
for(int currentAdSalesIndex = 0; currentAdSalesIndex < numberOfIterations; currentAdSalesIndex++)
{
    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
}

Then make a method/sub called InsertAdSales that will do what you do in Button1_Click and use input parameters of that method as values for parameters of your INSERT statement.

I hope this will help you.

Regards,
Sasa
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Albee_JAuthor Commented:
Thank you sasapopovic, I'm going to work on getting your code implemented and testing it.  Thank you!
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Albee_JAuthor Commented:
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
0
Albee_JAuthor Commented:
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) 
'----------------------------------------------------------------

Open in new window

0
sasapopovicCommented:
Yes, looks good.
0
Albee_JAuthor Commented:
Thank you sooo much sasapopovic!
0
sasapopovicCommented:
You are welcome :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.