I know this issue has been solved before for some people. I looked all over
the net and found some solutions but they do not work for me. I am fairly new
to SQL. I am not using stored procedures and due to schedule, do not have
time to go down a learning path for that. I am using MFC and ADO.

I can add the records (with AddNew) just fine. That works.

I am stuck!

Thanks in advance for any assistance.

Here is my code:

void CTestDlg::SubmitToDbProfile(void)
{
_RecordsetPtr pRecordSet;
HRESULT hr;
CString TimeTmp;
_variant_t nRecsAffected;

_bstr_t bstrQuery("SELECT * FROM COOK.UUT_TEST_PROFILE WHERE
test_profile_id IS NULL");

_variant_t vNull;
vNull.vt = VT_ERROR;
vNull.scode = DISP_E_PARAMNOTFOUND;

try
{
hr = pRecordSet.CreateInstance(_uuidof(Recordset));

if(SUCCEEDED(hr))
{
pRecordSet->PutRefActiveConnection(m_pConnection);
hr = pRecordSet->Open(_variant_t(bstrQuery), vNull,
adOpenDynamic, adLockOptimistic, adCmdText);
if(SUCCEEDED(hr))
{
COleSafeArray vaFieldlist;
vaFieldlist.CreateOneDim(VT_VARIANT, 13);

COleSafeArray vaValuelist;
vaValuelist.CreateOneDim(VT_VARIANT, 13);

long lArrayIndex[1] = {0};

vaFieldlist.PutElement(lArrayIndex,
&(_variant_t("test_facility_id")));
vaValuelist.PutElement(lArrayIndex, &(_variant_t(1)));
lArrayIndex[0]++;

vaFieldlist.PutElement(lArrayIndex, &(_variant_t("user_id")));
vaValuelist.PutElement(lArrayIndex,
&(_variant_t(pTis->pDataPoolPP1->pUser->ID)));
lArrayIndex[0]++;

vaFieldlist.PutElement(lArrayIndex,
&(_variant_t("test_type_id")));
vaValuelist.PutElement(lArrayIndex, &(_variant_t(1)));
lArrayIndex[0]++;

vaFieldlist.PutElement(lArrayIndex,
&(_variant_t("tech_type_id")));
vaValuelist.PutElement(lArrayIndex, &(_variant_t(1)));
lArrayIndex[0]++;

vaFieldlist.PutElement(lArrayIndex,
&(_variant_t("region_id")));
vaValuelist.PutElement(lArrayIndex, &(_variant_t(1)));
lArrayIndex[0]++;

vaFieldlist.PutElement(lArrayIndex,
&(_variant_t("tag_pol_magnetic_id")));
vaValuelist.PutElement(lArrayIndex, &(_variant_t(1)));
lArrayIndex[0]++;

vaFieldlist.PutElement(lArrayIndex,
&(_variant_t("tag_speed_id")));
vaValuelist.PutElement(lArrayIndex,
&(_variant_t(pTis->pDataPoolPP2->pTagSpeed->ID)));
lArrayIndex[0]++;

vaFieldlist.PutElement(lArrayIndex,
&(_variant_t("tag_label_type_id")));
vaValuelist.PutElement(lArrayIndex, &(_variant_t(1)));
lArrayIndex[0]++;

vaFieldlist.PutElement(lArrayIndex,
&(_variant_t("grid_size_id_h")));
vaValuelist.PutElement(lArrayIndex,
&(_variant_t(pTis->pDataPoolPP2->pGridH->ID)));
lArrayIndex[0]++;

vaFieldlist.PutElement(lArrayIndex,
&(_variant_t("grid_size_id_v")));
vaValuelist.PutElement(lArrayIndex,
&(_variant_t(pTis->pDataPoolPP2->pGridV->ID)));
lArrayIndex[0]++;

vaFieldlist.PutElement(lArrayIndex,
&(_variant_t("manufacturer_id")));
vaValuelist.PutElement(lArrayIndex, &(_variant_t(1)));
lArrayIndex[0]++;

//vaFieldlist.PutElement(lArrayIndex,
&(_variant_t("tag_characterized_id")));
//vaValuelist.PutElement(lArrayIndex, &(_variant_t(NULL)));
//If characterized, that must be submitted to a table and
the index to that table submitted here
//vaValuelist.PutElement(lArrayIndex,
&(_variant_t(pTis->pDataPoolPP1->TagChar)));
//lArrayIndex[0]++;

//vaFieldlist.PutElement(lArrayIndex,
&(_variant_t("noise_inj_id")));
//vaValuelist.PutElement(lArrayIndex, &(_variant_t(NULL)));
//lArrayIndex[0]++;

//vaFieldlist.PutElement(lArrayIndex,
&(_variant_t("configuration_released")));
//vaValuelist.PutElement(lArrayIndex, &(_variant_t(NULL)));
//lArrayIndex[0]++;

//vaFieldlist.PutElement(lArrayIndex,
&(_variant_t("test_name")));
//vaValuelist.PutElement(lArrayIndex, &(_variant_t("")));
//lArrayIndex[0]++;

