OLEDB, Fail Insert, SQL Server 7 
Author Message
 OLEDB, Fail Insert, SQL Server 7

I get the error -2147467262 (Error Occured) whenever using CRowset::Insert
or CRowset::SetData.  Since "Error Occured" is not very helpful, I tried to
use CRowset::GetRowStatus.  Then, I get the error -2147217887 (Interface not
supported)!

Here's my senario...

VC++ 6
SQL Server 7
OLE DB (Don't know version?)
NT 4 Option Pack

1.  I have a few columns which NULL values are NOT allowed.  I changed those
columns to accept NULL temporarily just to simplify the senario.  And, this
should require no change in my code, right?

2.  I do have a PK and identity column which is of type numeric.   I
followed the books and changed the ATL generated code to reflect the status
as follows...
    COLUMN_ENTRY_PS_STATUS( nOrdinal, nPrecision, nScale, myID, myIDStatus )

3.  I also make sure that myIDStatus is always set to DBSTATUS_S_IGNORE
before Insert or SetData.

4.  Could it be the db init property? Here 's the ATL wizard generated
OpenDataSource...

  CDBPropSet dbinit(DBPROPSET_DBINIT);

  dbinit.AddProperty(DBPROP_AUTH_INTEGRATED, OLESTR("SSPI"));
  dbinit.AddProperty(DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO, false);
  dbinit.AddProperty(DBPROP_INIT_CATALOG, OLESTR("MyDatabase"));
  dbinit.AddProperty(DBPROP_INIT_DATASOURCE, OLESTR("MyServer"));
  dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);
  dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4);
  hr = db.Open(_T("SQLOLEDB.1"), &dbinit);

TIA
--Wen



Fri, 08 Feb 2002 03:00:00 GMT  
 OLEDB, Fail Insert, SQL Server 7
Hi!

I think you should check rowset properties. It shoud look like that:

  CDBPropSet propset(DBPROPSET_ROWSET);
  propset.AddProperty(DBPROP_IRowsetChange, true);
  propset.AddProperty(DBPROP_UPDATABILITY, DBPROPVAL_UP_CHANGE |
DBPROPVAL_UP_INSERT | DBPROPVAL_UP_DELETE);

    Cih

Quote:

>I get the error -2147467262 (Error Occured) whenever using CRowset::Insert
>or CRowset::SetData.  Since "Error Occured" is not very helpful, I tried to
>use CRowset::GetRowStatus.  Then, I get the error -2147217887 (Interface
not
>supported)!

>Here's my senario...

>VC++ 6
>SQL Server 7
>OLE DB (Don't know version?)
>NT 4 Option Pack

>1.  I have a few columns which NULL values are NOT allowed.  I changed
those
>columns to accept NULL temporarily just to simplify the senario.  And, this
>should require no change in my code, right?

>2.  I do have a PK and identity column which is of type numeric.   I
>followed the books and changed the ATL generated code to reflect the status
>as follows...
>    COLUMN_ENTRY_PS_STATUS( nOrdinal, nPrecision, nScale, myID,
myIDStatus )

>3.  I also make sure that myIDStatus is always set to DBSTATUS_S_IGNORE
>before Insert or SetData.

>4.  Could it be the db init property? Here 's the ATL wizard generated
>OpenDataSource...

>  CDBPropSet dbinit(DBPROPSET_DBINIT);

>  dbinit.AddProperty(DBPROP_AUTH_INTEGRATED, OLESTR("SSPI"));
>  dbinit.AddProperty(DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO, false);
>  dbinit.AddProperty(DBPROP_INIT_CATALOG, OLESTR("MyDatabase"));
>  dbinit.AddProperty(DBPROP_INIT_DATASOURCE, OLESTR("MyServer"));
>  dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);
>  dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4);
>  hr = db.Open(_T("SQLOLEDB.1"), &dbinit);

>TIA
>--Wen



Sat, 09 Feb 2002 03:00:00 GMT  
 OLEDB, Fail Insert, SQL Server 7
Is there an identity field in this record set?
If so, you'll need to use a separate accessor map leaving out the
COLUMN_ENTRY
that would map to the identity field.  Then you need to call Insert(nnn,
true) where nnn
is the corresponding 0-based accessor map.

Also be sure you have set your data correctly.  If you have any out of the
ordinary
types - like DBTIMESTAMP and CURRENCY - you will  have to be very careful
how you set these values.  You will get 'errors occurred' if any of them are
wrong.

Quote:

>I get the error -2147467262 (Error Occured) whenever using CRowset::Insert
>or CRowset::SetData.  Since "Error Occured" is not very helpful, I tried to
>use CRowset::GetRowStatus.  Then, I get the error -2147217887 (Interface
not
>supported)!

>Here's my senario...

