1

Topic: ms sql a key field

How to receive value of a key field at adding in the table?  through SELECT SCOPE_IDENTITY - an error of a ghost of types SqlCommand scp = new SqlCommand ("select count (name) FROM Accounts WHERE Accounts.name = name;", connection); scp. Parameters. Add (new SqlParameter ("@name", name)); int no = (int) scp. ExecuteScalar (); if (no> 0) return false; SqlCommand sc = new SqlCommand ("insert into Accounts (name, pass) VALUES (@name, @pass); SELECT SCOPE_IDENTITY ();", connection); sc. Parameters. Add (new SqlParameter ("@name", name)); sc. Parameters. Add (new SqlParameter ("@pass", pass));//sc. ExecuteNonQuery (); Int64 i = (Int64) sc. ExecuteScalar ();//an error of a ghost of types

2

Re: ms sql a key field

Hello, vvv848165@ya.ru, you wrote: VYR> how to receive value of a key field at adding in the table? VYR>  through SELECT SCOPE_IDENTITY - an error of a ghost of types And table structure declaration you can result (CREATE TABLE)? At you the key field is exact BIGINT? And it is precisely marked as IDENTITY? I such things am normal through OUTPUT parameters I do, instead of through SELECT SCOPE_IDENTITY (): SqlCommand sc = new SqlCommand ("insert into Accounts (name, pass) VALUES (@name, @pass) set @AccountID = SCOPE_IDENTITY ()", connection); sc. Parameters. AddWithValue ("@name", name); sc. Parameters. AddWithValue ("@pass", pass); sc. Parameters. Add ("@AccountID", SqlDbType. BigInt).Direction = ParameterDirection. Output; sc. ExecuteNonQuery (); long i = (long) sc. Parameters ["@AccountID"].Value; And for your variant look in a debugger what exactly returns sc. ExecuteScalar (). For example here so: object o = sc. ExecuteScalar ();//what value is acquired by a variable "o"? Int64 i = (Int64) o;

3

Re: ms sql a key field

Hello, stomsky, you wrote: S> I such things am normal through OUTPUT parameters I do, instead of through SELECT SCOPE_IDENTITY (): tell as through OUTPUT parameters? Please! "CREATE TABLE Accounts (id INT PRIMARY KEY IDENTITY, name varchar (64) NOT NULL, pass varchar (64), UNIQUE (name));" object {decimal}//why?

4

Re: ms sql a key field

Hello, vvv848165@ya.ru, you wrote: VYR> Hello, stomsky, you wrote: S>> I such things am normal through OUTPUT parameters I do, instead of through SELECT SCOPE_IDENTITY (): VYR> tell as through OUTPUT parameters? Please! I gave an example (look at my previous answer once again). In it @AccountID is just OUTPUT-parameter. It becomes that when I initialized its property Direction value ParameterDirection. Output. VYR> "CREATE TABLE Accounts (id INT PRIMARY KEY IDENTITY, name varchar (64) NOT NULL, pass varchar (64), UNIQUE (name));" Well then, probably, to you value "id", at least, to lead not Int64, and to Int32 more correctly. I.e. in the example resulted by me (with OUTPUT-parameter) the last line will be such:... int i = (int) sc. Parameters ["@AccountID"].Value; VYR> object {decimal}//why? A good question... At me never such was...

5

Re: ms sql a key field

Hello, vvv848165@ya.ru, you wrote: VYR> how to receive value of a key field at adding in the table? VYR>  through SELECT SCOPE_IDENTITY - an error of a ghost of types lead decimal and ghosts will not hinder. SCOPE_IDENTITY has type numeric (38,0) that corresponds decimal.

6

Re: ms sql a key field

Hello, vmpire, you wrote: V> SCOPE_IDENTITY has type numeric (38,0) that corresponds decimal. Oh ... Since 2003 I use scope_identity (), and such and did not know! Directly revelation condescended! VYR>>  through SELECT SCOPE_IDENTITY - an error of a ghost of types V> lead decimal and ghosts will not hinder. Well it too considerably... Method Convert Can simply try. ToInt32? In a context of that wrote  here so (it is selected fat): SqlCommand sc = new SqlCommand ("insert into Accounts (name, pass) VALUES (@name, @pass); SELECT SCOPE_IDENTITY ();", connection); sc. Parameters. Add (new SqlParameter ("@name", name)); sc. Parameters. Add (new SqlParameter ("@pass", pass)); int i = Convert. ToInt32 (sc. ExecuteScalar ());

7

Re: ms sql a key field

Hello, vvv848165@ya.ru, you wrote: VYR> how to receive value of a key field at adding in the table? INSERT INTO Accounts (name, pass) OUTPUT inserted.id VALUES (@name, @pass);... <<RSDN@Home 1.0.0 alpha 5 rev. 0>>

