Conversion failed when converting character string to smalldatetime data type.

Hello, I have problem with this code.(This programpresents - there is GridView tied to a SQL database that will sort the data selected by a dropdownList at time categories. There are 2 time categories in DropDownList - this day, this week.

Problem: when I choose one categorie in dropDownlist for examle this week and submit data on the server I got this error.

Conversion failed when converting character string to smalldatetime data type.

Here is code:

<%

@.PageLanguage="C#" %>

<!

DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<

scriptrunat="server">

protectedvoid DropDownList1_SelectedIndexChanged(object sender,EventArgs e)

{

string datePatt =@."yyyymmdd";

// Get start and end of day

DateTime StartDate =DateTime.Today;

string @.StartDate1 = StartDate.ToString(datePatt);

string @.EndDate = StartDate.AddDays(1).ToString(datePatt);// Get start and end of weekstring @.startOfWeek = StartDate.AddDays(0 - (int)StartDate.DayOfWeek).ToString(datePatt);string @.startOfNextWeek = StartDate.AddDays(7 - (int)StartDate.DayOfWeek).ToString(datePatt);

switch (DropDownList1.SelectedValue)

{

case"1":

// day

SqlDataSource1.SelectCommand =

"SELECT [RC_USER_ID], [DATE], [TYPE] FROM [T_RC_IN_OUT]" +"WHERE" +"[DATE] >=" +"'@.StartDate1'" +" AND [DATE] < " +"'@.EndDate'";break;case"2"://week

SqlDataSource1.SelectCommand =

"SELECT [RC_USER_ID], [DATE], [TYPE] FROM [T_RC_IN_OUT]" +"WHERE" +"[DATE] >=" +"'@.startOfWeek'" +"AND [DATE] <" +"'@.startOfNextWeek'";break;

}

}

</

script>

<

htmlxmlns="http://www.w3.org/1999/xhtml">

<

headid="Head1"runat="server"><title>Untitled Page</title><styletype="text/css">body {font:1emVerdana;

}

</style>

</

head>

<

body><formid="form1"runat="server"><div>

<asp:DropDownListID="DropDownList1"runat="server"AutoPostBack="True"OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged"Style="z-index: 100; left: 414px; position: absolute; top: 22px"><asp:ListItemSelected="True"Value="1">jeden den</asp:ListItem><asp:ListItemValue="2">jeden tyden</asp:ListItem></asp:DropDownList>

<asp:GridViewID="GridView1"runat="server"Style="z-index: 102; left: 228px; position: absolute;

top: 107px"

DataSourceID="SqlDataSource1"AutoGenerateColumns="True">

</asp:GridView> <br/><br/><asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="Data Source=CR\SQLEXPRESS;

Initial Catalog=MyConn;Integrated Security=True"

ProviderName="System.Data.SqlClient"></asp:SqlDataSource></div></form>

</

body>

</

html>

string datePatt =@."yyyymmdd";

In your 'date' pattern, mm is minutes.

You want MM for month: yyyyMMdd

|||Thank you, for your reply, Icorrected this error. But the problem is still here.|||

I notice you are trying to use parameters but I don't see the code that adds parameters: Parameters.Add(...).

If you pass in the date as a DateTime, you will not have to format it to a string.

|||Thank you, I will try.

Control number of options selected in a multi select parameter

Hi All

I have a report which has a multi-value parameter. Problem is, it can contain up to 100 options.

Is there a way to limit the number of options that is passed to the SQL statement?. EG list has 100 options, user selects 10 but only the first 4 selected options are passed to the SQL statement.


Many Thanks
Delli
I will try an expression for the query parameter (Parameters tab in the dataset properties) which passes your parameter to code-behind function that in turn filters out the parameter values accordingly.|||

After looking into this some more, I’ve found a split

function for MS SQL server, and in PL/SQL. Have system in both databases

:( grrrrrrr

The split function takes in the multi select parameter as a comma separated

list, and creates a virtual table of the results.

By using SQL code similar to the following: select top 10 element

dbo.split('string,split,code',',') I could stop the SQL engine running

for too many selected parameters. A search on Google or msn search ;) will find

codes examples for these functions. Keywords: SQL split function or PL/SQL

split function.

Also helps to inform your users on the front page of the report you have

done this!!!