[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2004-12-01
13
Medium Priority
?
603 Views
Last Modified: 2008-01-09
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.?

0
Comment
Question by:rbend
  • 5
  • 4
  • 4
13 Comments
 
LVL 6

Expert Comment

by:wimthepimscake
ID: 12717021
Hi rbend,

what code is it? C# ?

Wimthepimscake
0
 
LVL 6

Expert Comment

by:wimthepimscake
ID: 12717060
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
0
 

Author Comment

by:rbend
ID: 12717296
No VB.
Please show example using my code.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 13

Expert Comment

by:MikeWalsh
ID: 12717351
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.
0
 

Author Comment

by:rbend
ID: 12717412
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?
0
 
LVL 6

Expert Comment

by:wimthepimscake
ID: 12717432
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
0
 
LVL 6

Expert Comment

by:wimthepimscake
ID: 12717452
rbend,

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

Expert Comment

by:MikeWalsh
ID: 12717467
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?
0
 

Author Comment

by:rbend
ID: 12717565
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.
0
 
LVL 13

Expert Comment

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

0
 
LVL 13

Accepted Solution

by:
MikeWalsh earned 90 total points
ID: 12717668
I am not much of a programmer, not sure what the proper syntax to use to set something null is but I would do something along these lineS:

if not isdate(UltraWebGrid1.Rows(xx).Cells(14).Text) Then
var14 = NULL
else
var14 = UltraWebGrid1.Rows(xx).Cells(14).Text
end if


replace NULL with whatever syntax you need to use for NULL.
0
 

Author Comment

by:rbend
ID: 12717816
right...that's precisely my issue Mike.
I don't know the syntax.
:-)
0
 

Author Comment

by:rbend
ID: 12718158
I found this nugget which answers my question quite well.

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

thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question