asked on

SQL making blank date fields 1/1/1900 ...need to change to 'blank' ??

I am binding data to a grid then updating data back to SQL with a button.
The update code is:
--------------------------------------------------------------------------------
For xx = 0 To UltraWebGrid1.Rows.Count - 1
var0 = UltraWebGrid1.Rows(xx).Cells(0).Text
var1 = UltraWebGrid1.Rows(xx).Cells(1).Text
var2 = UltraWebGrid1.Rows(xx).Cells(2).Text
var3 = UltraWebGrid1.Rows(xx).Cells(3).Text
var4 = UltraWebGrid1.Rows(xx).Cells(4).Text
Replace(var4, "'", "''")
var5 = UltraWebGrid1.Rows(xx).Cells(5).Text
var6 = UltraWebGrid1.Rows(xx).Cells(6).Text
var7 = UltraWebGrid1.Rows(xx).Cells(7).Text
var8 = UltraWebGrid1.Rows(xx).Cells(8).Text
var9 = UltraWebGrid1.Rows(xx).Cells(9).Text
var10 = UltraWebGrid1.Rows(xx).Cells(10).Text
var11 = UltraWebGrid1.Rows(xx).Cells(11).Text
var12 = UltraWebGrid1.Rows(xx).Cells(12).Text
var13 = UltraWebGrid1.Rows(xx).Cells(13).Text
var14 = UltraWebGrid1.Rows(xx).Cells(14).Text
var15 = UltraWebGrid1.Rows(xx).Cells(15).Text
var16 = UltraWebGrid1.Rows(xx).Cells(16).Text

sqlstring = "UPDATE EIS.dbo.tcdata set InvoiceNbr = '" & var1 & "', MstrBuildNr= '" & var2 & "', so_ord_date= '" & var8 & "', so_req_date= '" & var9 & "', so_due_date= '" & var10 & "', so_pricing_dt= '" & var11 & "', ActCarrier= '" & var13 & "', ActShipDate= '" & var14 & "', CarrInvcd= '" & var15 & "', Estimate= '" & var16 & "' where so_nbr = '" & var0 & "'"

sqlcmd.CommandText = sqlstring
sqlcmd.ExecuteNonQuery()
-------------------------------------------------------------------------------------------
var14 is quite often blank or "", but when I write the "" or blank to SQL its defaulting to 1/1/1900 and putting that in the field. This is not desireable. How can I tell it to write a "null" if it is "" or blank.?

wimthepimscake

Hi rbend,

what code is it? C# ?

Wimthepimscake

wimthepimscake

If it is C# you can set the value to DBNull.Value that will insert a null value in the column if the column is null allowed in the database.

Wim

rbend

ASKER

No VB.
Please show example using my code.

MikeWalsh

rbend, does your datetime column allow nulls? What is specified as the default? If it is 1/1/1990, you could change the default to NULL if you really want to allow null dates in that column. If you change the column constraint to allow nulls, and remove any defaults that may exist defaulting to 1/1/1990, then the NULL values should, and the empty string should just not update anything.

rbend

ASKER

yes...the column allows and initially defaults to nulls.
I believe that could be ok.
Where do I disalble the invokation of 1/1/1900 as the default?

wimthepimscake

rbend,

I'm a C# programmer but allright here it is:

if (UltraWebGrid1.Rows(xx).Cells(14).Text = "") then
var14 = DBNull.Value
else
var14 = UltraWebGrid1.Rows(xx).Cells(14).Text
end if

wimthepimscake

rbend,

you can not disable the default, if you did not defined that in the first place.

MikeWalsh

You can't disable that, that was my bad.. My solution would take care of nulls not empty strings, I would take care of it in VB with an ISdate function.. Is it ever possible that someone may put nondate characters or spaces in that column (var14)? Is var14 defined as a Date value in excel?

rbend

ASKER

Not Excel....A ASP page with Ultrawebgrid.
Taking the value from a grid column.
Not error checking the date value yet.
Could be prone to erroneous entry.

wimthepimscake:
Got error (squiggly blue underscore) on 'var14 = DBNull.Value'
Didn't like that.
ASP.NET is my platform.

MikeWalsh

that may be because your data type for that variable (var14) is defined as datetime.. Try and define it as a string.

ASKER CERTIFIED SOLUTION

MikeWalsh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rbend

ASKER

right...that's precisely my issue Mike.
I don't know the syntax.
:-)

rbend

ASKER

I found this nugget which answers my question quite well.

http://www.lazydba.com/sql/1__9303.html

thanks