Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 173
  • Last Modified:

How do I get the unique ID after insert?

Hi - thanks for looking.

I need to be able to capture the unique key once I have done an INSERT into the database.

I am struggling to get it working..

Can anyone help?


   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();
            conn.Close();

Open in new window

0
SmashAndGrab
Asked:
SmashAndGrab
  • 28
  • 20
  • 17
  • +3
1 Solution
 
Pawan KumarDatabase ExpertCommented:
can you post the schema for MCRS_MOBILE_DATA_HDR  table ?
0
 
Pawan KumarDatabase ExpertCommented:
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
0
 
ste5anSenior DeveloperCommented:
You have it already: It's tbRAF.Text.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Pawan KumarDatabase ExpertCommented:
I think tbRAF.Text is a text field.
0
 
ste5anSenior DeveloperCommented:
The relational theory does not impose any special data types for a candidate key.
0
 
SmashAndGrabAuthor Commented:
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;
                    }
0
 
SmashAndGrabAuthor Commented:
Sorry, should have been clear..


Do the update first.  Then do code I have just pasted.
0
 
Pawan KumarDatabase ExpertCommented:
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);
0
 
ste5anSenior DeveloperCommented:
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.
0
 
SmashAndGrabAuthor Commented:
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 ?
0
 
ste5anSenior DeveloperCommented:
Different question. But use TryParse().
0
 
abdul nazarCommented:
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();
         

Open in new window

0
 
SmashAndGrabAuthor Commented:
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.
0
 
ste5anSenior DeveloperCommented:
The OUTPUT INSERTED is T-SQL of SQL Server, not SQL Server Compact nor MySQL..
0
 
ste5anSenior DeveloperCommented:
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();

Open in new window

0
 
abdul nazarCommented:
in mysql i think need to run

SELECT LAST_INSERT_ID();

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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();

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
@Author - Is MAX(Column) doesn't work for you ?
0
 
SmashAndGrabAuthor Commented:
MAX didn't work I'm afraid.

@Vitor - I get this error:

INSERTERROR.PNG
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
What is the data type from your ID column? I used INT assuming that's an integer.
0
 
SmashAndGrabAuthor Commented:
Its an INT and it is set as an autonumbered column

The name is "REF"
0
 
Pawan KumarDatabase ExpertCommented:
In that case MAX(Ref) should work for you.

SELECT MAX(Ref) Ref FROM MCRS_MOBILE_DATA_HDR.
0
 
SmashAndGrabAuthor Commented:
datatype.PNG
0
 
ste5anSenior DeveloperCommented:
@Vitor: Does SQL CE support multiple statements in one command?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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();

Open in new window

I'm not sure if  SELECT SCOPE_IDENTITY() works for SQL CE but it worth a try.
0
 
Pawan KumarDatabase ExpertCommented:
can you provide the line on which the error is coming, its .Net error ..
0
 
SmashAndGrabAuthor Commented:
@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
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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();

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
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.
0
 
SmashAndGrabAuthor Commented:
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.

debugged.PNG
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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();

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
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
0
 
SmashAndGrabAuthor Commented:
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();";

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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();
"
0
 
Pawan KumarDatabase ExpertCommented:
Try this once..

string sql = @"Insert Into MCRS_MOBILE_DATA_HDR (Merchandiser) VALUES(" + tbRAF.Text + ") SELECT  IDENT_CURRENT( 'MCRS_MOBILE_DATA_HDR' );";
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
SmashAndGrabAuthor Commented:
I get this error..

error.PNG
0
 
ZberteocCommented:
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.
1
 
Pawan KumarDatabase ExpertCommented:
You are running this in .Net, but that is for SSMS.

Thats a query ..

string sql = @"SELECT  SCOPE_IDENTITY();";
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
SmashAndGrabAuthor Commented:
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();

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
ZberteocCommented:
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();

Open in new window

0
 
SmashAndGrabAuthor Commented:
sorry - should have attached the error..

error.PNG
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
sorry - should have attached the error..
Did you add the semicolon before the SELECT keyword?
0
 
