How do I get the unique ID after insert?

can you post the schema for MCRS_MOBILE_DATA_HDR  table ?

You can use something like..

You may have some identity column in MCRS_MOBILE_DATA_HDR  table, lets say Id..
 

SELECT MAX(Id)  LatestId FROM MCRS_MOBILE_DATA_HDR

You have it already: It's tbRAF.Text.

I think tbRAF.Text is a text field.

The relational theory does not impose any special data types for a candidate key.

Yes.    tbRaf.Text is just a text field.

The column I have is REF and is an autonumbered field so creates a unique reference.


Does this look ok?


 SqlCeConnection thisConnection = new SqlCeConnection(connLOCDAT);
                    SqlCeCommand thisCommand = new SqlCeCommand("SELECT MAX(REF) FROM MCRS_MOBILE_DATA_HDR where store = " + (cbStore.SelectedItem as ComboBoxItem).Value + " AND Status =0 Order by Date_Added DESC", thisConnection);

                    thisConnection.Open();
                    SqlCeDataReader thisReader = thisCommand.ExecuteReader();
           
                    thisReader.Read();
                    varREF = Convert.ToInt32(thisReader.GetValue(0));

                    thisConnection.Close();


                    if (!varREF == null) {
                        tbREFNo.Text == varREF;
                    }

Sorry, should have been clear..


Do the update first.  Then do code I have just pasted.

Yes it will work , a small modification..giving name to that column.

SqlCeCommand thisCommand = new SqlCeCommand("SELECT MAX(REF) REF FROM MCRS_MOBILE_DATA_HDR where store = " + (cbStore.SelectedItem as ComboBoxItem).Value + " AND Status =0 Order by Date_Added DESC", thisConnection);

Nope. When Merchandiser is not a candidate key, then you could pick up a row inserted by a concurrent process.

Also, as long as you don't use a trigger and a sequence to generate that REF value, there is no guarantee that it is strictly increasing.

btw, what database you use? SqlCeConnection works imho only with SQL Server Compact Edition. Not MySQL.

Thanks.

Can I also ask about a better way to do this code..


 varREF = Convert.ToInt32(thisReader.GetValue(0));

                                          if (!varREF == null) {
                         tbREFNo.Text == varREF;
                     }

Can I incorporate the if check ?

Different question. But use TryParse().

try output INSERTED.columnname in your sql

string sql = @"Insert Into MCRS_MOBILE_DATA_HDR (Merchandiser)  output INSERTED.ID VALUES(" + tbRAF.Text + ");

................
 int modified=     cmd.ExecuteNonQuery();
         

Select allOpen in new window

I am using SQL compact.

I was using this to get the last row..

SELECT TOP(1) Ref

I was hoping to incorporate some code that automatically retrieves the autonumbered data immediately after insert rather than having to query the database again.

The OUTPUT INSERTED is T-SQL of SQL Server, not SQL Server Compact nor MySQL..

So as it is SQL Server CE, use @@IDENTITY:

string sql = @"Insert Into MCRS_MOBILE_DATA_HDR (Merchandiser) VALUES(" + tbRAF.Text + ");
SqlCeConnection conn = new SqlCeConnection(connLOCDAT);
SqlCeCommand cmd = new SqlCeCommand(strSql, conn);
conn.Open();
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT @ID = @@IDENTITY"
SqlParameter ID = new SqlParameter("@ID", SqlDbType.Int);
ID.Direction = ParameterDirection.Output;
cmd.Parameters.Add(ID);
cmd.ExecuteNonQuery();
int NewID = (int)ID.Value;
conn.Close();

Select allOpen in new window

in mysql i think need to run

SELECT LAST_INSERT_ID();

Select allOpen in new window

Instead of ExecuteNonQuery method use ExecuteScalar method so it will return the value of the first column that's the ID in your case:

string sql = @"Insert Into MCRS_MOBILE_DATA_HDR (Merchandiser) VALUES(" + tbRAF.Text + ");
int newID;

SqlCeConnection conn = new SqlCeConnection(connLOCDAT);
SqlCeCommand cmd = new SqlCeCommand(strSql, conn);

conn.Open();
newID = cmd.ExecuteScalar();
conn.Close();

Select allOpen in new window

@Author - Is MAX(Column) doesn't work for you ?

MAX didn't work I'm afraid.

@Vitor - I get this error:

What is the data type from your ID column? I used INT assuming that's an integer.

