Calendar Control and Database Connection

Hi,
I m trying to implement a calendar, where you can select certain dates and view list of events for that day. The list is generated from database. I found a script on the web that is quite similar to what i want to achieve. I tried to run this script, however I get an exeption saying:

An error has occurred while establishing a connection to the server. .... error: 40 - Could not open a connection to SQL Server)

This is the code:

SqlConnection mycn;
SqlDataAdapter myda;
DataSet ds = new DataSet();
DataSet dsSelDate;
String strConn;
private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
strConn = "Data Source=localhost;Initial Catalog=pubs";

mycn = new SqlConnection(strConn);
myda = new SqlDataAdapter("Select * FROM EventsTable", mycn);
myda.Fill(ds, "Table"); //This where I get the exeption
}

protected void CalendarDRender(object sender, System.Web.UI.WebControls.DayRenderEventArgs e)
{
// If the month is CurrentMonth
if (!e.Day.IsOtherMonth)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
if ((dr["EventDate"].ToString() != DBNull.Value.ToString()))
{
DateTime dtEvent = (DateTime)dr["EventDate"];
if (dtEvent.Equals(e.Day.Date))
{
e.Cell.BackColor = System.Drawing.Color.PaleVioletRed;
}
}
}
}
//If the month is not CurrentMonth then hide the Dates
else
{
e.Cell.Text = "";
}
}

private void Calendar1_SelectionChanged(object sender, System.EventArgs e)
{
myda = new SqlDataAdapter("Select * from EventsTable where EventDate='" +
Calendar1.SelectedDate.ToString() + "'", mycn);
dsSelDate = new DataSet();
myda.Fill(dsSelDate, "AllTables");
if (dsSelDate.Tables[0].Rows.Count == 0)
{
DataGrid1.Visible = false;
}
else
{
DataGrid1.Visible = true;
DataGrid1.DataSource = dsSelDate;
DataGrid1.DataBind();
}
}

Can some one tell me what is wrong with the code.
Thank You.

Answer this question

Calendar Control and Database Connection

Max Henzi
This may occur if you have specified a data source for the Grid in the design time. Since you are binding the control to the DataSource programmatically, you should not specify the DataSourceID at design time.

To resolve this issue, go back to the WebForm designer, select the GridView from the page, right click on it and select properties from the context menu that appears.

In the properties window, find the DataSourceID property, and remove the value that is set for it.

Alternatively, you can go to the markup view and go to the declaration of the GridView. Then find property DataSourceID, and remove it.
Jason Holding
OK. Finally it worked, However, I get another exeption trown at me. It says:

Both DataSource and DataSourceID are defined on DataGrid1. Remove one of them.

I don't know what I need to remove to make this application up and running.
Seemsimnotalone
I tried the ToShortDateString(), but still get the same error message. This is how I used it, I don't know if it is the right way:

protected void Calendar1_SelectionChanged(object sender, System.EventArgs e)

{

myda = new SqlDataAdapter("Select * from EventsTable where EventDate='" +

Calendar1.SelectedDate.ToShortDateString() + "'", mycn);

dsSelDate = new DataSet();

myda.Fill(dsSelDate, "AllTables");

if (dsSelDate.Tables[0].Rows.Count == 0)

{

DataGrid1.Visible = false;

}

else

{

DataGrid1.Visible = true;

DataGrid1.DataSource = dsSelDate;

DataGrid1.DataBind();

}

}

Pete, I am storing the date as datetime field not string. When I created the database, I specified the column that sores date as datetime.

Thank you.
Philip Wright
Thank you deoprat it worked fine. However now i get new exeption thrown within Calendar1_SelectionChanged Method. This how the method looks like:

protected void Calendar1_SelectionChanged(object sender, System.EventArgs e)

{

myda = new SqlDataAdapter("Select * from EventsTable where EventDate='" +

Calendar1.SelectedDate.ToString() + "'", mycn);

dsSelDate = new DataSet();

myda.Fill(dsSelDate, "AllTables"); //This where i get the exeption.

if (dsSelDate.Tables[0].Rows.Count == 0)

{

DataGrid1.Visible = false;

}

else

{

DataGrid1.Visible = true;

DataGrid1.DataSource = dsSelDate;

DataGrid1.DataBind();

}

}

And this is the message I get:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

I am using SQL Server Ex. 2005. Also I stored the date in the database like dd/mm/yyyy. I beleive this the reson I get this exeption. However I don't know how to change the format of the date so I can store it like yyyy/mm/dd

Thank You

WEB HOSTS AND DOMAINS
Nitin Gupta MSFT

Hi!

Thanks for asking! I'm a member of the ASP.NET team, and was just popping over here to see what was going on. The best place to ask ASP.NET questions is over on the ASP.NET forums at http://www.asp.net/welcome.aspx tabindex=1&tabid=39

If you're storing the DateTime info in your database as a string instead of as a DateTime field, you may run into problems with this. The DateTime.ToString() will result in something like "12/13/2005 10:19:05AM", and that is probably much larger than your field has room for.

HTH,

PEte
Vic N.
Deobrat,

Help is much appriciated. It works ok now. I know I will come accross biger problems, but for know I am on the way. Thanks to you.

Built Your Site with One Click
gray82
Hi,
The answer lies in the size of the datetime datatype inside SQL Server. The datetime datatype is 8 bytes. And when you use ToString() on the Calendar.SelectedDate property, it results in a value much larger than 8 bytes.

So, instead of using ToString(), you should consider using ToShortDateString().

This should solve the problem.

If it doesn't, please get back.
JB Plum
Hey,
Seems you are having problem with the connection string
try the following:

      SqlConnection mycn;
      SqlDataAdapter myda;
      DataSet ds = new DataSet();
      DataSet dsSelDate;
      String strConn;
      private void Page_Load(object sender, System.EventArgs e)
      {
          strConn = "Data Source=localhost;Initial Catalog=pubs";

          mycn = new SqlConnection(strConn);
          try
          {
               mycn.Open();
          }
          catch(SqlException ex)
          {
               Response.Write("Connection string invalid");
               Response.End();
          }
          myda = new SqlDataAdapter("Select * FROM EventsTable", mycn);
          myda.Fill(ds, "Table");
      }

If you can see "Connection string invalid" on the browser, then you need to verify that the connection string that you are using is valid. Typically, you would want to check the following:

1. Make sure that the SQL Server instance is running and you have specified the correct name (the clause Data Source = localhost corresponds to the SQL Server instance). Try replacing localhost with the name of the instance running on your machine.

2. Make sure that the database that you are specifying is correct. (the clause Initial Catalog = pubs corresponds to the database you want to refer). Try replacing pubs with the name of your database.

This should solve your problem. If it doesn't, do post again.
Calendar Control and Database Connection

Answer this question