SmashAndGrab
asked on
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?
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();
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 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.
ASKER
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;
}
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
thisConnection.Close();
if (!varREF == null) {
tbREFNo.Text == varREF;
}
ASKER
Sorry, should have been clear..
Do the update first. Then do code I have just pasted.
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);
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.
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.
ASKER
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 ?
Can I also ask about a better way to do this code..
varREF = Convert.ToInt32(thisReader
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();
ASKER
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.
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();
in mysql i think need to run
SELECT LAST_INSERT_ID();
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();
@Author - Is MAX(Column) doesn't work for you ?
What is the data type from your ID column? I used INT assuming that's an integer.
ASKER
Its an INT and it is set as an autonumbered column
The name is "REF"
The name is "REF"
In that case MAX(Ref) should work for you.
SELECT MAX(Ref) Ref FROM MCRS_MOBILE_DATA_HDR.
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();
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 ..
ASKER
@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\Document s\Visual Studio 2010\Projects\MCRS_CSV_LAT EST\MCRS_C SV\Form1.c s 586 33 MCRS_CSV
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\Document
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();
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.
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.
ASKER
So the table was empty? If so you can always add +1 to get the last value: newID = Convert.ToInt32(cmd.Execut eScalar()) +1;.
Or try the version with the SELECT SCOPE_IDENTITY() (check the end of string sql statement):
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();
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
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
ASKER
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?
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();";
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.
"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' );";
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 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.
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();";
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.
ASKER
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?
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();
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.
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();
sorry - should have attached the error..Did you add the semicolon before the SELECT keyword?
ASKER
This is exactly what I have...
The error:
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();
The error:
ASKER
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?
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?
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?
ASKER
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? :)
ASKER
This is the SQL code.
sql "Insert Into MCRS_MOBILE_DATA_HDR (Merchandiser) VALUES('stuabuck');SELECT SCOPE_IDENTITY();" string
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?
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?
ASKER
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();"
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"?
ASKER
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 ]
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 ]
What exactly were you running in SQLCE? Can you post it here?
ASKER
*goes and gets shotgun*
;-)
;-)
ASKER
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...
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.
Insert Into MCRS_MOBILE_DATA_HDR (Merchandiser) VALUES('stuabuck')
SELECT SCOPE_IDENTITY()
Remove ; and put them on 2 different lines.
ASKER
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
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
ASKER
@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?
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.
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.
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.
ASKER
@Ste5an - and apologies as I didn't see your solution earlier.
@Zberteoc: It works, cause we use the same connection.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
C# Code
ENJOY !!
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
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();
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
C# Code
ENJOY !!
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
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();
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!!
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_HDRNo, 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 SEThat 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,
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!
Please pic an answer and award point to finish this question. Thanks!
ASKER
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
I've created a new question....
https://www.experts-exchange.com/questions/28984780/Table-Not-found-SQLITE-error.html