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.

ControlParameter and Stored Procedures

I'm sure I'm missing something silly. I have 3 textboxes, a stored procedure and a gridview. The user will put something in the 3 boxes, click submit, and see a grid with stuff (I hope). However, the grid will only return data is I EXCLUDE the controlparameters and only use the sessionparameter. It's like the stored proc won't even fire!

HTML:

<formid="form1"runat="server">
<div>
Lastname: <asp:textboxid="Lastname"runat="server"></asp:textbox>
Hobbies: <asp:textboxid="Hobbies"runat="server"></asp:textbox><br/>
Profession: <asp:textboxid="Profession"runat="server"></asp:textbox>
<asp:buttonid="Button1"runat="server"text="Button"/><br/>
<asp:gridviewskinid="DataGrid"id="GridView1"runat="server"allowpaging="True"allowsorting="True"autogeneratecolumns="False"datasourceid="SqlDataSource1">
<columns>
<asp:boundfielddatafield="Username"headertext="Username"sortexpression="Username"/>
<asp:boundfielddatafield="Lastname"headertext="Lastname"sortexpression="Lastname"/>
<asp:boundfielddatafield="Firstname"headertext="Firstname"sortexpression="Firstname"/>
</columns>
</asp:gridview><asp:sqldatasourceid="SqlDataSource1"runat="server"connectionstring="<%$ ConnectionStrings:HOAConnectionString %>"
selectcommand="spAddressBookSelect"selectcommandtype="StoredProcedure">
<selectparameters>
<asp:sessionparameterdefaultvalue="0"name="CommunityID"sessionfield="CommunityID"type="Int32"/>
<asp:controlparametercontrolid="Lastname"name="Lastname"propertyname="Text"type="String"/>
<asp:controlparametercontrolid="Profession"name="Profession"propertyname="Text"type="String"/>
<asp:controlparametercontrolid="Hobbies"name="Hobbies"propertyname="Text"type="String"/>
</selectparameters>
</asp:sqldatasource></div></form>

sp signature:
ALTER PROCEDURE[dbo].[spAddressBookSelect]
@.CommunityIDint= 0,
@.Lastnamevarchar(200) =NULL,
@.Professionvarchar(200) =NULL,
@.Hobbiesvarchar(200) =NULL

I'm guessing that you mean it doesn't work when all three textboxes aren't filled in. It should work if you fill out all of them. If that is the case, then your problem is that you are passing a NULL value if the textbox is blank, and you haven't set the "CancelSelectOnNullParameter" property of the sqldatasource control to false, so yes, it's not firing the SELECT.

|||Nicely done! That was it, thanks!