Record changes to NULL??

[blindlizard]

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?

[Bruce Fox]

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:

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.

[blindlizard]

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

[Bruce Fox]

Does this work (temp substitution):

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

[crptcblade]

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

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

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

or if its a numeric field

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

[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

[crptcblade]

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?

[blindlizard]

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

[blindlizard]

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.??

[Bruce Fox]

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.

[blindlizard]

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

[Bruce Fox]

Originally posted by crptcblade
Try

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:

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

Bruce.

[techgnome]

is to jsut append vbNullString ....

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

and for numbers...

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

TG

[blindlizard]

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") & ""