@@IDENTITY in code

Hi all,

I encountered a problem with SQL server mobile.

I am inserting a row inside a database table through C# code and need to obtain the ID of the last inserted row if successful. I have the following in my code:

SqlCeHelper.ExecuteNonQuery(connectionString, "*INSERT STATEMENT*");

Int64 id = (Int64)SqlCeHelper.ExecuteScalar(connectionString, "SELECT @.@.IDENTITY");

The variable id always ends up NULL. Is this the correct approach to this? Is there anything else I can use to obtain the id of the last inserted row in SQL server mobile?

-- The class SqlCeHelper is a wrapper that I wrote, it works 100%.

Thanks

you should be using Identity_Scope() - its better. I havent worked with SqlCE but this is what I use, and is best used than @.@.Identity, and no idea if this works for SqlCe

My apologies if it does not

I also believe, perhaps I am wrong again, that you are executing 2 different queries, hence why you are receiving null for your last query - again, I may well be totally wrong here. It's a learning curve for me also

|||

Hi,

No...unfortunately SQL mobile doesn't support the function.

I though of the two separate statements as well. Is it possible to run two different SQL statements inside one command?

Thanks

|||

OK,

Fixed the problem. It really was executing these two statements totally separately.

The correct way to fix this is to add a couple lines inside my SQLCeHelper...

I now have something like this inside the SQLCeHelper:

publicstaticobject ExecuteInsert(string connectionString, string command, paramsSqlCeParameter[] commandParameters){

object retval = null;

try{

SqlCeConnection cn = newSqlCeConnection(connectionString);

SqlCeCommand c = newSqlCeCommand(command, cn);

foreach (SqlCeParameter p in commandParameters)

c.Parameters.Add(p);

cn.Open();

c.ExecuteNonQuery();

c = newSqlCeCommand("SELECT @.@.IDENTITY AS [IDENTITY]", cn);

retval = c.ExecuteScalar();

}

catch

{

throw;

}

return retval;

}

sql

HOW: Auotincrement without using autoincrement

I have a master table which I have been updating in multiple queries to
create a new row. It has a primary key, call it WorkID. I can't make it
autoincrement. And it is more complicated because...
WorkID can also appear in two other tables, as references to the master
record, but I need to provide for the master record being gone, and as
such the records in the other two tables are orphaned. That is fine.
To create a new master record I select MAX(WorkID) unioned on all 3
tables, and increment the result, and this is my new WorkID. However,
even if in a transaction, this query work can result in duplicate WorkID
numbers when new masters are created in extremely rapid succession. I
see the flaw here. Don't think this approach can be salvaged when the
MAX and INSERT queries are done separately.
I have been racking my brain to find a way to deal with this WITHOUT
resorting to auto-increment on the master table. I can think of no
expression for an INSERT value which will take the MAX+1 of WorkID on
the three tables, but it seems there must be a way to do this in a
single statement. Something like:
INSERT WORK (WorkID) VALUES (SELECT MAX(WorkID)+1 FROM ... UNION ... )
Maybe I need a new single row, single column table that arbitrates new
WorkID values? Or maybe someone has a favorite trick?
Thx - Lee>> I have a master table .. <<
I have not heard that term since I moved from navigational DBMS to
RDBMS in thw 1970's. Let's get back to the basics of an RDBMS. Rows
are not records; fields are not columns; tables are not files. The
differences are VITAL!!!
Of course not! An exposed physical locator created inside the hardware
cannot be a relational key by definition.
Well, you need a new brain :) What is the natrual, relational key in
the SPECS YOU NEVER POSTED -- repeated SPECS YOU NEVER POSTED? I
assume that you are not soooooo ignorant that you want to have a
"magical one-size-fits-all" auto numbering.|||Wow.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1123641244.396767.117310@.g14g2000cwa.googlegroups.com...
> I have not heard that term since I moved from navigational DBMS to
> RDBMS in thw 1970's. Let's get back to the basics of an RDBMS. Rows
> are not records; fields are not columns; tables are not files. The
> differences are VITAL!!!
>
> Of course not! An exposed physical locator created inside the hardware
> cannot be a relational key by definition.
>
> Well, you need a new brain :) What is the natrual, relational key in
> the SPECS YOU NEVER POSTED -- repeated SPECS YOU NEVER POSTED? I
> assume that you are not soooooo ignorant that you want to have a
> "magical one-size-fits-all" auto numbering.
>|||On Tue, 09 Aug 2005 12:18:38 -0700, Lee Gillie wrote:

