Record changes to NULL??

I am running this query to a SQL Server database using ADO 2.5
SELECT * FROM SkillValues WHERE ApplicationID=9 AND SkillID=3
Which gives me this record
mCertification = rs.Fields("Certification")
mYearsExp = rs.Fields("YearsExp")
mMonthsExp = rs.Fields("MonthsExp")

The problem is when mCertification takes the value from rs.Fields("Certification") it changes to NULL. If I put a break point on mCertification = rs.Fields("Certification") and hover my mouse over rs.Fields("Certification"), it tells me the value is "any" which is the string value in the database, so far everything is good. But when I hit F8 to actually give that value to mCertification, I get an error saying "Invalid use of NULL". When I click debug and hover over rs.Fields("Certification") is now says the value is NULL. Any ideas?

When using F8, the value is set on the next line. So it sound like you do infact have a Null value in that Field.

There are a few soulutions, on way is to use IIF Function, like:

VB Code:

mCertification =  IIF(rs.Fields("Certification").Value = Null, "", rs.Fields("Certification").Value)

There has been discussion about using IIF, so do a seacrh and find whats best for you.




Bruce.

I tried using the IIF, but I got an error there too. I just tried
mCertification = IIf(rs.Fields("Certification") = Null, "", rs.Fields("Certification")) and got Invalid use of NULL and I tried
mCertification = IIf(IsNull(rs.Fields("Certification")), "", rs.Fields("Certification")) and the same thing

Does this work (temp substitution):

VB Code:

MsgBox  IIF(rs.Fields("Certification").Value = Null, "", rs.Fields("Certification").Value)

Quote:

---

Originally posted by Bruce Fox
Does this work (temp substitution):

VB Code:

MsgBox  IIF(rs.Fields("Certification").Value = Null, "", rs.Fields("Certification").Value)

---

You will keep getting the Invalid use of Null error, because all statements are evaluated, no matter what IIf is going to use.

Try

VB Code:

mCertification = rs.Fields("Certification") & ""

or if its a numeric field

VB Code:

mCertification = Val("0" & rs.Fields("Certification"))

Same error. It is really stange here, because if I hover over it once I get the value "any", but if I hover again I get NULL. This is so damn stange. Here is what I get from teh DB running the query in QueryAnalyzer:

SELECT * FROM EZapplication_SkillValuesTemp WHERE ApplicationID=9 AND SkillID=1

SkillValueID=252
ApplicationID=9
SkillID=1
YearsExp=10
MonthsExp=1
Certification=any

Quote:

---

Originally posted by blindlizard
Same error. It is really stange here, because if I hover over it once I get the value "any", but if I hover again I get NULL. This is so damn stange. Here is what I get from teh DB running the query in QueryAnalyzer:

SELECT * FROM EZapplication_SkillValuesTemp WHERE ApplicationID=9 AND SkillID=1

SkillValueID=252
ApplicationID=9
SkillID=1
YearsExp=10
MonthsExp=1
Certification=any

---

What kind of cursor are you using? ForwardOnly?

Oh, no crptcblade, your solution worked! I was posting that at the same time you were. Thank you guys so much for your help :thumb:

But, that still doesn't make sense why a field that had value was throwing that error, but if I append an empty string to it it works.??

I beleive that variables displays thier valus when you sequence to the next line (using F8), having said that the Value 'any' may be the
previous secords value.



Bruce.

No, that can't be it because I only get one record with my query.

Quote:

---

Originally posted by crptcblade
Try

VB Code:

mCertification = rs.Fields("Certification") & ""

---

blindlizard, crptcblade,

Sorry, my bad. I had been using the IIF (in reverse) which of course is different to blindlizard question. I had done:

VB Code:

.Fields("Equipment").Value = IIf(cboEquipment.Text = "", Null, cboEquipment.Text)

Bruce.

is to jsut append vbNullString ....

VB Code:

strMyString = rs.Fields("Something").Value & vbNullString

and for numbers...

VB Code:

lngMyLong = CLng(Val(rs.Fields("Something").Value & vbNullString))

TG

But I still don't understand why it says I have a NULL when I do not. If I append an empty string I get my value fine.

This gives me an error
mCertification = rs.Fields("Certification")

This gives me the value "any" from the DB
mCertification = rs.Fields("Certification") & ""