SqlDataSource: No valid declarative parameter value for a datetime?

Discussion in 'ASP .Net' started by Ken Cox [Microsoft MVP], Jun 16, 2007.

  1. 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]
     
    Ken Cox [Microsoft MVP], Jun 16, 2007
    #1
    1. Advertising

  2. 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)"
                [color=blue]
                >[/color]
                <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 (, 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.
     
    Walter Wang [MSFT], Jun 18, 2007
    #2
    1. Advertising

  3. 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]" <> wrote in message
    news:...
    > 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)"
    >[color=green]
    >            >[/color]
    >            <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 (, 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.
    > [/color]
     
    Ken Cox [Microsoft MVP], Jun 19, 2007
    #3
  4. 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 (, 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.
     
    Walter Wang [MSFT], Jun 20, 2007
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?SmVmZkRvdE5ldA==?=

    Declarative use of sqlDataSource in ASP.net 2.0 and Exception Hand

    =?Utf-8?B?SmVmZkRvdE5ldA==?=, Feb 1, 2006, in forum: ASP .Net
    Replies:
    1
    Views:
    2,888
    Steven Cheng[MSFT]
    Feb 2, 2006
  2. Christos TZOTZIOY Georgiou
    Replies:
    3
    Views:
    759
    Christos TZOTZIOY Georgiou
    Sep 13, 2003
  3. Tim Peters
    Replies:
    0
    Views:
    597
    Tim Peters
    Sep 9, 2003
  4. mp
    Replies:
    1
    Views:
    440
    John Machin
    Jul 28, 2006
  5. Guest
    Replies:
    2
    Views:
    1,966
    Guest
    Aug 18, 2007
Loading...

Share This Page