> I have been racking my brain to find a way to deal with this WITHOUT
> resorting to auto-increment on the master table. I can think of no
> expression for an INSERT value which will take the MAX+1 of WorkID on
> the three tables, but it seems there must be a way to do this in a
> single statement. Something like:
> INSERT WORK (WorkID) VALUES (SELECT MAX(WorkID)+1 FROM ... UNION ... )
> Maybe I need a new single row, single column table that arbitrates new
> WorkID values? Or maybe someone has a favorite trick?
> Thx - Lee
As Celko said, you could probably do well with a full redesign that uses a
natural key. Failing that, how about this:
INSERT WORK (WorkID)
SELECT 1+MAX(WorkID)
FROM (
SELECT MAX(WorkID) WorkID FROM WORK
UNION ALL
SELECT MAX(WorkID) WorkID FROM OtherTable1
UNION ALL
SELECT MAX(WorkID) WorkID FROM OtherTable2
)
A better way might be to keep your available unused workIDs in yet another
table. First gather all used WorkIDs in the table:
CREATE TABLE WorkIDs (WorkID int, Used bit)
INSERT INTO WorkIDs (WorkID, Used)
SELECT WorkID, 1 FROM WORK
UNION
SELECT WorkID, 1 FROM OtherTable1
UNION
SELECT WorkID, 1 FROM OtherTable2
Now add some new ones for future use:
DECLARE @.maxworkID int
DECLARE @.x int
SELECT @.maxworkID = MAX(WorkID) FROM WorkIDs
SET @.x = @.maxworkID
WHILE @.x < @.maxworkID + 10000
BEGIN
INSERT INTO WorkIDs (WorkID, Used) VALUES (@.x, 0)
SET @.x = @.x + 1
END
Now here's your procedure for creating a new workID:
BEGIN TRANSACTION
DECLARE @.newWorkID int
SELECT @.newWorkID = MIN(WorkID) FROM WorkIDs WHERE Used=0
INSERT INTO WORK (WorkID) VALUES (@.newWorkID)
UPDATE WorkIDs SET Used=1 WHERE WorkID=@.newWorkID
COMMIT TRANSACTION|||> BEGIN TRANSACTION
> DECLARE @.newWorkID int
> SELECT @.newWorkID = MIN(WorkID) FROM WorkIDs WHERE Used=0
> INSERT INTO WORK (WorkID) VALUES (@.newWorkID)
> UPDATE WorkIDs SET Used=1 WHERE WorkID=@.newWorkID
> COMMIT TRANSACTION
Actually this is the worst case scenario for a busy system unless you use
exclusive locks. Say two users start the select at the same time. Boom,
deadlock. They both have a shared read lock on the workId's, the both
insert into work and then the UPDATE tries to WorkIDs to the same value
requiring exclusive locks. (unless the Unique Key fails them in the Work
table.)

