System.DBNull value in sql parameter

[CygNuS]

Ok i'm having the weirdest problem with inserting records which can contain NULL values.

I will try to explain:

I'm using a dataadapter and dataset to insert my records into the SQL 2005 database.

I've also written my own Insert statement for the dataadapter to use (i really need to do this)

this insert statement contains a parameter @INTRASTATNUMMER

cmd_insertbonD.Parameters.Add("@INTRASTATNUMMER", SqlDbType.NVarChar, 9)
cmd_insertbonD.Parameters("@INTRASTATNUMMER").SourceColumn = "INTRASTATNUMMER"

This field can contain Null-values (System.DBNull)


I added watches on every record in my dataset:

Dataset_byref.Tables("BONDETAIL").Rows(0).Item("INTRASTATNUMMER") _________"0101.1010"
Dataset_byref.Tables("BONDETAIL").Rows(1).Item("INTRASTATNUMMER") _________{System.DBNull}
Dataset_byref.Tables("BONDETAIL").Rows(2).Item("INTRASTATNUMMER") _________{System.DBNull}

Now just after i run the Update command of my dataadaptor...

[SIZE=2]DataAdaptor_byref.Update(Dataset_byref, "BONDETAIL")[/SIZE]

...This is the watch on my parameter:

DataAdaptor_byref.InsertCommand.Parameters.Item("@INTRASTATNUMMER").Value ________"" {String}


This means that the @INTRASTATNUMMER parameter for the last record of my dataset (containing the System.DBNull value) is suddenly changed to an empty string "".

So the dataadapter tries to insert the records containing INTRASTATNUMMER "0101.1010", <NULL> and "".

I really need INTRASTATNUMMER in that last record to be NULL because "" violates a Foreign Key.

What am i doing wrong?

The VERY weird thing is that System.DBNULL is ALWAYS ONLY converted to "" if it is in THE LAST RECORD in my dataset. Why?

 

[CygNuS]

Ok, i will try out your sample project and am curious to see the better way, can you upload it please?

Thanks!

 

[cjard]

Attached is a zip, with tour project.

It has one form and on the CyGNus Code button i have tried to replicate the code youre using to insert to the db. The dataset was generated by the IDE after i connected it to the database tables your scripts made. I added the relation manually, and i turned off local enforcement of constraints. They are still active on the server

I added a datagridview for your code, and it is set up manually (by and large)

My datagrid was set up mainly from the ide.. i just drag dropped it on the form. The + and Disk (save) icons should work to add and save records to the db

The DB is accessed through the connection string in the application settings. change this setting and it should work with your db..

 

[CygNuS]

I checked out your project and it does work good no more unwanted unwanted behaviour and insertion problems so far...

Thanks a million for showing me this new way and taking the time to solve my issue!

 

[cjard]

Youre welcome. Sorry I didnt have a direct answer to your query, but I couldnt replicate it. I did wonder whether it was something to do with SourceColumnNullMapping but that didnt explain the inconsistency. Hopefully you can deconstruct the steps I did for adding my version of the datagrid and saving it, but if you cannot or you want some more info on the new ways we do data access in .NET 2 then feel free to ask..