ODBC SQL Server: Invalid attribute/option identifier

[Bhavin Patel]

Hi,
I am using MFC class with ODBC to connect to SQL Server. Below is my
partial code, when I do update(), I get an error "Invalid attribute/option
identifier".
--------------------------------------------------------
.Open(CRecordset::dynaset,"SELECT * FROM tblcomponentsInStream WHERE
FLUIDSTREAMSID = '233' ",CRecordset::none);
.Edit();
.Update(); //I get below error
------------------------------------------------------

"Invalid attribute/option identifier"
{"State:S1092,Native:0,Origin:[Microsoft][ODBC SQL Server Driver]
"}

Does any one have any clue about this error.

Thanks in Advance,
Bhavin

[John Bell]

Hi

It is not a good idea to use SELECT * in production code. You may want to
see if there is a Primary key on tblcomponentsInStream and check the
canUpdate member function to make sure that it is updatable. As there has
not been any change to the record so you would not want to update the record
at that point.

John

"Bhavin Patel" <bpa...@epcon.com> wrote in message
news:OLVtBuX...@TK2MSFTNGP12.phx.gbl...

[Bhavin Patel]

John:
There is a primary key in that table an it is FLUIDSTREAMSID. Also, I
have to SELECT * since I need all the fields. The other things, this code
already works greate with ORACLE, but I am trying to move to SQL server, and
it is giving me this kind of surprises.

Thank you in Advance
Bhavin

"John Bell" <jbellne...@hotmail.com> wrote in message
news:eicVhhiu...@TK2MSFTNGP10.phx.gbl...

[John Bell]

Hi

This is more to do with the ODBC driver than SQL Server itself, you may want
to check what version of MDAC you are running and that it is consistent
http://www.microsoft.com/downloads/details.aspx?FamilyId=8F0A8DF6-4A21-4B43-BF53-14332EF092C9&displaylang=en
by using the MDAC component checker.

Even if you are returning all columns you should avoid SELECT * as there are
circumstances where you will have problems if the column order changes.

John

"Bhavin Patel" <bpa...@epcon.com> wrote in message

news:ePrJ6yt...@TK2MSFTNGP10.phx.gbl...

[Bhavin Patel]

Hi
I am using
* MDAC 2.8 SP1 on Windows XP SP2
* ODBC Driver Manager (ODBC.dll) Version 3.525.1117.0

I think I am using very latest MDAC but I am not sure about my ODBC. If it
is old How can I upgrade it to latest version.

On more thing, I ran ODBC trace and I don't understand why my cursor type is
being changed.
-----------------------------trace file-------------------------------
PRV d50-6ec ENTER SQLExecute
HSTMT 0185EC08

PRV d50-6ec EXIT SQLExecute with return code 1
(SQL_SUCCESS_WITH_INFO)
HSTMT 0185EC08

DIAG [01S02] [Microsoft][ODBC SQL Server Driver]Cursor type changed (0)
DIAG [01S02] [Microsoft][ODBC SQL Server Driver]Cursor concurrency changed
(0)

---------------------Trace file---------------

PRV d50-6ec EXIT SQLSetPos with return code -1 (SQL_ERROR)
HSTMT 0185EC08
SQLSETPOSIROW SQLSETPOSIROW UWORD 2
<SQL_UPDATE>
BOOL 0 <SQL_LOCK_NO_CHANGE>

DIAG [S1092] [Microsoft][ODBC SQL Server Driver]Invalid attribute/option
identifier (0)

PRV d50-6ec ENTER SQLErrorW
HENV 01851A10
HDBC 01852F28
HSTMT 0185EC08
WCHAR * 0x0012D9AC (NYI)
SDWORD * 0x0012DC64
WCHAR * 0x0012D5AC
SWORD 511
SWORD * 0x0012DC60

PRV d50-6ec EXIT SQLErrorW with return code 0 (SQL_SUCCESS)
HENV 01851A10
HDBC 01852F28
HSTMT 0185EC08
WCHAR * 0x0012D9AC (NYI)
SDWORD * 0x0012DC64 (0)
WCHAR * 0x0012D5AC [ 70] "[Microsoft][ODBC SQL Server
Driver]Invalid attribute/option identifier"
SWORD 511
SWORD * 0x0012DC60 (70)

Thank you,
Bhavin

"John Bell" <jbellne...@hotmail.com> wrote in message

news:ebe0tBvu...@TK2MSFTNGP10.phx.gbl...

[John Bell]

Hi

This is probably all within the OPEN method if you used the default opentype
of Recordset::snapshot it would probably not appear.

From:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcmapping_replacement_functions_for_backward_compatibility_of_applications.asp

For SQLSetpos If the Operation argument (in your case SQL_UPDATE) is not
SQL_ADD, the driver returns SQLSTATE HY092 (Invalid attribute/option
identifier).

Why this is occuring I am not sure, you may want to try different Opentypes,
removing the where clause (and setting up m_strFilter!) or just specifying
the tablename.

You have not said what CanUpdate is!

It may be worth posting the DDL for your table.

John

"Bhavin Patel" <bpa...@epcon.com> wrote in message

news:elH76d0u...@TK2MSFTNGP14.phx.gbl...