> INSERT WORK (WorkID)
> SELECT 1+MAX(WorkID)
> FROM (
> SELECT MAX(WorkID) WorkID FROM WORK
> UNION ALL
> SELECT MAX(WorkID) WorkID FROM OtherTable1
> UNION ALL
> SELECT MAX(WorkID) WorkID FROM OtherTable2
> )
This would have to single thread over ALL tables! The SELECT by default
would only take shared locks, so any two users could be getting the max
values at the same time.
This gets even worse if this transaction gets called in another transaction.
There is not a good way to do this without single threading.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Ross Presser" <rpresser@.NOSPAMgmail.com.invalid> wrote in message
news:85kyr6seugm6$.dlg@.rosspresser.dyndns.org...
> On Tue, 09 Aug 2005 12:18:38 -0700, Lee Gillie wrote:
>
> As Celko said, you could probably do well with a full redesign that uses a
> natural key. Failing that, how about this:
> INSERT WORK (WorkID)
> SELECT 1+MAX(WorkID)
> FROM (
> SELECT MAX(WorkID) WorkID FROM WORK
> UNION ALL
> SELECT MAX(WorkID) WorkID FROM OtherTable1
> UNION ALL
> SELECT MAX(WorkID) WorkID FROM OtherTable2
> )
> A better way might be to keep your available unused workIDs in yet another
> table. First gather all used WorkIDs in the table:
> CREATE TABLE WorkIDs (WorkID int, Used bit)
> INSERT INTO WorkIDs (WorkID, Used)
> SELECT WorkID, 1 FROM WORK
> UNION
> SELECT WorkID, 1 FROM OtherTable1
> UNION
> SELECT WorkID, 1 FROM OtherTable2
> Now add some new ones for future use:
> DECLARE @.maxworkID int
> DECLARE @.x int
> SELECT @.maxworkID = MAX(WorkID) FROM WorkIDs
> SET @.x = @.maxworkID
> WHILE @.x < @.maxworkID + 10000
> BEGIN
> INSERT INTO WorkIDs (WorkID, Used) VALUES (@.x, 0)
> SET @.x = @.x + 1
> END
> Now here's your procedure for creating a new workID:
> BEGIN TRANSACTION
> DECLARE @.newWorkID int
> SELECT @.newWorkID = MIN(WorkID) FROM WorkIDs WHERE Used=0
> INSERT INTO WORK (WorkID) VALUES (@.newWorkID)
> UPDATE WorkIDs SET Used=1 WHERE WorkID=@.newWorkID
> COMMIT TRANSACTION|||On Thu, 11 Aug 2005 21:39:02 -0500, Louis Davidson wrote:
[snip]

> This gets even worse if this transaction gets called in another transactio
n.
> There is not a good way to do this without single threading.
Thanks for your comments. So, is there any decent answer to the problem of
generating new WorkIDs without using IDENTITY?

::FN_DBLOG() - Problem in retreiving [row data]

Subj: ::FN_DBLOG() - Problem in retreiving [row data]
Hello
While attempting to retrieve [row data] column using ::FN_DBLOG
an empty '0x' value is passed back:
select [Current Lsn], [row data] from ::fn_dblog(null,null)
The retrived record does contain row data. It can be seen when using
DBCC LOG.
However - DBCC LOG is not as versatile as ::FN_DBLOG(): which supports
a standard SQL query. In addition - DBCC seems to be a more resource
consuming
piece.
Please do not propse a 3rd party software solution.
I need the [row data] 'as is' in its very basic binary raw format and
::FN_DBLOG() fits exactly to my needs.
Any help will be appreciated.
Regards
Hillel.Both fn_dblog() and dbcc log are undocumented and therefore unsupported
features of SQL Server, so in all cases you are on you own.
If you can send me a repro I will look at this. What are you trying to do
with that information anyhow?
GertD@.SQLDev.Net
"Hillel" <hillel.eilat@.gmail.com> wrote in message
news:1144768876.281792.244330@.e56g2000cwe.googlegroups.com...
> Subj: ::FN_DBLOG() - Problem in retreiving [row data]
> Hello
> While attempting to retrieve [row data] column using ::FN_DBLOG
> an empty '0x' value is passed back:
> select [Current Lsn], [row data] from ::fn_dblog(null,null)
> The retrived record does contain row data. It can be seen when using
> DBCC LOG.
> However - DBCC LOG is not as versatile as ::FN_DBLOG(): which supports
> a standard SQL query. In addition - DBCC seems to be a more resource
> consuming
> piece.
> Please do not propse a 3rd party software solution.
> I need the [row data] 'as is' in its very basic binary raw format and
> ::FN_DBLOG() fits exactly to my needs.
> Any help will be appreciated.
> Regards
> Hillel.
>sql

