SELECT @@IDENTITY giving Specified Cast is not Valid?

Question

hope someone can help with this

I am trying to INSERT a row of data into table [Issues] then immediately get the Guid from the ID column of Issues so it can be inserted into another table Actions, creating a link between the two. The relationship is [Issues]one to many[Actions].

When I run this code it causes a Specified Cast is not valid error here: Guid lastID = (Guid)sel.ExecuteScalar();

lastID is the variable I want to assign the last Guid created to so it can be passed into another table INSERT for table Actions.

If anyone can help that would be great! Thanks

I am using C# 2010 Express

//This is declared at the top of the form
public Guid lastID;

        private void pbSaveIssue_Click(object sender, EventArgs e)
        {
        SqlCeConnection ArcBaseConn = new SqlCeConnection(@"Data Source=|DataDirectory|\ArcBase.sdf");

        try
        {
            string cmdIssueSubmit = "INSERT INTO Issues (Type,Description,Dateraised,Site,Reportedby,Status) VALUES (@Type,@Description,@Dateraised,@Site,@Reportedby,@Status)";
            string TypeSubmit = cbIssueType.Text;
            string DescriptionSubmit = rtbDescription.Text;
            string SiteSubmit = cbIssueSite.Text;
            string ReportedbySubmit = cbReportedBy.Text;
            string DateraisedSubmit = dtpDateRaised.Text;

            SqlCeCommand sel = new SqlCeCommand();
            sel.Connection = ArcBaseConn;
            sel.CommandType = CommandType.Text;
            sel.CommandText = cmdIssueSubmit;
            sel.Parameters.AddWithValue("@Type", TypeSubmit);
            sel.Parameters.AddWithValue("@Description", DescriptionSubmit);
            sel.Parameters.AddWithValue("@Dateraised", DateraisedSubmit);
            sel.Parameters.AddWithValue("@Site", SiteSubmit);
            sel.Parameters.AddWithValue("@Reportedby", ReportedbySubmit);
            sel.Parameters.AddWithValue("@Status", lbStatus.Text);

            ArcBaseConn.Open();
            sel.ExecuteNonQuery();


            //Here is the problem

            // Grab the last Unique ID for the Issue just entered and assign to a variable so that it can be entered with the Actions below.
            string cmdGetlastID = "SELECT @@IDENTITY";
            sel.CommandText=cmdGetlastID;

            Guid lastID = (Guid)sel.ExecuteScalar();
            ArcBaseConn.Close();
        }
        catch (SqlCeException ex)
        {
            MessageBox.Show(ex.Message, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
            Application.ExitThread();
        }

`@@IDENTITY` returns a number - it will not return a GUID. You'll need to capture the guid with an `OUTPUT` clause in your INSERT statement. See [here](http://stackoverflow.com/questions/6262222/how-to-get-inserted-sequential-uniqueidentifier) for an example. — D Stanley, Jul 13 '15 at 20:34
Thanks D Stanley, that's one of the things I've tried. The INSERT command I tried was string cmdIssueSubmit = "INSERT INTO Issues (Type,Description,Dateraised,Site,Reportedby,Status) OUTPUT inserted.id VALUES (@Type,@Description,@Dateraised,@Site,@Reportedby,@Status)";"but it return a token error for OUTPUT — Torque, Jul 13 '15 at 21:27

score 0 · Answer 1 · answered Jul 13 '15 at 21:21

Assuming the table your are inserting into has an identity column defined,

Identity columns must be integer types (tinyint, smallint, int or bigint), not Guid, so casting to a Guid won't work (as you discovered).
You probably want to use @@SCOPE_IDENTITY instead of @@IDENTITY, lest you discover the pain caused by race conditions.

From the looks of your code, you aren't doing this, but...

If you are trying to get the value of a column defined as uniqueid (and do it so you don't have a race condition), you need to do something like this:

declare @my_guid uniqueid = newid()

INSERT Issues
(
  Type        ,
  Description ,
  Dateraised  ,
  Site        ,
  Reportedby  ,
  Status       ,
  Unique_Guid_Column
)
VALUES
(
  @Type        ,
  @Description ,
  @Dateraised  ,
  @Site        ,
  @Reportedby  ,
  @Status      ,
  @my_guid
)

select case @@ERROR when 0 then @myguid else null end

And then execute that using ExecuteScalar().

Hi, yes the Issues table has the ID column set as type Uniqueidentfier and Is rowGuid is True. The insert command does successfully generate a guid in the table. I'll try your suggestion. — Torque, Jul 13 '15 at 21:45
Would it be possible for someone to add the suggested code by Nicolas into my code as I am new of SQL and have not seen this type of string query format. All I am familiar with is the one liners for INSERT, UPDATE and DELETE. I would really appreciate the assistance! thanks — Torque, Jul 16 '15 at 04:13

SELECT @@IDENTITY giving Specified Cast is not Valid?

1 Answers1