CRecordset and CDBException

[MJ Cop]

I really could use some help with this problem. I am using CRecordset
to work with a SQL Server 7.0 database. In my code I try to add a record
with AddNew(). No problem. I have exception code to catch any
CDBException.

In my catch code I wanted to determine if the exception was a duplicate
record.
If it was then I was going to increment my unique serial...and call update
again because
I obviously have a duplicate.

try
{
// update the database
if(!Update())
{
Close();
return 0;
}
bDuplicate = FALSE; // achieved our goal
}
catch(CDBException* e)
{
if(m_nRetCode was useful????)
m_SerialHex++;
else
clean up and quit
}

Note this code actually will attempt twice if duplicated serial number
and then
return\quit if it cannot add a record.

The problem is that when I get a duplicate (which I wrote a test case
specifically to test this)
the error code thrown to me is SQL_ERROR. This code does not allow me to
distinguish
what happened at all. Yes, the m_strError has a description but I really
need to be able to check
the error code and act on that. I can't see checking for a string on
m_strError.

Please if someone could help me out here I would really appreciate it. If I
need to explain more I can try.

Mike

[Pavel Chuchuva]

Check out CDBException::m_strStateNativeOrigin. But I recomend you first
query your table for value you are going add or update. This is more safe.
--
With best regards, Pavel Chuchuva
MCP

"MJ Cop" <mj....@verizon.net> wrote in message
news:OrVlHDmmCHA.1256@TK2MSFTNGP12...

[Tim]

Other options to consider:

Use an Identity column or
Use a stored procedure.

- Tim

"Pavel Chuchuva" <-> wrote in message news:uchmxBpmCHA.1608@TK2MSFTNGP08...

[MJ Cop]

Thanks Pavel,

I implemented a way to extract the state and native codes from the
exception.
I am now checking for those two variables to be a certain value. If any
other
value of those I fail and return otherwise I try a new value.

In the same function I get a recordset with a WHERE clause like
WHERE serial >= thisSerial;
I then see if my serial number is there and if there are greater serial
numbers.
If this is the case I make my current serial number 1 greater than the last
record serial number field.
So, in a sense I think that is what you were saying about doing a query.

I think it would have been much better to make my serial number field the
automatic index key
that auto increments. However, do to other constraints this was just not
possible.

Thank you for your response. I greatly appreciate it. I am definitely not
a database developer.

Mike

"Pavel Chuchuva" <-> wrote in message news:uchmxBpmCHA.1608@TK2MSFTNGP08...

[MJ Cop]

Hi Tim,

Thank you for the response. Ya know I tried to figure out how to make a
stored
procedure resonsible for creating a new record for me. I wanted it to be
responsible
for creating new records with unique serial numbers. I just do not have
enough experience
with SQL Server to get it done.

Can you suggest a book that is a good tool to learn this type of
programming?

Mike

"Tim" <T...@ErgoSys.co.nz> wrote in message
news:e0XQXmqmCHA.2280@TK2MSFTNGP10...

[Tim]

Sorry, I never had a book like that except the MS issue stuff.

Perhaps make a new posting in the SQL Server news group and see what others
say... there are no doubt web sites with tutorials.

SP's aren't too difficult - just a bunch (understatement of course) of TSQL
wrapped like so:

create proc MyNewProc
AS
-- code goes here
-- EG
print 'Hello World'

GO

then

EXEC MyNewProc

The first thing to learn is that it doesn't matter how trivial what you have
to do is - IE a single SELECT statement with or without parameters benefits
from being a compiled SP.

try working on this to get started perhaps:

create proc MyInsert ... parameter list here ...
AS
declare @newID int

BEGIN TRAN

SELECT @newID = isnull(Max(idfieldname), 0) + 1 FROM YourIDTable

INSERT INTO YourIDTable (idfieldname, ... ) VALUES( @newID, ...)

IF @@ERROR = 0
COMMIT TRAN
ELSE
ROLLBACK

GO

- Tim

"MJ Cop" <mj....@verizon.net> wrote in message

news:#9lBdfvmCHA.1608@TK2MSFTNGP10...

[MJ Cop]

Thanks Tim,

I have been messing around with it and was able to do a couple
of deletes and insertions. The tricky part is that I inherited this problem
and I just hate the table structure. Everything is a varchar even our
numbers....go figure ?#$%!

So, I guess I cant figure out how to convert this number (stored as text) to
a number.
BTW The number is stored as HEX. :-) Its very easy in my C\C++ app to go
to and fro.
But I have no idea how to take this "60080000" as text and make it
"60080001" in a stored
procedure.

In my C code I just use scanf to put it into a DWORD variable like this...

sscanf( m_szHexSerial,"%08X", &m_dwSerial );
m_dwSerial++;
m_szHexSerial.Format("%08X", m_dwSerial);

I would really like to do this in a stored procedure like you have listed
here.
Also, there may be three clients running at once. Can I be assured that
they all behave? Like if they all 3 call the same time what happens?
I am hoping that they are all queued and then execute one at a time.

I dunno. I'm going to mess around with what you gave me here. I really
appreciate
your help on this. If I can ever return the favor feel free to email me. I
mostly do
engineering tools for wireless devices, Bluetooth most recently.
If you're interested in that of course.

Sincerely, Mike Cop

"Tim" <T...@ErgoSys.co.nz> wrote in message

news:eCw#aO4mCHA.2288@TK2MSFTNGP10...