[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 407
  • Last Modified:

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

0
Albee_J
Asked:
Albee_J
  • 5
  • 3
1 Solution
 
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
 
Albee_JAuthor Commented:
Thank you sasapopovic, I'm going to work on getting your code implemented and testing it.  Thank you!
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now