>VC++ 6
>SQL Server 7
>OLE DB (Don't know version?)
>NT 4 Option Pack

>1.  I have a few columns which NULL values are NOT allowed.  I changed
those
>columns to accept NULL temporarily just to simplify the senario.  And, this
>should require no change in my code, right?

>2.  I do have a PK and identity column which is of type numeric.   I
>followed the books and changed the ATL generated code to reflect the status
>as follows...
>    COLUMN_ENTRY_PS_STATUS( nOrdinal, nPrecision, nScale, myID,
myIDStatus )

>3.  I also make sure that myIDStatus is always set to DBSTATUS_S_IGNORE
>before Insert or SetData.

>4.  Could it be the db init property? Here 's the ATL wizard generated
>OpenDataSource...

>  CDBPropSet dbinit(DBPROPSET_DBINIT);

>  dbinit.AddProperty(DBPROP_AUTH_INTEGRATED, OLESTR("SSPI"));
>  dbinit.AddProperty(DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO, false);
>  dbinit.AddProperty(DBPROP_INIT_CATALOG, OLESTR("MyDatabase"));
>  dbinit.AddProperty(DBPROP_INIT_DATASOURCE, OLESTR("MyServer"));
>  dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);
>  dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4);
>  hr = db.Open(_T("SQLOLEDB.1"), &dbinit);

>TIA
>--Wen



Sun, 10 Feb 2002 03:00:00 GMT  
 OLEDB, Fail Insert, SQL Server 7
You should add status variables to each field of your table.
Wizard doesn't do it by default. In that case you will be
able to find out which field caused error.

Stan


Quote:
> I get the error -2147467262 (Error Occured) whenever using CRowset::Insert
> or CRowset::SetData.  Since "Error Occured" is not very helpful, I tried
to
> use CRowset::GetRowStatus.  Then, I get the error -2147217887 (Interface
not
> supported)!

> Here's my senario...

> VC++ 6
> SQL Server 7
> OLE DB (Don't know version?)
> NT 4 Option Pack

> 1.  I have a few columns which NULL values are NOT allowed.  I changed
those
> columns to accept NULL temporarily just to simplify the senario.  And,
this
> should require no change in my code, right?

> 2.  I do have a PK and identity column which is of type numeric.   I
> followed the books and changed the ATL generated code to reflect the
status
> as follows...
>     COLUMN_ENTRY_PS_STATUS( nOrdinal, nPrecision, nScale, myID,
myIDStatus )

> 3.  I also make sure that myIDStatus is always set to DBSTATUS_S_IGNORE
> before Insert or SetData.

> 4.  Could it be the db init property? Here 's the ATL wizard generated
> OpenDataSource...

>   CDBPropSet dbinit(DBPROPSET_DBINIT);

>   dbinit.AddProperty(DBPROP_AUTH_INTEGRATED, OLESTR("SSPI"));
>   dbinit.AddProperty(DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO, false);
>   dbinit.AddProperty(DBPROP_INIT_CATALOG, OLESTR("MyDatabase"));
>   dbinit.AddProperty(DBPROP_INIT_DATASOURCE, OLESTR("MyServer"));
>   dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);
>   dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4);
>   hr = db.Open(_T("SQLOLEDB.1"), &dbinit);

> TIA
> --Wen



Fri, 15 Feb 2002 03:00:00 GMT  
 OLEDB, Fail Insert, SQL Server 7
Thx guys, I have figured out the error was caused by the fraction attribute
of a DBTIMESTAMP field.   Still can't figure out why GetRowStatus doesn't
work.  It seems to have something to do with the rowset property.  Using
DBPROP_IRowsetUpdate, GetRowStatus works, but SetData doesn't work.  Using
DBPROP_IRowsetChange, everything works except GetRowStatus.  I need a good
reference on these properties...the online help is of not much help here.

BTW, using a status variable for every field sounds like a good idea.  Do
you have to set the status to DBSTATUS_OK on every input?  As for parsing
the status returned by
the provider, is there any quick and dirty way to translate the DWORD into
it's English equivalent.  This applies to other error lookup routines in
windows programming too.  I always do it the brute force way - compare the
variable against the consts defined in the online help or the .h file.
There's got to be another way.

TIA
--wen


Quote:
> You should add status variables to each field of your table.
> Wizard doesn't do it by default. In that case you will be
> able to find out which field caused error.

> Stan



> > I get the error -2147467262 (Error Occured) whenever using
CRowset::Insert
> > or CRowset::SetData.  Since "Error Occured" is not very helpful, I tried
> to
> > use CRowset::GetRowStatus.  Then, I get the error -2147217887 (Interface
> not
> > supported)!

> > Here's my senario...

> > VC++ 6
> > SQL Server 7
> > OLE DB (Don't know version?)
> > NT 4 Option Pack

> > 1.  I have a few columns which NULL values are NOT allowed.  I changed
> those
> > columns to accept NULL temporarily just to simplify the senario.  And,
> this
> > should require no change in my code, right?

> > 2.  I do have a PK and identity column which is of type numeric.   I
> > followed the books and changed the ATL generated code to reflect the
> status
> > as follows...
> >     COLUMN_ENTRY_PS_STATUS( nOrdinal, nPrecision, nScale, myID,
> myIDStatus )

> > 3.  I also make sure that myIDStatus is always set to DBSTATUS_S_IGNORE
> > before Insert or SetData.

> > 4.  Could it be the db init property? Here 's the ATL wizard generated
> > OpenDataSource...

> >   CDBPropSet dbinit(DBPROPSET_DBINIT);

> >   dbinit.AddProperty(DBPROP_AUTH_INTEGRATED, OLESTR("SSPI"));
> >   dbinit.AddProperty(DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO, false);
> >   dbinit.AddProperty(DBPROP_INIT_CATALOG, OLESTR("MyDatabase"));
> >   dbinit.AddProperty(DBPROP_INIT_DATASOURCE, OLESTR("MyServer"));
> >   dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);
> >   dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4);
> >   hr = db.Open(_T("SQLOLEDB.1"), &dbinit);