::FN_DBLOG - Retreiving [row data]

Subj: ::FN_DBLOG - Retreiving [row data]
Hello
While attempting to retrieve [row data] column using ::FN_DBLOG
an empty '0x' value is passed back.
The retrived record does contain row data. It can be seen when using DBCC LO
G.
However - DBCC LOG is not as versatile as ::FN_DBLOG(): which supports
a standard SQL query. In addition - DBCC seems to be a more resource
consuming
piece.
Please do not propse a 3rd party software solution.
I need the [row data] 'as is' in its very basic binary raw format and ::
FN_DBLOG() fits exactly to my needs.
Any help will be appreciated.
Regards
Hillel.Hi hillel,
I don't know exactly what you are looking for but launching this you can
recover the log info:
SELECT * FROM ::fn_dblog(null,null)
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"hillel" wrote:

> Subj: ::FN_DBLOG - Retreiving [row data]
> Hello
> While attempting to retrieve [row data] column using ::FN_DBLOG
> an empty '0x' value is passed back.
> The retrived record does contain row data. It can be seen when using DBCC
LOG.
> However - DBCC LOG is not as versatile as ::FN_DBLOG(): which supports
> a standard SQL query. In addition - DBCC seems to be a more resource
> consuming
> piece.
> Please do not propse a 3rd party software solution.
> I need the [row data] 'as is' in its very basic binary raw format and ::
> FN_DBLOG() fits exactly to my needs.
> Any help will be appreciated.
> Regards
> Hillel.
>|||Thank you Enric.
My question is very specific.
In SQL 2000 - there are 2 interfaces by which I can access the transaction
LOG:
DBCC LOG is not a very convinient API in many aspects.
::FN_DBLOG() is much more tempting but - surprisingly - it does not display
the [row data] columns - as seen below:
SELECT [Current LSN],[Transaction ID],[Object Name],[row data]
FROM ::fn_dblog(null,null) where operation='LOP_INSERT_ROWS'
0000000b:0000018b:0002 0000:000005bf dbo
.jobs (1977058079) 0x
0000000b:00000190:0002 0000:000005c1 dbo
.jobs (1977058079) 0x
0000000b:00000190:0003 0000:000005c1 dbo
.jobs (1977058079) 0x
There is data associated with these records - as seen below:
dbcc log(DB5,3,logrecs,'LOP_INSERT_ROWS')
0000000b:0000018b:0002 ...0000:000005bf... dbo.jobs (1977058079)...
0x3000080025004749040000010030004261636B
75705F313931393139313931393139313931
3931393139313931393139
Did I miss something in the ::FN_DBLOG() invocation - or- what...?
Thanks
Hillel.
"Enric" wrote:
> Hi hillel,
> I don't know exactly what you are looking for but launching this you can
> recover the log info:
> SELECT * FROM ::fn_dblog(null,null)
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''s hard to provide information
> without seeing the code. location: Alicante (ES)
>
> "hillel" wrote:
>

**to trace what has happened**

