Solved

If Null Statement on ADO field

Posted on 2004-04-19
32
750 Views
Last Modified: 2013-11-23
This does not work...I need one string value if the DB field (SQL) is null, and seperate value to the string IF the field is not NUll.

Yes, the table is set to allow Nulls onthis column.

If IsNull(adoMRS.Fields("ClearName")) Then

strCD = "      "
strCN = "  "

strT1 = strCD & " " & strCN & " " & adoMRS.Fields("Code") & " " &adoMRS.Fields("Description")
ElseIf adoMRS.Fields("ClearName") <> "" Then
     
      strT1 = Format(adoMRS.Fields("ClearDate"), "mm/dd/yy") & " " & Left(adoMRS.Fields("ClearName"), 2) & " " & adoMRS.Fields("Code") & " " & adoMRS.Fields("Description")
End If  
0
Comment
Question by:lexo
  • 10
  • 4
  • 4
  • +9
32 Comments
 

Accepted Solution

by:
cnfei earned 100 total points
ID: 10865187
how it doesn't work...what is the result? or what is the error message?
0
 
LVL 8

Assisted Solution

by:rajaloysious
rajaloysious earned 100 total points
ID: 10865285
You can try out adoMRS.Fields("ClearName") = vbEmpty
OR
adoMRS.Fields("ClearName"))  = vbnull
OR
IsEmpty(adoMRS.Fields("ClearName")) )

also i guess that this could should work...
Good Luck....


0
 

Author Comment

by:lexo
ID: 10865302
"(10075) Null value can't be used here"
0
 
LVL 8

Expert Comment

by:rajaloysious
ID: 10865321
Get me the code snippet please if you dont mind .....
0
 

Author Comment

by:lexo
ID: 10865346
Tried em all, no luck...this should be a lot simpler than it's turning out to be. Here's ALL the code:

   Set adoMCN = New ADODB.Connection
   With adoMCN
      .ConnectionString = strCon
      .Open
   End With




   strProdID = GetCalcString(7073)  

strCond= "D"

   Set adoMRS = New ADODB.Recordset
   With adoMRS
      strSql = "Select * from UATracking_file  Where Loan_id= '" & strProdID & "' AND RemoveCond='" & strCond & "' Order by Code"
      .Open strSql, adoMCN, , , 1
   End With

   
   intReg = 8800
   
   While Not adoMRS.EOF


If IsEmpty(adoMRS.Fields("ClearName")) Then

strCD = "      "
strCN = "  "

strT1 = strCD & " " & strCN & " " & adoMRS.Fields("Code") & " " & adoMRS.Fields("Description")
ElseIf adoMRS.Fields("ClearName") <> "" Then

      strT1 = Format(adoMRS.Fields("ClearDate"), "mm/dd/yy") & " " & Left(adoMRS.Fields("ClearName"), 2) & " " & adoMRS.Fields("Code") & " " & adoMRS.Fields("Description")
End If  

      Do
         Select Case Len(strT1)
            Case Is <= 100
               strT2 = strT1
               strT1 = ""
            Case Is > 100
               strT2 = Left(strT1, 100)
               strT1 = Trim(Mid(strT1, 101))
         End Select
         intReg = intReg + 1
         SetRegString intReg, strT2

      Loop Until Len(strT1) = 0

      adoMRS.Movenext
   
 Wend  
   Set adoMRS = Nothing
   Set adoMCN = Nothing
0
 

Author Comment

by:lexo
ID: 10865368
Oh, and this question is posted inthe SQL section as well, I'll give both questions points if you can answer it...1000 points, I need this ASAP. Tired and need to go home.  

Also another more tip:  I'm using ghetto @$$ Sax Basic, so if I need to add a reference library to get functionality, I can do that.
0
 

Assisted Solution

by:StephenSimpsonx
StephenSimpsonx earned 50 total points
ID: 10865446

Which line specifically is giving the error? Did you step though it?

is code or cleardate null?

 


If IsNull(adoMRS.Fields("ClearName")) Then

  strCD = "      "
  strCN = "  "
  strT1 = strCD & " " & strCN & " " & adoMRS.Fields("Code") & " " &adoMRS.Fields("Description")

