2. ASPRunner.NET General questions
  3. [SOLVED]  Insert record and return new ID as SQL output giving error
This topic is locked
[SOLVED]

 Insert record and return new ID as SQL output giving error

 		2/21/2019 2:03:26 PM
ASPRunner.NET General questions
P
 precisiondi author

I am trying to insert a record into a table and then return the unique ID of the record added. I am using a GUID identifier and not a numeric auto-increment field.
I am trying to use the following SQL statement to insert the record, which inserts the record in the table:
string sql = String.Format(@"INSERT INTO Items(ItemType,DateCreated)

OUTPUT Inserted.ID

VALUES('{0}',GETDATE())",values["ItemType"]);
Which when logged, will appear like so:
INSERT INTO Items(ItemType,DateCreated)

OUTPUT Inserted.ID

VALUES('{a2db9dc1-f835-e911-8cd1-005056c00008}',GETDATE())
I am supposed to then run a SQL query will return me the new ID value, however when try to get the output, using the following code:
dynamic data;

dynamic rs = DB.Query(sql);

string id = rs.value(0);
It gives me a null reference error on the following line:

string id = rs.value(0);
I have tried to use:

string id = rs.value[0];

string id = rs.value["ID"];
My table definition is as follows:
CREATE TABLE [dbo].[Items](

[ID] [uniqueidentifier] NOT NULL,

[DateCreated] varchar NULL,

[ItemType] [uniqueidentifier] NULL,

) ON [PRIMARY]
P
 		precisiondi author 2/21/2019

I was able to get around this by using the following type of query, which ends with a standard select statement:
string sql = String.Format(@"DECLARE @newKeyTbl TABLE (newKey uniqueidentifier);

INSERT INTO Items(ItemType,DateCreated)

OUTPUT Inserted.ID INTO @newKeyTbl

VALUES('{0}',GETDATE());

SELECT newKey FROM @newKeyTbl;",values["ItemType"]);
dynamic data;

dynamic rs = DB.Query(sql);

string id = rs.value("newKey");
Admin 2/21/2019

There is an easier and cleaner way to do the same using database API:

string sql = String.Format("insert into ...");

DB.Exec(sql);

string id=DB.LastId();


https://xlinesoft.com/asprunnernet/docs/db_exec.htm

https://xlinesoft.com/asprunnernet/docs/db_lastid.htm
P
 		precisiondi author 2/21/2019



There is an easier and cleaner way to do the same using database API:

string sql = String.Format("insert into ...");

DB.Exec(sql);

string id=DB.LastId();


https://xlinesoft.com/asprunnernet/docs/db_exec.htm

https://xlinesoft.com/asprunnernet/docs/db_lastid.htm


This does not return me anything:

Code:
sql = String.Format(@"INSERT INTO Items(ItemType,DateCreated)

VALUES('{0}',GETDATE())",values["ItemType"]);

DB.Exec(sql);

id=String.Format("Lastid='{0}'",DB.LastId());
Output:

Lastid=''
Admin 2/25/2019

Tested in ASPRunner.NET 10.0 and it worked
Complete Code:

string sql = String.Format(@"INSERT INTO tests (name) values ('test@test.com')");

DB.Exec(sql);

MVCFunctions.Echo(String.Format("Lastid='{0}'",DB.LastId()));


Output:

Lastid='5'
P
 		precisiondi author 3/8/2019

This works up until I modify the database to not use integer unique identifiers but choose instead to use UNIQUEIDENTIFIER data types for the tables unique id column.
Pete K 3/12/2019



This works up until I modify the database to not use integer unique identifiers but choose instead to use UNIQUEIDENTIFIER data types for the tables unique id column.


Are you forced to use UNIQUEIDENTIFIER? Because it is not possible to retrieve this after insert. Things work much better with an int type. See: https://www.sqlteam.com/articles/uniqueidentifier-vs-identity
P
 		precisiondi author 3/12/2019



Are you forced to use UNIQUEIDENTIFIER? Because it is not possible to retrieve this after insert. Things work much better with an int type. See: https://www.sqlteam.com/articles/uniqueidentifier-vs-identity


I don't want to be stuck with a limit of 4 billion records, so I went with the GUID identifier.