Hi
In SQL2000, how can I find what has happened in my DB in particular date?
for example I insert a ne row in table1 and I update 2 rows in table2. now
how can find what did I perfome?
I don't want to monitor the changes simentanously, I want to refer to them
afre some times.
should I refer to .log file of my db?(if ye ,how?) or should I do
something else?
any help would be thanked.Hi,
SQL Server will not log the events by default. In this case probably you can
write triggers to audit the Delete/Insert and Update events.
Thanks
Hari
SQL Server MVP
"M" <rez1824@.yahoo.co.uk> wrote in message
news:op.tf2mdvein9ig5y@.system109.parskhazar.net...
> Hi
> In SQL2000, how can I find what has happened in my DB in particular date?
> for example I insert a ne row in table1 and I update 2 rows in table2. now
> how can find what did I perfome?
> I don't want to monitor the changes simentanously, I want to refer to them
> afre some times.
> should I refer to .log file of my db?(if ye ,how?) or should I do
> something else?
> any help would be thanked.|||you could enable c2 auditing as many gov types are forced to do.
mike.menard
"M" <rez1824@.yahoo.co.uk> wrote in message
news:op.tf2mdvein9ig5y@.system109.parskhazar.net...
> Hi
> In SQL2000, how can I find what has happened in my DB in particular date?
> for example I insert a ne row in table1 and I update 2 rows in table2. now
> how can find what did I perfome?
> I don't want to monitor the changes simentanously, I want to refer to them
> afre some times.
> should I refer to .log file of my db?(if ye ,how?) or should I do
> something else?
> any help would be thanked.|||I think it does it, cause when we set the database in full mode it somehow
save the changes so it can use it when it wants to restore the db based on
the last changes.
is it right?
in other words, if I want to know what has happened during a period of
time should I save the changes as a log file, or is there any place to
have it systematically?
thanks
On Mon, 18 Sep 2006 15:17:22 +0330, Hari Prasad
<hari_prasad_k@.hotmail.com> wrote:

> Hi,
> SQL Server will not log the events by default. In this case probably you
> can
> write triggers to audit the Delete/Insert and Update events.
> Thanks
> Hari
> SQL Server MVP
> "M" <rez1824@.yahoo.co.uk> wrote in message
> news:op.tf2mdvein9ig5y@.system109.parskhazar.net...
>
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/|||Have a look at these:
http://sqlserver2000.databases.aspf...erver-data.html
Andrew J. Kelly SQL MVP
"M" <rez1824@.yahoo.co.uk> wrote in message
news:op.tf33aps8n9ig5y@.system109.parskhazar.net...
>I think it does it, cause when we set the database in full mode it somehow
>save the changes so it can use it when it wants to restore the db based on
>the last changes.
> is it right?
> in other words, if I want to know what has happened during a period of
> time should I save the changes as a log file, or is there any place to
> have it systematically?
> thanks
> On Mon, 18 Sep 2006 15:17:22 +0330, Hari Prasad
> <hari_prasad_k@.hotmail.com> wrote:
>
>
> --
> Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

(RS 2000) Row Count in Header?

