Date format problem's when saving in SQL Server.

  1. #1

    Date format problem's when saving in SQL Server.

  2. #2
    You can try to use SqlDataSource.Updating event and validate/parse/change SqlDataSource.UpdateParameters
    Last edited by gappov; Nov 15, 2012 at 12:51 PM.
  3. #3

    Thank you.

    Quote Originally Posted by gappov View Post
    You can try to use SqlDataSource.Updating event and validate/parse/change SqlDataSource.UpdateParameters
    Thank you for this idea. I'm not sure exactly how, but I will explore in this direction.
  4. #4

    Date and number local formats

    Quote Originally Posted by Kuma View Post
    Hello, I have a problem with the date format. In Belgium, it is "d-M-Y". So I put on a mask "d-M-Y" which works very well in display, but when I save the SQL db and I read the record again it is become "M-d-Y". I have traveled a lot of post on this site but I have not found anything convincing.
    I have the same problem with date format (dd.MM.yyyy). What is worse, I can't cope with float numbers, because of my country's standard says the decimal separator is comma (0.000,00).
    It's really frustrating, although I'm really impressed by the features and effectiveness of this environment. But unfortunately, I'm struggling with a few basic thigs that would probably lead me away of Ext.net.

    The apps in my company are based on ASP.NET Web forms with Sql Server databases. Data types are mostly float/double and dates. I made some test with GridPanel/SqlDataSource, and data presentation in is ok, I could make it correctly shown. But, updating data to DB is something I just can't figure out what to do. Dots and commas are constantly replacing in numbers, as well as days and months in dates. Ok, I can make it parsed while updating and correctly send to SQL server, but that is just not the way I would like to solve it and waste time every time I'm coding updating or inserting data.
    So, my question would be is there any high-level way in Ext.net that I could specify what culture/formats I want to use, and that would be followed further in every aspect of Ext.net app? Just like MS does with ASP.NET.
  5. #5
    On my computer I use Russian locale (dd.MM.yyyy and comma as decimal separator) and I have no any problems with inserting date/float to database

    Here is my test case
    DB
    CREATE TABLE [dbo].[Table] (
        [Id]            INT        IDENTITY (1, 1) NOT NULL,
        [DateField]     DATE       NOT NULL,
        [DateTimeField] DATETIME   NOT NULL,
        [FloatField]    FLOAT (53) NOT NULL,
        PRIMARY KEY CLUSTERED ([Id] ASC)
    );
    Test page:
    <%@ Page Language="C#" Culture="ru-RU" UICulture="ru-RU" %>
    
    
    <%@ Register Assembly="Ext.Net" Namespace="Ext.Net" TagPrefix="ext" %>
    
    
    <!DOCTYPE html>
    
    
    <html>
    <head runat="server">
        <title></title>
    
    
        <script runat="server">
            private bool cancel;
            private string message;
            private int? insertedValue;
    
    
            protected void Store1_AfterRecordInserted(object sender, AfterRecordInsertedEventArgs e)
            {
                if (insertedValue.HasValue)
                {
                    e.Keys.Add("Id", insertedValue.Value);
                    insertedValue = null;
                }
            }
    
    
            protected void SqlDataSource1_Inserted(object sender, SqlDataSourceStatusEventArgs e)
            {
                if (e.Command.Parameters["@newId"].Value != null)
                {
                    insertedValue = (int)e.Command.Parameters["@newId"].Value;
                }
                else
                {
                    insertedValue = null;
                }
            }
        </script>
    </head>
    <body>
        <form runat="server">
            <ext:ResourceManager runat="server" />
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
                ConnectionString="<%$ ConnectionStrings:Database1ConnectionString %>" 
                DeleteCommand="DELETE FROM [Table] WHERE [Id] = @Id" 
                InsertCommand="INSERT INTO [Table] ([DateField], [DateTimeField], [FloatField]) VALUES (@DateField, @DateTimeField, @FloatField); SELECT @newId = @@Identity;" 
                SelectCommand="SELECT [Id], [DateField], [DateTimeField], [FloatField] FROM [Table]" 
                UpdateCommand="UPDATE [Table] SET [DateField] = @DateField, [DateTimeField] = @DateTimeField, [FloatField] = @FloatField WHERE [Id] = @Id"
                OnInserted="SqlDataSource1_Inserted">
                <DeleteParameters>
                    <asp:Parameter Name="Id" Type="Int32" />
                </DeleteParameters>
                <InsertParameters>                
                    <asp:Parameter DbType="Date" Name="DateField" />
                    <asp:Parameter Name="DateTimeField" Type="DateTime" />
                    <asp:Parameter Name="FloatField" Type="Double" />
                    <asp:Parameter Direction="Output" Name="newId" Type="Int32" />
                </InsertParameters>
                <UpdateParameters>
                    <asp:Parameter DbType="Date" Name="DateField" />
                    <asp:Parameter Name="DateTimeField" Type="DateTime" />
                    <asp:Parameter Name="FloatField" Type="Double" />
                    <asp:Parameter Name="Id" Type="Int32" />
                </UpdateParameters>
            </asp:SqlDataSource>
    
    
            <ext:Store 
                ID="Store1" 
                runat="server" 
                DataSourceID="SqlDataSource1"
                OnAfterRecordInserted="Store1_AfterRecordInserted">
                <Model>
                    <ext:Model runat="server" IDProperty="Id">
                        <Fields>
                            <ext:ModelField Name="DateField" Type="Date" />
                            <ext:ModelField Name="DateTimeField" Type="Date" />
                            <ext:ModelField Name="FloatField" Type="Float" />
                        </Fields>
                    </ext:Model>
                </Model>            
            </ext:Store>
    
    
            <ext:GridPanel 
                ID="GridPanel1" 
                runat="server"  
                Height="300"            
                StoreID="Store1">
                <ColumnModel runat="server">
                    <Columns>
                        <ext:Column runat="server" 
                            DataIndex="DateField" 
                            Text="DateField"
                            Flex="1">
                            <Editor>
                                <ext:DateField runat="server" />
                            </Editor>
                        </ext:Column>                                            
    
    
                        <ext:Column runat="server" 
                            DataIndex="DateTimeField" 
                            Text="DateTimeField"
                            Flex="1">
                            <Editor>
                                <ext:DateField runat="server" />
                            </Editor>
                        </ext:Column>     
    
    
                        <ext:Column runat="server" 
                            DataIndex="FloatField" 
                            Text="FloatField"
                            Flex="1">
                            <Editor>
                                <ext:NumberField runat="server" />
                            </Editor>
                        </ext:Column>     
                    </Columns>
                </ColumnModel>                                                                            
                <Plugins>
                    <ext:CellEditing runat="server" />
                </Plugins>
                 <Buttons>
                    <ext:Button ID="btnSave" runat="server"  Text="Sync" Icon="Disk">
                        <Listeners>
                            <Click Handler="#{Store1}.sync();" />
                        </Listeners>
                    </ext:Button>
                    <ext:Button ID="btnDelete" runat="server"  Text="Delete selected records" Icon="Delete">
                        <Listeners>
                            <Click Handler="#{GridPanel1}.deleteSelected();" />
                        </Listeners>
                    </ext:Button>
                    <ext:Button ID="btnInsert" runat="server"  Text="Insert" Icon="Add">
                        <Listeners>
                            <Click Handler="#{Store1}.insert(0, {});#{GridPanel1}.editingPlugin.startEditByPosition({row:0, column:0});" />
                        </Listeners>
                    </ext:Button>
                    <ext:Button ID="btnRefresh" runat="server"  Text="Refresh" Icon="ArrowRefresh">
                        <Listeners>
                            <Click Handler="#{Store1}.reload();" />
                        </Listeners>
                    </ext:Button>                           
                </Buttons>
            </ext:GridPanel>
        </form>
    </body>
    </html>
    I suggest to debug sync process: add breakpoint to MakeInsertes method in Store.cs file in Ext.Net
    In that method, check what values are submitted and what value are passed to Insert method of DataSourceView (in that method)
  6. #6
    Quote Originally Posted by Vladimir View Post
    On my computer I use Russian locale (dd.MM.yyyy and comma as decimal separator) and I have no any problems with inserting date/float to database
    Thank you for your response.
    When I run your code example, with "ru-RU" culture, I'm still not able to edit/insert float number.
    Entering "5" is ok, but "5,1" returns this error:
    {serviceResponse:{success:false,message:"System.Ex ception: Input string was not in a correct format. ---> System.FormatException: Input string was not in a correct format.\r\n at System.Number.ParseDouble(String value, NumberStyles options, NumberFormatInfo numfmt)\r\n at System.String.System.IConvertible.ToDouble(IFormat Provider provider)\r\n at System.Convert.ChangeType(Object value, TypeCode typeCode, IFormatProvider provider)\r\n at System.Web.UI.WebControls.Parameter.GetValue(Objec t value, String defaultValue, TypeCode type, Boolean convertEmptyStringToNull, Boolean ignoreNullableTypeChanges)\r\n at System.Web.UI.WebControls.Parameter.GetValue(Objec t value, Boolean ignoreNullableTypeChanges)\r\n at System.Web.UI.WebControls.SqlDataSourceView.AddPar ameters(DbCommand command, ParameterCollection reference, IDictionary parameters, IDictionary exclusionList, String oldValuesParameterFormatString)\r\n at System.Web.UI.WebControls.SqlDataSourceView.Execut eUpdate(IDictionary keys, IDictionary values, IDictionary oldValues)\r\n at System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback)\r\n at Ext.Net.Store.MakeUpdates(IDataSource ds, JArray data)\r\n at Ext.Net.Store.MakeChanges()\r\n at Ext.Net.Store.DoSaving(String action, String jsonData, JToken parameters)\r\n --- End of inner exception stack trace ---\r\n at Ext.Net.Store.DoSaving(String action, String jsonData, JToken parameters)\r\n at Ext.Net.Store.RaiseAjaxPostBackEvent(String eventArgument)"}}
    I'll try more detailed debugging. Any further idea/comment is appreciated.
  7. #7
    I retest again and has no such error
    May be it is already fixed in the latest code (now all row values are deserialized before to pass to SqlDataSource, previously values are passed as strings)

    Please retest after next public release
  8. #8
    Thanks Vladimir. Guess I'll have to wait the next release.

Similar Threads

  1. Replies: 1
    Last Post: Apr 13, 2012, 1:52 PM
  2. Replies: 1
    Last Post: Nov 29, 2011, 5:11 PM
  3. Replies: 3
    Last Post: Nov 01, 2011, 6:15 PM
  4. Replies: 2
    Last Post: Apr 14, 2011, 9:03 AM
  5. Replies: 0
    Last Post: Jul 07, 2009, 4:34 AM

Tags for this Thread

Posting Permissions