Solved

Getting "1/1/0001 12:00:00 AM" for a datetime SQL field when there are no records for that field.

Posted on 2004-04-27
9
1,601 Views
Last Modified: 2012-05-04
Problem:  "1/1/0001 12:00:00 AM" is assigned to dtInvDate
               "0" to curInvAmt

When there is no record matching the query below. How can I have the two variables to display nothing  in their respective textboxes when there is no record?

Dim strInvoiceNo As String
Dim curInvAmt As Double
Dim dtInvDate As DateTime

strSQL2 = "SELECT InvoiceNo, InvoiceAmount, InvoiceDate FROM tblInvoice Where ObligationID = " & request("obid")
 cmdObl2 = New SqlCommand(strSQL2,dsConn2)
  dtrObl2 = cmdObl2.ExecuteReader()

       If dtrObl2.HasRows Then
            strInvoiceNo = dtrObl2("InvoiceNo")
             curInvAmt =  dtrObl2("InvoiceAmount")       
            dtInvDate = dtrObl2("InvoiceDate")
       Else
             strInvoiceNo = ""
             curInvAmt = Nothing       
           dtInvDate = Nothing       
       End If
       'Close DataReader
       dtrObl2.Close()

Please help. I need a solution to this problem urgently. I have to be able to deal with NULLS in .NET. In ASP this was not a problem.
0
Comment
Question by:DeMyu
9 Comments
 
LVL 6

Expert Comment

by:gtvingo14
ID: 10932236
here is how I deal with nulls from a queery

If IsDBNull(myReader("ColumnName")) = true
    txtDisplay.text = ""
end if

let me know if this is what you are looking for
0
 

Author Comment

by:DeMyu
ID: 10932745
gtvingo14,

The code above works for string data not date or double. My problem is with these datatypes.

Here is a solution that I have that is currently working:

       If dtrObl2.Read() Then
            strInvoiceNo = dtrObl2("InvoiceNo")
            curInvAmt =  dtrObl2("InvoiceAmount")
            strInvAmt = curInvAmt.ToString( "c" )       
            dtInvDate = dtrObl2("InvoiceDate")
            strInvDate = dtInvDate.ToString( "d" )
       Else
       strInvoiceNo = ""
       strInvAmt = ""      
       strInvDate = ""
      
       End If
       'Close DataReader
       dtrObl2.Close()

I am sure there is a better way!
0
 
LVL 28

Accepted Solution

by:
mmarinov earned 125 total points
ID: 10933019
have you tried this

Dim first as Boolean
first = true
Dim indexDate as Integer
If dtrObl2.Read() Then

   If first Then
    indexDate = dtrObl2.GetOrdinal("InvoiceDate")
    first = false
   End If

    If Not dtrObl2.IsDbNull(indexDate) Then
       dtInvDate = dtrObl2.GetDateTime(indexDate)
    End If
End If

HTH
B..M
0
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
LVL 6

Assisted Solution

by:zulu_11
zulu_11 earned 125 total points
ID: 10936506
The best solution would be chage your SQL Query so that you do your checking for null at the DB level itself..

Like so..--- ISNULL(check_expression,replacement_value)

strSQL2 = "SELECT InvoiceNo, InvoiceAmount, InvoiceDate

SELECT ISNULL(InvoiceNo,""),ISNULL( InvoiceAmount,0),ISNULL( InvoiceDate,SYSDATE)

so this would effectively return

"" if InvoiceNo is NULL
0 if InvoiceAmount is NULL
SysDate if InvoceDate is NULL

you can change them to whatever you want in case of NULL values...

No need to put check in the code..

Hope this helps

Zulu
0
 
LVL 6

Assisted Solution

by:Volkan Vardar
Volkan Vardar earned 125 total points
ID: 10937281
change your SQL query like that:

strSQL2 = "SELECT InvoiceNo, InvoiceAmount, ISNULL(CONVERT(varchar, InvoiceDate),'') FROM tblInvoice Where ObligationID = " & request("obid")

0
 
LVL 1

Expert Comment

by:nparthi
ID: 10988995
Why not have another WHERE clause for the date field in your query. THis would return records where the date is >=1/1/1900 there by eliminating the date 1/1/0001 12:00:00 AM
that is displayed now. I didnt quite get your question and sorry if this is not you were asking.

WHERE dtInvDate >= to_date('1/1/1900 00:00:00' ,'MM/DD/YYYY HH24:MI:SS')
0
 
LVL 4

Assisted Solution

by:Kittrick
Kittrick earned 125 total points
ID: 11021379
I have had the same problems you are having, and here's how I solved them:

 ABOUT NULLS AND DATES

I don't exactly know how you have your code laid out, but I had this function (vb.net) in my codebehind to verify the field <%# DataBinder.Eval(Container.DataItem, "BirthDate") %> , but to get the function to work, I had to call it from the ASP.NET syntax in the form like this:

<%# CHECKbdate(DataBinder.Eval(Container.DataItem, "BirthDate")) %>

here is the function:
''''''''''''''''''''''''''''''FUNCTION''''''''''''''''''''''''''''''
Function CHECKbdate(var1 as object) as String

'this line verifies that the field itself  isn't empty, if so what to do...
if var1 is DBNull.Value Then
   Return"000"
end if

'The mid function trims everything after ten characters, just long enough for a date in mm/dd/yyyy format. Returns the result back to:
'<%# CHECKbdate(DataBinder.Eval(Container.DataItem, "BirthDate")) %>
'to post back to the page.

Return (mid(var1,1,10)).ToString
End Function

''''''''''''''''''''''''''''''FUNCTION''''''''''''''''''''''''''''''

This should help you, since you are used to old asp. I used a repeater with my fields, but like I said, I don't know how your data is set up, but I hope I helped.

Kittrick
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

815 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now