SQL Sends NULL value to a Datetime column

Asked

Viewed 2,301 times

2

I have a form in VB.NET where the user informs the delivery dates and delivery forecast of the order and other information. Since he does not yet have, for example, the delivery date because he has not yet been informed by the carrier.

My query takes all the form information stored in an Arraylist and sends it to the method it sends to the database. I put a checkbox in the 'Delivery Date' field to send the date if it is marked. But I want to send NULL if it is not marked, but it is giving error, saying that the parameter has no value.

I made these forms inside the Arraylist before sending to the method:

Dados.Add("NULL")
Dados.Add("'NULL'")
Dados.Add(Nothing)

'Data' is the name of the Arraylist that the method receives and replaces in the parameters. The part where the value of the delivery date is:

DT_ENTREGA = @DT_ENTREGA

I made a provisional solution until I knew how to do it. I’m sending you the amount 1900-01-01 where it should be NULL, when you finish running the query, send an update to update the value to NULL where that column contains 1900-01-01. It worked, but I don’t think that’s the procedure.

The method receives an Arraylist with several values and then play in the parameters:

objcmd.Parameters.Add(New SqlParameter("@DT_ENTREGA", Registro(5)))

The update I did so:

UPDATE LOGISTICA SET DT_ENTREGA = NULL WHERE DT_ENTREGA = '1900-01-01' AND PEDIDO = @PEDIDO;
sql-server winforms vb.net
  • 1

    I don’t understand anything that’s going on and what the problem is, maybe there’s more code to understand. The only thing that’s become clear to me is that you’re using ArrayList what is not recommended.

  • My problem is that I cannot send NULL to the SQL Datetime field when the field in the form is not filled.

  • All right, but what’s the problem, send a null. If you can’t, explain what you’re doing, why you can’t, what happens.

  • That’s true, sorry. I edited the text and added the part where I add NULL in the method Arraylist.

  • The first two do not make sense. The third in thesis is to work, but I do not know why I never used the ArrayList it is problematic. The problem may even be another because it is not yet clear what the problem is, the flow of the code, things are very loose. What is this Registro(5). We have to keep guessing what’s going on.

  • Particularly I have no problems with Arraylist, I find it very useful to pass many parameters. I did another test using Data.Add("") and he wrote '1900-01-01'. I really wanted to pass something in the parameter to save NULL in the database. Record(5) is the 6th position of Arraylist 'Data'

  • You do not need to pass "NULL", add the blank parameter. The sql that will insert NULL in the table if it accepts null fields.

  • @You have problems with him, but you still don’t understand. I won’t be able to help more than this.

  • @Robss70 did not work. Blank parameter saved '1900-01-01'.

  • @Mustache, what do you need? I think my problem is in this part: Sqlparameter("@DT_ENTREGA", "value passed") where 'value passed' must be something that Sqlparameter accepts as '= NULL'. I may also be wrong.

Show 5 more comments

1 answer

2


In your stored file "if you are using one" state the parameter thus:

@DT_ENTREGA datetime = null

This way when you pass the parameter Dados.Add("") the table will be null by default.

Another way would be to pass the value of the parameter as DBNull.Value;

Or else SqlDateTime.Null;

I believe they’re not the right way to solve it, but it’ll give you more time to think about how to fix it.

  • 1

    Thank you very much, I used Data.Add(Dbnull.Value) and it worked. Sqlparameter("@DT_ENTREGA", Record(5)), converted the value of Arraylist to NULL and recorded it in the database.

  • @Beautiful marlemos, why are you wearing ArrayList ? Using it can happen runtime errors in some conversion cases, while the List<T> would generate compile time error.

  • I started using Arraylist due to a video class where I was learning how to pass values to the record method in the bank. Actually it gives errors at runtime. I also use list however to read from the bank. For example, this query I built is a custom INSERT or UPDATE for the user. If he wants to save the date, the parameter goes with the date, if he does not want it, the parameter will be empty, that in the whole form. I put all the fields in an Arraylist and sent to the method, I use a validator for the fields before sending. There in the method inform which position for each column and write.

  • But I can also think of a list, it would be practical not to have to number as I do in Arraylist if I change the position. I hadn’t thought about it.

Browser other questions tagged sql-server winforms vb.net

You are not signed in. Login or sign up in order to post.