SmashAndGrabAuthor Commented:
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();

Open in new window


The error:
error.PNG
0
 
SmashAndGrabAuthor Commented:
string sql = @"Insert Into MCRS_MOBILE_DATA_HDR (Merchandiser) VALUES('" + strCompany[1]  "');SELECT SCOPE_IDENTITY();";
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Zberteoc called attention for the @. Why do you need it in the INSERT string?
Can you also print the sql command before running it?
0
 
ZberteocCommented:
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?
0
 
SmashAndGrabAuthor Commented:
Would it be simpler if I just killed myself? ;)
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Would it be simpler if I just killed myself? ;)
Only after you have this working, ok? :)
0
 
SmashAndGrabAuthor Commented:
This is the SQL code.


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

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
0
 
SmashAndGrabAuthor Commented:
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();"
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok, maybe the SCOPE_IDENTITY doesn't work in SQL CE. Can you replace it with "SELECT @@IDENTITY"?
0
 
SmashAndGrabAuthor Commented:
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 ]
0
 
SmashAndGrabAuthor Commented:
if I run directly in SQLCE..

error.PNG
0
 
ZberteocCommented:
What exactly were you running in SQLCE? Can you post it here?
0
 
SmashAndGrabAuthor Commented:
*goes and gets shotgun*

;-)
0
 
SmashAndGrabAuthor Commented:
Insert Into MCRS_MOBILE_DATA_HDR (Merchandiser) VALUES('stuabuck');SELECT @@IDENTITY();
0
 
ste5anSenior DeveloperCommented:
SQL CE cannot imho execute multi statement batches..
Thus my sample using a separate cmd execution...
0
 
ZberteocCommented:
Try this:

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

Remove ; and put them on 2 different lines.
0
 
SmashAndGrabAuthor Commented:
What is strange is that the rows are actually being created in the database
0
 
ZberteocCommented:
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
0
 
SmashAndGrabAuthor Commented:
@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?
0
 
SmashAndGrabAuthor Commented:
version
0
 
ste5anSenior DeveloperCommented:
It does not like two statements in one call..

Take a look at my example.
1
 
ZberteocCommented:
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.
0
 
SmashAndGrabAuthor Commented:
@ste5an -

Just tried youe examples..

error.PNG
0
 
SmashAndGrabAuthor Commented:
@Ste5an - and apologies as I didn't see your solution earlier.
0
 
ste5anSenior DeveloperCommented:
@Zberteoc: It works, cause we use the same connection.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Forget about the parameters. Just use the same solution as for the Insert command:
string sql = "SELECT @@IDENTITY"; 
                      
int NewID;

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

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

Open in new window

0
 
ste5anSenior DeveloperCommented:
No, don't reconnect. Reuse the existing connection without closing it.
1
 
Pawan KumarDatabase ExpertCommented:
What is the status now ? Which solution we are trying - Vitor sir one or something else?
0
 
Pawan KumarDatabase ExpertCommented:
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

Open in new window


Table data

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();

Open in new window


UI code
ENJOY !!
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Pawan, did you realize that is a SQL CE database and not SQL Server?
0
 
Pawan KumarDatabase ExpertCommented:
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

Open 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();

Open in new window




ENJOY !!
0
 
Pawan KumarDatabase ExpertCommented:
@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!!
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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 :)
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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).
0
 
ZberteocCommented:
Pawan:
NOTE - SCOPE_IDENTITY() AND @@IDENTITY doesnot work in SQL SE
That is not correct, @@IDENTITY works. See my link above: ID: 41831881
0
 
Pawan KumarDatabase ExpertCommented:
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,
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Pawan, in that question the author stated that @@IDENTITY worked:
I at least get a result for @@IDENTITY,  but it is NULL.
0
 
Pawan KumarDatabase ExpertCommented:
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.:(
0
 
ZberteocCommented:
@SmashAndGrab

Please pic an answer and award point to finish this question. Thanks!
0
 
SmashAndGrabAuthor Commented:
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
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 28
  • 20
  • 17
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now