Discussion:
ODBC SQL Server: Invalid attribute/option identifier
(too old to reply)
Bhavin Patel
2005-09-14 22:27:23 UTC
Permalink
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
2005-09-15 19:06:03 UTC
Permalink
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
Post by 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
Bhavin Patel
2005-09-16 16:35:46 UTC
Permalink
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
Post by 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
Post by 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
2005-09-16 18:58:04 UTC
Permalink
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
Post by Bhavin Patel
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
Post by 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
Post by 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
Bhavin Patel
2005-09-17 05:19:55 UTC
Permalink
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
Post by 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
Post by Bhavin Patel
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
Post by 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
Post by 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
2005-09-17 06:39:46 UTC
Permalink
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
Post by 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
Post by 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
Post by Bhavin Patel
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
Post by 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
Post by 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
Loading...