CompanyId是autoincrement one [英] CompanyId is autoincrement one

问题描述

我的代码如下

  public   string  getMaxID（）
 {
 
  try  
 {
 SqlCommand cmd =  new  SqlCommand（  select MAX（Companyid） ）来自Customer_Registration的+1，con）; 
 SqlDataReader dr = cmd.ExecuteReader（）; 
 dr.Read（）; 
  return  dr [ 0 ]。ToString（）; 
} 
  catch （Exception ex）
 {
 lblmessage.Text = ex.ToString（）; 
 
} 
 
} 
 受保护  void  btnnew_Click（ object  sender，EventArgs e）
 {
  int  companyid; 
 companyid = Convert.ToInt32（getMaxID（））; 
 companyid ++; 
 ddlCompanyid.SelectedItem.Text =   C000 + companyid.ToString（）; 
} 

当我在上面运行代码时显示错误如下

'Customer_Registration.getMaxID（）'：并非所有代码路径都返回值

什么是我的代码中的错误





问候

narasiman P.

解决方案

没有 - 他们没有。



编译器说的是通过你的方法有一条没有价值的路线返回。

这是不允许的，因为它意味着调用代码不能依赖于teh方法给它们任何有用的东西。在这种情况下：

  public   string  getMaxID（）
 {
  try  
 {
 SqlCommand cmd =  new  SqlCommand（ 从Customer_Registration选择MAX（Companyid）+1，con）; 
 SqlDataReader dr = cmd.ExecuteReader（）; 
 dr.Read（）; 
  return  dr [ 0 ]。ToString（）; 
} 
  catch （Exception ex）
 {
 lblmessage.Text = ex.ToString（）; 
} 
} 
} 

如果没有错误，你只返回一个值 - 如果有错误，你只需退出方法设置了一个返回值。



我不打算为你修复它，或者建议你自己解决它 - 因为很简单的原因，整个想法是错的。您不能假设新的公司ID值将比数据库中当前的最大值大一个：SQL Server是一个多用户系统，因此可能有任意数量的用户坐在那里，准备好相同的下一个ID值当他们按Enter时。这可能造成的混乱是非常非常非常难以理解的 - 即使它在开发中确实可以正常工作。



只有尝试分配公司ID刚刚将公司保存到数据库中时的价值 - 你不能依赖未来的价值。

首先，请阅读OriginalGriff的答案。



其次，还有其他一些问题...



如果 CustomerID 是 NULL ？

  SELECT  MAX（ NULL ）
   -    return：NULL  

为避免上述行为，请使用 COALESCE [ ^ ]功能：

  SELECT  MAX（ COALESCE （CustomerID， 0 ））
   -   返回：0如果为NULL或CustomerID  

请记住： CustomerID 必须是有效的整数值！



如果 CustomerId 是一个字符串值（ C0001，C0123等），您应该转换它，删除一个'C'字母，例如：

  SELECT   COALESCE （ CONVERT （ INT ， RIGHT （CompanyId，LEN（CompanyId）-1）），< span class =code-digit> 0 ）+1  AS  CompanyId 
  FROM  TableName 
   -   返回：1,2,3等 

如需了解更多信息，请阅读以下文章：自动生成序列SQL Server中的数字 [ ^ ]



我担心，您可以遇到更多问题。我建议你阅读有关数据完整性 [ ^ ]。

My code as follows

  public string getMaxID()
    {

        try
        {
            SqlCommand cmd = new SqlCommand("select MAX(Companyid)+1 from Customer_Registration", con);
            SqlDataReader dr = cmd.ExecuteReader();
            dr.Read();
            return dr[0].ToString();
        }
        catch (Exception ex)
        {
            lblmessage.Text = ex.ToString();
            
        }

    }
protected void btnnew_Click(object sender, EventArgs e)
    {
        int companyid;
        companyid = Convert.ToInt32(getMaxID());
        companyid++;
        ddlCompanyid.SelectedItem.Text = "C000" + companyid.ToString();
    }

when i run above code shows error as follows

'Customer_Registration.getMaxID()': not all code paths return a value

what is the mistake in my code


regards
narasiman P.

解决方案

Well no - they don't.

What the compiler is saying is that there is a route through your method where no value is returned.
This is not allowed, as it would mean that calling code could not rely on teh method giving them anything useful. In this case:

public string getMaxID()
    {
    try
        {
        SqlCommand cmd = new SqlCommand("select MAX(Companyid)+1 from Customer_Registration", con);
        SqlDataReader dr = cmd.ExecuteReader();
        dr.Read();
        return dr[0].ToString();
        }
    catch (Exception ex)
        {
        lblmessage.Text = ex.ToString();
        }
    }
}

You only return a value if there is no error - if there is an error, you just "fall through" out of the method without a return value being set.

I'm not going to fix it for you, or suggest that you fix it yourself - for teh very simple reason that the whole idea is wrong. You cannot assume that the new company id value will be one grater than the largest value currently in the database: SQL Server is a multiuser system, so there could be any number of users sitting there with the same "next ID value" ready to go when they press "Enter". The confusion this can cause is significant and very, very hard to sort out afterwards - even if it does work perfectly in development.

Only ever try to assign the company ID value when you have just saved the company into the database - you cannot rely on "future" values.

First of all, please read OriginalGriff answer.

Secondly, there are few other problems...

What if CustomerID is NULL?

SELECT MAX(NULL)
--return: NULL

to avoid above behaviour, use COALESCE[^] function:

SELECT MAX(COALESCE(CustomerID,0)) 
--return: 0 if NULL or CustomerID

Remember: CustomerID must be valid integer value!

In case of CustomerId is a string value (C0001, C0123, etc.), you should convert it, removing a 'C' letter, for example:

SELECT COALESCE(CONVERT(INT, RIGHT(CompanyId, LEN(CompanyId) -1)),0) +1 AS CompanyId
FROM TableName
--return: 1, 2, 3 , etc.

For futher information, please read this article: Auto generated sequence number in SQL Server[^]

I'm afraid, there is more issues you can meet. I would suggest you to read about data integrity[^].

这篇关于CompanyId是autoincrement one的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！