allow null in database

Question

question

Kim-3960 asked May 8, '22 Kim-3960 answered May 12, '22

allow null in database

I have a query where I calculate the total sum of an item based on different prices.
If I enter a new article and I leave certain cells empty, the price is calculated correctly with an empty cell.
If I now put a value in the empty cell, the calculation also happens correctly, but the cell cannot be emptied afterwards (no empty cell is allowed).
In the DB it says "Allow null's"
This only happens in cells where a number is stated, not in a cell with text.

 CREATE VIEW dbo.Particulier
 AS
 SELECT        Auto_Merk, Plaats, ref_vast, ref_es, Ref, Omschrijving, Datum, snijden, snijden_foto, Bol, ref_set7P, ref_set13P, werk_vast, werk_orig, werk_es, opleg_13P, Caravan, prijs_vast, prijs_es, prijs_set7P, prijs_set13P, Spoiler_ref, 
                          Spoiler_prijs, Codering, Codering_prijs, Uitbreidingsset, Uitbreidingsset_prijs, PDC_uitschakeling, ROUND((prijs_es * 1.21 + werk_es + prijs_set7P + ISNULL(Spoiler_prijs, '0') + ISNULL(Codering_prijs, '0') 
                          + ISNULL(Uitbreidingsset_prijs, '0')) / 10, 0) * 10 AS geplaatstes, ROUND((prijs_vast * 1.21 + werk_orig + prijs_set7P + ISNULL(Spoiler_prijs, '0') + ISNULL(Codering_prijs, '0') + ISNULL(Uitbreidingsset_prijs, '0')) / 10, 0) 
                          * 10 AS geplaatstorig, ROUND((prijs_vast * 1.21 + werk_vast + prijs_set7P + ISNULL(Spoiler_prijs, '0') + ISNULL(Codering_prijs, '0') + ISNULL(Uitbreidingsset_prijs, '0')) / 10, 0) * 10 AS geplaatstvast, checkvast, checkorig, 
                          checkes, Cataloog_ID
 FROM            dbo.Cataloog

dotnet-visual-basic

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2022-05-08T08:39:32.437Z

Viorel-1 answered May 8, '22 Viorel-1 commented May 10, '22

If you are using Management Studio, then try pressing <Ctrl+0> to empty the numeric nullable cell.

· 4

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Kim-3960 · May 08 at 12:08 PM

I use a vb.net app.

0 ·

Viorel-1 Kim-3960 · May 08 at 01:20 PM

What are you doing in VB, that does not work?

0 ·

Kim-3960 · May 08 at 04:17 PM

I fill my database via a winform.
If I enter data in these fields, I can no longer make them empty "field cannot be empty"
I can make the text fields empty, but not the fields with a number.

0 ·

knipsel.jpg (14.4 KiB)

Viorel-1 Kim-3960 · May 10 at 07:19 PM

Do you have some VB code that is associated with this form or which saves the data to database?

What kind of window is this?

0 ·

Answer 2 · 2022-05-11T14:58:51.24Z

Kim-3960 answered May 11, '22 Viorel-1 edited May 11, '22

it's a winform and i use this code to save the data.

  Function Save_cataloog() As Boolean
         Try
             Me.Validate()
             Me.CataloogBindingSource.EndEdit()
             Me.CataloogTableAdapter.Update(Me.CataloogDataSet.Cataloog)
    
             Return True
         Catch ex As Exception
             MsgBox(ex.ToString)
             Return False
         End Try
     End Function

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 · May 11 at 07:10 PM

As an intermediate workaround, maybe write a loop that replaces the empty strings from corresponding columns of Me.CataloogDataSet.Cataloog with DbNull.Value or with Nothing. Do it before calling Update.

1 ·

Answer 3 · 2022-05-12T08:00:49.797Z

Kim-3960 answered May 12, '22

I don't think the problem is in saving once I clear the cell I can't select anything else.
Something has to be filled in first before I can continue.
Could this be because the cell is used in a query calculation and therefore cannot be empty?

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question