Convert dbnull to string. Record dbnull.value in database instead of Nothing

Question

I have an insertRecord function in vb.net that uses a parameter query. If there is no error date given, I need to insert dbnull.value into the db instead of ""(Nothing).

Dim strErrorDate2 As DateTime = Request.Form("dateOfErrorDatePicker2").ToString

.Add(New SqlParameter("@ErrorDate2", If(strErrorDate2 <> Nothing, strErrorDate2.Date, DBNull.Value)))

I keep getting conversion errors on dbnull to string. Originally it had it's own if/else block where I initialized the variable, but I couldn't figure out how to convert it correctly. I tried the inline if, but I still need to do a conversion or try something else.

Any ideas? The problem is that when I insert an empty string or DateTime into the database it records as a min possible date such as 1/1/1900.

EDIT: "Cannot infer a common type, 'object' assumed." That's the error I'm currently getting.

I suggest you put Option Strict On as the first line of the file with that code in. Then, when you have fixed the problems it highlights, your code may be in a working state :) — Andrew Morton
– Andrew Morton, Commented Dec 13, 2012 at 17:24

Ciarán · Accepted Answer · 2012-12-13 18:28:03Z

0

It's because strErrorDate2.Date is a DateTime and System.DbNull.Value is an object. Either Direct Cast strErrorDate2 to object in the IF() or do something like this

Dim oErrorDate2 as Object = System.DbNull.Value

If strErrorDate2 <> Nothing Then oErrorDate = strErrorDate2

.Add(New SqlParameter("@ErrorDate2",oErrorDate2))

answered Dec 13, 2012 at 18:28

Ciarán

3,0571 gold badge18 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BearSkyview Over a year ago

I ended up using a variable like above for DbNull.Value. I ran into problems trying to convert my input strings to DateTime when input was "", so I did an if to check if it's nothing. I convert to DateTime if there's something there, if not I use the DbNull variable as my parameter.

RBarryYoung · Accepted Answer · 2012-12-13 18:12:06Z

0

This is almost certainly happening because of what is on your SQL Server side. It is very likely that either you are calling a stored procedure that is replacing the Null with a minimum date, or else (more likely) the table that you are inserting into has either a Trigger or a Default column value that is assigning a minimum date when it is Null.

Just to clarify: First, you shouldn't have to worry about turning Nothing into DBNulls, ADO.net should be doing that for you.

Secondly, if you send SQL Server Nothing/Null and it stores something else, then that's because it (the table or the stored procedure) is replacing the NULL with a default value. This is most likely because the data design does not want NULLs to be in the column that you are trying to insert them to.

edited Dec 13, 2012 at 18:12

answered Dec 13, 2012 at 17:46

RBarryYoung

57k15 gold badges99 silver badges142 bronze badges

9 Comments

BearSkyview Over a year ago

If pass in DBNull.Value it works correctly. I just can't figure out the logic of correctly deciding between the actual value and DBNull. Do you still think it's something SQL server side?

RBarryYoung Over a year ago

I'm confused. In your OP you state that you get a string conversion error when you pass in DBNull.Value?

BearSkyview Over a year ago

.Add(New SqlParameter("@ErrorDate2", strErrorDate2)) .Add(New SqlParameter("@ErrorDate2", DBNull.Value)) The first one works, but sets to a minimum date if isNothing. The second one works to insert null regardless.

RBarryYoung Over a year ago

And what happens if you do: .Add(New SqlParameter("@ErrorDate2", Nothing))?

BearSkyview Over a year ago

.Add(New SqlParameter("@ErrorDate2", If(strErrorDate2 <> Nothing, strErrorDate2.Date, DBNull.Value))) - I'm trying to do both with this. This is where I receive an error.

|

Collectives™ on Stack Overflow

Convert dbnull to string. Record dbnull.value in database instead of Nothing

2 Answers 2

1 Comment

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related