if(strlen(pTis->pDataPoolPP1->AddtnlNotes) > 0)
{
vaFieldlist.PutElement(lArrayIndex,
&(_variant_t("notes")));
vaValuelist.PutElement(lArrayIndex,
&(_variant_t(pTis->pDataPoolPP1->AddtnlNotes)));
lArrayIndex[0]++;
}

//markfordelete testing purposes only
pTis->TimeStart = CTime::GetCurrentTime();

vaFieldlist.PutElement(lArrayIndex,
&(_variant_t("test_date_time_start")));
vaValuelist.PutElement(lArrayIndex,
&(_variant_t(DateTime2DbFormat(&pTis->TimeStart))));
lArrayIndex[0]++;

//markfordelete testing purposes only
pTis->TimeEnd = CTime::GetCurrentTime();

vaFieldlist.PutElement(lArrayIndex,
&(_variant_t("test_date_time_end")));
vaValuelist.PutElement(lArrayIndex,
&(_variant_t(DateTime2DbFormat(&pTis->TimeEnd))));
lArrayIndex[0]++;

//vaFieldlist.PutElement(lArrayIndex,
&(_variant_t("test_height_id")));
//vaValuelist.PutElement(lArrayIndex,
&(_variant_t(pTis->pDataPoolPP2->pTestHt->ID)));
//lArrayIndex[0]++;

//vaFieldlist.PutElement(lArrayIndex,
&(_variant_t("ped_cntr_id")));
//vaValuelist.PutElement(lArrayIndex,
&(_variant_t(pTis->pDataPoolPP2->pPedCenter->ID)));
//lArrayIndex[0]++;

pRecordSet->AddNew(vaFieldlist, vaValuelist);

//-------------------------------------------------------------
//pRecordSet->Resync causes the following error:
//Exc thrown for classes generated by #import Code =
800a0cb3
//Code meaning = Unknown error 0x800A0CB3
//Source = ADODB.Recordset
//Description = Current provider does not support refreshing
underlying values.

//pRecordSet->Resync(adAffectAll, adResyncUnderlyingValues);


//-------------------------------------------------------------
//Next line: pRecordSet->Fields->GetItem... returns "EMPTY"
to _variant_t
//_variant_t var =
pRecordSet->Fields->GetItem("test_profile_id")->GetValue();


//-------------------------------------------------------------

//http://msdn.microsoft.com/en-us/library/ms677536%28VS.85%29.aspx
//If you pass the Fieldlist and Values arguments, ADO
immediately posts the
//new record to the database (no Update call is necessary);
//pRecordSet->Update();


//-------------------------------------------------------------
//pRecordSet->AbsolutePosition();
//pRecordSet->Requery(adCmdUnknown);


//-------------------------------------------------------------
//http://support.microsoft.com/kb/233299
//Starting with ADO 2.1, the ADO client cursor engine uses
this query to try
//to retrieve the new auto-increment value and place that in
the appropriate
//field in your Recordset. If you add a row to your
recordset and that
//recordset contains an auto-increment value, ADO will issue
the SELECT
//@@identity query after the INSERT INTO ... query.

//Ssooo... shouldn't need to call "SELECT @@Identity"
because ADO does it already??

//Next 3 lines: bstrQuery = "SELECT @@Identity";
conn->Execute;pRecordSet->Fields->GetItem... returns "EMPTY"

//bstrQuery = "SELECT @@Identity";
//m_pConnection->Execute(bstrQuery, &nRecsAffected,
adCmdUnknown);
//_variant_t var =
pRecordSet->Fields->GetItem("test_profile_id")->GetValue();


