[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

validate promo code and expiration date

Posted on 2010-11-17
6
Medium Priority
?
1,244 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will give core knowledge of JavaScript and will head in to your first JavaScript program. I am Durvesh Naik and I am here to deal with this series of JavaScript. I will teach you JavaScript in part wise , as its quite boring to read big…
This article discusses the difference between strict equality operator and equality operator in JavaScript. The Need: Because JavaScript performs an implicit type conversion when performing comparisons, we have to take this into account when wri…
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…
Suggested Courses

656 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