Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1643
  • Last Modified:

Check if SQL field NULL

I need to check if the SQL field is null...this code deos not work, says NULL value is not acceptable for this field...Please help!!


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
lexo
Asked:
lexo
  • 6
  • 4
  • 3
  • +3
10 Solutions
 
LowfatspreadCommented:
where does it say null is not acceptable...

is the column nullable on the table?

what is your SQL Statement...
0
 
Anthony PerkinsCommented:
Please followup on this very old open question:
http://www.experts-exchange.com/Web/Q_20798684.html
0
 
Shailesh15Commented:
You can use Nullif in your original sql statement to eliminate null Value from the query itself.

If myfield has null value.. Returns ' '.

Select isnull(myfield,' ') form table

On your question about above code ..Are you sure that error occurs at "If IsNull(adoMRS.Fields("ClearName")) Then"
It could be any field (adoMRS.Fields("ClearDate")) with null value.
0
Industry Leaders: 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!

 
Shailesh15Commented:
Oops mistake... "You can use Nullif in your "... should be "You can use isNull in your"...
0
 
lexoAuthor Commented:
Yes the column is "nullable"

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

   
   intReg = 8800
   
   While Not adoMRS.EOF
0
 
lexoAuthor Commented:
I need the string t equal a certain value IF the field is Null, and another value if the field is not Null...I don't want to elimintate the null results.
0
 
chenkewindCommented:
You can try to look at the length of the field. The length will equal 0 if the field is NULL or empty:

If len(adoMRS.Fields("ClearName"))<=0 Then
   strCD = "      "
   strCN = "  "
   strT1 = strCD & " " & strCN & " " & adoMRS.Fields("Code") & " " &adoMRS.Fields("Description")
Else
      strT1 = Format(adoMRS.Fields("ClearDate"), "mm/dd/yy") & " " & Left(adoMRS.Fields("ClearName"), 2) & " " & adoMRS.Fields("Code") & " " & adoMRS.Fields("Description")
End If  
0
 
lexoAuthor Commented:
"Please followup on this very old open question:
http://www.experts-exchange.com/Web/Q_20798684.html"

THis is OLD, I don't care about this question anymore...plesae answer the current question, it's 500 points, the xml question is 50.
0
 
Anthony PerkinsCommented:
>>THis is OLD, I don't care about this question anymore<<
Exactly.  Please post a message in Community Support to have closed.

Any question over 21 days is considerd technically abandoned:
What happens if I don't close my question?
http://www.experts-exchange.com/help.jsp#hi78
0
 
Anthony PerkinsCommented:
chenkewind,

>>The length will equal 0 if the field is NULL or empty:<<
Nope.
Len(Null) = Null
0
 
ChrisFretwellCommented:
I think you mixed up the use of the isnull and is null and made things more difficult than you had to

isnull isnt a check to see if something is null. For that you would do this:

If (adoMRS.Fields("ClearName"))  is NULL 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  


If you all you want to do is assign a different value if a field is null, then you can do it in a single line
select isnull(fieldtocheck,valueifnull)  
this will return your original field if the value isnt null, or what you want it to be if it is null

Subsequently, if you want multiple options, one of which is null, you can do a case statement

str1 = case
when adoMRS.Fields("ClearName")  is null then strCD & " " & strCN & " " & adoMRS.Fields("Code") & " " &adoMRS.Fields("Description")
when adoMRS.Fields("ClearName") = "" then Format(adoMRS.Fields("ClearDate"), "mm/dd/yy") & " " & Left(adoMRS.Fields("ClearName"), 2) & " " & adoMRS.Fields("Code")
else "what todo when not null"
end
0
 
Anthony PerkinsCommented:
ChrisFretwell,

>>isnull isnt a check to see if something is null<<
I suspect you are confusing T-SQL with VBScript.  In VBScript IsNull is used to see if a value is Null

<quote>
IsNull Function
Returns a Boolean value that indicates whether an expression contains no valid data ( Null).
</quote>
0
 
ChrisFretwellCommented:
Ah, so having this in the sql forum doesnt mean it was a sql error. My mistake. Forget what I said.
0
 
Shailesh15Commented:
>>  >>isnull isnt a check to see if something is null<<
>>  I suspect you are confusing T-SQL with VBScript.  In VBScript IsNull is used to see if a value is Null


"Set adoMRS = New ADODB.Recordset"
I think he is using VB!!!... Guess.. everyone is confused!
0
 
Anthony PerkinsCommented:
ChrisFretwell,

>>Ah, so having this in the sql forum doesnt mean it was a sql error.<<
Right.  This question would be better served in the Visual Basic or VB Databases TA's

>>I think he is using VB!!!<<
I know.  It is the same function in VBScript.
0
 
lexoAuthor Commented:
Yeah, VB...

Umm so, I guess I should place this in another section to get it answered...
0
 
Anthony PerkinsCommented:
It would help.  But please close this question, even if you do not care about it any more.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 6
  • 4
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now