error when i insert data and select last id

Question

I want to get id after I insert a row of data into a table but I get an error:

System.InvalidCastException: Specified cast is not valid.

This is my code

SqlCommand cmd_insert = new SqlCommand("insert into ITM_OrderItem (ITM_Code, ITM_Desc, ORCAT_Desc, ORSCT_Desc, ARCBG_Abbrev, ARCSG_Desc, ARCSC_Code, ARCIM_DerivedFeeItem, OPD_Price, IPD_Price, Tou_Price, App_Status, SITES, CSSUSR_RowId, CSSUSR_DepId, CSSUSR_PosId , CDate, MDate) values('"
                       + dt.Rows[i]["EARCIM_Code"].ToString() + "','"
                       + dt.Rows[i]["EARCIM_Desc"].ToString() + "','"
                       + dt.Rows[i]["EORCAT_Desc"].ToString() + "','"
                       + dt.Rows[i]["EARCIC_Desc"].ToString() + "','"
                       + dt.Rows[i]["EARCBG_Abbrev"].ToString() + "','"
                       + dt.Rows[i]["EARCSG_Desc"].ToString() + "','"
                       + dt.Rows[i]["EARCSC_Desc"].ToString() + "','"
                       + dt.Rows[i]["EARCIM_DerivedFeeItem"].ToString() + "','"
                       + dt.Rows[i]["OPD"].ToString() + "','"
                       + dt.Rows[i]["IPD"].ToString() + "','"
                       + dt.Rows[i]["Tourist"].ToString() + "','"
                       + status_app + "','"
                       + usr_site + "','"
                       + usrID + "','"
                       + Dep_RowId + "','"
                       + Pos_RowId + "','"
                       + Cdate + "','"
                       + Cdate + "') select SCOPE_IDENTITY()", conEMR);

// error on this line
insert_RowId = (Int32)cmd_insert.ExecuteScalar();

Thank you a lot.

Remove the quotes (i.e. single) `''` for numeric data types. — Edper, Jul 09 '14 at 04:36
PLEASE look into SQL injection and use parametrized queries instead of text see http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter(v=vs.110).aspx — 3dd, Jul 09 '14 at 04:54
Would you mind showing the schema of your `ITM_OrderItem` table? — Edper, Jul 09 '14 at 05:28

score 0 · Answer 1 · answered Jul 09 '14 at 04:43

0

It looks as if the identity column in your table ITM_OrderItem does not use int as data type, What I can guess is that it is a bigint data type and if that is the case you should change the line in your code to:

insert_RowId = (Int64)cmd_insert.ExecuteScalar();

Also make sure that you change the datatype of variable 'insert_RowId' to Int64 in your C# code declaration.

answered Jul 09 '14 at 04:43

Pradeep Singh

1
1

What is the data type of the Identity Column of Your Table **ITM_OrderItem**. – Pradeep Singh Jul 09 '14 at 04:58
data type of the Identity Column is int – user3001046 Jul 09 '14 at 05:14
[ITM_RowId] INT IDENTITY (1, 1) NOT NULL, – user3001046 Jul 09 '14 at 05:15
In that case the issue is not with line of with the data type in C# code and the casting. I can now bet that the insert statement fails returning a null value for 'select SCOPE_IDENTITY()' and hence you should try generating the T-SQL for the insertion from the code, run the T-SQL in Sql Server Management Studio and Fix Accordingly. It seems as if you are passing character data in the insert statement where numeric data is expected or vice-versa. if you can share the schema of the table I sure will be able to help you on this. – Pradeep Singh Jul 09 '14 at 06:02
i try to dbug porcess and copy cmd to write on sql query it's work fine. – user3001046 Jul 09 '14 at 06:14

error when i insert data and select last id

1 Answers1