> > TIA
> > --Wen



Sat, 16 Feb 2002 03:00:00 GMT  
 OLEDB, Fail Insert, SQL Server 7
You are supposed to set status to DBSTATUS_S_OK only for fields
with good data before Insert and Update. If you want to insert NULL,
just set status variable to DBSTATUS_S_ISNULL.

As for description of status, I've seen good example somewhere that reads
binding section and shows fields with wrong data automagically.

Stan

Quote:
> BTW, using a status variable for every field sounds like a good idea.  Do
> you have to set the status to DBSTATUS_OK on every input?  As for parsing



Sat, 16 Feb 2002 03:00:00 GMT  
 OLEDB, Fail Insert, SQL Server 7
If you use DBPROP_IRowsetUpdate you must call Update after calling Insert or
SetData

Quote:

>Thx guys, I have figured out the error was caused by the fraction attribute
>of a DBTIMESTAMP field.   Still can't figure out why GetRowStatus doesn't
>work.  It seems to have something to do with the rowset property.  Using
>DBPROP_IRowsetUpdate, GetRowStatus works, but SetData doesn't work.  Using
>DBPROP_IRowsetChange, everything works except GetRowStatus.  I need a good
>reference on these properties...the online help is of not much help here.

>BTW, using a status variable for every field sounds like a good idea.  Do
>you have to set the status to DBSTATUS_OK on every input?  As for parsing
>the status returned by
>the provider, is there any quick and dirty way to translate the DWORD into
>it's English equivalent.  This applies to other error lookup routines in
>windows programming too.  I always do it the brute force way - compare the
>variable against the consts defined in the online help or the .h file.
>There's got to be another way.

>TIA
>--wen



>> You should add status variables to each field of your table.
>> Wizard doesn't do it by default. In that case you will be
>> able to find out which field caused error.

>> Stan



>> > I get the error -2147467262 (Error Occured) whenever using
>CRowset::Insert
>> > or CRowset::SetData.  Since "Error Occured" is not very helpful, I
tried
>> to
>> > use CRowset::GetRowStatus.  Then, I get the error -2147217887
(Interface
>> not
>> > supported)!

>> > Here's my senario...

>> > VC++ 6
>> > SQL Server 7
>> > OLE DB (Don't know version?)
>> > NT 4 Option Pack

>> > 1.  I have a few columns which NULL values are NOT allowed.  I changed
>> those
>> > columns to accept NULL temporarily just to simplify the senario.  And,
>> this
>> > should require no change in my code, right?

>> > 2.  I do have a PK and identity column which is of type numeric.   I
>> > followed the books and changed the ATL generated code to reflect the
>> status
>> > as follows...
>> >     COLUMN_ENTRY_PS_STATUS( nOrdinal, nPrecision, nScale, myID,
>> myIDStatus )

>> > 3.  I also make sure that myIDStatus is always set to DBSTATUS_S_IGNORE
>> > before Insert or SetData.

>> > 4.  Could it be the db init property? Here 's the ATL wizard generated
>> > OpenDataSource...

>> >   CDBPropSet dbinit(DBPROPSET_DBINIT);

>> >   dbinit.AddProperty(DBPROP_AUTH_INTEGRATED, OLESTR("SSPI"));
>> >   dbinit.AddProperty(DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO, false);
>> >   dbinit.AddProperty(DBPROP_INIT_CATALOG, OLESTR("MyDatabase"));
>> >   dbinit.AddProperty(DBPROP_INIT_DATASOURCE, OLESTR("MyServer"));
>> >   dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);
>> >   dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4);
>> >   hr = db.Open(_T("SQLOLEDB.1"), &dbinit);

>> > TIA
>> > --Wen



Fri, 08 Mar 2002 03:00:00 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. ATL and OLEDB Samples for SQL Server

2. OLEDB Connection for SQL Server

3. Problem:OLEDB connect SQL Server in IP

4. OLEDB - CDynamicAccessor and Timestamp SQL Server fields

5. OLEDB SQL SERVER C++ sample code

6. OLEDB and SQL Server Functions

7. OLEDB consumer: IRowsetIndex and MS SQL Server 7.0

8. OLEDB Connection for SQL Server

9. VC++ 6 and OLEDB SQL Server

10. OLEDB, eVC and SQL Server CE

11. Trying to insert data into SQL Server

12. SQL Server: Slow INSERT performance??

 

 
Powered by phpBB® Forum Software