SqlDataSource: No valid declarative parameter value for a datetime?

Ken Cox [Microsoft MVP] · Jun 16, 2007

I've been going around and around on this one. I can't believe that it is
"by design" as Microsoft says.

Here's the situation:

In *declarative* syntax, I'm trying to create a default datetime value for a
SqlDataSource parameter. (I know how to insert this parameter in code. I
want to use declarative markup.)

Here's the declarative markup with ???????? indicating where I'm stumped.

<insertparameters>
<asp

arameter Name="Title" Type="String"/>
<asp

arameter Name="Description" Type="String" />
<asp

arameter Name="DateAdded" Type="DateTime" DefaultValue="????????" />
</insertparameters>

There's a bug report on this dating back to 2004 from my MVP colleague
Frederik Normen. Here's the reply to the bug from the Web Platform and
Tools Team.

"If you specify a type on a Parameter, that type is used to convert the
DefaultValue or Value of the parameter to an object of that type, then the
object itself is added to the Command's parameter collection and it formats
it as you see it in the trace. If you would like full control over the
format of the date sent to the SQL server, type the parameter as a string
and your string-date will be passed to the SQL server untouched."

So I assume that I'd have to change to using the type as a string. But what
should the string look like now? I've tried lots of combinations but haven't
hit the right one.

<insertparameters>
<asp

arameter Name="Title" Type="String"/>
<asp

arameter Name="Description" Type="String" />
<asp

arameter Name="DateAdded" Type="String" DefaultValue="????????" />
</insertparameters>

I'm hoping that I'm just missing something simple on this and someone will
give me a boot in the right direction.

Ken
Microsoft MVP [ASP.NET]

Walter Wang [MSFT] · Jun 18, 2007

Hi Ken,

I've done some test and it seems it's working on my side when you use:

<asp

arameter Name="lastUpdated" Type="DateTime" DefaultValue="2007/12/24"
/>

Here's my test steps:

1) Create a SQLExpress database in VS2005, add a table with three fields:
id, code, lastUpdated.

2) Add a FormView and SqlDataSource to use the table:

<%@ Page Language="C#" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:FormView ID="FormView1" runat="server" DataKeyNames="id"
DataSourceID="SqlDataSource1">
<EditItemTemplate>
id:
<asp:Label ID="idLabel1" runat="server" Text='<%#
Eval("id") %>'></asp:Label><br />
code:
<asp:TextBox ID="codeTextBox" runat="server" Text='<%#
Bind("code") %>'>
</asp:TextBox><br />
lastUpdated:
<asp:TextBox ID="lastUpdatedTextBox" runat="server"
Text='<%# Bind("lastUpdated") %>'>
</asp:TextBox><br />
<asp:LinkButton ID="UpdateButton" runat="server"
CausesValidation="True" CommandName="Update"
Text="Update">
</asp:LinkButton>
<asp:LinkButton ID="UpdateCancelButton" runat="server"
CausesValidation="False" CommandName="Cancel"
Text="Cancel">
</asp:LinkButton>
</EditItemTemplate>
<InsertItemTemplate>
id:
<asp:TextBox ID="idTextBox" runat="server" Text='<%#
Bind("id") %>'>
</asp:TextBox><br />
code:
<asp:TextBox ID="codeTextBox" runat="server" Text='<%#
Bind("code") %>'>
</asp:TextBox><br />
lastUpdated:
<asp:TextBox ID="lastUpdatedTextBox" runat="server"
Text='<%# Bind("lastUpdated") %>'>
</asp:TextBox><br />
<asp:LinkButton ID="InsertButton" runat="server"
CausesValidation="True" CommandName="Insert"
Text="Insert">
</asp:LinkButton>
<asp:LinkButton ID="InsertCancelButton" runat="server"
CausesValidation="False" CommandName="Cancel"
Text="Cancel">
</asp:LinkButton>
</InsertItemTemplate>
<ItemTemplate>
id:
<asp:Label ID="idLabel" runat="server" Text='<%# Eval("id")
%>'></asp:Label><br />
code:
<asp:Label ID="codeLabel" runat="server" Text='<%#
Bind("code") %>'></asp:Label><br />
lastUpdated:
<asp:Label ID="lastUpdatedLabel" runat="server" Text='<%#
Bind("lastUpdated") %>'>
</asp:Label><br />
<asp:LinkButton ID="link1" runat="server" CommandName="New"
Text="New"></asp:LinkButton>
</ItemTemplate>
</asp:FormView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [id],

Code:

, [lastUpdated] FROM [Table1]"
            InsertCommand="Insert into Table1(id,code,lastUpdated) 
values(@id,@code,@lastUpdated)"
            [QUOTE]
>[/QUOTE]
            <InsertParameters>
                <asp:Parameter Name="id" Type="int32" />
                <asp:Parameter Name="code" Type="String" 
DefaultValue="default code" />
                <asp:Parameter Name="lastUpdated" Type="DateTime" 
DefaultValue="2007/12/24" />
            </InsertParameters>
            </asp:SqlDataSource>
    
    </div>
    </form>
</body>
</html>



I think the date parameter's DefaultValue string is parsed using the 
current culture of the webform when it's executed, which means this will 
depend on the user's browser setting if you're using auto culture for the 
webform.