8

Re: ms sql a key field

Hello, stomsky, you wrote: V>> SCOPE_IDENTITY has type numeric (38,0) that corresponds decimal. S> Oh ... Since 2003 I use scope_identity (), and such and did not know! Directly revelation condescended! VYR>>>  through SELECT SCOPE_IDENTITY - an error of a ghost of types V>> lead decimal and ghosts will not hinder. S> well it too considerably... S> method Convert Can simply try. ToInt32? In a context of that wrote  here so (it is selected fat): S> S> SqlCommand sc = new SqlCommand ("insert into Accounts (name, pass) VALUES (@name, @pass); SELECT SCOPE_IDENTITY ();" connection); S> sc. Parameters. Add (new SqlParameter ("@name", name)); S> sc. Parameters. Add (new SqlParameter ("@pass", pass)); S> int i = Convert. ToInt32 (sc. ExecuteScalar ()); S> I do not love class Convert. At it too big dial-up of conversion with too unsharp semantics. As consequence - it often provokes errors. Here and in your variant as soon as value identity gets out for limits Int32 - there will be an error.

9

Re: ms sql a key field

Hello, vmpire, you wrote: V> Here and in your variant as soon as value identity gets out for limits Int32 - will be an error. How it gets out for limits Int32 if in a database a column marked "identity", has type INT??? Well in remaining, of course, a matter of taste...

10

Re: ms sql a key field

Hello, vvv848165@ya.ru, you wrote: VYR> how to receive value of a key field at adding in the table? VYR>  through SELECT SCOPE_IDENTITY - an error of a ghost of types VYR> VYR> SqlCommand scp = new SqlCommand ("select count (name) FROM Accounts WHERE Accounts.name = name;", connection); VYR> scp. Parameters. Add (new SqlParameter ("@name", name)); VYR> int no = (int) scp. ExecuteScalar (); VYR> if (no> 0) VYR> return false; VYR> SqlCommand sc = new SqlCommand ("insert into Accounts (name, pass) VALUES (@name, @pass); SELECT SCOPE_IDENTITY ();" connection); VYR> sc. Parameters. Add (new SqlParameter ("@name", name)); VYR> sc. Parameters. Add (new SqlParameter ("@pass", pass)); VYR>//sc. ExecuteNonQuery (); VYR> Int64 i = (Int64) sc. ExecuteScalar ();//an error of a ghost of types VYR> Here also I do not remember when by hands the such did. We take and  linq2db. We can  model from basis: https://linq2db.github.io/articles/get- … ng-db.html And we can make hands a class (how to adjust  to basis in the previous link) [Table ("Accounts"]] public class Account {[PrimaryKey, Identity, Column ("id")] public long Id; [Column ("name")] public string Name; [Column ("pass")] public string Password;} public bool InsertNewAccount (string name, string password) {using (var db = new DataConnection ()) {if (db. GetTable <Account> ().Any (a => a. Name == name)) return false; var id = db. GetTable <Account> ().Value (a => a. Name, name).Value (a => a. Password, password).InsertWithInt64Identity ();} return true;} I to you would advise to cease to be played with such low level as SqlCommand. It is a lot of fuss on it leaves.

11

Re: ms sql a key field

Hello, stomsky, you wrote: V>> Here and in your variant as soon as value identity gets out for limits Int32 - will be an error. S> as it gets out for limits Int32 if in a database the column marked "identity", has type INT??? If INT - that does not get out, but it is not known. I did not see the table description . But even in this case I would write not Convert. ToInt32 (sc. ExecuteScalar ()) and (int) (decimal) sc. ExecuteScalar () it is simple not any case.

12

Re: ms sql a key field

Hello, vmpire, you wrote: V> If INT - that does not get out, but it is not known. I did not see the table description . Well so therefore I beforehand also specified the Author: vvv848165@ya.ru Date: 06.08 10:14 tables V> (int) (decimal) sc. ExecuteScalar () V> it is simple not any case. Double coercion of types?. In my opinion, it too...

13

Re: ms sql a key field

Hello, stomsky, you wrote: V>> If INT - that does not get out, but it is not known. I did not see the table description . S> Well so therefore I beforehand also specified the Author: vvv848165@ya.ru Date: 06.08 10:14 structure of table V>> (int) (decimal) sc. ExecuteScalar () V>> it is simple not any case. S> double coercion of types?. In my opinion, it too... No. It , and then type coercion. They simply syntactically look equally. Class Convert hinders them in one heap,  it not same. I prefer to write such things explicitly that in accuracy the nobility, as  fell.