Exception Details: MySql.Data.MySqlClient.MySqlException: Incorrect date value: '20/12/2014 00:00:00' for column 'dt_cadastro' at row 1
Exception Details: MySql.Data.MySqlClient.MySqlException: Incorrect date value: '20/12/2014' for column 'dt_cadastro' at row 1
Hello guys, I have a table on my DataBase called "calls" and I have two Date fields type. Date not DateTime...
Every data I try to insert by Update pop up this error... AS Data it save just date (oh really? -_-" )
But on my gridview its showing "dd/mm/yyy 00:00:00" Wasn't suppose to show only date without the TIME ?
I tryed inser values like "17/12/2012" "17/12/2012 00:00:00" And everything fire that error ! I really don't know what to do...
I want to show only the Date without ( "00:00:00" ) on my GridView and be able to update it. On my table its saved just the date "20/12/2014" - "14/12/2012" - "17/12/2012" - 20/01/2012" - 20/02/2012" But here shows the time too =\
Here's my DataSource Code that's in Aspx page
<asp:SqlDataSource ID="MyDataSource" runat="server"
ConnectionString="server=localhost;User Id=root;password=XXXXX;database=XXXX"
ProviderName ="MySql.Data.MySqlClient"
SelectCommand="SELECT prioridade, status, responsavel, dt_cadastro, previsao_termino, titulo FROM chamados"
UpdateCommand="Update chamados SET prioridade = @prioridade, status = @status , responsavel = @responsavel,
dt_cadastro = @dt_cadastro WHERE responsavel = @responsavel"
DeleteCommand="Delete FROM chamados WHERE [responsavel] = @responsavel"> </asp:SqlDataSource>
From my experience with MySQL, dates are stored as YYYY/MM/DD. So what you need to do is convert your dates in your datagrid to match that format when you insert or update. To do this you will need to handle the grid Inserting and Updating event. Cancel
= true for your datasource, assign the values and then datasource.Update or datasource.insert. Here is an example of what I mean.
From my experience with MySQL, dates are stored as YYYY/MM/DD. So what you need to do is convert your dates in your datagrid to match that format when you insert or update. To do this you will need to handle the grid Inserting and Updating event. Cancel
= true for your datasource, assign the values and then datasource.Update or datasource.insert. Here is an example of what I mean.
Thanks for helping me !! I hjave to write this on my <SqlDataSource /> right ? I don't get this InserParameter.... Why use this and what for is this ? I use something simillar to this in WebForms, but this time I didnt get it =s cuz my Update I just write
Id = @ id =s
Hello, you need to handle the grid Inserting and updating event:
Private Sub ASPxGridView1_RowInserting(sender As Object, e As DevExpress.Web.Data.ASPxDataInsertingEventArgs) Handles ASPxGridView1.RowInserting
e.Cancel = True
SqlDataSource1.InsertParameters("SiteUserID").DefaultValue = lblUserID.Text.Trim
SqlDataSource1.InsertParameters("OrgID").DefaultValue = e.NewValues("OrganizationID").ToString
SqlDataSource1.InsertParameters("SurveyReponsePermID").DefaultValue = e.NewValues("SurveyResponsePermissionID").ToString
SqlDataSource1.InsertParameters("CalendarPermID").DefaultValue = e.NewValues("CalendarPermissionID").ToString
SqlDataSource1.Insert()
ASPxGridView1.CancelEdit()
End Sub
Click your grid and hit F4 to access properties window. Click on the lightning bolt on your properties window to expose events. Double click on the RowInserting, RowUpdating events to create those events. Then you use the code above to access and insert
/ update using your SQLDataSource.
Hello, you need to handle the grid Inserting and updating event:
Private Sub ASPxGridView1_RowInserting(sender As Object, e As DevExpress.Web.Data.ASPxDataInsertingEventArgs) Handles ASPxGridView1.RowInserting
e.Cancel = True
SqlDataSource1.InsertParameters("SiteUserID").DefaultValue = lblUserID.Text.Trim
SqlDataSource1.InsertParameters("OrgID").DefaultValue = e.NewValues("OrganizationID").ToString
SqlDataSource1.InsertParameters("SurveyReponsePermID").DefaultValue = e.NewValues("SurveyResponsePermissionID").ToString
SqlDataSource1.InsertParameters("CalendarPermID").DefaultValue = e.NewValues("CalendarPermissionID").ToString
SqlDataSource1.Insert()
ASPxGridView1.CancelEdit()
End Sub
Click your grid and hit F4 to access properties window. Click on the lightning bolt on your properties window to expose events. Double click on the RowInserting, RowUpdating events to create those events. Then you use the code above to access and insert
/ update using your SQLDataSource.
There's no RowInserting what now ?
I only have RowUpdate - RowUpdating - RowEdit - RowEditing - RowCreate - RowDataBound - RowDelete/ing - RowCommand
GoodKnews -> I found out the problem. My Table isn't accepting date format "dd/mm/yyyy" it has to be "yyyy/mm/dd" OK !
But my update is not working anyway... I need this to finish my project here at my Work, COuld you keep helping ? =\
Ghaleon
Member
22 Points
89 Posts
Exception Details: MySql.Data.MySqlClient.MySqlException: Incorrect date value:
Dec 17, 2012 03:34 PM|LINK
Exception Details: MySql.Data.MySqlClient.MySqlException: Incorrect date value: '20/12/2014 00:00:00' for column 'dt_cadastro' at row 1
Exception Details: MySql.Data.MySqlClient.MySqlException: Incorrect date value: '20/12/2014' for column 'dt_cadastro' at row 1
Hello guys, I have a table on my DataBase called "calls" and I have two Date fields type. Date not DateTime...
Every data I try to insert by Update pop up this error... AS Data it save just date (oh really? -_-" )
But on my gridview its showing "dd/mm/yyy 00:00:00" Wasn't suppose to show only date without the TIME ?
I tryed inser values like "17/12/2012" "17/12/2012 00:00:00" And everything fire that error ! I really don't know what to do...
I want to show only the Date without ( "00:00:00" ) on my GridView and be able to update it. On my table its saved just the date "20/12/2014" - "14/12/2012" - "17/12/2012" - 20/01/2012" - 20/02/2012" But here shows the time too =\
Here's my DataSource Code that's in Aspx page
<asp:SqlDataSource ID="MyDataSource" runat="server" ConnectionString="server=localhost;User Id=root;password=XXXXX;database=XXXX" ProviderName ="MySql.Data.MySqlClient" SelectCommand="SELECT prioridade, status, responsavel, dt_cadastro, previsao_termino, titulo FROM chamados" UpdateCommand="Update chamados SET prioridade = @prioridade, status = @status , responsavel = @responsavel, dt_cadastro = @dt_cadastro WHERE responsavel = @responsavel" DeleteCommand="Delete FROM chamados WHERE [responsavel] = @responsavel"> </asp:SqlDataSource>It's my GridView Thanks everybody !
aptbid2002
Member
244 Points
52 Posts
Re: Exception Details: MySql.Data.MySqlClient.MySqlException: Incorrect date value:
Dec 17, 2012 05:23 PM|LINK
From my experience with MySQL, dates are stored as YYYY/MM/DD. So what you need to do is convert your dates in your datagrid to match that format when you insert or update. To do this you will need to handle the grid Inserting and Updating event. Cancel = true for your datasource, assign the values and then datasource.Update or datasource.insert. Here is an example of what I mean.
e.Cancel = True
SqlDataSource1.InsertParameters("SiteUserID").DefaultValue = lblUserID.Text.Trim
SqlDataSource1.InsertParameters("OrgID").DefaultValue = e.NewValues("OrganizationID").ToString
SqlDataSource1.InsertParameters("SurveyReponsePermID").DefaultValue = e.NewValues("SurveyResponsePermissionID").ToString
SqlDataSource1.InsertParameters("CalendarPermID").DefaultValue = e.NewValues("CalendarPermissionID").ToString
SqlDataSource1.Insert()
Grid.CancelEdit()
Ghaleon
Member
22 Points
89 Posts
Re: Exception Details: MySql.Data.MySqlClient.MySqlException: Incorrect date value:
Dec 17, 2012 05:45 PM|LINK
Thanks for helping me !! I hjave to write this on my <SqlDataSource /> right ? I don't get this InserParameter.... Why use this and what for is this ? I use something simillar to this in WebForms, but this time I didnt get it =s cuz my Update I just write Id = @ id =s
Could You Explain please? Thank you !
aptbid2002
Member
244 Points
52 Posts
Re: Exception Details: MySql.Data.MySqlClient.MySqlException: Incorrect date value:
Dec 17, 2012 06:04 PM|LINK
Hello, you need to handle the grid Inserting and updating event:
Private Sub ASPxGridView1_RowInserting(sender As Object, e As DevExpress.Web.Data.ASPxDataInsertingEventArgs) Handles ASPxGridView1.RowInserting
e.Cancel = True
SqlDataSource1.InsertParameters("SiteUserID").DefaultValue = lblUserID.Text.Trim
SqlDataSource1.InsertParameters("OrgID").DefaultValue = e.NewValues("OrganizationID").ToString
SqlDataSource1.InsertParameters("SurveyReponsePermID").DefaultValue = e.NewValues("SurveyResponsePermissionID").ToString
SqlDataSource1.InsertParameters("CalendarPermID").DefaultValue = e.NewValues("CalendarPermissionID").ToString
SqlDataSource1.Insert()
ASPxGridView1.CancelEdit()
End Sub
Click your grid and hit F4 to access properties window. Click on the lightning bolt on your properties window to expose events. Double click on the RowInserting, RowUpdating events to create those events. Then you use the code above to access and insert / update using your SQLDataSource.
Ghaleon
Member
22 Points
89 Posts
Re: Exception Details: MySql.Data.MySqlClient.MySqlException: Incorrect date value:
Dec 17, 2012 06:28 PM|LINK
There's no RowInserting what now ?
I only have RowUpdate - RowUpdating - RowEdit - RowEditing - RowCreate - RowDataBound - RowDelete/ing - RowCommand
GoodKnews -> I found out the problem. My Table isn't accepting date format "dd/mm/yyyy" it has to be "yyyy/mm/dd" OK !
But my update is not working anyway... I need this to finish my project here at my Work, COuld you keep helping ? =\
Thank you !
aptbid2002
Member
244 Points
52 Posts
Re: Exception Details: MySql.Data.MySqlClient.MySqlException: Incorrect date value:
Dec 17, 2012 06:57 PM|LINK
Update code is going to look like the insert code, except instead of
SqlDataSource1.InsertParameters("SiteUserID").DefaultValue = lblUserID.Text.Trim
Use
SqlDataSource1.UpdateParameters("SiteUserID").DefaultValue = lblUserID.Text.Trim
SQLDataSource1.Update()
You do not have an insert command in your datasource so don't worry about that.
Ghaleon
Member
22 Points
89 Posts
Re: Exception Details: MySql.Data.MySqlClient.MySqlException: Incorrect date value:
Dec 17, 2012 07:02 PM|LINK
Thank you ! But what about that I dont have a RowInsert Event ? ;s
aptbid2002
Member
244 Points
52 Posts
Re: Exception Details: MySql.Data.MySqlClient.MySqlException: Incorrect date value:
Dec 17, 2012 07:14 PM|LINK
You said you don't have an RowInserting Event. I say don't worry about it because you did not specify an Insert command in your datasource.
oned_gk
All-Star
31784 Points
6497 Posts
Re: Exception Details: MySql.Data.MySqlClient.MySqlException: Incorrect date value:
Dec 18, 2012 01:44 AM|LINK
to display your date as you want, use boundfield and set dataformatstring like below
<asp:BoundField DataField="DATA_CADASTRO" DataFormatString="{0:dd/MM/yyyy}" HeaderText="DATA_CADASTRO" SortExpression="DATA_CADASTRO" />or
<asp:BoundField DataField="DATA_CADASTRO" DataFormatString="{0:d}" HeaderText="DATA_CADASTRO" SortExpression="DATA_CADASTRO" />Ghaleon
Member
22 Points
89 Posts
Re: Exception Details: MySql.Data.MySqlClient.MySqlException: Incorrect date value:
Dec 18, 2012 10:28 AM|LINK
Hi, thanks for answerign me !
Worked like a charm ! But what May I do about the Date Insert ? ;\ It keeps givin' me an error on Insert and Update =s
How May I "convert" Because clients will type the date like dd/mm/yyyy but where/how I convert it to yyyy/mm/dd for the mysql accept ? ;s