a question about @@IDENTITY

hi, I want to follow some example, but it does not work. It does not add the data into the table(Login). Can someone help me? Thanks in advance!

the code is :

myTransaction = myConnection.BeginTransaction(IsolationLevel.ReadCommitted,
"NewUser");
strSql= "insert into Login (UserName,Password) VALUES ('" +
TextBox_username.Text.Trim() + "','" +
TextBox_psw.Text.Trim() + "') select @.ID = @.@.IDENTITY";
myCommand =
new System.Data.SqlClient.SqlCommand(strSql, myConnection, myTransaction);
myCommand.Parameters.Add("@.ID", System.Data.SqlDbType.Int);
myCommand.Parameters["@.ID"].Direction =
ParameterDirection.Output;
myCommand.ExecuteNonQuery();

Hi,

There are at least a couple of things wrong here. First, separate multiple SQL statements with a semicolon. So the line should be:

strSql= "insert into Login (UserName,Password) VALUES ('" +
TextBox_username.Text.Trim() + "','" +
TextBox_psw.Text.Trim() + "'); select @.ID = @.@.IDENTITY";

Note the ; before the SELECT keyword.

Next, you are executing the ExecuteNonQuery method on the command object. But you are returning results, the new identity value. So if you want to actually use the value returned, you'll need to use something like ExecuteScalar.

Third, it's better to use the SCOPE_IDENTITY() function instead of @.@.IDENTITY. With the latter you might not get the results you expect.

Are you getting an exception? I'd guess you are based on the first problem.

Does this help?

Don|||

Hi,

First of all, thank you very much for your kind help.

Second, I tried your suggestion, but it does not work. I can not see any data inserted in the table "Login". Besides, my operating system is Microsoft XP Home edition SP2. I use the UltiDev Cassini to debug my project. So, I do not know how to run the project step by step and can not see which code line is wrong. I do not see the exception.

Table Login: 3 column: UserName, Password and ID

|||

Hi, now I got an exception:

System.InvalidCastException: Object cannot be cast from DBNull to other types

Exception Details:System.InvalidCastException: Object cannot be cast from DBNull to other types.

Source Error:

Line 271:ParameterDirection.Output;Line 272:myCommand.ExecuteScalar();Line 273:int nID =Convert.ToInt32(myCommand.Parameters["@.ID"].Value);Line 274:myCommand = Line 275:new System.Data.SqlClient.SqlCommand("insert into PersonalInfo (UserId) VALUES (" +

What does this mean? What should I do? thanks in advance!|||

--

strSql ="INSERT INTO Login (UserName, Password) VALUES" +"('"+TextBox_username.Text+"','"+TextBox_psw.Text+"')" "SELECT @.@.IDENTITY AS 'IDENT'";SqlDataReader myReader =new SqlCommand(strSq, MyConnection).ExecuteReader();--

NowmyReader.GetInt32(0).ToString() is equal to the identity column of the last
inserted record...