else
   If adoMRS.Fields("ClearName") <> "" Then
     
       strT1 = Format(adoMRS.Fields("ClearDate"), "mm/dd/yy") & " " & Left(adoMRS.Fields("ClearName"), 2) & " " &      _
       adoMRS.Fields("Code") & " " & adoMRS.Fields("Description")
  endif
End If

0
 
LVL 7

Assisted Solution

by:ramesh12
ramesh12 earned 50 total points
ID: 10865450
Use Trim

ElseIf trim(adoMRS.Fields("ClearName")) <> "" Then
0
 

Author Comment

by:lexo
ID: 10865486
Same damn Error....this sucks.
0
 

Assisted Solution

by:cnfei
cnfei earned 100 total points
ID: 10865743
add a function

Public Function checkNull(ByVal tmpString As Variant) As String
    If IsNull(tmpString) Then
        checkNull = ""
    Else
        checkNull = tmpString
    End If
End Function

and change the code to

If IsNull(adoMRS.Fields("ClearName")) Then

  strCD = "      "
  strCN = "  "
  strT1 = strCD & " " & strCN & " " & checknull(adoMRS.Fields("Code")) & " " & checknull(adoMRS.Fields("Description"))

elseIf adoMRS.Fields("ClearName") <> "" Then

      if not isdate(checknull(adoMRS.Fields("ClearDate"))) then
            msgbox "The value of Clear date is invalid"
      else

            strT1 = Format(adoMRS.Fields("ClearDate"), "mm/dd/yy") & " " & Left(adoMRS.Fields("ClearName"), 2)    & " " &      _
            checknull(adoMRS.Fields("Code")) & " " & checknull(adoMRS.Fields("Description"))
      end if
end if
0
 
LVL 8

Assisted Solution

by:rajaloysious
rajaloysious earned 100 total points
ID: 10865816
hmmmmmmmmm,
if u want to solve it now by any means... see this ... i attack the problem from the other side
Instead of using Select * from table name

use this
const XXX_XXX_XXXX = "_____________________XX_" or 99999999 ( if it is an integer)
Select NVL(ClearName, XXX_XXX_XXXX), ClearDate ..... from table name

XXX_XXX_XXXX can be any pre defined value

and check in the code
If (adoMRS.Fields("ClearName") = XXX_XXX_XXXX) Then
instead of If IsNull(adoMRS.Fields("ClearName")) Then

Good Luck
0
 

Assisted Solution

by:8tricks
8tricks earned 50 total points
ID: 10866898
You didnt indicate which line of code that contains that error, but i think it is the 'If' and 'ElseIF' part that you have used.

1) If IsEmpty(NULL) Then
function IsEmpty cannot use to check Null so this will throw false
You must change this to use -> If IsNull(adoMRS.Fields("ClearName") Then
                                                ====
2) ElseIf adoMRS.Fields("ClearName") <> "" Then
0
 

Expert Comment

by:8tricks
ID: 10866900
You didnt indicate which line of code that contains that error, but i think it is the 'If' and 'ElseIF' part that you have used.

1) If IsEmpty(NULL) Then
function IsEmpty cannot use to check Null so this will throw false
You must change this to use -> If IsNull(adoMRS.Fields("ClearName") Then
                                                ====
2) ElseIf adoMRS.Fields("ClearName") <> "" Then
0
 

Expert Comment

by:8tricks
ID: 10866923
continued....

2) ElseIf adoMRS.Fields("ClearName") <> "" Then
I do not recommend this. Split the else if. Use..
 
Else
If adoMRS.Fields("ClearName") <> "" or isEmpty(adoMRS.Fields("ClearName")) Then
....

End If
End If

0
 
LVL 8

Expert Comment

by:rajaloysious
ID: 10867342
The below statement works with me - from VB 6 connecting to Oracle 8i
It works for a string column with null and not null values perfectly well
If IsNull(adoMRS.Fields("char_value")) Then

''''''''''''''''''''''''''''''''''''''''''
    If IsNull(adoMRS.Fields("Col1")) Then
        MsgBox "Col1 value is null"
    End If
   
    If Not IsNull(adoMRS.Fields("Col2")) Then
        MsgBox "Col2 is " & adoMRS.Fields("Col2")
    End If
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
You have said initially that this does not work out.
I guess there is not an issue with this ISNull.

