Solved

How do I get the unique ID after insert?

Posted on 2016-10-06
87
91 Views
Last Modified: 2016-11-22
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
Comment
Question by:SmashAndGrab
  • 28
  • 20
  • 17
  • +3
87 Comments
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41831529
can you post the schema for MCRS_MOBILE_DATA_HDR  table ?
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41831532
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
 
LVL 32

Expert Comment

by:ste5an
ID: 41831538
You have it already: It's tbRAF.Text.
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41831544
I think tbRAF.Text is a text field.
0
 
LVL 32

Expert Comment

by:ste5an
ID: 41831545
The relational theory does not impose any special data types for a candidate key.
0
 

Author Comment

by:SmashAndGrab
ID: 41831552
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
 

Author Comment

by:SmashAndGrab
ID: 41831555
Sorry, should have been clear..


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

Expert Comment

by:Pawan Kumar Khowal
ID: 41831568
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
 
LVL 32

Expert Comment

by:ste5an
ID: 41831570
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
 

Author Comment

by:SmashAndGrab
ID: 41831574
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
 
LVL 32

Expert Comment

by:ste5an
ID: 41831577
Different question. But use TryParse().
0
 

Expert Comment

by:abdul nazar
ID: 41831578
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
 

Author Comment

by:SmashAndGrab
ID: 41831583
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
 
LVL 32

Expert Comment

by:ste5an
ID: 41831587
The OUTPUT INSERTED is T-SQL of SQL Server, not SQL Server Compact nor MySQL..
0
 
LVL 32

Expert Comment

by:ste5an
ID: 41831595
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
 

Expert Comment

by:abdul nazar
ID: 41831598
in mysql i think need to run

SELECT LAST_INSERT_ID();

Open in new window

0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41831620
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
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41831621
@Author - Is MAX(Column) doesn't work for you ?
0
 

Author Comment

by:SmashAndGrab
ID: 41831632
MAX didn't work I'm afraid.

@Vitor - I get this error:

INSERTERROR.PNG
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41831642
What is the data type from your ID column? I used INT assuming that's an integer.
0
 

Author Comment

by:SmashAndGrab
ID: 41831648
Its an INT and it is set as an autonumbered column

The name is "REF"
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41831651
In that case MAX(Ref) should work for you.

SELECT MAX(Ref) Ref FROM MCRS_MOBILE_DATA_HDR.
0
 

Author Comment

by:SmashAndGrab
ID: 41831653
datatype.PNG
0
 
LVL 32

Expert Comment

by:ste5an
ID: 41831657
@Vitor: Does SQL CE support multiple statements in one command?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41831658
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
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41831662
can you provide the line on which the error is coming, its .Net error ..
0
 

Author Comment

by:SmashAndGrab
ID: 41831667
@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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41831670
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
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41831672
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
 

Author Comment

by:SmashAndGrab
ID: 41831700
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41831707
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
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41831709
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
 

Author Comment

by:SmashAndGrab
ID: 41831713
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41831715
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41831717
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
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41831719
Try this once..

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

Expert Comment

by:Vitor Montalvão
ID: 41831726
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
 

Author Comment

by:SmashAndGrab
ID: 41831729
I get this error..

error.PNG
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41831732
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
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41831734
You are running this in .Net, but that is for SSMS.

Thats a query ..

string sql = @"SELECT  SCOPE_IDENTITY();";
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41831735
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
 

Author Comment

by:SmashAndGrab
ID: 41831741
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41831748
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 26

Expert Comment

by:Zberteoc
ID: 41831749
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
 

Author Comment

by:SmashAndGrab
ID: 41831751
sorry - should have attached the error..

error.PNG
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41831754
sorry - should have attached the error..
Did you add the semicolon before the SELECT keyword?
0
 

Author Comment

by:SmashAndGrab
ID: 41831761
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
 

Author Comment

by:SmashAndGrab
ID: 41831764
string sql = @"Insert Into MCRS_MOBILE_DATA_HDR (Merchandiser) VALUES('" + strCompany[1]  "');SELECT SCOPE_IDENTITY();";
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41831776
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
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41831778
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
 

Author Comment

by:SmashAndGrab
ID: 41831784
Would it be simpler if I just killed myself? ;)
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41831788
Would it be simpler if I just killed myself? ;)
Only after you have this working, ok? :)
0
 

Author Comment

by:SmashAndGrab
ID: 41831790
This is the SQL code.


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

Open in new window

0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41831798
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
 

Author Comment

by:SmashAndGrab
ID: 41831799
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41831807
Ok, maybe the SCOPE_IDENTITY doesn't work in SQL CE. Can you replace it with "SELECT @@IDENTITY"?
0
 

Author Comment

by:SmashAndGrab
ID: 41831831
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
 

Author Comment

by:SmashAndGrab
ID: 41831839
if I run directly in SQLCE..

error.PNG
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41831843
What exactly were you running in SQLCE? Can you post it here?
0
 

Author Comment

by:SmashAndGrab
ID: 41831845
*goes and gets shotgun*

;-)
0
 

Author Comment

by:SmashAndGrab
ID: 41831850
Insert Into MCRS_MOBILE_DATA_HDR (Merchandiser) VALUES('stuabuck');SELECT @@IDENTITY();
0
 
LVL 32

Expert Comment

by:ste5an
ID: 41831863
SQL CE cannot imho execute multi statement batches..
Thus my sample using a separate cmd execution...
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41831864
Try this:

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

Remove ; and put them on 2 different lines.
0
 

Author Comment

by:SmashAndGrab
ID: 41831876
What is strange is that the rows are actually being created in the database
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41831881
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
 

Author Comment

by:SmashAndGrab
ID: 41831897
@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
 

Author Comment

by:SmashAndGrab
ID: 41831900
version
0
 
LVL 32

Expert Comment

by:ste5an
ID: 41831903
It does not like two statements in one call..

Take a look at my example.
1
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41831921
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
 

Author Comment

by:SmashAndGrab
ID: 41831923
@ste5an -

Just tried youe examples..

error.PNG
0
 

Author Comment

by:SmashAndGrab
ID: 41831939
@Ste5an - and apologies as I didn't see your solution earlier.
0
 
LVL 32

Expert Comment

by:ste5an
ID: 41832008
@Zberteoc: It works, cause we use the same connection.
0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 41832039
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
 
LVL 32

Expert Comment

by:ste5an
ID: 41832105
No, don't reconnect. Reuse the existing connection without closing it.
1
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41832121
What is the status now ? Which solution we are trying - Vitor sir one or something else?
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41833451
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41833462
Pawan, did you realize that is a SQL CE database and not SQL Server?
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41833489
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
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41833498
@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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41833501
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41833506
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
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41838368
Pawan:
NOTE - SCOPE_IDENTITY() AND @@IDENTITY doesnot work in SQL SE
That is not correct, @@IDENTITY works. See my link above: ID: 41831881
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41838410
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41838428
Pawan, in that question the author stated that @@IDENTITY worked:
I at least get a result for @@IDENTITY,  but it is NULL.
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41838443
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
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41858762
@SmashAndGrab

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

Author Comment

by:SmashAndGrab
ID: 41897809
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now