//-------------------------------------------------------------
_variant_t vID = pRecordSet->GetCollect("test_profile_id");
pRecordSet->Close();
}

}
}
catch( _com_error &e )
{
::CatchError(e);
}
catch(...)
{
::CatchError();
}
}

Also, I forgot to mention. I am using MS SQL 2005. I am testing with EXPRESS
on a local machine and the production server is 2005 (non-EXPRESS).

"Jimmie C." wrote:

Attempting to cross-post to:
microsoft.public.data.ado; microsoft.public.sqlserver.programming

I was told by a colleague that these were more active. Hopefully the
cross-post will be successful.

JC

"Jimmie C." wrote:

Jimmie C. (JimmieC (AT) discussions (DOT) microsoft.com) writes:
And so you get stuck with second-guessing a client API instead.

Writing a stored procedure to this is a no-brainer:

CREATE PROCEDURE insert_a_row @par1 int,
@par2 int,
@ident int OUTPUT AS
SET NOCOUNT ON
INSERT tbl(col1, col2) VALUES(@par1, @par2)

And the you use CREATE PARAMETER to pass the parameters.

If you don't have the time to learn this, you don't have the time for
much of all. Least of all to fight with AddNew.

By the way, if you are learning things, I cannot really congratulate you
to having write ADO code in C++. ADO is not really this year's technology.
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Hi Erland,

Thanks for your reply. It did not show up in the MSDN web-interface
version of the newsgroups (all 3). I logged into my MSDN account to check
it. However, it did show up in the newsgroups using the X-news reader.
Very strange ?!?!?!?

So, I am replying from X-news reader instead of my MSDN login and web
interface.

Anyway, I guess your right. Trying to find the right behavior in AddNew
by experimentation is probably more time consuming that a stored
procedure. I am going to go ahead and implement a stored procedure.

You mention that ADO is yesterday's news. I knew it was aging. What is
the current technology for C++ database interface? Or, has everything
just gone to C#? I tend to like C++.

Regards,
Jim

--------------------------------------------------------------------

Erland Sommarskog <esquel (AT) sommarskog (DOT) se> wrote in
news:Xns9C82A514D9Yazorman (AT) 127 (DOT) 0.0.1:

JC (elvis_is_king (AT) bellsouth (DOT) net) writes:
Hm, I never use the web interfaces, so I don't know if there is any
hiccup. But maybe I should check it out.

Xnews? I use it too! Luu rules!

The best option for C++ programmers is probably to use ODBC. It's
maybe a little more low-level than ADO, but if you use SQL Server Native
Client which comes with SQL Server, you get an API that has full
support for new features in SQL Server.

SQL Server Native Client is a DLL which includes both an ODBC driver
and an OLE DB provider, which makes OLE DB an option too. But OLE DB
is quite verbose, and its myriad of interfacing can be daunting. ODBC
is likely to be more straightforward. (Myself, I have only used OLE DB,
not ODBC.)

ADO sits on top of OLE DB, so you can use the SQLNCLI provider with
ADO. But ADO does not know of the new data types like xml, so you
would lose some functionality.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Erland,
------------------------------------------------------------------------
Attempting to post - 5th TIME! I don't get it. I saw the header in Xnews
once but then it was gone. Other times, it just never showed up in the
newsgroups. Maybe my late cross-post is confusing the newsgroups system
although it didn't bother it on the previous couple of posts.

This time I am posting from MSDN web interface via public.sqlserver.clients.

------------------------------------------------------------------------
As far as the web interface not being updated, it was either the cookies
or the fact that our IT department caches a lot pages locally.

We have a guy that really watches golf here. The other week he was
complaining that the online scoreboard wouldn't update on his PC due to
IT cache.

I restarted Firefox and then your SQL responses were available on the
web-interface version of the newsgroups. Even though previously, I was
clicking on the newsgroup hyperlinks, all three, which should have
reloaded the page, I did not see the replies. I have Firefox configured
to clear all private info, including cookies, on exit. After a restart, I
could see the responses in the web interface so the problem was a local
(or loco) one. However, I am sending this response from X-news anyway.

