?
Solved

detailsview inserting date uk format to sql server 2000

Posted on 2009-04-22
7
Medium Priority
?
319 Views
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" />
        <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
Comment
Question by:iepaul
  • 5
7 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24202789
0
 
LVL 4

Expert Comment

by:Igor-K
ID: 24202846
Maybe specifying format in the convert  will do the trick?
For example:
select convert(datetime,'30/01/1991',103)
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24202858
You can convert your input from dd/mm/yyyy to mm/dd/yyyy before you give it to your DML statement.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 31

Expert Comment

by:RiteshShah
ID: 24202866
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
 

Author Comment

by:iepaul
ID: 24202872
How do I convert the input date before the insert command?
0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 2000 total points
ID: 24202916
you can use ItemInserting event for that.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24202938
see how you can utilize ItemInserting event for your task.

http://forums.asp.net/p/1398774/3018360.aspx 
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

807 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