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?

 

[cjard]

Is INTRASTATNUMMBER an Input, Output or IO parameter?

What relations do you have set up in your dataset

When you say "violates an FK value" do you mean on the DB or the dataset?

 

[CygNuS]

I have no relations set up in my dataset.

They are set up in my DB like this: table BONDETAIL contains field INTRASTATNUMMER (can be NULL), which is a FK that references table INTRASTATGOEDERENCODES (field CODE).

The field CODE in INTRASTATGOEDERENCODES has values like "xxxx.xxxx", so "" is not possible there.

Now when a couple of new rows are added to my dataset, i try to save it to the database. (to table BONDETAIL)

The dataAdapter fills the parameters with the values coming from my dataset (which represents table "BONDETAIL")

Apparently, if the last record in the dataset has INTRASTATNUMMER with value System.DBNull.Value, the parameter @INTRASTATNUMMER gets the value "" instead of System.DBNULL.value which actually is the value in my Sourcecolumn INTRASTATNUMMER

The dataAdapter then tries to execute the insert statement and i get a DB error telling me the FK between BONDETAIL and INTRASTATGOEDERENCODES ("" does not exist in INTRASTATGOEDERENCODES)

So i really need him to insert NULL.

I noticed this happens only on the LAST ROW in the dataset AND when there are more than ONE rows.

btw @INTRASTATNUMMER is an input parameter

 

[cjard]

Is there any way you can post an example project?

What db are you using? SQL Server?

Are stored procedures in use?

Does the log you have kept from the stored procedure indicate that the sproc is receiving a string directly?

If no relations exist in your dataset, can you be sure that the only records you are inserting are relevant to the current parent table?

Can you post the INSERT statement in use?

If you add a constraint to your dataset and enforce it, do you get an exception related to the modification of a value while you are performing the insert?

Can you configure your IDE to break on errors thrown rather than errors unhadled?

 

[CygNuS]

I'm using SQL server. I can't really give an example project but i can give the most relevant code:

This is the code filling the dataset when the form is loaded:

