Cathi,

Hmmmm ...ok, then maybe it's a C# bug? Have you tried it in C#? Maybe I'll have to try it in VB and see what happens. (I haven't done anything in VB yet, but maybe if I get some time to mess with it, I'll give it a try).

~~Bonnie



>Bonnie,
>
>I just tried my sample code using a SQL Server stored procedure instead of the INSERT statement and the null value worked just fine. Here is the stored procedure I used. It is a very basic one but still performs the insert:
>
>

>CREATE PROCEDURE dbo.NewInsertCommand
>(
>	@LastName nvarchar(20),
>	@FirstName nvarchar(10),
>	@Title nvarchar(30)
>)
>AS
>INSERT INTO Employees(LastName, FirstName, Title) VALUES (@LastName, @FirstName, @Title);
>GO
>

>
>The only change I made to my .NET code was change the CommandText and set the CommandType to the following:
>
>

>Dim strInsert As [String] = "[NewInsertCommand]"
>Dim oInsertCommand As New SqlCommand(strInsert, oConn)
>oInsertCommand.CommandType = System.Data.CommandType.StoredProcedure
>

>
>The Title field is null in the Employees table in SQL Server.
>
>>Cathi,
>>
>>Maybe it's just something weird with Stored Procedures (we do all our data access only through SPs) ... =(
>>
>>~~Bonnie
>>
>>
>>>Hi Bonnie,
>>>
>>>The Convert.DBNull works for me. Here is the sample code (I happen to have this sample in VB .NET but you can get the general idea):
>>>
>>>

>>>Dim oConnStr As String = "data source=localhost;initial catalog=Northwind;integrated security=SSPI;persist security info=False;packet size=4096"
>>>
>>>' Make connection to database
>>>Dim oConn As New SqlConnection()
>>>oConn.ConnectionString = oConnStr
>>>oConn.Open()
>>>
>>>' Create a Command object with select statement
>>>Dim strSelect As [String] = "SELECT FirstName,LastName,Title FROM EMPLOYEES"
>>>Dim oSelectCommand As New SqlCommand(strSelect, oConn)
>>>
>>>' Create a Command object with insert statement
>>>Dim strInsert As [String] = "INSERT INTO EMPLOYEES(FirstName,LastName,Title) values (@FirstName,@LastName,@Title)"
>>>
>>>Dim oInsertCommand As New SqlCommand(strInsert, oConn)
>>>oInsertCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@FirstName", System.Data.SqlDbType.NVarChar, 10, "FirstName"))
>>>oInsertCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@LastName", System.Data.SqlDbType.NVarChar, 20, "LastName"))
>>>oInsertCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Title", System.Data.SqlDbType.NVarChar, 30, "Title"))
>>>
>>>' Create a DataAdapter
>>>Dim da As New SqlDataAdapter()
>>>da.SelectCommand = oSelectCommand
>>>da.InsertCommand = oInsertCommand
>>>
>>>' Create a DataSet
>>>Dim ds As New DataSet()
>>>
>>>' Fill the DataSet with table information
>>>da.Fill(ds, "Employees")
>>>
>>>Dim dr As DataRow = ds.Tables(0).NewRow()
>>>dr("FirstName") = "NetFN"
>>>dr("LastName") = "NetLN"
>>>dr("Title") = Convert.DBNull
>>>ds.Tables(0).Rows.Add(dr)
>>>
>>>da.Update(ds.Tables(0))
>>>
>>>oConn.Close()
>>>

>>>
>>>>Cathi,
>>>>
>>>>If you recall a previous thread where I asked a question about inserting a NULL, I could never get the darn thing to work. I don't know if it's a bug or what, but I gave up on it.
>>>>
>>>>~~Bonnie
>>>>
>>>>
>>>>>The SQL Server syntax for the insert statement is:
>>>>>
>>>>>

>>>>>insert into TABLENAME (FIELD1, FIELD2) values ('TEXT', NULL)
>>>>>

>>>>>
>>>>>If you are setting the value in a DataRow inside of a DataTable, then you set the value of the DataRow like the following:
>>>>>
>>>>>

>>>>>rowCustomer("ContactName") = Convert.DBNull
>>>>>

>>>>>
>>>>>
>>>>>>how to insert a null value to database (DB2,SQL Server) from vb.net ?