Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

passing asp:calendar value back to same page

Posted on 2011-10-27
11
Medium Priority
?
275 Views
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.
QUERY CODE

<MM:DataSet 
id="DataSet1"
runat="Server"
IsStoredProcedure="false"
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 = ?" %>'
Debug="true"
><Parameters>
<Parameter  Name="@MatchDate"  Value='<%# IIf((Request.QueryString("CalendarVal") <> Nothing), Request.QueryString("CalendarVal"), "") %>'  Type="Date"   /></Parameters></MM:DataSet>
<MM:PageBind runat="server" PostBackBind="true" />


CALENDAR CODE

<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" />
    </div>
  </form>

Open in new window

0
Comment
Question by:Enduro1983
  • 6
  • 5
11 Comments
 
LVL 25

Expert Comment

by:Rouchie
ID: 37045034
Are you using Dreamweaver for this?
0
 
LVL 25

Expert Comment

by:Rouchie
ID: 37045046
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

0
 

Author Comment

by:Enduro1983
ID: 37046903
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.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 25

Expert Comment

by:Rouchie
ID: 37049498
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:

DEFAULT.ASPX
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
	<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
	<asp:Calendar ID="CalendarVal" runat="server" BackColor="White" Width="300px" Height="200px" Font-Size="12px" Font-Names="Arial" DayNameFormat="FirstTwoLetters" />
</div>
</form>
</body>
</html>

Open in new window


DEFAULT.ASPX.VB
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
		cmd.Connection.Open()
		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"
		Page.Form.Controls.Add(gv)
		gv.DataSource = dt
		gv.DataBind()
	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...!
0
 

Author Comment

by:Enduro1983
ID: 37050094
Thanks Rouchie,

I have tried testing that but it gives me an;

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

at

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

Expert Comment

by:Rouchie
ID: 37050482
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.
0
 

Author Comment

by:Enduro1983
ID: 37050497
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

0
 
LVL 25

Expert Comment

by:Rouchie
ID: 37050513
Ahh the curse of DW's MM generated code.

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

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

Open in new window


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

Author Comment

by:Enduro1983
ID: 37050560
hi rouchie,

changed that, it is saying

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

on

cmd.Connection.Open()

0
 
LVL 25

Accepted Solution

by:
Rouchie earned 1000 total points
ID: 37055515
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?
0
 

Author Closing Comment

by:Enduro1983
ID: 37078115
Cheers rouchie
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

580 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