[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2] dabond [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlDataAdapter[/SIZE]
[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2] dsbond [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataSet[/SIZE]
 
...
cmdCommando_bondetail.CommandType = CommandType.Text
cmdCommando_bondetail.Connection = cnSql
dabond = New SqlDataAdapter(cmdCommando_bondetail)
dabond.TableMappings.Add("Table", "BONDETAIL")
dsbond.Clear()
dsbond.Relations.Clear()
dabond.Fill(dsbond)
cnSql.Close()
...

This is what my insert statement looks like:

Sqlstring = "INSERT INTO BONDETAIL (BONNR , LIJNNR , TYPE , ARTNR , OMS , AANTAL , EENH , PRIJS , TOTAAL , BTW_CODE , ALG_CODE , ANAL , REKOMS , KORTING, AANTALPEREENHEID, INTRASTATNUMMER, GEWICHT ) " & _
"VALUES (@BONNR, @LIJNNR, @TYPE, @ARTNR, @OMS, @AANTAL, @EENH , @PRIJS, @TOTAAL , @BTW_CODE , @ALG_CODE , @ANAL , @REKOMS , @KORTING, @AANTALPEREENHEID, @INTRASTATNUMMER, @GEWICHT)"
 
Dim cmd_insertbonD As SqlCommand
cmd_insertbonD = New SqlCommand(Sqlstring, dabond.SelectCommand.Connection)
cmd_insertbonD.Parameters.Add("@BONNR", SqlDbType.BigInt, 8, "BONNR").Value = Txt_bonnr.Text
cmd_insertbonD.Parameters.Add("@LIJNNR", SqlDbType.BigInt, 8, "LIJNNR")
cmd_insertbonD.Parameters.Add("@TYPE", SqlDbType.NVarChar, 1, "TYPE")
cmd_insertbonD.Parameters.Add("@ARTNR", SqlDbType.NVarChar, 15, "ARTNR")
cmd_insertbonD.Parameters.Add("@OMS", SqlDbType.NVarChar, 80, "OMS")
cmd_insertbonD.Parameters.Add("@AANTAL", SqlDbType.Decimal, 15, "AANTAL")
cmd_insertbonD.Parameters.Add("@EENH", SqlDbType.NVarChar, 3, "EENH")
cmd_insertbonD.Parameters.Add("@PRIJS", SqlDbType.Decimal, 15, "PRIJS")
cmd_insertbonD.Parameters.Add("@TOTAAL", SqlDbType.Decimal, 15, "TOTAAL")
cmd_insertbonD.Parameters.Add("@BTW_CODE", SqlDbType.NVarChar, 3, "BTW_CODE")
cmd_insertbonD.Parameters.Add("@ALG_CODE", SqlDbType.BigInt, 8, "ALG_CODE")
cmd_insertbonD.Parameters.Add("@ANAL", SqlDbType.BigInt, 8, "ANAL")
cmd_insertbonD.Parameters.Add("@REKOMS", SqlDbType.NVarChar, 50, "REKOMS")
cmd_insertbonD.Parameters.Add("@KORTING", SqlDbType.Decimal, 15, "KORTING")
cmd_insertbonD.Parameters.Add("@AANTALPEREENHEID", SqlDbType.SmallInt, 2, "AANTALPEREENHEID")
 
cmd_insertbonD.Parameters.Add("@INTRASTATNUMMER", SqlDbType.NVarChar, 9)
cmd_insertbonD.Parameters("@INTRASTATNUMMER").SourceColumn = "INTRASTATNUMMER"
 
cmd_insertbonD.Parameters.Add("@GEWICHT", SqlDbType.Decimal, 15, "GEWICHT")
 
dabond.InsertCommand = cmd_insertbonD

When i press my save button i do this:

Opslaan_record(sender, e, dsbond, dabond, "BONDETAIL")

This is the function that updates/inserts...

Public Function Opslaan_record(ByVal sender As System.Object, ByVal e As System.EventArgs, ByRef Dataset_byref As DataSet, ByRef DataAdaptor_byref As SqlClient.SqlDataAdapter, ByVal tabel As String) As Boolean
If Dataset_byref.HasChanges Then
Try
AddHandler DataAdaptor_byref.RowUpdated, New SqlClient.SqlRowUpdatedEventHandler(AddressOf onrowupdated)
 
DataAdaptor_byref.Update(Dataset_byref, tabel) '<--- problem occurs here
 
RemoveHandler DataAdaptor_byref.RowUpdated, New SqlClient.SqlRowUpdatedEventHandler(AddressOf onrowupdated)
Dataset_byref.Tables(tabel).AcceptChanges()
[SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] exSql [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlException[/SIZE]
[SIZE=2]msgbox (exSql.toString)
[/SIZE]End Try
End If
...
End Function

Now, just before this line ( DataAdaptor_byref.Update(Dataset_byref, tabel) )

At that point, the dataset dsbond looks exactly the way i want it to be (cfr. the watches from in my first post). The sourcecolumn that is about to be used to fill the @INTRASTATNUMMER parameter has the value System.DBNULL.value

the rows in the dataset look like :
1 .... INTRASTATNUMMER "1001.1001"
2 .... INTRASTATNUMMER "1001.1001"
3 .... INTRASTATNUMMER {System.DBNull.Value}

after that line: i get the sqlexception, i look at the watch on parameter @INTRASTATNUMMER and it's "". All other parameters are filled correctly.


If the rows in the dataset had looked like :
1 .... INTRASTATNUMMER "1001.1001"
2 .... INTRASTATNUMMER {System.DBNull.Value}
3 .... INTRASTATNUMMER "1001.1001"

there wouldn't have been any problem (i tested it), and the records are correctly inserted in the DB

I will answer the rest of your questions later after testing those things you mentioned.

 

[cjard]

I note with interest that the only column you have an issue with is not created in the same way as the others, though there should be no difference.

Can you provide the creation script for your table so I can attempt to replicate your code here?


Also; I dont data access using dataadapters any more using VS2005. I can show you a way to quickly and easily get the IDE to do a lot of the donkeywork for you. Would you like me to, so you can try this way if I am unsuccessful at recreating your problem?

 

[CygNuS]

This is the script for creating INTRASTATGOEDERENCODES (generated with SQL server):

CREATE TABLE [dbo].[INTRASTATGOEDERENCODES] (
[ID] [bigint] IDENTITY (1, 1) NOT NULL ,

 [nvarchar] (9) COLLATE Latin1_General_CI_AS NULL ,
[OMSN] [nvarchar] (1250) COLLATE Latin1_General_CI_AS NULL ,
[OMSF] [nvarchar] (1305) COLLATE Latin1_General_CI_AS NULL ,
[OMSE] [nvarchar] (1165) COLLATE Latin1_General_CI_AS NULL ,
[OMSD] [nvarchar] (1140) COLLATE Latin1_General_CI_AS NULL ,
[ISE_ID] [bigint] NULL 
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[INTRASTATGOEDERENCODES] WITH NOCHECK ADD 
CONSTRAINT [PK_INTRASTATGOEDERENCODES] PRIMARY KEY CLUSTERED 
(
[ID]
) ON [PRIMARY] 
GO
CREATE UNIQUE INDEX [ISG_CODE] ON [dbo].[INTRASTATGOEDERENCODES]([code]) ON [PRIMARY]
GO
 
 
 
This is the script for creating BONDETAIL (generated with SQL server):
 
 
CREATE TABLE [dbo].[BONDETAIL] (
[ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[BONNR] [bigint] NOT NULL ,
[LIJNNR] [bigint] NOT NULL ,
[TYPE] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[ARTNR] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[OMS] [nvarchar] (200) COLLATE Latin1_General_CI_AS NULL ,
[EENH] [char] (3) COLLATE Latin1_General_CI_AS NULL ,
[AANTAL] [decimal](18, 2) NULL ,
[PRIJS] [decimal](18, 6) NULL ,
[TOTAAL] [decimal](18, 2) NULL ,
[BTW_CODE] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
[ALG_CODE] [bigint] NOT NULL ,
[ANAL] [bigint] NULL ,
[REKOMS] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[KORTING] [decimal](5, 2) NULL ,
[EXTRA_LB] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[AANTALPEREENHEID] [smallint] NULL ,
[INTRASTATNUMMER] [nvarchar] (9) COLLATE Latin1_General_CI_AS NULL ,
[GEWICHT] [decimal](18, 3) NULL ,
[EXTRA_LL] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[BONDETAIL] WITH NOCHECK ADD 
CONSTRAINT [PK_BONDETAIL] PRIMARY KEY CLUSTERED 
(
[ID]
) ON [PRIMARY] 
GO
ALTER TABLE [dbo].[BONDETAIL] ADD 
CONSTRAINT [FK_BONDETAIL_INTRASTATGOEDERENCODES] FOREIGN KEY 
(
[INTRASTATNUMMER]
) REFERENCES [dbo].[INTRASTATGOEDERENCODES] (
[code]
)
GO
 
 
btw you noticed that the parameter @INTRASTATNUMMER is created in another way, it's because i tried this:
 
[code] 
cmd_insertbonD.Parameters.Add("@INTRASTATNUMMER", SqlDbType.NVarChar, 9)
 
'---cmd_insertbonD.Parameters("@INTRASTATNUMMER").SourceColumn = "INTRASTATNUMMER"
 
cmd_insertbonD.Parameters("@INTRASTATNUMMER").Value = System.DBNull.Value

This was pure for testing: That writes NULL's to the DB (but for all the rows in the dataset and i don't want that).

So the problem is entirely this line:

cmd_insertbonD.Parameters("@INTRASTATNUMMER").SourceColumn = "INTRASTATNUMMER"

It's like the data-adapter "thinks" like this when filling the parameters:
if there is more then one row in the dataset and the last row has a field that contains system.dbnull.value .... then i convert it to "" before i assign it to the parameter.


I also tried using this, but to no avail:

cmd_insertbonD.Parameters("@INTRASTATNUMMER").SourceColumnNullMapping = True
 
cmd_insertbonD.Parameters("@INTRASTATNUMMER").SourceColumn = "INTRASTATNUMMER"

Thanks for hearing me out and trying to reproduce the problem, remember it works perfectly if there is only ONE ROW in the dataset.

Btw yes i really would like to know the other way, for later forms, but in this form it would be too big a change, i think.

If i can't figure it out i'll just drop that FK and do my checks manually, it's not the way it's done perfectly but for now, it would do fine!

Thanks a lot in advance!

 

[CygNuS]

Important thing when you recreate the situation:

The data-adapter will only use those values in the dataset that have been changed.

At certain points in the form, i explicitly fill in the FIELD INTRASTATNUMMER (of some of the rows in the dataset) with System.DBNull.Value. This is something i have to do.

When recreating, you should do that too, because otherwise the data-adapter won't see the field INTRASTATNUMMER as changed and won't do anything with it (and not have problems with it).

 

[cjard]

As far as I was aware, the individual values of a column are irrelevant when it comes to determining whether to update.. I thought it was the RowState being Modified that causes the update query to be called, and Added that causes Insert to be called.. Individual column values are ignored.

I'll attempt to recreate your problem using exactly the codes you have here and let you know my results. I'll also do the data access the way I'm used to (tableAdapters) and see if I can reproduce. At the end of it all, I'll make available any code I generated

 

[CygNuS]

You are right, indiviual columns don't matter but what i meant was:

If a column in a dataset is never assigned a value, then it will remain NULL, causing no trouble.

But i explicitly assign the value System.DBNull.value to column INTRASTATNUMMER in some rows in the dataset during several actions on my Form.

So you should do that too.

Thx for all the effort!

 

[cjard]

I've so far failed to recreate the relationship in my dataset because the data types in the keyed columns are different.
BONDETAIL.INSTRASTATNUMMER (string) is an FK of INSTRASTATGOEDERENCODES (number).
Is it impossible to have INTRASTATNUMMER as a bigint?

Are you running without a relationship on the client side?
Why are the datatypoes of two columns keyed together, different?
If a relation exists, then they should be the same?

 

[CygNuS]

Child table BONDETAIL:

[INTRASTATNUMMER] [nvarchar] (9) COLLATE Latin1_General_CI_AS NULL

Parent table INTRASTATGOEDERENCODES:

 [nvarchar] (9) COLLATE Latin1_General_CI_AS NULL 
 
That looks like the same type? 
 
I know the FK does not reference the PK "ID" in the parent table, it references Unique Key "code".
 
The "code" looks something like 0010.1234 (4 digits dot 4 digits), but that's a detail :-)
 
---------------------------------------
Edit:
 
Is that a problem? I know the FK is supposed to reference to a PK, but even then, the problem at hand remains the same ("" will still fail the constraint)
 
If the relation in the dataset is a problem then you should indeed change INTRASTATNUMMER to bigint to get further. (i think the problem will change a little now: the parameter will be a bigint and will suddenly contain 0 instead of "")

 

[cjard]

OK, sorry.. i missed that - it's not often that I work in foreign languages.

I'm curious to know then, if Code is unique, why can it not be the primary key?

I'll go back to looking at the problem now

 

[CygNuS]

Well it can be PK actually

I myself did not create the DB, i'm working at this company for a month now (first job) and i have to make changes to this some forms in a large program, i don't really know the impact of DB changes like that, so i'll leave it Unique

 

[cjard]

ok. Unfortunately (or fortunately?) I have so far failed to replicate the issue. I'm using the same codes you have here to insert into the database, and I have tried all combinations of 3 rows i can in order to get one value to finish as a ""

Instead, I would like to give you my sample project but changed to do the data access in the modern .NET 2.0 way. I would like you to run the app (after checking it of course) and see if you can reproduce data insertion problems with it?