Using a variable in select statement for gridview in form - Visual Web Developer

Hi Experts,
I call another page from first one. I need to pass date selected from calendar into a variable converted into an interger (julian number in my case).
Using this number - I need to search a database for records matching this number and return records to gridview.

I can't seem to get it to work. Can anyone help please? Keep getting message 'Variable must be declared' - Here is my code....

benchmark3.aspx (calling file)
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="benchmark3.aspx.vb" Inherits="_benchmark" %>

<%@ Register Assembly="obout_Calendar2_Net" Namespace="OboutInc.Calendar2" TagPrefix="obout" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "">

<html xmlns="">
<head runat="server">
    <title>Untitled Page</title>
    <style type="text/css">
            height: 489px;
            width: 542px;
            height: 387px;

  <form id="formElement" runat="server">
        <input type="hidden" id="formElementVar1" value="" />

<script type="text/javascript">
function onDateChange(sender, selectedDate) {
    var formattedDate;  
    formattedDate = sender.formatDate(selectedDate, "dd MMM, yyyy");    
    document.getElementById('formElementVar1').value = formattedDate;

<br />
        <asp:TextBox ID="txtNormalDate" runat="server"></asp:TextBox>
        <asp:TextBox ID="txtIntDate" runat="server"></asp:TextBox>
        <asp:TextBox ID="txtJulianNumber" runat="server"></asp:TextBox>
        <asp:TextBox ID="txtEpicorDate" runat="server"></asp:TextBox>
        <asp:TextBox ID="txtRptSelect" runat="server"></asp:TextBox>
 <br />
 <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="False">
        <asp:ListItem Value="1">-------- Select a report --------</asp:ListItem>
        <asp:ListItem Value="2">Fund Manager List</asp:ListItem>
        <asp:ListItem Value="3">Currency List</asp:ListItem>
        <asp:ListItem Value="4">Instrument List</asp:ListItem>
        <asp:ListItem Value="5">Currency Policy</asp:ListItem>
        <asp:ListItem Value="6">Liquidity Policy</asp:ListItem>
        <asp:ListItem Value="7">Exchange Rates</asp:ListItem>
        <asp:ListItem Value="8">Revaluation</asp:ListItem>
        <asp:ListItem Value="9">Forex Revaluation</asp:ListItem>
        <asp:ListItem Value="10">Realised and Unrealised Gains</asp:ListItem>
   <asp:Button ID="Button1" runat="server" Text="Submit query"/>
    <br />
   <br />
 <asp:TextBox runat="server"  ID="txtDate"></asp:TextBox>
<div style="float:left; width:362px;">  
     <obout:Calendar id="CalTest" runat="server"
                        DatePickerImagePath ="c:/icon2.gif"
                        TextBoxId="txtDate" DateFormat="dd/MM/yyyy"
                        TitleText="<span style='color:crimson'>Select : </span>"



artial Class _benchmark
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        txtRptSelect.Text = DropDownList1.SelectedItem.Text
        '    Button1.Attributes.Add("onClick", "return confirmSubmit();")
    End Sub

    Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged
        txtRptSelect.Text = DropDownList1.SelectedItem.Text
    End Sub

    Protected Sub calTest_DateChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles CalTest.DateChanged
        '    'Create a variable named DatePicked and
        '    'set it to the user selected date/time
        Dim DatePicked As DateTime
        Dim SelectedDate As String 'create a string

        DatePicked = CalTest.SelectedDate.ToShortDateString
        SelectedDate = DatePicked.ToString("dd/MM/yyyy")
        txtNormalDate.Text = SelectedDate

        '    'For test purposes - creating an integer from the date in reverse YYYYMMDD
        '    'Also to extract the date partions yyyy mm dd from the date string
        Dim DateString As String
        Dim IntDay, IntMonth, IntYear As String
        Dim DateNumber As Integer

        DateString = txtNormalDate.Text
        IntDay = Left(DateString, 2)
        IntMonth = Mid(DateString, 4, 2)
        IntYear = Right(DateString, 4)
        DateString = IntYear + IntMonth + IntDay

        DateNumber = Val(DateString)
        txtIntDate.Text = DateNumber

        'Determining julian number to convert to Epicor date
        Dim YearStrToNum, MonthStrToNum, DayStrToNum As Integer
        Dim Y, M, D, A, B, C, F, G, JD, EpicorBase As Integer

        YearStrToNum = Val(IntYear)
        MonthStrToNum = Val(IntMonth)
        DayStrToNum = Val(IntDay)

        'Epicor constant - base julian number
        EpicorBase = 1721424

        'Check if month is January or February
        If MonthStrToNum = 1 Or MonthStrToNum = 2 Then
            Y = YearStrToNum - 1
            M = MonthStrToNum + 12
            Y = YearStrToNum
            M = MonthStrToNum
        End If

        D = DayStrToNum
        A = Int(Y / 100)
        B = Int(A / 4)
        C = Int(2 - A + B)
        F = Int(365.25 * (Y + 4716))
        G = Int(30.6001 * (M + 1))
        JD = Int(C + D + F + G - 1524.5)

        txtJulianNumber.Text = JD
        txtEpicorDate.Text = JD - EpicorBase
    End Sub

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim RptChoice As String
        RptChoice = DropDownList1.SelectedItem.Text

        While Not txtDate.Text = ""

            Select Case RptChoice
                Case "Fund Manager List"
                    Context.Items("RptSelect") = txtRptSelect.Text
                    Context.Items("EpicorDate") = txtEpicorDate.Text
                Case "Currency List"
                    Context.Items("RptSelect") = txtRptSelect.Text
                    Context.Items("EpicorDate") = txtEpicorDate.Text
                Case "Instrument List"
                    Context.Items("RptSelect") = txtRptSelect.Text
                    Context.Items("EpicorDate") = txtEpicorDate.Text
                Case "Currency Policy"
                    Context.Items("RptSelect") = txtRptSelect.Text
                    Context.Items("EpicorDate") = txtEpicorDate.Text
                Case "Liquidity Policy"
                    Context.Items("RptSelect") = txtRptSelect.Text
                    Context.Items("EpicorDate") = txtEpicorDate.Text
                Case "Exchange Rates"
                    Context.Items("RptSelect") = txtRptSelect.Text
                    Context.Items("EpicorDate") = txtEpicorDate.Text
                Case "Revaluation"
                    Context.Items("RptSelect") = txtRptSelect.Text
                    Context.Items("EpicorDate") = txtEpicorDate.Text
                Case "Forex Revaluation"
                    Context.Items("RptSelect") = txtRptSelect.Text
                    Context.Items("EpicorDate") = txtEpicorDate.Text
                Case "Realised and Unrealised Gains"
                    Context.Items("RptSelect") = txtRptSelect.Text
                    Context.Items("EpicorDate") = txtEpicorDate.Text
                Case Else
                    Context.Items("RptSelect") = txtRptSelect.Text
                    Context.Items("EpicorDate") = txtEpicorDate.Text
            End Select

            'Server.Transfer("passtoasp.aspx?") ' this moves to a new page
            'Form.Visible = False
            'Button1.Attributes.Add("onClick", "return confirmSubmit();")

        End While

    End Sub

End Class


fundmgrlist.aspx (called from benchmrak3.aspx)

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="fundmgrlist.aspx.vb" Inherits="fundmgrlist" %>

<%@ Register Assembly="obout_Grid_NET" Namespace="Obout.Grid" TagPrefix="cc1" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "">

<html xmlns="">
<head runat="server">
    <link href="layoutbenchmark.css" rel="stylesheet" type="text/css" />


    <form id="form1" runat="server">
        <table><tr><th>FUND MANAGER LISTING</th></tr></table>

<asp:Label ID="Label1" runat="server" Text='<%# Eval("nEpicorDate") %>'></asp:Label>
  <asp:SqlDataSource ID="dsExrates" runat="server"
  ConnectionString="<%$ ConnectionStrings:RBVCtrlConnectionString %>"
     SelectCommand="SELECT [buy_rate], [sell_rate], [from_currency], [to_currency], [convert_date], [rate_type] FROM mccurtdt;" />
      <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
          BackColor="White" BorderColor="#336666" BorderStyle="Double" BorderWidth="3px"
          Caption="Exchange Rates" CellPadding="4" DataSourceID="dsExrates"
          Font-Names="Verdana" Font-Size="Smaller" GridLines="Horizontal">
          <FooterStyle BackColor="White" ForeColor="#333333" />
          <RowStyle BackColor="White" ForeColor="#333333" />
              <asp:BoundField DataField="buy_rate" HeaderText="buy_rate"
                  SortExpression="buy_rate" />
              <asp:BoundField DataField="sell_rate" HeaderText="sell_rate"
                  SortExpression="sell_rate" />
              <asp:BoundField DataField="from_currency" HeaderText="from_currency"
                  SortExpression="from_currency" />
              <asp:BoundField DataField="to_currency" HeaderText="to_currency"
                  SortExpression="to_currency" />
              <asp:BoundField DataField="convert_date" HeaderText="convert_date"
                  SortExpression="convert_date" />
              <asp:BoundField DataField="rate_type" HeaderText="rate_type"
                  SortExpression="rate_type" />
          <PagerStyle BackColor="#336666" ForeColor="White" HorizontalAlign="Center" />
          <SelectedRowStyle BackColor="#339966" Font-Bold="True" ForeColor="White" />
          <HeaderStyle BackColor="#336666" Font-Bold="True" ForeColor="White" />




Partial Class fundmgrlist
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim sEpicorDate As String
        Dim sRptSelect As String
        Dim nEpicordate As Integer

        'Dim TheString As String

        sEpicorDate = Context.Items("EpicorDate").ToString
        sRptSelect = Context.Items("RptSelect").ToString

        nEpicordate = Int(sEpicorDate)

        'Label1.Visible = False
        'Label1.Text = nEpicordate

        'Response.Write("Fund Manager List")

        'Response.Write("Epicor Date is " & sEpicorDate)
        'Response.Write("Report Selected is " & sRptSelect)
        Response.Write("Epicor number date is " & nEpicordate)

    End Sub

End Class
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RBVITAuthor Commented:

Here is the select statement I am using and keep getting error:

SelectCommand="SELECT [buy_rate], [sell_rate], [from_currency], [to_currency], [convert_date], [rate_type] FROM mccurtdt
WHERE [convert_date] = @nEpicorDate;" />
Are you getting right nEpicorDate on the next page?
Try this:
1: Adjust your SqlDataSource to have SelectParamter like:
<asp:SqlDataSource ID="dsExrates" runat="server"
  ConnectionString="<%$ ConnectionStrings:RBVCtrlConnectionString %>"
   SelectCommand="SELECT [buy_rate], [sell_rate], [from_currency], [to_currency], [convert_date], [rate_type] FROM mccurtdt WHERE [convert_date] = @nEpicorDate;" >
            <asp:Parameter Name="nEpicorDate" Type="String" />

2: OnSelecting handler in code-behind like below:

 Protected Sub dsExrates_Selecting(ByVal sender As Object, ByVal e As SqlDataSourceSelectingEventArgs) Handles dsExrates.Selecting
        e.Command.Parameters("@nEpicorDate").Value = nEpicorDate
         ' if above line throws error about  @nEpicorDate  then try removing @
    End Sub

3: make nEpicorDate accessible to whole class i.e. move its definition outside pageload

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RBVITAuthor Commented:

Are you getting right nEpicorDate on the next page? ---------- Yes I am

I moved nEpicorDate outside pageload

I tested with and without the @ nEpicorDate

I get the error below:

Input string was not in a correct format.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.FormatException: Input string was not in a correct format.

Source Error:

The source code that generated this unhandled exception can only be shown when compiled in debug mode. To enable this, please follow one of the below steps, then request the URL:

1. Add a "Debug=true" directive at the top of the file that generated the error. Example:

  <%@ Page Language="C#" Debug="true" %>


2) Add the following section to the configuration file of your application:

       <compilation debug="true"/>