------------------------------------------------------------------------
You know, when I started with this little project, I was going to use
ODBC but I was told by some colleagues that it is depricated and that I
should use ADO as that is the next thing in the MSoft path.

Here are a few problems I have found with learning ADO:
1)GetCollect is used in a lot of examples but not well documented.

2)When there is a problem with the results, it does not give a clear
indication of what went wrong. The inner workings are not explained very
well.

3)The biggest issue is that most of the examples are in VB!!!!!

I am not a database guy so this has been an interesting, and frustrating,
learning curve. Normally, I work on embedded systems and only maintain
some lightweight Windows utilities. So this project is stretching me
somewhat (which is a good thing)!

------------------------------------------------------------------------
I created a simple stored procedure:
~~~~~~~~~~~
ALTER PROCEDURE [dbo].[usp_getidentity]
AS
BEGIN
SET NOCOUNT ON;
SELECT @@IDENTITY
RETURN @@Identity
END
~~~~~~~~~~~
I tried RETURN into a recordset first:
pRecordSet = pCmd->Execute(NULL, NULL, adCmdUnknown);
I did not get a value back. I don't recall but maybe it was NULL.

------------------------------------------------------------------------
Then I changed the procedure, keeping it very basic to start with:
~~~~~~~~~~~
ALTER PROCEDURE [dbo].[usp_getidentity]
@rtn int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SET @rtn = @@Identity
END
~~~~~~~~~~~
Examples on the web indicate that I should not return to a recordset. I
should append a return variable to the CommandPtr object. I haven't
gotten that to work yet but I am working on it. I will probably have a
specific question to post about this later as I am not having good
results.

************************************************** ********
"Erland Sommarskog" wrote:

JC (elvis****_is_***king**at**bellsouth***dot***net) writes:
Ah, that's good to know. Then I should not research any further!

When did you start the project? In 1999? :-)

It's not always easy to follow Microsoft as new things come out. ADO
certainly was king for a while, and ODBC was not really so hot. But
OLE DB never took off as Microsoft had intended. There are still many
data sources for which there is not an OLE DB provider, but there is an
ODBC driver.

Have I ever seen that one?

Yes, error diagnosis with ADO is not that good. Most of all, you don't
get all information SQL Server provides.

Yup. If you are in Visual Basic, admittedly ADO is your best choice. I
don't think that you can all the ODBC API directly from VB. But even if
can, ADO is likely to be more conventient. From VB, that is.



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

~~~~~~~~~~~~~~~~~~
Jimmie C. wrote:
"Erland Sommarskog" wrote:
~~~~~~~~~~~~~~~~~~
Hey Erland,
Thanks for your reply.

If you look at my most recent post about writing PDF files to the Db, you
will see an example where I am using GetCollect().

I got GetCollect() from the "Sams Teach Yourself Database Programming with
Visual Basic 6 in 21 Days" book... I think. I also saw it on web examples as
well. The ONLY other book I have seen for C++ and Db is "Visual C++ 6
Database Programming Tutorial" by Wendy Sarret. Old, old books to be sure,
but there is not much out there for C++!

I guess I will have to learn VB or C# or become extinct. Wait wasn't C++
supposed to be more powerful and surpass VB?!?! Oh well.

~~~~~~~~~~~~~~~~~~
On a side-note, I see that you were able to actually reply to my last post.
I tried about 7 times to get it to post. It would never show up in the
newsgroup. My guess is that you got a copy via email from the news server.
Damndest thing! I mean it just would NOT post. I the post once in Xnews on
one group and then the header in a second cross-posted group but then it was
gone... vanished. I tried and tried and then quit. Anyway, it doesn't matter
now just letting you know I tried to reply to you.

Regards,
Jim

------------------------------------------------------------------
"Erland Sommarskog" wrote:

Jimmie C. (JimmieC (AT) discussions (DOT) microsoft.com) writes:
It doesn't seem to be recognized from within Visual Basic 6, but VB6
is a kind of a sheltered world.

Learning C# and .Net is definitely not a bad idea. But there will still
be place for native code. It is after all usually faster than managed
code.

There of them made it to my newsserver.

I don't really know what could have caused your problems. I rarely have
any problems posting with Xnews. I noticed that you have an older
version, 5.something. Maybe what Luu calls the "Latest version", but
the latest version is in fact 2009.05.01, although the changes are fairly
minor, I think.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx