By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,357 Members | 1,021 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,357 IT Pros & Developers. It's quick & easy.

SqlDataSource: No valid declarative parameter value for a datetime?

P: n/a
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:parameter Name="Title" Type="String"/>
<asp:parameter Name="Description" Type="String" />
<asp:Parameter 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:parameter Name="Title" Type="String"/>
<asp:parameter Name="Description" Type="String" />
<asp:Parameter 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]
Jun 16 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Hi Ken,

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

<asp:Parameter 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 (wa****@online.microsoft.com, 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.

Jun 18 '07 #2

P: n/a
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 [MSFT]" <wa****@online.microsoft.comwrote in message
news:6q**************@TK2MSFTNGHUB02.phx.gbl...
Hi Ken,

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

<asp:Parameter 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 (wa****@online.microsoft.com, 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.

Jun 19 '07 #3

P: n/a
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 (wa****@online.microsoft.com, 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.

Jun 20 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.