Dataset containing nullable datetime values does not like a null datetime?
-
I am using a windows forms app vb.net 3.5 SP1 w/VS 2008. I have a DS set up. I have about 4 DateTime Columns in the DS in a particular table that are defined in the SQL2005 server as DateTime NULLable. I've extended the DateTimePicker class to be able to accept a Date? value. The problem is that the DS does not believe that the date columns that are nullable, really are. It always wants to throw an exception. Per someone elses suggestion i set the date fields in the DS designer to DataType "System.Data.SqlTypes.SqlDateTime" with DefaultValue "Null", and AllowDBNull "True", yet the NullValue Property *only* allows "(Throw Exception)" and I think to fix my issue I need it to be "Null" however if I do that, the compiler says "Null is not a valid AllXsd value", which is where I'm stuck and I'm afraid is a little beyond my understanding... are there any suggestions you could make to me? I'd really appreciate it!! Thanks in advance.
-
I am using a windows forms app vb.net 3.5 SP1 w/VS 2008. I have a DS set up. I have about 4 DateTime Columns in the DS in a particular table that are defined in the SQL2005 server as DateTime NULLable. I've extended the DateTimePicker class to be able to accept a Date? value. The problem is that the DS does not believe that the date columns that are nullable, really are. It always wants to throw an exception. Per someone elses suggestion i set the date fields in the DS designer to DataType "System.Data.SqlTypes.SqlDateTime" with DefaultValue "Null", and AllowDBNull "True", yet the NullValue Property *only* allows "(Throw Exception)" and I think to fix my issue I need it to be "Null" however if I do that, the compiler says "Null is not a valid AllXsd value", which is where I'm stuck and I'm afraid is a little beyond my understanding... are there any suggestions you could make to me? I'd really appreciate it!! Thanks in advance.
try setting it to DBNull.Value That is the equiv. of a null in the db, "Null" is not.
Troy Johnson www.anothercodesite.com/blog
-
try setting it to DBNull.Value That is the equiv. of a null in the db, "Null" is not.
Troy Johnson www.anothercodesite.com/blog
Thanks for the reply Troy...Just tried your suggestion and the same thing happens. The actal error it gives is: Error 44 Custom tool error: Failed to generate code. Failed to generate code. Exception has been thrown by the target of an invocation. The string 'DBNull.Value' is not a valid AllXsd value. Exception has been thrown by the target of an invocation. The string 'DBNull.Value' is not a valid AllXsd value. C:\Users\kkomar\Documents\Visual Studio 2008\Projects\Idaho AGC DataCenter\Idaho AGC DataCenter\Forms\OrganizationsFrmDataSet.xsd 1 1 Idaho AGC DataCenter ...As soon as i plug that value in.
-
I am using a windows forms app vb.net 3.5 SP1 w/VS 2008. I have a DS set up. I have about 4 DateTime Columns in the DS in a particular table that are defined in the SQL2005 server as DateTime NULLable. I've extended the DateTimePicker class to be able to accept a Date? value. The problem is that the DS does not believe that the date columns that are nullable, really are. It always wants to throw an exception. Per someone elses suggestion i set the date fields in the DS designer to DataType "System.Data.SqlTypes.SqlDateTime" with DefaultValue "Null", and AllowDBNull "True", yet the NullValue Property *only* allows "(Throw Exception)" and I think to fix my issue I need it to be "Null" however if I do that, the compiler says "Null is not a valid AllXsd value", which is where I'm stuck and I'm afraid is a little beyond my understanding... are there any suggestions you could make to me? I'd really appreciate it!! Thanks in advance.
personaly me I am not a fan or leaving vales null, most controls like the dataset will through a null exception, that said what I prefer to do is when filling the dataset with defaults on null
din ds as datarow = mydataset.mydatatable.newrow ds("MyDateColumn") = IIf(IsDbNull(MyDbColumn),"01/01/1900",MyDbColumn) mydataset.mydatatable.rows.add(ds) -
personaly me I am not a fan or leaving vales null, most controls like the dataset will through a null exception, that said what I prefer to do is when filling the dataset with defaults on null
din ds as datarow = mydataset.mydatatable.newrow ds("MyDateColumn") = IIf(IsDbNull(MyDbColumn),"01/01/1900",MyDbColumn) mydataset.mydatatable.rows.add(ds)I appreciate the suggestion, however, I don't bind each column individually like that. I just fill the DS and I use the designer to bind all the controls to the binding source columns. Does this mean I'll have to manually code all the 80 column bindings on my form? :(
-
I appreciate the suggestion, however, I don't bind each column individually like that. I just fill the DS and I use the designer to bind all the controls to the binding source columns. Does this mean I'll have to manually code all the 80 column bindings on my form? :(
you can still use the designer to bind your controls to the dataset, but I personaly would use an nTier approach and create a class to fill the dataset. From the top of my head it will go something like this, but you may want to google sqlconnection and sqlcommand on the proper implementation
public class mydataleyer
public function FillDataset() as mydataset
dim results as new mydataset
dim con as new sqlconnection("server","database","username","password")
dim qry as new sqlcommand("select * from mytable")
con.open
dim rdr as datareader = qry.executereader(cn)
while rdr.read()
dim rw as datarow = results.tables(0).newrow
rw("datecolumn") = IIF(IsDBNull(rdr(0)),"01/01/1900",rdr(0))
results.tables(0).rows.add(rw)
end while
return results
end function
end classfrom you form onload event
private sub myform_load(byval sender as object,byval e as eventargs) handles mybase.load
dim ldr as new mydatalayer
me.dataset1 = ldr.FillDataset
end sub -
you can still use the designer to bind your controls to the dataset, but I personaly would use an nTier approach and create a class to fill the dataset. From the top of my head it will go something like this, but you may want to google sqlconnection and sqlcommand on the proper implementation
public class mydataleyer
public function FillDataset() as mydataset
dim results as new mydataset
dim con as new sqlconnection("server","database","username","password")
dim qry as new sqlcommand("select * from mytable")
con.open
dim rdr as datareader = qry.executereader(cn)
while rdr.read()
dim rw as datarow = results.tables(0).newrow
rw("datecolumn") = IIF(IsDBNull(rdr(0)),"01/01/1900",rdr(0))
results.tables(0).rows.add(rw)
end while
return results
end function
end classfrom you form onload event
private sub myform_load(byval sender as object,byval e as eventargs) handles mybase.load
dim ldr as new mydatalayer
me.dataset1 = ldr.FillDataset
end subAha, so will doing: rw("datecolumn") = IIF(IsDBNull(rdr(0)),"01/01/1900",rdr(0)) make it so that anytime that column is accessed, it will filter out the DBNull, or just for the initial loading? The reason I ask is because the date columns i have are to be re-nullable, even if they're given a value; the value has to be able to go back to null in the actual database at some point...
-
Aha, so will doing: rw("datecolumn") = IIF(IsDBNull(rdr(0)),"01/01/1900",rdr(0)) make it so that anytime that column is accessed, it will filter out the DBNull, or just for the initial loading? The reason I ask is because the date columns i have are to be re-nullable, even if they're given a value; the value has to be able to go back to null in the actual database at some point...
when you write values to the database you can simply reverse the logic IIF(ds("Datacolumn") = "01/01/1900",nothing,ds("datacolumn")
-
I am using a windows forms app vb.net 3.5 SP1 w/VS 2008. I have a DS set up. I have about 4 DateTime Columns in the DS in a particular table that are defined in the SQL2005 server as DateTime NULLable. I've extended the DateTimePicker class to be able to accept a Date? value. The problem is that the DS does not believe that the date columns that are nullable, really are. It always wants to throw an exception. Per someone elses suggestion i set the date fields in the DS designer to DataType "System.Data.SqlTypes.SqlDateTime" with DefaultValue "Null", and AllowDBNull "True", yet the NullValue Property *only* allows "(Throw Exception)" and I think to fix my issue I need it to be "Null" however if I do that, the compiler says "Null is not a valid AllXsd value", which is where I'm stuck and I'm afraid is a little beyond my understanding... are there any suggestions you could make to me? I'd really appreciate it!! Thanks in advance.
OK, for those poor unfortunate souls who are having this same problem as me, here are the steps I've done so far to remedy: 1) Use the nulldatetimepicker extension shown here: http://www.codeproject.com/KB/selection/NullableDateTimePicker.aspx[^] and if you're using VB, convert it with this: http://www.developerfusion.com/tools/convert/csharp-to-vb/[^] 2) open the .xsd file of your dataset and manually change the msprop:nullValue="_throw" property of any datetime field to msprop:nullValue="_null". Be aware: every so often the designer will decide to just omit the datetime fields because it's caught on to your treacherous workaround of Microsoft's poor design; and that just cannot be allowed. :P If that happens just go to the designer again and set the fields as (throw exception), save, close, and follow this step again. This seems to work just fine for existing records. You can re-null datetime fields and successfully update. The only thing I'm having trouble with is inserting new records. I always get: {"Failed to convert parameter value from a Int32 to a DateTime."} {"Invalid cast from 'Int32' to 'DateTime'."} ...which is baffling me, but I'm still trying to research, debug, reinvent the wheel for. I'll keep posting as I figure it out. Here is my entire "add record" sub:
Private Sub AddBranchToolStripButton\_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddBranchToolStripButton.Click If PermissionsMode = "ReadWrite" Then Dim MainID As String = "0" Dim OrgName As String = "Error" 'Set the OrgRowIsAdding flag to true so that the AutoSave feature doesnt try to save a row with blank data OrgRowIsAdding = True 'record the main loc ID and org name MainID = ParentOrgIDTextBox.Text OrgName = OrganizationTextBox.Text 'Actually add the branch Try If Me.OrganizationsBindingSource.AddNew() IsNot Nothing Then OrganizationTextBox.Text = OrgName ParentOrgIDTextBox.Text = MainID MemberStatusComboBox. -
I am using a windows forms app vb.net 3.5 SP1 w/VS 2008. I have a DS set up. I have about 4 DateTime Columns in the DS in a particular table that are defined in the SQL2005 server as DateTime NULLable. I've extended the DateTimePicker class to be able to accept a Date? value. The problem is that the DS does not believe that the date columns that are nullable, really are. It always wants to throw an exception. Per someone elses suggestion i set the date fields in the DS designer to DataType "System.Data.SqlTypes.SqlDateTime" with DefaultValue "Null", and AllowDBNull "True", yet the NullValue Property *only* allows "(Throw Exception)" and I think to fix my issue I need it to be "Null" however if I do that, the compiler says "Null is not a valid AllXsd value", which is where I'm stuck and I'm afraid is a little beyond my understanding... are there any suggestions you could make to me? I'd really appreciate it!! Thanks in advance.
Update: Still nothing. I've even set my code to set any Null date to 1/1/1900 even though I really didnt want to do that. When I add a new record, no matter what, when i try to save back to the DB, I always get: "Invalid cast from 'Int32' to 'DateTime'." It shows the exception originates in the automatically generated "UpdateAll" Sub created by the dataset designer. Here is the code just before where the exception occurs:
Try '---- Prepare for update ----------- ' If (Not (Me.\_employeesTableAdapter) Is Nothing) Then revertConnections.Add(Me.\_employeesTableAdapter, Me.\_employeesTableAdapter.Connection) Me.\_employeesTableAdapter.Connection = CType(workConnection,Global.System.Data.SqlClient.SqlConnection) Me.\_employeesTableAdapter.Transaction = CType(workTransaction,Global.System.Data.SqlClient.SqlTransaction) If Me.\_employeesTableAdapter.Adapter.AcceptChangesDuringUpdate Then Me.\_employeesTableAdapter.Adapter.AcceptChangesDuringUpdate = false adaptersWithAcceptChangesDuringUpdate.Add(Me.\_employeesTableAdapter.Adapter) End If End If If (Not (Me.\_organizationMemosTableAdapter) Is Nothing) Then revertConnections.Add(Me.\_organizationMemosTableAdapter, Me.\_organizationMemosTableAdapter.Connection) Me.\_organizationMemosTableAdapter.Connection = CType(workConnection,Global.System.Data.SqlClient.SqlConnection) Me.\_organizationMemosTableAdapter.Transaction = CType(workTransaction,Global.System.Data.SqlClient.SqlTransaction) If Me.\_organizationMemosTableAdapter.Adapter.AcceptChangesDuringUpdate Then Me.\_organizationMemosTableAdapter.Adapter.AcceptChangesDuringUpdate = false adaptersWithAcceptChangesDuringUpdate.Add(Me.\_organizationMemosTableAdapter.Adapter) End If End If If (Not (Me.\_peopleTableAdapter) Is Nothing) Then revertConnections.Add(Me.\_peopleTableAdapter, Me.\_peopleTableAdapter.Connection) Me.\_peopleTableAdapter.Connection = CType(workConnection,Global.System.Data.SqlClient.SqlConnection) Me.\_peopleTableAdapter.Transaction = CType(workTransaction,Global.System.Data.SqlClient.SqlTransaction) -
Update: Still nothing. I've even set my code to set any Null date to 1/1/1900 even though I really didnt want to do that. When I add a new record, no matter what, when i try to save back to the DB, I always get: "Invalid cast from 'Int32' to 'DateTime'." It shows the exception originates in the automatically generated "UpdateAll" Sub created by the dataset designer. Here is the code just before where the exception occurs:
Try '---- Prepare for update ----------- ' If (Not (Me.\_employeesTableAdapter) Is Nothing) Then revertConnections.Add(Me.\_employeesTableAdapter, Me.\_employeesTableAdapter.Connection) Me.\_employeesTableAdapter.Connection = CType(workConnection,Global.System.Data.SqlClient.SqlConnection) Me.\_employeesTableAdapter.Transaction = CType(workTransaction,Global.System.Data.SqlClient.SqlTransaction) If Me.\_employeesTableAdapter.Adapter.AcceptChangesDuringUpdate Then Me.\_employeesTableAdapter.Adapter.AcceptChangesDuringUpdate = false adaptersWithAcceptChangesDuringUpdate.Add(Me.\_employeesTableAdapter.Adapter) End If End If If (Not (Me.\_organizationMemosTableAdapter) Is Nothing) Then revertConnections.Add(Me.\_organizationMemosTableAdapter, Me.\_organizationMemosTableAdapter.Connection) Me.\_organizationMemosTableAdapter.Connection = CType(workConnection,Global.System.Data.SqlClient.SqlConnection) Me.\_organizationMemosTableAdapter.Transaction = CType(workTransaction,Global.System.Data.SqlClient.SqlTransaction) If Me.\_organizationMemosTableAdapter.Adapter.AcceptChangesDuringUpdate Then Me.\_organizationMemosTableAdapter.Adapter.AcceptChangesDuringUpdate = false adaptersWithAcceptChangesDuringUpdate.Add(Me.\_organizationMemosTableAdapter.Adapter) End If End If If (Not (Me.\_peopleTableAdapter) Is Nothing) Then revertConnections.Add(Me.\_peopleTableAdapter, Me.\_peopleTableAdapter.Connection) Me.\_peopleTableAdapter.Connection = CType(workConnection,Global.System.Data.SqlClient.SqlConnection) Me.\_peopleTableAdapter.Transaction = CType(workTransaction,Global.System.Data.SqlClient.SqlTransaction)Final Update: Microsoft, you've won. I've been beaten into submission. Despite countless forum posts, weeks of web research, and direct queries to MS employees with no solid answer, I've succumbed to the workaround. Here's what to do: 1) Follow my original instructions to use the nullabledatetimepicker. modify it to accept 1/1/1900 as it's "null" value, so it shows the null string but reports it's value as 1/1/1900. 2) change your DB schema so that all dates are NOT nullable. Set the default value to 1/1/1900 12:00:00 AM, or getdate(), depending on what you're doing with it. You'll also need to do an update query for all NULL dates to set to '1/1/1900 12:00:00 AM'. 3) match the DS to the DB schema. 4) adjust any code in your program to understand the 1/1/1900 value as actually "null". 5) Be over and done with it and get on with your life! I hate workarounds. Maybe Visual Studio 2010 won't be such a compromised program...we shall see.