Solved

validate promo code and expiration date

Posted on 2010-11-17
6
1,169 Views
Last Modified: 2012-08-13
Hello,

I have a  user control form built using Visual Studio 2005 on VB.NET with a sql2008 backend.
This user control validates a Promo Code by checking it against PromoCodes stored in a database table.  This feature works, however, I am attempting to add an additional validation against another field "End_Date" to make the promo codes valid within a range of dates.

The code that validates the Promo Codes follows:
-------
Protected Sub btnVerifyPromos_Click(ByVal 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.SqlConnection = New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("DSN_PROD").ConnectionString)
     
        myConnection.Open()

        ' Build a sql statement string    
        Dim query1 As String = "Select PromoCode, DiscountValue, End_Date FROM tblPromoCodes WHERE PromoCode = @PromoCode"

        ' Initialize the sqlCommand with the new sql string.  
        Dim Command1 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(query1, myConnection)
       
        ' Declare txtPromoCode As TextBox and find in the form.
        Dim txtPromoCode As TextBox = DirectCast(RSVPForm1.FindControl("txtPromoCode"), TextBox)
        Dim lbl_Discount As Label = DirectCast(RSVPForm1.FindControl("lbl_Discount"), Label)
        Dim txtPromoEndDate As TextBox = DirectCast(RSVPForm1.FindControl("txtPromoEndDate"), TextBox)
       
        Dim lbl_PromoEndDate As Label = DirectCast(RSVPForm1.FindControl("lbl_PromoEndDate"), Label)
       
       
        Command1.Parameters.AddWithValue("@PromoCode", 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 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.SqlDataReader = Command1.ExecuteReader
       
        If reader IsNot Nothing AndAlso reader.HasRows Then
            reader.Read()
            PromoCode = reader.GetString(0)
            DiscountValue = reader.GetInt32(1)
            PromoEndDate = reader.GetDateTime((2))
           
           
        End If

        ' Display whether the page passed validation.
        Dim lbl_message As Label = DirectCast(RSVPForm1.FindControl("lbl_message"), Label)
         
        If Not String.IsNullOrEmpty(PromoCode) Then
         
            lbl_message.Text = "Promo Code Accepted."
            lbl_Discount.Text = "Discount Applied."
           
            txtPromoEndDate.Text = PromoEndDate.ToShortDateString()
            lbl_PromoEndDate.Text = "Promo Date Accepted"
           
           
            If PromoEndDate.ToShortDateString > Date.Now Then
                lbl_PromoEndDate.Text = "Promo Date Expired"
               
            End If
           
           
        Else
            lbl_message.Text = "Promo Code Invalid."
            lbl_Discount.Text = "Discount Not Applied."
           
            txtPromoEndDate.Text = ""
           
            lbl_PromoEndDate.Text = "Promo Date Expired"

        End If

        ' args.IsValid = False
       
        If Not String.IsNullOrEmpty(PromoCode) Then
         
            lbl_message.Text = "Promo Code Accepted."
            lbl_Discount.Text = "Discount Applied."
           
            txtPromoEndDate.Text = PromoEndDate.ToShortDateString()
           
            lbl_PromoEndDate.Text = "Promo Date Accepted"
           
        Else
            lbl_message.Text = "Promo Code Invalid."
            lbl_Discount.Text = "Discount Not Applied."
           
            txtPromoEndDate.Text = ""
           
            lbl_PromoEndDate.Text = "Promo Date Expired"
                       
         

        End If

        '******* Calculate the event price if PromoCode isValid *********
        Dim txtTotal As TextBox = DirectCast(RSVPForm1.FindControl("txtTotal"), TextBox)
       
        Dim txtNumberofTickets As TextBox = DirectCast(RSVPForm1.FindControl("txtNumberofTickets"), TextBox)

        Dim hdnTotal As HiddenField = DirectCast(RSVPForm1.FindControl("hdnTotal"), HiddenField)
             
       
       
        If Not String.IsNullOrEmpty(txtNumberofTickets.Text) Then

            Dim nt As Integer = Convert.ToInt32((txtNumberofTickets.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 call to this sub 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>&nbsp;</td>
     
<td style="text-align: left; white-space: nowrap; height: 25px;">
    <asp:TextBox ID="txtPromoCode" Width="150px" runat="server"></asp:TextBox>  
 </td>    
   
 <td style="text-align: left; white-space: nowrap; height: 25px;">&nbsp; </td>
 
 <td style="text-align: left; white-space: nowrap; height: 25px;"><asp:Button ID="btnVerifyPromos" runat="server" OnClick="btnVerifyPromos_Click" Text="Verify"  />
     <asp:Label ID="lbl_message" runat="server" Text="" ></asp:Label><br />
     <asp:Label ID="lbl_Discount" runat="server" Text="" BackColor="white"></asp:Label>
 </td>      
 
  </tr>
     <!--Promo Code call ends here -->
-------

ANY help would be greatly appreciated.
0
Comment
Question by:Codeaddict7423
  • 3
  • 3
6 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 34158755
What is the problem?

You obviously need to change your query to something like this (if the End_Date is of type date)

Select PromoCode, DiscountValue, End_Date FROM tblPromoCodes WHERE End_Date >= GetDate() AND  PromoCode = @PromoCode
0
 

Author Comment

by:Codeaddict7423
ID: 34158958
CodeCruiser,
Thank  your your quick reply.
The query modification worked. however, i'm attempting to pull the end_date to populate a textbox and I'm only getting the start_date. Below, please find the sub code:
--------
Protected Sub btnVerifyPromos_Click(ByVal 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.SqlConnection = New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("DSN_PROD").ConnectionString)
     
        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.SqlCommand = New System.Data.SqlClient.SqlCommand(query1, myConnection)
       
        ' Declare txtPromoCode As TextBox and find in the form.
        Dim txtPromoCode As TextBox = DirectCast(RSVPForm1.FindControl("txtPromoCode"), TextBox)
        Dim lbl_Discount As Label = DirectCast(RSVPForm1.FindControl("lbl_Discount"), Label)
       
        Dim txtPromoStartDate As TextBox = DirectCast(RSVPForm1.FindControl("txtPromoStartDate"), TextBox)
       
        Dim lbl_PromoStartDate As Label = DirectCast(RSVPForm1.FindControl("lbl_PromoStartDate"), Label)
       
        Dim txtPromoEndDate As TextBox = DirectCast(RSVPForm1.FindControl("txtPromoEndDate"), TextBox)
       
        Dim lbl_PromoEndDate As Label = DirectCast(RSVPForm1.FindControl("lbl_PromoEndDate"), Label)
       
       
        Command1.Parameters.AddWithValue("@PromoCode", 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.SqlDataReader = 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((2))
           
           
        End If

        ' Display whether the page passed validation.
        Dim lbl_message As Label = DirectCast(RSVPForm1.FindControl("lbl_message"), Label)
         
        If Not String.IsNullOrEmpty(PromoCode) Then
         
            lbl_message.Text = "Promo Code Accepted."
            lbl_Discount.Text = "Discount Applied."
           
            txtPromoStartDate.Text = PromoStartDate.ToShortDateString()
            txtPromoEndDate.Text = PromoEndDate.ToShortDateString()
           
            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(PromoCode) Then
         
            lbl_message.Text = "Promo Code Accepted."
            lbl_Discount.Text = "Discount Applied."
           
            txtPromoStartDate.Text = PromoStartDate.ToShortDateString()
            txtPromoEndDate.Text = PromoEndDate.ToShortDateString()
           
            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.FindControl("txtTotal"), TextBox)
       
        Dim txtNumberofTickets As TextBox = DirectCast(RSVPForm1.FindControl("txtNumberofTickets"), TextBox)

        Dim hdnTotal As HiddenField = DirectCast(RSVPForm1.FindControl("hdnTotal"), HiddenField)
             
       
       
        If Not String.IsNullOrEmpty(txtNumberofTickets.Text) Then

            Dim nt As Integer = Convert.ToInt32((txtNumberofTickets.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 call to the promo code 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>&nbsp;</td>
     
<td style="text-align: left; white-space: nowrap; height: 25px;">
    <asp:TextBox ID="txtPromoCode" Width="150px" runat="server"></asp:TextBox>  
 </td>    
   
 <td style="text-align: left; white-space: nowrap; height: 25px;">&nbsp; </td>
 
 <td style="text-align: left; white-space: nowrap; height: 25px;"><asp:Button ID="btnVerifyPromos" runat="server" OnClick="btnVerifyPromos_Click" Text="Verify"  />
     <asp:Label ID="lbl_message" runat="server" Text="" ></asp:Label><br />
     <asp:Label ID="lbl_Discount" runat="server" Text="" BackColor="white"></asp:Label>
 </td>      
 
  </tr>
     <!--Promo Code call ends here -->
------
below, please find the textboxes that display the start_date/ end_date:
-----
<!--Promo Start_Date starts here -->
 <tr>
  <td style="text-align: right; white-space: nowrap; height: 25px;">
<asp:Label ID="lbl_StartDate" runat="server" Text="Promo Start Date:"></asp:Label>&nbsp;</td>
     
<td style="text-align: left; white-space: nowrap; height: 25px;">    
<asp:TextBox ID="txtPromoStartDate" runat="server" Text="" Width="150px" ReadOnly="true" BackColor="#FFF8C6" ></asp:TextBox>
 </td>    
 <td style="text-align: left; white-space: nowrap; height: 25px;">&nbsp;</td>
 
 <td style="text-align: left; white-space: nowrap; height: 25px;">
 <asp:Label ID="lbl_PromoStartDate" runat="server" Text="" BackColor="white"></asp:Label> </td>      
  </tr>
<!--Promo Start_Date ends here -->

<!--Promo End_Date starts here -->
 <tr>
  <td style="text-align: right; white-space: nowrap; height: 25px;">
<asp:Label ID="lbl_EndDate" runat="server" Text="Promo End Date:"></asp:Label>&nbsp;</td>
     
<td style="text-align: left; white-space: nowrap; height: 25px;">    
<asp:TextBox ID="txtPromoEndDate" runat="server" Text="" Width="150px" ReadOnly="true" BackColor="#FFF8C6" ></asp:TextBox>
 </td>    
   
 <td style="text-align: left; white-space: nowrap; height: 25px;">&nbsp;</td>
 
 <td style="text-align: left; white-space: nowrap; height: 25px;">
 <asp:Label ID="lbl_PromoEndDate" runat="server" Text="" BackColor="white"></asp:Label> </td>      
  </tr>
<!--Promo End_Date ends here -->
-------
a working example is located at: http://www.h-gac.com/TRAINING/discount_coupon_test.aspx

Can you please review?
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
ID: 34159117
>i'm attempting to pull the end_date to populate a textbox and I'm only getting the start_date

Its because you are using the wrong index. Change this

            PromoStartDate = reader.GetDateTime((2))
            PromoEndDate = reader.GetDateTime((2))

to

            PromoStartDate = reader.GetDateTime((2))
            PromoEndDate = reader.GetDateTime((3))
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:Codeaddict7423
ID: 34159160
CodeCruiser,
THANK YOU. that's exactly what I needed.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 34159180
Glad to help :- )
0
 

Author Comment

by:Codeaddict7423
ID: 34159187
i'm starting a enew post, perhaps you can help again... same user control, different question.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In Part 1 (http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/A_7849-Hex-Maze.html) we covered the hexagonal maze basics -- how the cells are represented in a JavaScript array and how the maze is displayed.  In this part, we'…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now