Check with the columns in the table. Check whether BOF is true ... in case
Also try MoveFirst ( even though it is not necessary). Also try my options in the database.
0
 
LVL 3

Assisted Solution

by:virenderpratap
virenderpratap earned 50 total points
ID: 10868224
"(10075) Null value can't be used here"

doesn't say that your
>If IsNull(adoMRS.Fields("ClearName")) Then
line of code has any error.
Error 10075 can be occured due to your invalid cursor position or statement.

Even if u want to get the size of Fields("ClearName")  then use ActualSize Property


>If adoMRS.Fields("ClearName").Actualsize < 1 Then
then allocate ur String.(or Whatever).



0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 9

Assisted Solution

by:_ys_
_ys_ earned 50 total points
ID: 10868355
Try this:

If "" = (adoMRS.Fields("ClearName") & "") Then
 ...
0
 
LVL 2

Expert Comment

by:taherzm
ID: 10869318
which db are u using?
cant u use the IIF condition in the SELECT query itself?
0
 

Author Comment

by:lexo
ID: 10869731
I am using SQL..sure I do whatever...I just need it to work.  Wouldn't I have to use two queries?  No big deal there either though.
0
 

Author Comment

by:lexo
ID: 10869769
Public Function checkNull(ByVal tmpString As Variant) As String
    If IsNull(tmpString) Then
        checkNull = ""
    Else
        checkNull = tmpString
    End If
End Function

and change the code to

If IsNull(adoMRS.Fields("ClearName")) Then

  strCD = "      "
  strCN = "  "
  strT1 = strCD & " " & strCN & " " & checknull(adoMRS.Fields("Code")) & " " & checknull(adoMRS.Fields("Description"))

elseIf adoMRS.Fields("ClearName") <> "" Then

     if not isdate(checknull(adoMRS.Fields("ClearDate"))) then
          msgbox "The value of Clear date is invalid"
     else

          strT1 = Format(adoMRS.Fields("ClearDate"), "mm/dd/yy") & " " & Left(adoMRS.Fields("ClearName"), 2)    & " " &      _
          checknull(adoMRS.Fields("Code")) & " " & checknull(adoMRS.Fields("Description"))
     end if
end if


...this errors at: Public Function checkNull(ByVal tmpString As Variant) As String

0
 
LVL 9

Expert Comment

by:_ys_
ID: 10869825
Any luck with what I suggested?

>> If "" = (adoMRS.Fields("ClearName") & "") Then
0
 
LVL 2

Assisted Solution

by:taherzm
taherzm earned 50 total points
ID: 10870093
try this out -
strSql = "Select field1,field2,IIF(ISNULL(field3),"stringvalue_ifTrue","stringValue_ifFalse") from UATracking_file  Where Loan_id= '" & strProdID & "' AND RemoveCond='" & strCond & "' Order by Code"

please replace field1,field2,field3 with the approriate fieldnames...
and mention all the fields you want your query to fetch...

this way u will not need 2 queries...the query itself sets the appropriate value depending onthe value of the field...



0
 

Author Comment

by:lexo
ID: 10870126
OK ok, this is not the problem I thought it was.  DAMN IT!!

For some reason it's thinking that (adoMRS.Fields("Description") is null, even though it's not in the db (SQL).  here's the code, that works, but my string is not including the value of (adoMRS.Fields("Description"))..it's blank in the string, but not null in the db...what gives?

If IsNull (adoMRS.Fields("Description")) Then
adoMRS.Fields("Description") = ""
End If

 If "" = (adoMRS.Fields("ClearName") & "") Then


strCD = "       "
strCN = "  "

strT1 = strCD & " " & strCN & " " & adoMRS.Fields("Code") & " " & adoMRS.Fields("Description")
Else
'adoMRS.Fields("ClearName") isnot ""
      strT1 = Format(adoMRS.Fields("ClearDate"), "mm/dd/yy") & " " & Left(adoMRS.Fields("ClearName"), 2) & " " & adoMRS.Fields("Code") & " " & adoMRS.Fields("Description")
