Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How do I get the unique ID after insert?

Posted on 2016-10-06
87
Medium Priority
?
161 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 28
  • 20
  • 17
  • +3
87 Comments
 
LVL 30

Expert Comment

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

Expert Comment

by:Pawan Kumar
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 35

Expert Comment

by:ste5an
ID: 41831538
You have it already: It's tbRAF.Text.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 30

Expert Comment

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

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 30

Expert Comment

by:Pawan Kumar
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 35

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 35

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 35

Expert Comment

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

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 52

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 30

Expert Comment

by:Pawan Kumar
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 52

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 30

Expert Comment

by:Pawan Kumar
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 35

Expert Comment

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

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 30

Expert Comment

by:Pawan Kumar
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 52

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 30

Expert Comment

by:Pawan Kumar
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 52

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 30

Expert Comment

by:Pawan Kumar
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 52

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 52

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 30

Expert Comment

by:Pawan Kumar
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 52

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 27

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 30

Expert Comment

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

Thats a query ..

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

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 52

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
 
LVL 27

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 52

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 52

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 27

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 52

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 52

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 52

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 27

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 35

Expert Comment

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

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 27

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 35

Expert Comment

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

Take a look at my example.
1
 
LVL 27

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 35

Expert Comment

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

Accepted Solution

by:
Vitor Montalvão earned 2000 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 35

Expert Comment

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

Expert Comment

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

Expert Comment

by:Pawan Kumar
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 52

Expert Comment

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

Expert Comment

by:Pawan Kumar
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 30

Expert Comment

by:Pawan Kumar
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 52

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 52

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 27

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 30

Expert Comment

by:Pawan Kumar
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 52

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 30

Expert Comment

by:Pawan Kumar
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 27

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

688 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