I am using Reporting Services 2000 and need to know if there is a way to
list the number of rows returned in the Header or Footer?
Any help is greatly appreciated.
- CarlCarl,
If you use the COUNT function in SSRS, it should calculate that for
you. In one of the header/footer cells, make the expression something
like: =Count(Fields!fieldName.Value)
You can make these more informative by putting a descriptor after the
count value like: =Count(Fields!fieldName.Value) &
Iif(Count(Fields!fieldName.Value) = 1, " Employee", " Employees")
Also, the count function just looks within its own scope. If you use a
table header, it looks at the whole table. If you use a group header,
it looks just within its group.
Hope this helps!
-Josh
Vagabond Software wrote:
> I am using Reporting Services 2000 and need to know if there is a way to
> list the number of rows returned in the Header or Footer?
> Any help is greatly appreciated.
> - Carl|||"Josh" <bell.joshua@.gmail.com> wrote in message
news:1155300982.086540.318880@.p79g2000cwp.googlegroups.com...
> Carl,
> If you use the COUNT function in SSRS, it should calculate that for
> you. In one of the header/footer cells, make the expression something
> like: =Count(Fields!fieldName.Value)
> You can make these more informative by putting a descriptor after the
> count value like: =Count(Fields!fieldName.Value) &
> Iif(Count(Fields!fieldName.Value) = 1, " Employee", " Employees")
> Also, the count function just looks within its own scope. If you use a
> table header, it looks at the whole table. If you use a group header,
> it looks just within its group.
> Hope this helps!
> -Josh
Though I couldn't use the COUNT function in the Header or the Footer, I was
able to make space in the Body, above the table, to use the function.
Thanks for the help.
- Carl|||Carl,
Glad you got it working, but you might want to further investigate the
Count problem... I borrowed a friends workstation that has SSRS 2000
and confirmed that the Count function DOES work in that version of the
product.
I made a simple query that returned 1 field and put a Count expression
in the table footer. It displayed the correct number.
Best wishes!
-Josh
Vagabond Software wrote:
> "Josh" <bell.joshua@.gmail.com> wrote in message
> news:1155300982.086540.318880@.p79g2000cwp.googlegroups.com...
> >
> > Carl,
> >
> > If you use the COUNT function in SSRS, it should calculate that for
> > you. In one of the header/footer cells, make the expression something
> > like: =Count(Fields!fieldName.Value)
> >
> > You can make these more informative by putting a descriptor after the
> > count value like: =Count(Fields!fieldName.Value) &
> > Iif(Count(Fields!fieldName.Value) = 1, " Employee", " Employees")
> >
> > Also, the count function just looks within its own scope. If you use a
> > table header, it looks at the whole table. If you use a group header,
> > it looks just within its group.
> >
> > Hope this helps!
> >
> > -Josh
>
> Though I couldn't use the COUNT function in the Header or the Footer, I was
> able to make space in the Body, above the table, to use the function.
> Thanks for the help.
> - Carl|||"Josh" <bell.joshua@.gmail.com> wrote in message
news:1155530591.436146.248650@.75g2000cwc.googlegroups.com...
> Carl,
> Glad you got it working, but you might want to further investigate the
> Count problem... I borrowed a friends workstation that has SSRS 2000
> and confirmed that the Count function DOES work in that version of the
> product.
> I made a simple query that returned 1 field and put a Count expression
> in the table footer. It displayed the correct number.
> Best wishes!
> -Josh
I will keep playing with it, but if I add a Textbox to the footer and
attempt to use the Count expression in the Value property of that Textbox, I
get a build error something like this:
"The value expression for the textbox 'textbox15' refers to a field. Fields
cannot be used in page headers or footers."
Thanks for all the help.
- carl|||Carl,
You are adding the textbox to the PAGE footer, not the table footer.
The table is bound to a dataset, so you can reference a field in the
table footer by using "Fields!fieldName.Value" without having to
specify the dataset to which that field belongs.
If you add a textbox to the body, page header, or page footer, you need
to reference the dataset as well. Usually, one you do this, it has to
return a scalar value, so you might be forced to use a First, Last,
Sum, Count, etc. function to condense the result into a single value.
If you use the expression builder (right-click on the textbox and
select Expression), you can click on Datasets on the left and see your
options. If it is a text datatype, I think it defaults to First; if it
is a number datatype, I think it defaults to Sum. The expression will
automatically add the name of the dataset using the correct syntax.
If you need it in the PAGE footer, try this approach. If you meant it
to be a table footer, it should be a simple
Count(Fields!fieldName.Value).
Any closer?
-Josh
Vagabond Software wrote:
> "Josh" <bell.joshua@.gmail.com> wrote in message
> news:1155530591.436146.248650@.75g2000cwc.googlegroups.com...
> >
> > Carl,
> >
> > Glad you got it working, but you might want to further investigate the
> > Count problem... I borrowed a friends workstation that has SSRS 2000
> > and confirmed that the Count function DOES work in that version of the
> > product.
> >
> > I made a simple query that returned 1 field and put a Count expression
> > in the table footer. It displayed the correct number.
> >
> > Best wishes!
> >
> > -Josh
> I will keep playing with it, but if I add a Textbox to the footer and
> attempt to use the Count expression in the Value property of that Textbox, I
> get a build error something like this:
> "The value expression for the textbox 'textbox15' refers to a field. Fields
> cannot be used in page headers or footers."
> Thanks for all the help.
> - carl