Its an INT and it is set as an autonumbered column

The name is "REF"

In that case MAX(Ref) should work for you.

SELECT MAX(Ref) Ref FROM MCRS_MOBILE_DATA_HDR.

@Vitor: Does SQL CE support multiple statements in one command?

So isn't working then. Try this variant please:

string sql = @"Insert Into MCRS_MOBILE_DATA_HDR (Merchandiser) VALUES(" + tbRAF.Text + ");  SELECT SCOPE_IDENTITY()"
int newID;

SqlCeConnection conn = new SqlCeConnection(connLOCDAT);
SqlCeCommand cmd = new SqlCeCommand(strSql, conn);

conn.Open();
newID = cmd.ExecuteScalar();
conn.Close();

Select allOpen in new window

I'm not sure if  SELECT SCOPE_IDENTITY() works for SQL CE but it worth a try.

can you provide the line on which the error is coming, its .Net error ..

@Vitor..

Did you change anything?

I still get this error:

Error      1      Cannot implicitly convert type 'object' to 'int'. An explicit conversion exists (are you missing a cast?)      C:\Users\FFtilsbl\Documents\Visual Studio 2010\Projects\MCRS_CSV_LATEST\MCRS_CSV\Form1.cs      586      33      MCRS_CSV

Sorry, my fault. It's even in the MSDN help article to implicit use the convert function:

string sql = @"Insert Into MCRS_MOBILE_DATA_HDR (Merchandiser) VALUES(" + tbRAF.Text + ");
int newID;

SqlCeConnection conn = new SqlCeConnection(connLOCDAT);
SqlCeCommand cmd = new SqlCeCommand(strSql, conn);

conn.Open();
newID = Convert.ToInt32(cmd.ExecuteScalar());
conn.Close();

Select allOpen in new window

Yes public override object ExecuteScalar() returns an object.

As per msdn It Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

Thanks for this..

However - after debugging.. I noticed that the 'NEWID' = 0 after insert.   I checked and there is a new record in the database.

So the table was empty? If so you can always add +1 to get the last value: newID = Convert.ToInt32(cmd.ExecuteScalar()) +1;.

Or try the version with the SELECT  SCOPE_IDENTITY() (check the end of string sql statement):

string sql = @"Insert Into MCRS_MOBILE_DATA_HDR (Merchandiser) VALUES(" + tbRAF.Text + ");
 SELECT  SCOPE_IDENTITY();"
int newID;

SqlCeConnection conn = new SqlCeConnection(connLOCDAT);
SqlCeCommand cmd = new SqlCeCommand(strSql, conn);

conn.Open();
newID = Convert.ToInt32(cmd.ExecuteScalar());
conn.Close();

Select allOpen in new window

I think ExecuteScalar will NOT work. because it will Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

It will not return you the MAX Ref.

REF - https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar(v=vs.110).aspx

The tables was not empty.

The record created was number "29".

I'll try the SCOPE_IDENTITY.

Where does that code go?  with the sql string?

1:string sql = @"Insert Into MCRS_MOBILE_DATA_HDR (Merchandiser) VALUES(" + tbRAF.Text + ") SELECT  SCOPE_IDENTITY();";

Select allOpen in new window

Check the example. It's about an identity value and is what the author want:
"The function returns the new Identity column value if a new row was inserted, 0 on failure."

Actually, 0 means failure.

Where does that code go?  with the sql string?

You need to use a semicolon to separate the INSERT from the SELECT so it will return the latest:

string sql = @"Insert Into MCRS_MOBILE_DATA_HDR (Merchandiser) VALUES(" + tbRAF.Text + ");
 SELECT  SCOPE_IDENTITY();"

Try this once..

string sql = @"Insert Into MCRS_MOBILE_DATA_HDR (Merchandiser) VALUES(" + tbRAF.Text + ") SELECT  IDENT_CURRENT( 'MCRS_MOBILE_DATA_HDR' );";

IDENT_CURRENT() gives the last identity inserted. If another user is running the same code at same time you can't know if the returned value is the one you want and that's why you should use SELECT  SCOPE_IDENTITY() because is returning the identity value used in the same scope of the insert command so no way to get the wrong information.

I get this error..

I am surprise that it took so many answers to get to the obvious solution that is what Vitor posted above. If you insert rows into a table that has an identity column the best and easiest way to return the last identity value that was generated is to use the SCOPE_IDENTITY() function right after the insert:

INSERT INTO table VALUES (...)
SELECT SCOPE_IDENTITY() as LastID

If you execute from any application this script and grab the returned value it is all you need.

You are running this in .Net, but that is for SSMS.

Thats a query ..

string sql = @"SELECT  SCOPE_IDENTITY();";

I get this error..

You can't run that like that. Use it inside the same variable where you have the Insert command. That's the idea, to run immediately after the Insert.

ok.   I'm getting a little lost - sorry!


Is anyone able to tell me exactly what I need here?

Here's what I have and this errors...

Where am I going wrong?

 string sql = @"Insert Into MCRS_MOBILE_DATA_HDR (Merchandiser) VALUES('" + strCompany[1] + "') SELECT  SCOPE_IDENTITY();";
                       
                        
                        int NewID;
                        SqlCeConnection conn = new SqlCeConnection(connLOCDAT);
                        SqlCeCommand cmd = new SqlCeCommand(sql, conn);

                        conn.Open();
                        NewID = Convert.ToInt32(cmd.ExecuteScalar());
                        conn.Close();

Select allOpen in new window

Again, you're missing a semicolon (;) before the SELECT keyword:

string sql = @"Insert Into MCRS_MOBILE_DATA_HDR (Merchandiser) VALUES(" + tbRAF.Text + "); SELECT  SCOPE_IDENTITY();";

Replace your code with the above and it should work.

I am no expert in C# code but I think you have some errors in tha one you posted in your question. That @ is not necessary, that text has to be wrapped in single quotes, ' , and the sql string value that it is assigned needs to be ended with "So your code should look something like this:

string sql = "Insert Into MCRS_MOBILE_DATA_HDR (Merchandiser) VALUES('" + tbRAF.Text + "'); SELECT SCOPE_IDENTITY() as LastID;"

SqlCeConnection conn = new SqlCeConnection(connLOCDAT);
            SqlCeCommand cmd = new SqlCeCommand(strSql, conn);

            conn.Open();
            cmd.ExecuteNonQuery();
			 
			<here you will add code to get the value returned by the execution of the sql>
			  
            conn.Close();

Select allOpen in new window

sorry - should have attached the error..

sorry - should have attached the error..

Did you add the semicolon before the SELECT keyword?

This is exactly what I have...

 string sql = @"Insert Into MCRS_MOBILE_DATA_HDR (Merchandiser) VALUES('" + strCompany[1] + "');SELECT SCOPE_IDENTITY();"; 
                      
 int NewID;
                        SqlCeConnection conn = new SqlCeConnection(connLOCDAT);
                        SqlCeCommand cmd = new SqlCeCommand(sql, conn);

                        conn.Open();
                        NewID = Convert.ToInt32(cmd.ExecuteScalar());
                        conn.Close();

Select allOpen in new window

The error:

string sql = @"Insert Into MCRS_MOBILE_DATA_HDR (Merchandiser) VALUES('" + strCompany[1]  "');SELECT SCOPE_IDENTITY();";

Zberteoc called attention for the @. Why do you need it in the INSERT string?
Can you also print the sql command before running it?

Remove the @.

string sql = "Insert Into MCRS_MOBILE_DATA_HDR (Merchandiser) VALUES('" + strCompany[1]  "'); SELECT SCOPE_IDENTITY();";

Why do you have that? Is it some .NET convention that I am not aware of?

Would it be simpler if I just killed myself? ;)

Would it be simpler if I just killed myself? ;)

Only after you have this working, ok? :)

This is the SQL code.

		sql	"Insert Into MCRS_MOBILE_DATA_HDR (Merchandiser) VALUES('stuabuck');SELECT SCOPE_IDENTITY();"	string

Select allOpen in new window

It's look ok to me.
Is it possible for you to copy the SQL code and execute it directly in the database, so we can see if returns error there?

After removing the "@"

I get the Token error still.

"There was an error parsing the query. [ Token line number = 1,Token line offset = 266,Token in error = SELECT ]"


Once again the SQL (taken whilst debugging)..
"Insert Into MCRS_MOBILE_DATA_HDR (Merchandiser) VALUES('stuabuck');SELECT SCOPE_IDENTITY();"

Ok, maybe the SCOPE_IDENTITY doesn't work in SQL CE. Can you replace it with "SELECT @@IDENTITY"?

