Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Check if SQL field NULL

Posted on 2004-04-19
17
Medium Priority
?
1,617 Views
Last Modified: 2007-12-19
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
Comment
Question by:lexo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
  • +3
17 Comments
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 200 total points
ID: 10862072
where does it say null is not acceptable...

is the column nullable on the table?

what is your SQL Statement...
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 600 total points
ID: 10862076
Please followup on this very old open question:
http://www.experts-exchange.com/Web/Q_20798684.html
0
 
LVL 10

Assisted Solution

by:Shailesh15
Shailesh15 earned 600 total points
ID: 10862078
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 10

Assisted Solution

by:Shailesh15
Shailesh15 earned 600 total points
ID: 10862085
Oops mistake... "You can use Nullif in your "... should be "You can use isNull in your"...
0
 

Author Comment

by:lexo
ID: 10862089
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
 

Author Comment

by:lexo
ID: 10862104
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
 
LVL 4

Assisted Solution

by:chenkewind
chenkewind earned 200 total points
ID: 10862243
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
 

Author Comment

by:lexo
ID: 10862399
"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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 600 total points
ID: 10862493
>>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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10862517
chenkewind,

>>The length will equal 0 if the field is NULL or empty:<<
Nope.
Len(Null) = Null
0
 
LVL 7

Assisted Solution

by:ChrisFretwell
ChrisFretwell earned 400 total points
ID: 10862521
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 600 total points
ID: 10862672
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
 
LVL 7

Assisted Solution

by:ChrisFretwell
ChrisFretwell earned 400 total points
ID: 10862706
Ah, so having this in the sql forum doesnt mean it was a sql error. My mistake. Forget what I said.
0
 
LVL 10

Assisted Solution

by:Shailesh15
Shailesh15 earned 600 total points
ID: 10862731
>>  >>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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10862761
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
 

Author Comment

by:lexo
ID: 10865153
Yeah, VB...

Umm so, I guess I should place this in another section to get it answered...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10868622
It would help.  But please close this question, even if you do not care about it any more.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.

597 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