NOT NULL + DefaultValue column in DataGridView
-
Hi First of all Merry Cristmas all of you. My question is: I have an table in my database where one of the columns have default value = GETDATE() and this column also marked as NOT NULL. I.e. when i insert an row in this table by mean of QueryAnalyizer and INSERT statement INSERT INTO MyTable (Col1, Col2) VALUES (1,2) and suppose what Col3 is the described column. All work fine. But when i isnsert this row by using DataGridView (this DataGridView have 2 displayed column where may be enetered significant (not default) values for table) and try to save this changes i have exception (Col3 cannot be NULL), Plaese help: HOW i can avoid this. Thank
-
Hi First of all Merry Cristmas all of you. My question is: I have an table in my database where one of the columns have default value = GETDATE() and this column also marked as NOT NULL. I.e. when i insert an row in this table by mean of QueryAnalyizer and INSERT statement INSERT INTO MyTable (Col1, Col2) VALUES (1,2) and suppose what Col3 is the described column. All work fine. But when i isnsert this row by using DataGridView (this DataGridView have 2 displayed column where may be enetered significant (not default) values for table) and try to save this changes i have exception (Col3 cannot be NULL), Plaese help: HOW i can avoid this. Thank
Actually this is a very good question. I have assumed that you used typed dataset in your code (that is bounded to the DataGridView). * The nature of the ADO.NET is a disconnected way of work. Therefore I thought first that we need to define the default property in the DS. When the value of a DateTime column in the DataGridView and the underline DS isn't supplied it is assigned with its default value. So I have tried to accomplish this solution by defining a default value for the DateTime column in the DS's schema. After reading W3C and MSDN definitions of the DateTime constants I have added the following to the schema:
default="2006-01-01T00:00:00"but surprise, surprise: I have got the following statement (which actually states that the DS cannot be generated): "Failed to generate code. Invalid Primitive Type: System.DateTime. Only CLS compliant primitive types can be used. Consider using CodeObjectCreateExpression." Somehow it seems to be a bug, or maybe the DateTime type should be declared different in the schema when using defaults ??? (Does anybody know something about it ???) After searching the web a while I have found thaturn:schemas-microsoft-com:xml-mspropcan be used. In nutshell it can be used to access the DS's columns that areDBNull. So this is how the schema looks like now:<xs:schema
...
xmlns:codegen="urn:schemas-microsoft-com:xml-msprop"
......
<xs:element name="d" type="xs:dateTime" codegen:nullValue="2006-01-01T00:00:00" minOccurs="0" />
...But this isn't the end! In order to actually pass the default value to the DB we should assign this value to the desired row by providing the
RowChangedevent, like follows:... tds.t.tRowChanged += new tRowChangeEventHandler(t_tRowChanged); ... private void t_tRowChanged (object sender, TDS.tRowChangeEvent e) { if(e.Action == DataRowAction.Add) { e.Row.d = e.Row.d; } }Brrrr... this is a very ugly solution. * Another way to solve this problem is to avoid to insert values that areNULLs (orDBNulls) i.e. when the values are nulls generate insert sql statement that just omits those values. When omiting the null values the default values of the DB will be applied.Uri
-
Actually this is a very good question. I have assumed that you used typed dataset in your code (that is bounded to the DataGridView). * The nature of the ADO.NET is a disconnected way of work. Therefore I thought first that we need to define the default property in the DS. When the value of a DateTime column in the DataGridView and the underline DS isn't supplied it is assigned with its default value. So I have tried to accomplish this solution by defining a default value for the DateTime column in the DS's schema. After reading W3C and MSDN definitions of the DateTime constants I have added the following to the schema:
default="2006-01-01T00:00:00"but surprise, surprise: I have got the following statement (which actually states that the DS cannot be generated): "Failed to generate code. Invalid Primitive Type: System.DateTime. Only CLS compliant primitive types can be used. Consider using CodeObjectCreateExpression." Somehow it seems to be a bug, or maybe the DateTime type should be declared different in the schema when using defaults ??? (Does anybody know something about it ???) After searching the web a while I have found thaturn:schemas-microsoft-com:xml-mspropcan be used. In nutshell it can be used to access the DS's columns that areDBNull. So this is how the schema looks like now:<xs:schema
...
xmlns:codegen="urn:schemas-microsoft-com:xml-msprop"
......
<xs:element name="d" type="xs:dateTime" codegen:nullValue="2006-01-01T00:00:00" minOccurs="0" />
...But this isn't the end! In order to actually pass the default value to the DB we should assign this value to the desired row by providing the
RowChangedevent, like follows:... tds.t.tRowChanged += new tRowChangeEventHandler(t_tRowChanged); ... private void t_tRowChanged (object sender, TDS.tRowChangeEvent e) { if(e.Action == DataRowAction.Add) { e.Row.d = e.Row.d; } }Brrrr... this is a very ugly solution. * Another way to solve this problem is to avoid to insert values that areNULLs (orDBNulls) i.e. when the values are nulls generate insert sql statement that just omits those values. When omiting the null values the default values of the DB will be applied.Uri
Thanks for contribution Very usefull Already one question: You wrote --- * Another way to solve this problem is to avoid to insert values that are NULLs (or DBNulls) i.e. when the values are nulls generate insert sql statement that just omits those values. When omiting the null values the default values of the DB will be applied. --- How i can accomplish this? I suppose this may be done just of substition of InsertCommand proerty of adapter class, but this don't work Already you wrote:
private void t_tRowChanged (object sender, TDS.tRowChangeEvent e) { if(e.Action == DataRowAction.Add) { e.Row.d = e.Row.d;//What????????:omg: } }--- And another thing, you suppose what dateTime column has default value, but in my case one actually not default(one defined when row insert in db by GETDATE() function) Regrats -
Thanks for contribution Very usefull Already one question: You wrote --- * Another way to solve this problem is to avoid to insert values that are NULLs (or DBNulls) i.e. when the values are nulls generate insert sql statement that just omits those values. When omiting the null values the default values of the DB will be applied. --- How i can accomplish this? I suppose this may be done just of substition of InsertCommand proerty of adapter class, but this don't work Already you wrote:
private void t_tRowChanged (object sender, TDS.tRowChangeEvent e) { if(e.Action == DataRowAction.Add) { e.Row.d = e.Row.d;//What????????:omg: } }--- And another thing, you suppose what dateTime column has default value, but in my case one actually not default(one defined when row insert in db by GETDATE() function) RegratsHi, 1. The cleanest way to do it in my opinion is to use SPs. The SP will test using sql whether the DateTime is
NULLand if so won't insert it.... SqlCommand insertCommand = new SqlCommand(...); insertCommand.CommandType = CommandType.StoredProcedure; insertCommand.CommandText = "YourSPNameHere"; ...2. It seems wrong but it's ok. if you read aboutnullValeinurn:schemas-microsoft-com:xml-msprop(with replacement value) you will find that it just provides a mean to accessDBNullcell without throwing an exception, but it doesn't assigns that default value to the DS's cell. Therefore this code just assigns that default value to the desired cell. 3. Yes, you are right. You can change the code I have provided to be:private void t_tRowChanged (object sender, TDS.tRowChangeEvent e) { if(e.Action == DataRowAction.Add) { e.Row.d = System.DateTime.Now; } }or to use SP as I have stated early.Uri
-
Hi, 1. The cleanest way to do it in my opinion is to use SPs. The SP will test using sql whether the DateTime is
NULLand if so won't insert it.... SqlCommand insertCommand = new SqlCommand(...); insertCommand.CommandType = CommandType.StoredProcedure; insertCommand.CommandText = "YourSPNameHere"; ...2. It seems wrong but it's ok. if you read aboutnullValeinurn:schemas-microsoft-com:xml-msprop(with replacement value) you will find that it just provides a mean to accessDBNullcell without throwing an exception, but it doesn't assigns that default value to the DS's cell. Therefore this code just assigns that default value to the desired cell. 3. Yes, you are right. You can change the code I have provided to be:private void t_tRowChanged (object sender, TDS.tRowChangeEvent e) { if(e.Action == DataRowAction.Add) { e.Row.d = System.DateTime.Now; } }or to use SP as I have stated early.Uri
Clear Regrats