产生ID的最大值 [英] generating max of id

问题描述

检索ID的最大值时得到错误的ID，我使用了此代码.

获取ID的最大值

getting wrong ids when I am retriving Max of Id ,I used this code.

to get the max Of ID

cn.Close();
       cmd = new SqlCommand("Select Max(IsNull(Consumer_ID,0))+1 from CC_Consumer_Detail", cn);
       cn.Open();
       dr = cmd.ExecuteReader();
       if (dr.Read())
       {
           maxcid = Convert.ToInt64(dr[0].ToString());
       }

我正在使用此查询插入记录

and i am inserting records using this query

cmd = new SqlCommand("insert into CC_Consumer_Detail (Consumer_Id, Appointment_Status_ID, First_Name,Last_Name,A_First_Name,A_Last_Name,Gender,Age,CC_ID,Country_ID,City_ID,District_ID,LandPhone,Mobile_No,Profession,Location,Direction,A_Location,A_Direction,Marital_Status,Email,Created_By,Created_Date,Last_Updated_By,Last_Updated_Date,Valid,Referer_type,Referer_id )

Values ("+maxcid+",(Select Appointment_Status_ID from dbo.CC_Appointment_Status WHERE Appointment_Status = 'Pending'), '" + Txtfirstname.Text + "','" + Txtlastname.Text + "','" + Txtaname.Text + "','" + TxtLname.Text + "','" + gender + "','" + Txtage.Text + "','" + Ddlcallcenter.SelectedValue.ToString() + "','" + Ddlcountry.SelectedValue.ToString() + "','" + Ddlcity.SelectedValue.ToString() + "','" + Ddldist.SelectedValue.ToString() + (Convert.ToInt32(Session["S_ucountry"].ToString())) + ",'" + logged + "'," + clsGen.getLocalDateTime(Convert.ToInt32(Session["S_ucountry"].ToString())) + ",'true','" + Label29.Text + "', '" + txtreferredby.Text + "')", cn);

但是在数据库中它会生成错误的IDs

就像在
之后 2867532
它正在
28675330
(在某些情况下，它将在生成的ID后面附加"0")
请给我一些建议，

but in DB it is generating wrong ids

like after
2867532
it is taking
28675330
(In some cases , It is appending ''0'' with the generated id)
Plese Advice, What I need to do?

推荐答案

我认为是因为您的Consumer_Id列是一个alpha列，而不是数字列.在这种情况下，按照您在问题中描述的方式进行订购是正确的.生成下一个ID时，您需要对值进行转换，以确保获得最大的数值.

在下列值的列表中:

10，200，5000，49598393，21234436546，9

如果值为alpha，则最大值为9.

通过使用以下内容将其转换为BIGINT.最大值为21234436546.

I think it''s because your Consumer_Id column is an alpha column, not a numeric column. In this this instance it would be correct to order in the way you described in your question. When generating the next id you need to do a conversion of the value to ensure you''re getting the max numeric value.

In a list of the following values:

10, 200, 5000, 49598393, 21234436546, 9

9 would be the max value if the values were alphas.

By using the following which converts to a BIGINT. The max value would be 21234436546.

Select Max(IsNull(CAST(Consumer_ID AS BIGINT),0))+1 from CC_Consumer_Detail

>

如果您要为一个列分配一个序列.您可以将行为构建到表中. SQL支持标识列.使用这些变量时，您只需从要插入的字段中排除该值，SQL就会自动将序列中的下一个值分配给该列.

以下是用于身份列的MSDN:

http://msdn.microsoft.com/en-us/library/ms186775.aspx [ ^ ]

为具有标识列的表插入行时，可以使用sql方法SCOPE_IDENTITY获得插入后插入的值.

If you''re looking to assigned a sequence to a column. You can build the behaviour into the table. SQL supports identity columns. When using these you simply exclude the value from the fields being inserted and SQL will automatically assigned the next value in the sequence to the column.

The following is the MSDN for identity columns:

http://msdn.microsoft.com/en-us/library/ms186775.aspx[^]

When inserting a row for a table which has an identity column you can get the value which was inserted, after the insert, by using the sql method SCOPE_IDENTITY.

DECLARE @newId BIGINT
SELECT @newId = SCOPE_IDENTITY()

这篇关于产生ID的最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！