How to check if field value is NULL?

[anju]

Hi all, How to check if field value is NULL? In my application i am using the code as follows to get the Recordset field value while (VARIANT_FALSE == pRecordset->EndOfFile) { strTemp.Format("Test Column:%s", (LPSTR)(_bstr_t)pRecordset->Fields->GetItem("Test")->Value); AfxMessageBox(strTemp); pRecordset->MoveNext(); } while running the application if the Field value is NULL. I am getting Runtime Error as "abnormal Programme termination". please help me anju

[Rama Krishna Vavilala]

You need to do something like this.

_variant_t vt = pRecordset->Fields->GetItem("Test")->Value;

if (vt.vt == VT_NULL)
{
AfxMessageBox("Field is NULL");
}
else
{
strTemp.Format("Test Column:%s",(LPSTR)(_bstr_t)vt);
AfxMessageBox(strTemp);
}

C# is fundamentally broken. - Christian Graus

[Imran Farooqui]

Consider query: select nick from from mytable. Suppose the value represented by nick can be NULL. So change the query as: select isnull(nick, 'something') as nick from mytable Here 'something' can be anything you desire. This can be a single space character also. Now when you collect value of nick from Recordset object, it'll not be null but the value supplied by you as a parmeter above. You can check it and treat as NULL Imran Farooqui World first Urdu Instant Messenger[^] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Java is a tool for creating applications that torture users with its awful speed and its ugly interfaces. Daniel Turini commenting on this article

[James Pullicino]

This line: (LPSTR)(_bstr_t)pRecordset->Fields->GetItem("Test")->Value); is very dangerous because of the following reasons: If Fields fails, an exception will be thrown. If GetItem fails, an exception will be thrown. If Value cannot be converted to a _bstr_t, an exception will be thrown. Segment your code a little better. Add exception handling, or use the raw_ methods of your smart pointers. Here is an example on how to improve your code (this is from memory): IFieldPtr field; try { field = pRecordset->Fields->GetItem("Test"); } catch(com_error e) { field = NULL; } if(field != NULL) { _variant_t vtVal; try { vtVal = field->Value; } catch (_com_error e) {vtVal.vt = VT_ERROR; } _bstr_t bstrVal; if(vtVal.vt != VT_ERROR) { try{ bstrVal = (_bstr_t)vtVal; } catch(com_error e) {bstrVal = L"";} } AfxMessageBox((LPCTSTR)bstrVal); } --James Drinking In The Sun Forgot Password?

[anju]

Hi Imran Farooqui, Thanks for Your Reply. Please need more help to me.............. Suppose i have to select entire table then how can i make all null fields in the table to 'Something'. in my application i am opening the recordset as follows pRecordset->Open("mytable", _variant_t((IDispatch*)pConnection,true), adOpenDynamic, adLockPessimistic,adCmdTable); and i am getting all the fields pRecordset->MoveFirst(); while (!pRecordset->EndOfFile) { strTemp.Format("Field1:%s",(LPSTR)(_bstr_t)pRecordset->Fields->GetItem("Field1")->Value); AfxMessageBox(strTemp); pRecordset->MoveNext(); } :rose: anju

[anju]

Hi Rama Krishna, ThankQ this technique is work to me well :rose: anju

[anju]

Hi James Pullicino, Thanks a lot.. I never thing about what u pointed. now my code is changed as per your instructions. once again thank to you :rose: anju

[Imran Farooqui]

In your code, you'r opening a complete table using RecordSet object. I do not prefer this technique because you have no control over the selected records. Instead i prefer to explicitly open the table using SQL queries. m_pRecordSet = m_pConnection->Execute("select * from mytable", &vRecsAffected, adOptionUnspecified); Only if you are accessing the table in this way, you can use, isnull option. Imran Farooqui World first Urdu Instant Messenger[^] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Java is a tool for creating applications that torture users with its awful speed and its ugly interfaces. Daniel Turini commenting on this article