End If  

      Do
         Select Case Len(strT1)
            Case Is <= 100
               strT2 = strT1
               strT1 = ""
            Case Is > 100
               strT2 = Left(strT1, 100)
               strT1 = Trim(Mid(strT1, 101))
         End Select
         intReg = intReg + 1
         SetRegString intReg, strT2

      Loop Until Len(strT1) = 0

      adoMRS.Movenext
   
 Wend  
   Set adoMRS = Nothing
   Set adoMCN = Nothing
0
 
LVL 2

Expert Comment

by:sbennetts
ID: 10871020
Have you tried
If "" & adoMRS.Fields("ClearName")= "" Then
   ...
else
   ...
end if
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10874056
>>For some reason it's thinking that (adoMRS.Fields("Description") is null, even though it's not in the db (SQL).  <<
Again, I can help, but please first close this old open question:
11/14/2003 How to "decode" this XML file  Open Web Development
http://www.experts-exchange.com/Web/Q_20798684.html

Let me know.
0
 

Author Comment

by:lexo
ID: 10874181
>>For some reason it's thinking that (adoMRS.Fields("Description") is null, even though it's not in the db (SQL).  <<
Again, I can help, but please first close this old open question:
11/14/2003 How to "decode" this XML file  Open Web Development
http://www.experts-exchange.com/Web/Q_20798684.html

Let me know.

I've got this issue fixed...I'd love to close the questions, is there any way to do it other than assigning the points?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10874247
>>I'd love to close the questions, is there any way to do it other than assigning the points?<<
Sure.  From the EE Guidelines at:
http://www.experts-exchange.com/help.jsp#hi67
<quote>
What are my choices?  
 
You have five choices when it comes to closing your open questions:
Accept an Expert's Comment as the Answer http://www.experts-exchange.com/help.jsp#hi68
Accept Comments from more than one Expert (a Split) http://www.experts-exchange.com/help.jsp#hi69
Request a refund because you answered your own question (Refund/PAQ) http://www.experts-exchange.com/help.jsp#hi69
Request a refund because no one answered your question (Delete) http://www.experts-exchange.com/help.jsp#hi70
Abandon your question and leave the mess for someone else to clean up  http://www.experts-exchange.com/help.jsp#hi78
</quote>

>>I've got this issue fixed<<
Glad to here it.  So you no longer need help with this question?  If so, please close this question (and the other cross-posted question) as per the third choice.
0
 

Author Comment

by:lexo
ID: 10874638
Abandon your question and leave the mess for someone else to clean up  http://www.experts-exchange.com/help.jsp#hi78
 sounds good to me, I don't have time to do whatever your asking me...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10874923
Here is the full text:
<quote>
There are several possible actions that may be taken, and while the Moderators and Page Editors don't like any of them, they are also very much inclined to take them.

By way of explanation, the Moderators and Page Editors consider abandoning questions as a violation of the Membership Guidelines, specifically the clause regarding "abuse of the points system", in that you've promised points for an answer and have received it, but haven't followed through in awarding them. Further, among the criteria used in selecting them is their tenure and familiarity with both Experts Exchange and a wide variety of subjects. As such, they are going to be inclined to believe Experts who say they, in fact, answered a question you've asked -- so they'll also be inclined to not believe you when you say that you didn't get an answer if 23 of your 32 asked questions are still open.

So here's what can happen. The first step is that a Page Editor or Moderator will ask you, politely, to deal with your open questions. Most of the Experts will tell you that it's not a good idea to ignore that request.

If you choose to ignore the request, you will probably receive an email, usually including a list of your open questions. You'll be asked to close them, usually within a week. Such an email is your last warning.

The third step is that your account gets suspended. You will receive an email notification; your account will be reinstated only after you've agreed to some severe conditions, and the Moderators and Page Editors will pay attention to your behavior.
</quote>
0
 
LVL 1

Expert Comment

by:issamtaher
ID: 10878553
hi lexo

maybe it is strange way but it's work

if  adoMRS.Fields("ClearName") &"A" = "A" then
' this mean the value is null
else
' value not null
endif

i tried by myself

good luck
0
 

Author Comment

by:lexo
ID: 10879210
Actually this problem was solved when I held a string value with the column value and usd that in sting strT1...

I would like to split the points with all of you just for trying....
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10924498
Thank you for maintaining your old open question.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

705 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

21 Experts available now in Live!

Get 1:1 Help Now