Codeaddict7423
asked on
Simplify calculation of discount promotional code
Hello,
I have a user control built on Visual Studio 2005 using vb.net with a sql2008 backend.
I am attempting to simplify the calculation of promo codes to offer discounts on event ticktets.
The code that calculates this is as follows:
-------
Protected Sub btnVerifyPromos_Click(ByVa l sender As Object, ByVal e As System.EventArgs)
' Creates myConnection as a new sqlconnection and sets it equal to DSN_PROD
Dim myConnection As System.Data.SqlClient.SqlC onnection = New System.Data.SqlClient.SqlC onnection( Configurat ionManager .Connectio nStrings(" DSN_PROD") .Connectio nString)
myConnection.Open()
' Build a sql statement string
Dim query1 As String = "Select PromoCode, DiscountValue, Start_Date, End_Date FROM tblPromoCodes WHERE End_Date >= GetDate() AND PromoCode = @PromoCode"
' Initialize the sqlCommand with the new sql string.
Dim Command1 As System.Data.SqlClient.SqlC ommand = New System.Data.SqlClient.SqlC ommand(que ry1, myConnection)
' Declare txtPromoCode As TextBox and find in the form.
Dim txtPromoCode As TextBox = DirectCast(RSVPForm1.FindC ontrol("tx tPromoCode "), TextBox)
Dim lbl_Discount As Label = DirectCast(RSVPForm1.FindC ontrol("lb l_Discount "), Label)
Dim txtPromoStartDate As TextBox = DirectCast(RSVPForm1.FindC ontrol("tx tPromoStar tDate"), TextBox)
Dim lbl_PromoStartDate As Label = DirectCast(RSVPForm1.FindC ontrol("lb l_PromoSta rtDate"), Label)
Dim txtPromoEndDate As TextBox = DirectCast(RSVPForm1.FindC ontrol("tx tPromoEndD ate"), TextBox)
Dim lbl_PromoEndDate As Label = DirectCast(RSVPForm1.FindC ontrol("lb l_PromoEnd Date"), Label)
Command1.Parameters.AddWit hValue("@P romoCode", txtPromoCode.Text)
'If there is a single quote in the string below (Dim PromoCode As String = ""), this causes validation of the PromoCode to fail
Dim PromoCode As String = ""
Dim DiscountValue As Integer = 0
Dim PromoStartDate As Date
Dim PromoEndDate As Date
'Create new parameters for the sqlCommand object and initialize them to the input values.
'Execute the command
Dim reader As System.Data.SqlClient.SqlD ataReader = Command1.ExecuteReader
If reader IsNot Nothing AndAlso reader.HasRows Then
reader.Read()
PromoCode = reader.GetString(0)
DiscountValue = reader.GetInt32(1)
PromoStartDate = reader.GetDateTime((2))
PromoEndDate = reader.GetDateTime((3))
End If
' Display whether the page passed validation.
Dim lbl_message As Label = DirectCast(RSVPForm1.FindC ontrol("lb l_message" ), Label)
If Not String.IsNullOrEmpty(Promo Code) Then
lbl_message.Text = "Promo Code Accepted."
lbl_Discount.Text = "Discount Applied."
txtPromoStartDate.Text = PromoStartDate.ToShortDate String()
txtPromoEndDate.Text = PromoEndDate.ToShortDateSt ring()
lbl_PromoStartDate.Text = "Pomo Start Date Accepted"
lbl_PromoEndDate.Text = "Promo End Date Accepted"
Else
lbl_message.Text = "Promo Code Invalid."
lbl_Discount.Text = "Discount Not Applied."
txtPromoStartDate.Text = ""
txtPromoEndDate.Text = ""
lbl_PromoStartDate.Text = "Promo Start Date Expired"
lbl_PromoEndDate.Text = "Promo End Date Expired"
End If
' args.IsValid = False
If Not String.IsNullOrEmpty(Promo Code) Then
lbl_message.Text = "Promo Code Accepted."
lbl_Discount.Text = "Discount Applied."
txtPromoStartDate.Text = PromoStartDate.ToShortDate String()
txtPromoEndDate.Text = PromoEndDate.ToShortDateSt ring()
lbl_PromoStartDate.Text = "Promo Start Date Accepted"
lbl_PromoEndDate.Text = "Promo End Date Accepted"
Else
lbl_message.Text = "Promo Code Invalid."
lbl_Discount.Text = "Discount Not Applied."
txtPromoStartDate.Text = ""
txtPromoEndDate.Text = ""
lbl_PromoStartDate.Text = "Promo Start Date Expired"
lbl_PromoEndDate.Text = "Promo End Date Expired"
End If
'******* Calculate the event price if PromoCode isValid *********
Dim txtTotal As TextBox = DirectCast(RSVPForm1.FindC ontrol("tx tTotal"), TextBox)
Dim txtNumberofTickets As TextBox = DirectCast(RSVPForm1.FindC ontrol("tx tNumberofT ickets"), TextBox)
Dim hdnTotal As HiddenField = DirectCast(RSVPForm1.FindC ontrol("hd nTotal"), HiddenField)
If Not String.IsNullOrEmpty(txtNu mberofTick ets.Text) Then
Dim nt As Integer = Convert.ToInt32((txtNumber ofTickets. Text))
'below this line, the numer after the asterisk is the price of the event
Dim price As Integer = nt * 100
'to have a percent discount from price, use the formula below like this:
'Dim total As Decimal = price - (price * DiscountValue / 100)
'to have a hard-dollar discount from price, use the formula below like this:
Dim total As Decimal = price - DiscountValue
txtTotal.Text = total.ToString()
hdnTotal.Value = Convert.ToInt32((txtTotal. Text))
hdnTotal.Value = Convert.ToInt32((total))
Else
txtTotal.Text = "0"
End If
myConnection.Close()
End Sub
--------
The code that calls the sun above is as follows:
-------
<!--Promo Code call starts here -->
<tr>
<td style="text-align: right; white-space: nowrap; height: 25px; background-color:#FFFFFF">
<asp:Label ID="Label2" runat="server" Text="Promo Code:"></asp:Label> < /td>
<td style="text-align: left; white-space: nowrap; height: 25px;">
<asp:TextBox ID="txtPromoCode" Width="150px" runat="server"></asp:TextB ox>
</td>
<td style="text-align: left; white-space: nowrap; height: 25px;"> </td>
<td style="text-align: left; white-space: nowrap; height: 25px;"><asp:Button ID="btnVerifyPromos" runat="server" OnClick="btnVerifyPromos_C lick" Text="Verify" />
<asp:Label ID="lbl_message" runat="server" Text="" ></asp:Label><br />
<asp:Label ID="lbl_Discount" runat="server" Text="" BackColor="white"></asp:La bel>
</td>
</tr>
<!--Promo Code call ends here -->
<tr>
<td style="text-align: right; white-space: nowrap; height: 25px;">
<asp:Label ID="Label3" runat="server" Text="Number of Tickets:"></asp:Label>&nbs p;</td>
<td style="text-align: left; white-space: nowrap; height: 25px;">
<asp:TextBox ID="txtNumberofTickets" runat="server" Text="1" Width="150px" ReadOnly="true" BackColor="#ebebeb" ></asp:TextBox>
</td>
<td style="text-align: left; white-space: nowrap; height: 25px;"> </td>
<td style="text-align: left; white-space: nowrap; height: 25px;"> </td>
</tr>
-----
What I'm attempting to do is to remove the "txtNumberofTickets" option (at this time, I'm assuming that one user will purchase one ticket) such that the calculation of the discount promo codes is not dependent on the number of tickets (which is 1).
a working example: http://www.h-gac.com/TRAINING/discount_coupon_test.aspx
ANY help would be greatly appreciated.
I have a user control built on Visual Studio 2005 using vb.net with a sql2008 backend.
I am attempting to simplify the calculation of promo codes to offer discounts on event ticktets.
The code that calculates this is as follows:
-------
Protected Sub btnVerifyPromos_Click(ByVa
' Creates myConnection as a new sqlconnection and sets it equal to DSN_PROD
Dim myConnection As System.Data.SqlClient.SqlC
myConnection.Open()
' Build a sql statement string
Dim query1 As String = "Select PromoCode, DiscountValue, Start_Date, End_Date FROM tblPromoCodes WHERE End_Date >= GetDate() AND PromoCode = @PromoCode"
' Initialize the sqlCommand with the new sql string.
Dim Command1 As System.Data.SqlClient.SqlC
' Declare txtPromoCode As TextBox and find in the form.
Dim txtPromoCode As TextBox = DirectCast(RSVPForm1.FindC
Dim lbl_Discount As Label = DirectCast(RSVPForm1.FindC
Dim txtPromoStartDate As TextBox = DirectCast(RSVPForm1.FindC
Dim lbl_PromoStartDate As Label = DirectCast(RSVPForm1.FindC
Dim txtPromoEndDate As TextBox = DirectCast(RSVPForm1.FindC
Dim lbl_PromoEndDate As Label = DirectCast(RSVPForm1.FindC
Command1.Parameters.AddWit
'If there is a single quote in the string below (Dim PromoCode As String = ""), this causes validation of the PromoCode to fail
Dim PromoCode As String = ""
Dim DiscountValue As Integer = 0
Dim PromoStartDate As Date
Dim PromoEndDate As Date
'Create new parameters for the sqlCommand object and initialize them to the input values.
'Execute the command
Dim reader As System.Data.SqlClient.SqlD
If reader IsNot Nothing AndAlso reader.HasRows Then
reader.Read()
PromoCode = reader.GetString(0)
DiscountValue = reader.GetInt32(1)
PromoStartDate = reader.GetDateTime((2))
PromoEndDate = reader.GetDateTime((3))
End If
' Display whether the page passed validation.
Dim lbl_message As Label = DirectCast(RSVPForm1.FindC
If Not String.IsNullOrEmpty(Promo
lbl_message.Text = "Promo Code Accepted."
lbl_Discount.Text = "Discount Applied."
txtPromoStartDate.Text = PromoStartDate.ToShortDate
txtPromoEndDate.Text = PromoEndDate.ToShortDateSt
lbl_PromoStartDate.Text = "Pomo Start Date Accepted"
lbl_PromoEndDate.Text = "Promo End Date Accepted"
Else
lbl_message.Text = "Promo Code Invalid."
lbl_Discount.Text = "Discount Not Applied."
txtPromoStartDate.Text = ""
txtPromoEndDate.Text = ""
lbl_PromoStartDate.Text = "Promo Start Date Expired"
lbl_PromoEndDate.Text = "Promo End Date Expired"
End If
' args.IsValid = False
If Not String.IsNullOrEmpty(Promo
lbl_message.Text = "Promo Code Accepted."
lbl_Discount.Text = "Discount Applied."
txtPromoStartDate.Text = PromoStartDate.ToShortDate
txtPromoEndDate.Text = PromoEndDate.ToShortDateSt
lbl_PromoStartDate.Text = "Promo Start Date Accepted"
lbl_PromoEndDate.Text = "Promo End Date Accepted"
Else
lbl_message.Text = "Promo Code Invalid."
lbl_Discount.Text = "Discount Not Applied."
txtPromoStartDate.Text = ""
txtPromoEndDate.Text = ""
lbl_PromoStartDate.Text = "Promo Start Date Expired"
lbl_PromoEndDate.Text = "Promo End Date Expired"
End If
'******* Calculate the event price if PromoCode isValid *********
Dim txtTotal As TextBox = DirectCast(RSVPForm1.FindC
Dim txtNumberofTickets As TextBox = DirectCast(RSVPForm1.FindC
Dim hdnTotal As HiddenField = DirectCast(RSVPForm1.FindC
If Not String.IsNullOrEmpty(txtNu
Dim nt As Integer = Convert.ToInt32((txtNumber
'below this line, the numer after the asterisk is the price of the event
Dim price As Integer = nt * 100
'to have a percent discount from price, use the formula below like this:
'Dim total As Decimal = price - (price * DiscountValue / 100)
'to have a hard-dollar discount from price, use the formula below like this:
Dim total As Decimal = price - DiscountValue
txtTotal.Text = total.ToString()
hdnTotal.Value = Convert.ToInt32((txtTotal.
hdnTotal.Value = Convert.ToInt32((total))
Else
txtTotal.Text = "0"
End If
myConnection.Close()
End Sub
--------
The code that calls the sun above is as follows:
-------
<!--Promo Code call starts here -->
<tr>
<td style="text-align: right; white-space: nowrap; height: 25px; background-color:#FFFFFF">
<asp:Label ID="Label2" runat="server" Text="Promo Code:"></asp:Label> <
<td style="text-align: left; white-space: nowrap; height: 25px;">
<asp:TextBox ID="txtPromoCode" Width="150px" runat="server"></asp:TextB
</td>
<td style="text-align: left; white-space: nowrap; height: 25px;"> </td>
<td style="text-align: left; white-space: nowrap; height: 25px;"><asp:Button ID="btnVerifyPromos" runat="server" OnClick="btnVerifyPromos_C
<asp:Label ID="lbl_message" runat="server" Text="" ></asp:Label><br />
<asp:Label ID="lbl_Discount" runat="server" Text="" BackColor="white"></asp:La
</td>
</tr>
<!--Promo Code call ends here -->
<tr>
<td style="text-align: right; white-space: nowrap; height: 25px;">
<asp:Label ID="Label3" runat="server" Text="Number of Tickets:"></asp:Label>&nbs
<td style="text-align: left; white-space: nowrap; height: 25px;">
<asp:TextBox ID="txtNumberofTickets" runat="server" Text="1" Width="150px" ReadOnly="true" BackColor="#ebebeb" ></asp:TextBox>
</td>
<td style="text-align: left; white-space: nowrap; height: 25px;"> </td>
<td style="text-align: left; white-space: nowrap; height: 25px;"> </td>
</tr>
-----
What I'm attempting to do is to remove the "txtNumberofTickets" option (at this time, I'm assuming that one user will purchase one ticket) such that the calculation of the discount promo codes is not dependent on the number of tickets (which is 1).
a working example: http://www.h-gac.com/TRAINING/discount_coupon_test.aspx
ANY help would be greatly appreciated.
ASKER
CodeCruiser,
At this time, I'm evisioning a situation where one user will purchase one ticket (this will expand to where one user can purchase multiple tickets and still use the promo code)
but for now: one user=one discount.
what I'm trying to do is to get away from the "txtNumberofTickets" and pull the calculation out of this statement:
"If Not String.IsNullOrEmpty(txtNu mberofTick ets.Text) Then
End IF"
so that we can have this line:
-----------
'below this line, the numer after the asterisk is the price of the event
Dim price As Integer = nt * 100
----------
stand on its own without the txtNumbeofTickets.Text condition.
ANY hel p would be greatly appreciated.
At this time, I'm evisioning a situation where one user will purchase one ticket (this will expand to where one user can purchase multiple tickets and still use the promo code)
but for now: one user=one discount.
what I'm trying to do is to get away from the "txtNumberofTickets" and pull the calculation out of this statement:
"If Not String.IsNullOrEmpty(txtNu
End IF"
so that we can have this line:
-----------
'below this line, the numer after the asterisk is the price of the event
Dim price As Integer = nt * 100
----------
stand on its own without the txtNumbeofTickets.Text condition.
ANY hel p would be greatly appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
CodeCruiser,
THANK You. this worked perfectly.
I have two more tasks on my list for th is user control and I'll post each as a separate question. Hopefully you can help me.
THANK You. this worked perfectly.
I have two more tasks on my list for th is user control and I'll post each as a separate question. Hopefully you can help me.
Sure.
You could use a stored procedure and return an integer value which would indicate whether the code is valid or not and if not whether its expired or invalid.