I tried it 2 times with different variations..

 string sql = "Insert Into MCRS_MOBILE_DATA_HDR (Merchandiser) VALUES( '" + tbTotBearer.Text + "');SELECT @@IDENTITY();";
                       

 string sql = "Insert Into MCRS_MOBILE_DATA_HDR (Merchandiser) VALUES( '" + tbTotBearer.Text + "');SELECT @@IDENTITY;";


Both get the same error:

Error:

There was an error parsing the query. [ Token line number = 1,Token line offset = 266,Token in error = SELECT ]

if I run directly in SQLCE..

What exactly were you running in SQLCE? Can you post it here?

*goes and gets shotgun*

;-)

Insert Into MCRS_MOBILE_DATA_HDR (Merchandiser) VALUES('stuabuck');SELECT @@IDENTITY();

SQL CE cannot imho execute multi statement batches..
Thus my sample using a separate cmd execution...

Try this:

Insert Into MCRS_MOBILE_DATA_HDR (Merchandiser) VALUES('stuabuck')
SELECT SCOPE_IDENTITY()

Remove ; and put them on 2 different lines.

What is strange is that the rows are actually being created in the database

Ok, for sure SCOPE_IDENTITY() is not supported but @@IDENTITY is:

https://msdn.microsoft.com/en-us/library/ms174077(v=sql.105).aspx

So use:

Insert Into MCRS_MOBILE_DATA_HDR (Merchandiser) VALUES('stuabuck')
SELECT @@IDENTITY

@Zberteoc,


When I run just this..

Insert Into MCRS_MOBILE_DATA_HDR (Merchandiser) VALUES('stuabuck')

It works fine.



If I add .. SELECT @@IDENTITY .

So I run this..

Insert Into MCRS_MOBILE_DATA_HDR (Merchandiser) VALUES('stuabuck')
 SELECT @@IDENTITY


I get the token error again.

It doesn't seem to like the @@IDENTITY.

Perhaps its a version issue?

It does not like two statements in one call..

Take a look at my example.

It seems you need to go as ste5an says in his post, ID: 41831595, and use that code.

But you will need to get rid of the @ still...

The only problem with that is using @@IDENTITY in a separate call might give wrong value if somebody else does that at the same time. I might be wrong, though.

@ste5an -

Just tried youe examples..

@Ste5an - and apologies as I didn't see your solution earlier.

@Zberteoc: It works, cause we use the same connection.

No, don't reconnect. Reuse the existing connection without closing it.

What is the status now ? Which solution we are trying - Vitor sir one or something else?

Okay So I have done this from Scratch.. It is working now :)

SQL Code

--

USE [SampleDB]
GO

