Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > SqlDataSource: No valid declarative parameter value for a datetime?

Reply
Thread Tools

SqlDataSource: No valid declarative parameter value for a datetime?

 
 
Ken Cox [Microsoft MVP]
Guest
Posts: n/a
 
      06-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>
<asparameter Name="Title" Type="String"/>
<asparameter Name="Description" Type="String" />
<asparameter 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>
<asparameter Name="Title" Type="String"/>
<asparameter Name="Description" Type="String" />
<asparameter 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]


 
Reply With Quote
 
 
 
 
Walter Wang [MSFT]
Guest
Posts: n/a
 
      06-18-2007
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>
<asparameter Name="id" Type="int32" />
<asparameter Name="code" Type="String"
DefaultValue="default code" />
<asparameter 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 ((E-Mail Removed), 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.

 
Reply With Quote
 
 
 
 
Ken Cox [Microsoft MVP]
Guest
Posts: n/a
 
      06-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 [MSFT]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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>
> <asparameter Name="id" Type="int32" />
> <asparameter Name="code" Type="String"
> DefaultValue="default code" />
> <asparameter 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 ((E-Mail Removed), 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.
>



 
Reply With Quote
 
Walter Wang [MSFT]
Guest
Posts: n/a
 
      06-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 ((E-Mail Removed), 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.

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Specified argument was out of the range of valid values. Parameter name: value? darrel ASP .Net 0 08-14-2006 10:32 PM
Interdependent DropDownLists (with declarative data binding) =?Utf-8?B?VGltbQ==?= ASP .Net 11 06-29-2006 06:49 AM
is it possible to get hidden parameter values using window.opener.document.form.parameter.value. Abdul Mohsin Javascript 1 09-06-2005 03:38 PM
how to sort hash by value, where value is a valid date perl_help_needed Perl Misc 12 06-02-2005 06:27 PM
Declarative Security in ASP .net MS Newsgroups ASP .Net 1 10-19-2003 07:21 AM



Advertisments