Note that this second technique will cause all files within a given application to be compiled in debug mode. The first technique will cause only that particular file to be compiled in debug mode.

Important: Running applications in debug mode does incur a memory/performance overhead. You should make sure that an application has debugging disabled before deploying into production scenario.  

Stack Trace:

[FormatException: Input string was not in a correct format.]
   Microsoft.VisualBasic.CompilerServices.Conversions.ParseDouble(String Value, NumberFormatInfo NumberFormat) +211
   Microsoft.VisualBasic.CompilerServices.Conversions.ToInteger(String Value) +84

[InvalidCastException: Conversion from string "SELECT date_convert, FROM mccurt" to type 'Integer' is not valid.]
   Microsoft.VisualBasic.CompilerServices.Conversions.ToInteger(String Value) +238
   fundmgrlist.Page_Load(Object sender, EventArgs e) +215
   System.Web.UI.Control.OnLoad(EventArgs e) +99
   System.Web.UI.Control.LoadRecursive() +47
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1436


Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

RBVITAuthor Commented:
Could it be that :

<asp:Parameter Name="nEpicorDate" Type="String" />

is expecting a string with numbers and not characters and if so how can I ensure correct format then?
RBVITAuthor Commented:
Guru Sami - you are the best!

Your initial fix was spot on! I had a statement in there that I overlooked to comment out or remove when testing - when I commented that and ran it again - the fix you provided worked like a charm!
Thank you very much for saving me a lot of time - so much time I have spent trying to find a solution.
Hence I will award you the full points.

RBVITAuthor Commented:
Full points!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.