Let me know if this simple test works on your side.


Regards,
Walter Wang ([email protected], remove 'online.')
Microsoft Online Community Support

==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

Ken Cox [Microsoft MVP] · Jun 19, 2007

Thanks Walter. I knew it was something simple.

One more question:

How would I (using declarative markup) set the defaultdate to the current
date?

Thanks again,

Ken
Microsoft MVP [ASP.NET]

Walter Wang said:
Hi Ken,

I've done some test and it seems it's working on my side when you use:

<asparameter Name="lastUpdated" Type="DateTime"
DefaultValue="2007/12/24"
/>

Here's my test steps:

1) Create a SQLExpress database in VS2005, add a table with three fields:
id, code, lastUpdated.

2) Add a FormView and SqlDataSource to use the table:

<%@ Page Language="C#" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:FormView ID="FormView1" runat="server" DataKeyNames="id"
DataSourceID="SqlDataSource1">
<EditItemTemplate>
id:
<asp:Label ID="idLabel1" runat="server" Text='<%#
Eval("id") %>'></asp:Label><br />
code:
<asp:TextBox ID="codeTextBox" runat="server" Text='<%#
Bind("code") %>'>
</asp:TextBox><br />
lastUpdated:
<asp:TextBox ID="lastUpdatedTextBox" runat="server"
Text='<%# Bind("lastUpdated") %>'>
</asp:TextBox><br />
<asp:LinkButton ID="UpdateButton" runat="server"
CausesValidation="True" CommandName="Update"
Text="Update">
</asp:LinkButton>
<asp:LinkButton ID="UpdateCancelButton" runat="server"
CausesValidation="False" CommandName="Cancel"
Text="Cancel">
</asp:LinkButton>
</EditItemTemplate>
<InsertItemTemplate>
id:
<asp:TextBox ID="idTextBox" runat="server" Text='<%#
Bind("id") %>'>
</asp:TextBox><br />
code:
<asp:TextBox ID="codeTextBox" runat="server" Text='<%#
Bind("code") %>'>
</asp:TextBox><br />
lastUpdated:
<asp:TextBox ID="lastUpdatedTextBox" runat="server"
Text='<%# Bind("lastUpdated") %>'>
</asp:TextBox><br />
<asp:LinkButton ID="InsertButton" runat="server"
CausesValidation="True" CommandName="Insert"
Text="Insert">
</asp:LinkButton>
<asp:LinkButton ID="InsertCancelButton" runat="server"
CausesValidation="False" CommandName="Cancel"
Text="Cancel">
</asp:LinkButton>
</InsertItemTemplate>
<ItemTemplate>
id:
<asp:Label ID="idLabel" runat="server" Text='<%# Eval("id")
%>'></asp:Label><br />
code:
<asp:Label ID="codeLabel" runat="server" Text='<%#
Bind("code") %>'></asp:Label><br />
lastUpdated:
<asp:Label ID="lastUpdatedLabel" runat="server" Text='<%#
Bind("lastUpdated") %>'>
</asp:Label><br />
<asp:LinkButton ID="link1" runat="server" CommandName="New"
Text="New"></asp:LinkButton>
</ItemTemplate>
</asp:FormView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [id],

Code:

, [lastUpdated] FROM [Table1]" InsertCommand="Insert into Table1(id,code,lastUpdated) values(@id,@code,@lastUpdated)" <InsertParameters> <asp:Parameter Name="id" Type="int32" /> <asp:Parameter Name="code" Type="String" DefaultValue="default code" /> <asp:Parameter Name="lastUpdated" Type="DateTime" DefaultValue="2007/12/24" /> </InsertParameters> </asp:SqlDataSource> </div> </form> </body> </html> I think the date parameter's DefaultValue string is parsed using the current culture of the webform when it's executed, which means this will depend on the user's browser setting if you're using auto culture for the webform. Let me know if this simple test works on your side. Regards, Walter Wang ([email protected], remove 'online.') Microsoft Online Community Support ================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights. [/QUOTE]

Walter Wang [MSFT] · Jun 20, 2007

Hi Ken,

The InsertParameters of SqlDataSource is persisted as
"PersistenceMode(PersistenceMode.InnerProperty)", therefore I don't think
we're able to use an expression such as this to get the current datetime
declaratively:

DefaultValue="<%= DateTime.Now.ToString() %>"

Regards,
Walter Wang ([email protected], remove 'online.')
Microsoft Online Community Support

==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

Return value parameter error	12	Sep 16, 2009
Change SqlDataSource Parameter	3	Sep 21, 2008
OleDb SqlDataSource stored procedure call parameter order mixed up ?	0	Sep 30, 2010
Sqldatasource parameter date	5	Apr 16, 2010
Insert Parameter using .DefaultValue	2	Apr 10, 2009
SqlDataSource UpdateCommand problem	1	Aug 24, 2007
ViewState Value as Parameter for SQLDataSource	2	Dec 21, 2009
SqlDataSource, Eval, and Label Text	2	Nov 29, 2009

SqlDataSource: No valid declarative parameter value for a datetime?

Ken Cox [Microsoft MVP]

Walter Wang [MSFT]

Ken Cox [Microsoft MVP]

Walter Wang [MSFT]

Ask a Question

Similar Threads

Members online

Forum statistics

Latest Threads