/****** Object:  Table [dbo].[MCRS_MOBILE_DATA_HDR]    Script Date: 07-Oct-16 4:03:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MCRS_MOBILE_DATA_HDR](
       [Id] [int] IDENTITY(1,1) NOT NULL,
       [Merchandiser] [varchar](100) NULL,
CONSTRAINT [PK_MCRS_MOBILE_DATA_HDR] PRIMARY KEY CLUSTERED 
(
       [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[MCRS_MOBILE_DATA_HDR] ON 

GO
INSERT [dbo].[MCRS_MOBILE_DATA_HDR] ([Id], [Merchandiser]) VALUES (1, N'Test')
GO
INSERT [dbo].[MCRS_MOBILE_DATA_HDR] ([Id], [Merchandiser]) VALUES (2, N'Test1')
GO
INSERT [dbo].[MCRS_MOBILE_DATA_HDR] ([Id], [Merchandiser]) VALUES (3, N'Test')
GO
INSERT [dbo].[MCRS_MOBILE_DATA_HDR] ([Id], [Merchandiser]) VALUES (4, N'Test1')
GO
INSERT [dbo].[MCRS_MOBILE_DATA_HDR] ([Id], [Merchandiser]) VALUES (5, N'Pawan')
GO
SET IDENTITY_INSERT [dbo].[MCRS_MOBILE_DATA_HDR] OFF
GO

Select allOpen in new window

C# Code

string connLOCDAT = "Data Source=localhost;Initial Catalog=SampleDB;Integrated Security = true;";
        string valueToInsert = "Pawan";
        string strSql = @"Insert Into MCRS_MOBILE_DATA_HDR (Merchandiser) VALUES('" + valueToInsert + "') select Scope_Identity()";

        SqlConnection conn = new SqlConnection(connLOCDAT);
        SqlCommand cmd = new SqlCommand(strSql, conn);

        conn.Open();
        int currentIdd = Convert.ToInt32(cmd.ExecuteScalar());

        conn.Close();

Select allOpen in new window

ENJOY !!

Pawan, did you realize that is a SQL CE database and not SQL Server?

Ohh!! Thanks Vitor for pointing that out..

UPDATED... FOR SQL CE
NOTE - SCOPE_IDENTITY() AND @@IDENTITY doesnot work in SQL SE.
Only this which will work in this case is - SELECT MAX(Ref) from MCRS_MOBILE_DATA_HDR
Also I dont think people will use SQL CE in production.

SQL Code

--

USE [SampleDB]
GO

/****** Object:  Table [dbo].[MCRS_MOBILE_DATA_HDR]    Script Date: 07-Oct-16 4:03:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MCRS_MOBILE_DATA_HDR](
       [Id] [int] IDENTITY(1,1) NOT NULL,
       [Merchandiser] [varchar](100) NULL,
CONSTRAINT [PK_MCRS_MOBILE_DATA_HDR] PRIMARY KEY CLUSTERED 
(
       [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[MCRS_MOBILE_DATA_HDR] ON 

GO
INSERT [dbo].[MCRS_MOBILE_DATA_HDR] ([Id], [Merchandiser]) VALUES (1, N'Test')
GO
INSERT [dbo].[MCRS_MOBILE_DATA_HDR] ([Id], [Merchandiser]) VALUES (2, N'Test1')
GO
INSERT [dbo].[MCRS_MOBILE_DATA_HDR] ([Id], [Merchandiser]) VALUES (3, N'Test')
GO
INSERT [dbo].[MCRS_MOBILE_DATA_HDR] ([Id], [Merchandiser]) VALUES (4, N'Test1')
GO
INSERT [dbo].[MCRS_MOBILE_DATA_HDR] ([Id], [Merchandiser]) VALUES (5, N'Pawan')
GO
SET IDENTITY_INSERT [dbo].[MCRS_MOBILE_DATA_HDR] OFF
GO

Select allOpen in new window

C# Code

string connLOCDAT = "Data Source=localhost;Initial Catalog=SampleDB;Integrated Security = true;";
        string valueToInsert = "Pawan";
        string strSql = @"Insert Into MCRS_MOBILE_DATA_HDR (Merchandiser) VALUES('" + valueToInsert + "') SELECT MAX(Ref) Ref from MCRS_MOBILE_DATA_HDR";

        SqlConnection conn = new SqlConnection(connLOCDAT);
        SqlCommand cmd = new SqlCommand(strSql, conn);

        conn.Open();
        int currentIdd = Convert.ToInt32(cmd.ExecuteScalar());

        conn.Close();

Select allOpen in new window

ENJOY !!

@All-

A similar question was asked in EE-

https://www.experts-exchange.com/questions/26630665/SQLServer-CE-identity-column.html

The only way is SELECT MAX(Ref) from MCRS_MOBILE_DATA_HDR

Enjoy!!

Point is that SQL CE does not accept multiple commands.

Also I dont think people will use SQL CE in production.

You make a lot of assumptions maybe based in your own experience but you'll be surprised on how many different solutions can be used around the world for similar problems :)

The only way is SELECT MAX(Ref) from MCRS_MOBILE_DATA_HDR

No, is not. If you loose some time reading other proposals from Experts you'll see that we are only waiting in author's feedback (hopefully he didn't shoot himself as he told).

Pawan:

NOTE - SCOPE_IDENTITY() AND @@IDENTITY doesnot work in SQL SE

That is not correct, @@IDENTITY works. See my link above: ID: 41831881

Sir,

I am referring below-

https://www.experts-exchange.com/questions/26630665/SQLServer-CE-identity-column.html

I dont have SQL CE so cant test. :(

Regards,

Pawan, in that question the author stated that @@IDENTITY worked:

I at least get a result for @@IDENTITY,  but it is NULL.

Yes sir you are correct , but it is giving NULL. We area looking for the maximum Id. I mean when add a row , a new Identity value for that row. I dont have SQL SE so cant test.:(

@SmashAndGrab

Please pic an answer and award point to finish this question. Thanks!

Hi all - Does any of you have any experince SQLLITE?  

I've created a new question....


https://www.experts-exchange.com/questions/28984780/Table-Not-found-SQLITE-error.html