passing asp:calendar value back to same page

Posted on 2011-10-27
Last Modified: 2012-05-12
Hi ,

I have a simple page that has a calendar control and a simple query that tries to use a value when a date is clicked on the calendar to query a sql database table for that given date.  the sql server column is of type datetime whereas the parameter in the query uses Type="Date".  I am not sure if this is a problem?  It is not doing anything though, even when I click a date on the calendar that has a matching field in the database table.

I am not sure if I am understanding postback properly (posting back to same page) or if I should be referencing the parameter from the calendar as a querystring value??

I have posted both parts of the code below.

ConnectionString='<%# System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_conn_results1") %>'
DatabaseType='<%# System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_DATABASETYPE_conn_results1") %>'
CommandText='<%# "SELECT *  FROM dbo.results  WHERE MatchDate = ?" %>'
<Parameter  Name="@MatchDate"  Value='<%# IIf((Request.QueryString("CalendarVal") <> Nothing), Request.QueryString("CalendarVal"), "") %>'  Type="Date"   /></Parameters></MM:DataSet>
<MM:PageBind runat="server" PostBackBind="true" />


<form class="centered" runat="server">
    <div align="center">
      <asp:calendar id="CalendarVal" runat="server" backcolor="white"
width="300px" height="200px" font-size="12px" font-names="Arial"
daynameformat="firsttwoletters" />

Open in new window

Question by:Enduro1983
    LVL 25

    Expert Comment

    Are you using Dreamweaver for this?
    LVL 25

    Expert Comment

    Try this out.  If it doesn't work you'd best do it all in code-behind (the proper way!) which I'll help you with...

    <Parameter  Name="@MatchDate"  Value='<%# CalendarVal.SelectedDate.ToShortDateString() %>'  Type="Date"   /></Parameters>

    Open in new window


    Author Comment

    Hi Rouchie,

    Still having problems, would be good to do it with a code behind! I was looking for a quick shortcut, but what has happened is I am stuck with Dw generated code in parts that is not that easy to analyse because it comes from a DreamweaverCtrls.dll.  

    So what I have is a stored procedure that takes an input from  @calendarVal DATETIME parameter, i.e. the asp:calendar, it all works fine in sql server with a supplied date in the parameter, it is just from the web page trying to pass the calender value that has been clicked into @calendarVal uisng a parameter and doPostBack.
    LVL 25

    Expert Comment

    Yes it will be.  First off please make sure you are NOT using Dreamweaver from this point onwards.  DW has never properly supported ASP.NET nor the way it works.  Macromedia (at the time) used their own proprietry code which was rediculous, then Adobe stopped supporting it altogether.  

    The full source code for the page should now look like this:

    <%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "">
    <html xmlns="">
    <head runat="server">
    <form id="form1" runat="server">
    	<asp:Calendar ID="CalendarVal" runat="server" BackColor="White" Width="300px" Height="200px" Font-Size="12px" Font-Names="Arial" DayNameFormat="FirstTwoLetters" />

    Open in new window

    Imports System.Data
    Imports System.Data.Sql
    Imports System.Data.SqlClient
    Partial Class _Default
        Inherits System.Web.UI.Page
    	Protected Sub CalendarVal_SelectionChanged(sender As Object, e As System.EventArgs) Handles CalendarVal.SelectionChanged
    		Dim conn As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("MM_CONNECTION_STRING_conn_results1")) ' connection
    		Dim cmd As New SqlCommand ' sql command object
    		cmd.Connection = conn ' set the sqlCommand to use the connection defined
    		cmd.CommandType = CommandType.Text ' set the sqlCommand to use a text command (alternative is a stored procedure)
    		cmd.CommandText = "SELECT * FROM dbo.results WHERE MatchDate = @MyDate"	' define the command text with any parameters required
    		Dim param1 As New SqlParameter	' create the parameter object
    		param1.ParameterName = "@MyDate"
    		param1.SqlDbType = SqlDbType.Date
    		param1.Value = CalendarVal.SelectedDate
    		cmd.Parameters.Add(param1)	' add the parameter into the command
    		Dim myDataReader As SqlDataReader ' this will read the database
    		Dim dt As New DataTable	' this will hold the results
    		myDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection) ' execute the command then close the connection
    		dt.Load(myDataReader) ' loads the results into our datatable variable
    		cmd.Dispose() ' clear the resources used by the sqlCommand
    		conn.Dispose() ' clear the resources used by the connection
    		' ---------------------------------------------------------------------------------
    		' The remaining code is optional - you can do what you need instead
    		' ---------------------------------------------------------------------------------
    		Dim gv As New GridView ' Now process the results as required
    		gv.AutoGenerateColumns = True
    		gv.EmptyDataText = "No dates found matching the chosen date"
    		gv.DataSource = dt
    	End Sub
    End Class

    Open in new window

    Unforunately EE's code formatting makes it hideous to read here but copy and paste into a new file and you should get something like what you need...!

    Author Comment

    Thanks Rouchie,

    I have tried testing that but it gives me an;

    Exception Details: System.ArgumentException: Keyword not supported: 'provider'.


    Dim conn As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("MM_CONNECTION_STRING_conn_results1")) ' connection
    LVL 25

    Expert Comment

    Okay I think this is an error within your web.config.
    Can you please post the line referencing MM_CONNECTION_STRING_conn_results1 ?

    Obiously blank out your password while doing this.

    Author Comment

    Hi rouchie, attached, cheeers

    <add key="MM_CONNECTION_STRING_conn_results1" value="Provider=SQLOLEDB.1;Password=********;Persist Security Info=True;User ID=********;Initial Catalog=whatsthescore;Data Source=*****" />

    Open in new window

    LVL 25

    Expert Comment

    Ahh the curse of DW's MM generated code.

    Make sure web.config is in the following format with the line corrected as shows:

    		<add name="MM_CONNECTION_STRING_conn_results1" connectionString="Persist Security Info=False; User ID=******; Password=*****; Initial Catalog=whatsthescore; Data Source=*****" providerName="System.Data.SqlClient"/>

    Open in new window

    So basically you are removing provider from the string and referencing seperately using the correct object syntax.

    Author Comment

    hi rouchie,

    changed that, it is saying

    System.InvalidOperationException: The ConnectionString property has not been initialized.



    LVL 25

    Accepted Solution

    This error can mean a number of things, but usually means that there is a problem with the connection string.  Could you please double-check that it looks correct?

    Author Closing Comment

    Cheers rouchie

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    761 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

    8 Experts available now in Live!

    Get 1:1 Help Now