• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • Last Modified:

detailsview inserting date uk format to sql server 2000

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" />
        <Fields>
            <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">
                                <EditItemTemplate>
                        <asp:TextBox ID="tbUserID2" runat="server" ></asp:TextBox>
                    </EditItemTemplate>
</asp:TemplateField>
            <asp:TemplateField HeaderText="Password"><EditItemTemplate>
                        <asp:TextBox ID="tbPassword2" runat="server" TextMode="Password"></asp:TextBox>
                    </EditItemTemplate></asp:TemplateField>
        </Fields>
        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <EditRowStyle BackColor="#2461BF" />
        <AlternatingRowStyle BackColor="White" />
    </asp:DetailsView>
 
    <asp:SqlDataSource ID="SqlDataSource3" runat="server" 
        ConflictDetection="CompareAllValues" 
        ConnectionString="<%$ ConnectionStrings:KPIConnectionString %>" 
 
        InsertCommand="INSERT INTO [tblSAPrepProjects] ([fldReg], [fldSerial], [fldProj], [fldCustomer], [fldStart]) VALUES (@fldReg, @fldSerial, @fldProj, @fldCustomer, convert(datetime,@fldStart,103)); SELECT @ProjectID = @@IDENTITY" 
        OldValuesParameterFormatString="original_{0}" 
 
        <InsertParameters>
            <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" />
        </InsertParameters>
    </asp:SqlDataSource>

Open in new window

0
iepaul
Asked:
iepaul
  • 5
1 Solution
 
RiteshShahCommented:
0
 
Igor-KCommented:
Maybe specifying format in the convert  will do the trick?
For example:
select convert(datetime,'30/01/1991',103)
0
 
RiteshShahCommented:
You can convert your input from dd/mm/yyyy to mm/dd/yyyy before you give it to your DML statement.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
RiteshShahCommented:
you can do the trick as IGOR said but I guess you want don't want to do it with SQL Server's code,
0
 
iepaulAuthor Commented:
How do I convert the input date before the insert command?
0
 
RiteshShahCommented:
you can use ItemInserting event for that.
0
 
RiteshShahCommented:
see how you can utilize ItemInserting event for your task.

http://forums.asp.net/p/1398774/3018360.aspx 
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now