VB.NET – Parameters

Parameter default value in code behind

SQL Datasource

<asp:sqldatasource ID="ServiceExpired" runat="server" 
       ConnectionString="< %$ ConnectionStrings:intranetConnectionString2 %>" 
       SelectCommand="SELECT * FROM [Invoice] _
                      WHERE _
                           ([EXPIRATION_DATE] >= @EXP_BEG) _
                      AND _
                           ([EXPIRATION_DATE] < @EXP_END) _
                      AND 
                           ([EXPIRATION_CONTACT] = @EXPIRATION_CONTACT) _
                      ORDER BY _
                             EXPIRATION_DATE ">
<selectparameters>
      <asp:parameter DefaultValue="YES" Name="EXPIRATION_CONTACT" Type="String" />
      <asp:parameter Name="EXP_BEG" Type="DateTime"/>
      <asp:parameter Name="EXP_END" Type="DateTime"/>
</selectparameters>

Code behind

Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
  CuMonthLbl.Text = MonthName(Date.Now.Month)     ' - current month name
  NxMonthLbl.Text = MonthName(Date.Now.Month + 1) ' - next to current month name
  Dim NxMonth As String                           ' - define next month value
      NxMonth = (Date.Now.Month + 1)
 ' - query range labels
  MoBegLbl.Text = DateTime.Now.Month.ToString() _
                    + "/1/" + DateTime.Now.Year.ToString()
  MoEndLbl.Text = NxMonth + "/1/" + DateTime.Now.Year.ToString()

' - parameters values
' - beginning of month
  ServiceExpired.SelectParameters("EXP_BEG").DefaultValue = _
                                             DateTime.Now.Month.ToString() _
                                   + "/1/" + DateTime.Now.Year.ToString() 
' - end of month
  ServiceExpired.SelectParameters("EXP_END").DefaultValue = _
                                             NxMonth _
                                   + "/1/" + DateTime.Now.Year.ToString()
End Sub

' - label
Current month: <asp:label ID="CuMonthLbl" runat="server"> -
Next monht:  <asp:label ID="NxMonthLbl" runat="server">
Records from: <asp:label ID="MoBegLbl" runat="server"> -
              <asp:label ID="MoEndLbl" runat="server">
' - output

--------------------------------------------------------
|  #  | Company Name | Location | Expire     | Contact |
--------------------------------------------------------
|  1  | Kiklop       | Europe   | 09/01/2011 | YES     |
|  2  | Acer         | Europe   | 09/09/2011 | YES     |
|  3  | King Z       | Africa   | 09/22/2011 | YES     |
|  4  | Chung Fu     | Asia     | 09/30/2011 | YES     |

Current month: September
Next month: October
Records from: 09/01/2011 - 10/01/2011