detailsview inserting date uk format to sql server 2000

Posted on 2009-04-22
Last Modified: 2012-05-06
I have a details view that is inserting some data into a MS SQL 2000 database.  One of the fields is looking for a date.  Our users will be entering the date in the format dd/MM/yyyy.  This is not accepted by the SQL server.  If I use the date format MM/dd/yyyy or yyyy/MM/dd it works fine.

How can I convert the date format before inserting the record?  I do not want to alter and settings on the SQL server.

<asp:DetailsView ID="DetailsView1" runat="server" 

        AutoGenerateInsertButton="True" AutoGenerateRows="False" CellPadding="4" 

        DataSourceID="SqlDataSource3" DefaultMode="Insert" ForeColor="#333333" 

        GridLines="None" Height="50px" Width="125px">

        <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />

        <CommandRowStyle BackColor="#D1DDF1" Font-Bold="True" />

        <RowStyle BackColor="#EFF3FB" />

        <FieldHeaderStyle BackColor="#DEE8F5" Font-Bold="True" />

        <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />


            <asp:BoundField DataField="fldID" HeaderText="ID" InsertVisible="False" 

                ReadOnly="True" SortExpression="fldID" Visible="False" />

            <asp:BoundField DataField="fldReg" HeaderText="Reg" 

                SortExpression="fldReg" />

            <asp:BoundField DataField="fldSerial" HeaderText="Serial" 

                SortExpression="fldSerial" />

            <asp:BoundField DataField="fldProj" HeaderText="Proj No" 

                SortExpression="fldProj" />

            <asp:BoundField DataField="fldCustomer" HeaderText="Customer" 

                SortExpression="fldCustomer" />

            <asp:BoundField DataField="fldStart" HeaderText="Start Date" 

                SortExpression="fldStart" DataFormatString="{0:dd/MM/yyyy}" 

                HtmlEncode="false" ApplyFormatInEditMode="True" />

            <asp:BoundField DataField="fldActive" HeaderText="Active" 

                SortExpression="fldActive" ReadOnly="True" Visible="False" />

            <asp:BoundField DataField="fldLastChange" HeaderText="Last Change" 

                SortExpression="fldLastChange" ReadOnly="True" Visible="False" />

            <asp:TemplateField HeaderText="User ID">


                        <asp:TextBox ID="tbUserID2" runat="server" ></asp:TextBox>



            <asp:TemplateField HeaderText="Password"><EditItemTemplate>

                        <asp:TextBox ID="tbPassword2" runat="server" TextMode="Password"></asp:TextBox>



        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />

        <EditRowStyle BackColor="#2461BF" />

        <AlternatingRowStyle BackColor="White" />


    <asp:SqlDataSource ID="SqlDataSource3" runat="server" 


        ConnectionString="<%$ ConnectionStrings:KPIConnectionString %>" 

        InsertCommand="INSERT INTO [tblSAPrepProjects] ([fldReg], [fldSerial], [fldProj], [fldCustomer], [fldStart]) VALUES (@fldReg, @fldSerial, @fldProj, @fldCustomer, convert(datetime,@fldStart,103)); SELECT @ProjectID = @@IDENTITY" 



            <asp:Parameter Name="fldReg" Type="String" />

            <asp:Parameter Name="fldSerial" Type="String" />

            <asp:Parameter Name="fldProj" Type="String" />

            <asp:Parameter Name="fldCustomer" Type="String" />

            <asp:Parameter Name="fldStart" Type="DateTime" />

            <asp:Parameter Name="fldActive" Type="String" />

            <asp:Parameter Name="fldLastChange" Type="String" />

            <asp:parameter direction="Output" name="ProjectID" type="Int32" />



Open in new window

Question by:iepaul
    LVL 31

    Expert Comment

    LVL 4

    Expert Comment

    Maybe specifying format in the convert  will do the trick?
    For example:
    select convert(datetime,'30/01/1991',103)
    LVL 31

    Expert Comment

    You can convert your input from dd/mm/yyyy to mm/dd/yyyy before you give it to your DML statement.
    LVL 31

    Expert Comment

    you can do the trick as IGOR said but I guess you want don't want to do it with SQL Server's code,

    Author Comment

    How do I convert the input date before the insert command?
    LVL 31

    Accepted Solution

    you can use ItemInserting event for that.
    LVL 31

    Expert Comment

    see how you can utilize ItemInserting event for your task.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Grouping within groups: SQL 5 29
    report c# 9 60
    Adding WebGrid Checkbox Column checked by default 3 15
    sQL pivot 9 23
    Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now