Access Query Max Function Return Value

[Landon]

Hi I use Visual C++ MFC 4.2.

I need to get the last support_code from the table so I make query like this:
CString sSQL;
sSQL.Format( "SELECT MAX( support_no ) AS \"last\" FROM history WHERE
support_no LIKE \'%s*\'", sYear );

I have tested it in the MS Access and it worked fine.

It should return Support Code in the form like this: "S08-00012" so it has S
and - characters inside.

Now. the problem is from the programming side.

rs.Open( CRecordset::forwardOnly, ( sSQL ) );

CString sSupportNo;
CString sNextCounter;

if( !rs.IsEOF() ){
rs.GetFieldValue( "last", varSupportNo );
ASSERT( varSupportNo.m_dwType == DBVT_STRING );

sSupportNo = *varSupportNo.m_pstring;
}

The error is at the ASSERT part. I know the error but I don't know how to
fix it.
The point is that the value of field "last" is not a CString. I don't know
what it is, because I also have tried DOUBLE type it neither work.

What is the return value of MAX to my code?

Thank you very much.

 

[Brendan Reynolds]

Hi I use Visual C++ MFC 4.2.

I need to get the last support_code from the table so I make query like
this:
CString sSQL;
sSQL.Format( "SELECT MAX( support_no ) AS \"last\" FROM history WHERE
support_no LIKE \'%s*\'", sYear );

I have tested it in the MS Access and it worked fine.

It should return Support Code in the form like this: "S08-00012" so it has
S
and - characters inside.

Now. the problem is from the programming side.

rs.Open( CRecordset::forwardOnly, ( sSQL ) );

CString sSupportNo;
CString sNextCounter;

if( !rs.IsEOF() ){
rs.GetFieldValue( "last", varSupportNo );
ASSERT( varSupportNo.m_dwType == DBVT_STRING );

sSupportNo = *varSupportNo.m_pstring;
}

The error is at the ASSERT part. I know the error but I don't know how to
fix it.
The point is that the value of field "last" is not a CString. I don't know
what it is, because I also have tried DOUBLE type it neither work.

What is the return value of MAX to my code?

Thank you very much.

In VB or VBA it would be a Variant. I'm not sure what the C++ equivalent
would be?

You could explicitly convert the value to a string in the SQL statement ...

SELECT CStr(Max(support_no)) ...

.... but that would fail if there were no matching records, as Max() would
return Null, which cannot be assigned to a string. (Null in JET and VB is
not, as I understand it, the same as null in C#, and may well not be the
same as null in C++, if there is a null in C++, I'm not familiar enough with
C++ to know).

In C# and ADO.NET, I'd handle it by testing the return value using IsDbNull
before attempting to assign it to a string, but again I'm not sure what the
C++ equivalent would be.