数据表:ttest
id (int) 单位编号varchar(20)
1 00101
1 00102
1 00103
1 00106
1 00107
需要找出不连续的编号,最小的不连续编号。
这里少了00104,00105要找出00104
- using System;
- using System.Data;
- using System.Configuration;
- using System.Collections;
- using System.Web;
- using System.Web.Security;
- using System.Web.UI;
- using System.Web.UI.WebControls;
- using System.Web.UI.WebControls.WebParts;
- using System.Web.UI.HtmlControls;
- using System.Data.SqlClient;
- public partial class test_getNext : System.Web.UI.Page
- {
- protected void Page_Load(object sender, EventArgs e)
- {
- Bind();
- }
- private void Bind()
- {
- string connStr = ConfigurationManager.ConnectionStrings["hrmbookConnectionString"].ToString();
- SqlConnection conn = new SqlConnection(connStr);
- DataSet ds = new DataSet();
- try
- {
- conn.Open();
- string Sqlstr = "SELECT 单位编号 FROM ttest order by 单位编号";
- SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
- da.Fill(ds);
- Response.Write(getNextFixed(ds, 0));
- }
- catch (Exception ex)
- {
- Response.Write(ex.ToString());
- }
- finally
- {
- conn.Close();
- }
- }
- /// <summary>
- /// 01,02,04和形式,则返回03.
- /// </summary>
- /// <param name="ds"></param>
- /// <param name="pos">判断的列的位置</param>
- /// <returns></returns>
- public string getNextFixed(DataSet ds,int pos)
- {
- string returnCode = "01";
- if (ds == null) return returnCode;
- if (ds.Tables.Count == 0) return returnCode;
- if (ds.Tables[0].Columns.Count == 0) return returnCode;
- if (ds.Tables[0].Rows.Count == 0) return returnCode;
- for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
- {
- string s = ds.Tables[0].Rows[i][pos].ToString().Trim();
- if (s.Length > 2) s = s.Substring(s.Length - 2);//一个参数时,表示从哪里开始截取,而不是长度。这里表示最后两位。
- if (s != returnCode) break;//不为01则跳出,这种思路比较奇怪,但是这里正好实用。
- if (s == "99") return "";//不能超过三位
- //格式输出,比如:one{0},two{1}.string.Format("{0:C3}", 2)输出$2.000
- //这里把1转化为01
- returnCode = string.Format("{0:00}", Convert.ToUInt16(s) + 1);//touint16不需要长整型
- }
- return returnCode;
- }
- }