Very Computer

I am having a strange problem.  The user selects print from the main menu.
My printing functions work great except one part.  I open a recordset (one
is already open pointing to one table, but this is another table).  I use
the following code:

myset.m_strFilter.Format("UserID = %ld",id); //  id is a long.
if(!myset.IsOpen())
    myset.Open(CRecordset::dynaset);

this is all in a try/catch block.

It catches an error stating
    Syntax error in number in query 'UserID = 494'

I have tried putting in a number (i.e. 1, 2, 100) but I get the same result.
The strangest part is that if I put a goto in the catch and go back up to
the same line the exception is thrown (opening the recordset) it works fine!

Basically, what is happening is that whenever I try to open a table with a
query that uses a number (if I filter with a string it is fine) it doesn't
work, but every query after does.  If I were to skip this table, it gets
thrown on the next table I open, but fine after that.  It doesn't happen
anywhere else in my program (i can open and close a bunch of tables, but
when I want to go print and change to this view[a derived crecordview], it
acts funny the first time, but then is fine.)  All the views I use and
change between are derived crecordviews.

Anyone have any idea what the hell is wrong?  When an exception is thrown,
does the connection to the database get cleaned or something?  My initial
query is completely valid too, there is no illeagle characters in the
string.  I am sure of that because it works the second time.

Thanks,
    Matt

First of all there is no problem about your ommission of square brackets ..
you only need them if your column name has white space:

"[car registration] = 'N567YYY'"

try quoting the number with both single and double quotes:

myset.m_strFilter.Format("UserID = '%ld'",id) and
myset.m_strFilter.Format("UserID = \"%ld\"",id)

Now I hate the binding mechanism for the parameters in a recordset that's
why I write direct SQL !! but ...

1. Check the member variable m_nParams in your recordset is set to the
correct number of parameters you wish to bind. This is normally set in the
recordset's constructor.
2. Check that you have a statement
pFX->SetFieldType(CFieldExchange::inputParam) immediately before your RFX
function for the parameter binding
3. Check that your RFX function is of the desired type .. RFX_Long ? for the
param.

You can set a breakpoint the open statement

if(!myset.IsOpen())
 -->   myset.Open(CRecordset::dynaset);

and step into it (F11). It is very educational to see how the SQL string is
formed anyhow ... but if you step slowly and keep examining the SQL string
in your watch window you might see a syntax error creep into the SELECT
statement being built.

Quote:> Win95/98/NT
> VC++5.0
> SDI app w/database (Access 97, ODBC)

> I am having a strange problem.  The user selects print from the main menu.
> My printing functions work great except one part.  I open a recordset (one
> is already open pointing to one table, but this is another table).  I use
> the following code:

> myset.m_strFilter.Format("UserID = %ld",id); //  id is a long.
> if(!myset.IsOpen())
>     myset.Open(CRecordset::dynaset);

> this is all in a try/catch block.

> It catches an error stating
>     Syntax error in number in query 'UserID = 494'

> I have tried putting in a number (i.e. 1, 2, 100) but I get the same
result.
> The strangest part is that if I put a goto in the catch and go back up to
> the same line the exception is thrown (opening the recordset) it works
fine!

> Basically, what is happening is that whenever I try to open a table with a
> query that uses a number (if I filter with a string it is fine) it doesn't
> work, but every query after does.  If I were to skip this table, it gets
> thrown on the next table I open, but fine after that.  It doesn't happen
> anywhere else in my program (i can open and close a bunch of tables, but
> when I want to go print and change to this view[a derived crecordview], it
> acts funny the first time, but then is fine.)  All the views I use and
> change between are derived crecordviews.

> Anyone have any idea what the hell is wrong?  When an exception is thrown,
> does the connection to the database get cleaned or something?  My initial
> query is completely valid too, there is no illeagle characters in the
> string.  I am sure of that because it works the second time.

> Thanks,
>     Matt

matt