Last post Sep 01, 2013 12:20 PM by Javid5555
Mar 31, 2009 12:01 PM|rogerwithnell|LINK
I have two columns not nullable (A and B) and one column nullable (C). All the columns are nvarchar.
I want to insert a record and leave C null.
How do I do this with:
MyTableAdapter.Insert ( "A", "B", X) where X is the syntax that sets column C to NULL.
Thanking you in anticipation.
Mar 31, 2009 12:27 PM|jacobodolil|LINK
You can do like this
If u want the 3 rd column to be null then declare it as null in the table and while inserting specify the column names
insert into tblname(a,b) values('a','b')
For any help reply me
Thanks & Regards
If you find my post have helped you out please mark it as answer
Apr 03, 2009 06:06 AM|rogerwithnell|LINK
Sure. I understand the SQL syntax to insert into selected columns.
But I have a query in a table adapter which has generated its own Insert (and Update and Delete) statements and has its own Insert (and Update and Delete) methods as well.
Accordingly I can Insert into the table using the table adapter like so:
MyTableAdapter.Insert(ColA, ColB, ColC)
But ColC, which is nullable, needs to be set to NULL. How do set it to NULL using this command?
Apr 03, 2009 02:52 PM|Naom|LINK
Can you post your Insert method definition?
In C# to make a datatype nullable you add ? at the end, e.g. int? or datetime?
I'm not sure how to create nullable datatype in VB.NET, need to double check.
If you want to pass NULL value to database you usually use DbNull.Value as the value. Or particular type null.
Apr 10, 2009 11:31 AM|TenCentProgrammer|LINK
I too have this same problem, trying to use the insert commands with the TableAdapters. I want to write a NULL value back to a date field in the SQL table, but i have found no way to do this using insert commands with table adapters. I did read an article
on another support forum, where someone posted findings that this is a known issue with the table adapters and limitations of .net 1 and 2 -- http://www.velocityreviews.com/forums/t366691-null-intfloat-columns-with-tableadapters.html
I have tried using nullable types in VB, and it simply will not store the null back to the table. For example, this code will work:
Dim OrderDate As Nullable(Of SqlTypes.SqlDateTime)
OrderDate = SqlTypes.SqlDateTime.Null
...then you pass OrderDate in your TableAdapter.Insert(OrderDate, ...) command as a variable. However, when this insert is performed, a minimum value is stored back, rather than a null. So in the table when this gets written, it shows up as 1/1/1900 rather
than null. I have only experimented with date fields so far.
For me, minimum values aren't satisfactory, since some fields the users leave dates blank until they have valid info to put in. If a user is looking at a date field and needs to know if any data has been entered in, seeing an empty date field means no one
has entered data for that field yet. Seeing 1/1/1900 means the user is going to be confused. I guess i could write some patch code to handle all this, but I would rather it work right to start with.
i hope this helps. I have wasted quite a bit of time trying to get this to work, and am reverting back to SQL commands, ditching the table adapters.
Sep 29, 2010 02:32 PM|dbatesx|LINK
I have a TableAdapter on a table with a nullable field (integer), so in VB I defined the variable storing the value as Global.System.Nullable(Of Integer), and then when I want a Null value sent to the database, I set the variable to Nothing, then assigning
the table column in the TableAdapter to that variable (ie in an Insert statement, or through a DataTable assignment) will set the value in the database table to Null.
Jun 10, 2011 10:16 PM|cschaus|LINK
I have solved this problem by creating a custom insert or update query (e.g., "UpdateNULLDate") in the Table Adapter where I have replaced the New Value parameter, e.g. @NewDate, with NULL. Then in code I find the cases where I need to insert or update
to a NULL value into the Date field and use the "UpdateNULLDate" query
Here is a very simple example:
Default Update Query generated by TableAdapter:
SET NewDate = @NewDate
WHERE (ID = @Original_ID)
Modified Update Query added to Table Adapter ("UpdateNULLDate"):
SET NewDate = NULL
WHERE (ID = @Original_ID)
In VB code I use this:
If you're using a GridView to make the updates, then place the above code in the GridView RowUpdating event handler followed by e.cancel=True to change the default GridView update behavior to what you need to insert or update the NULL date field values.
Sep 01, 2013 12:20 PM|Javid5555|LINK
use YourTableAdapter.Insert("A", "B", Nothing)