Windows窗体-从上次访问表值自动递增窗体编号

Question

我有一个windows表单(在VisualStudio-10中)，它可以将条目加载/保存到数据库中，导航现有条目并在表单中编辑它们。我想做的是，在创建新条目时，从Access表中的最后一个条目中将表单号(每个条目的主键为4位)增加1。

是否有方法调用上一项的索引，将其增加1并将该数字打印到表单编号文本框中？到目前为止，我还没有在我的搜索中找到大量有用的信息，所以如果你能给我指出正确的方向或回答另一个问题，我将不胜感激。

这是到数据库的更新函数/连接。数据库名为servicereq1，表名为servicereq。我想让更新的键显示在is serviceRequestNumberTextBox.中的文本框。再次感谢你的帮助。

private void updateButton_Click_1(object sender, EventArgs e)
    {
        {

            OleDbConnection conn = new OleDbConnection();
            AppDomain.CurrentDomain.SetData("DataDirectory", @"\\prod\ServiceRequests");
            conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\servicereq1.mdb";

            OleDbCommand cmd = new OleDbCommand("UPDATE servicereq SET ServiceRequestNumber = @servreq, DateLogged = @datelogged, LoggedBy = @loggedby, [Problem In Environment] = @problemfoundin, Function = @function, [Other Impacts] = @summary, Account = @earningsaccount, [From] = @from, [To] = @to, Status = @status, Description = @description, Fixer = @fixer, [Time Estimate] = @timeestimate, [Actual Start] = @actualstart, [Actual Finish] = @actualfinish, [Actual Time] = @actualtime, [Programs/Forms] = @programsforms, Comments = @comments, [Retest Date] = @requestdate, Tester = @tester, [Code In Environment] = @codein WHERE (ServiceRequestNumber = @servreq)");
            cmd.Connection = conn;

            conn.Open();

            if (conn.State == ConnectionState.Open)
            {
                cmd.Parameters.AddWithValue("@servreq", serviceRequestNumberTextBox.Text);
                cmd.Parameters.AddWithValue("@datelogged", dateLoggedTextBox.Text);
                cmd.Parameters.AddWithValue("@loggedby", loggedByComboBox.Text);
                cmd.Parameters.AddWithValue("@problemfoundin", problem_In_EnvironmentComboBox.Text);
                cmd.Parameters.AddWithValue("@function", functionTextBox.Text);
                cmd.Parameters.AddWithValue("@summary", other_ImpactsTextBox.Text);
                cmd.Parameters.AddWithValue("@earningsaccount", accountTextBox.Text);
                cmd.Parameters.AddWithValue("@from", fromTextBox.Text);
                cmd.Parameters.AddWithValue("@to", toTextBox.Text);
                cmd.Parameters.AddWithValue("@status", statusComboBox.Text);
                cmd.Parameters.AddWithValue("@description", descriptionTextBox.Text);
                cmd.Parameters.AddWithValue("@fixer", fixerComboBox.Text);
                cmd.Parameters.AddWithValue("@timeestimate", time_EstimateTextBox.Text);
                cmd.Parameters.AddWithValue("@actualstart", actual_StartTextBox.Text);
                cmd.Parameters.AddWithValue("@actualfinish", actual_FinishTextBox.Text);
                cmd.Parameters.AddWithValue("@actualtime", actual_TimeTextBox.Text);
                cmd.Parameters.AddWithValue("@programsforms", programs_FormsTextBox.Text);
                cmd.Parameters.AddWithValue("@comments", commentsTextBox.Text);
                cmd.Parameters.AddWithValue("@requestdate", retest_DateTextBox.Text);
                cmd.Parameters.AddWithValue("@tester", testerComboBox.Text);
                cmd.Parameters.AddWithValue("@codein", code_In_EnvironmentComboBox.Text);
                cmd.Parameters.AddWithValue("@servreq", serviceRequestNumberTextBox.Text);
                try
                {
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("Form Updated Successfully");
                    conn.Close();
                }
                catch (OleDbException ex)
                {
                    MessageBox.Show(ex.Message);
                    conn.Close();
                }
            }
            else
            {
                MessageBox.Show("Connection Failed");
            }
        }
    }

下面是保存新记录的函数。

    private void updateButton_Click(object sender, EventArgs e)
    {
        {
            OleDbConnection conn = new OleDbConnection();
            AppDomain.CurrentDomain.SetData("DataDirectory", @"\\prod\ServiceRequests");
            conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\servicereq1.mdb";

            String servreq = serviceRequestNumberTextBox.Text;
            String datelogged = dateLoggedTextBox.Text;
            String loggedby = loggedByComboBox.Text;
            String problemfoundin = problem_In_EnvironmentComboBox.Text;
            String function = functionTextBox.Text;
            String summary = other_ImpactsTextBox.Text;
            String earningsaccount = accountTextBox.Text;
            String from = fromTextBox.Text;
            String to = toTextBox.Text;
            String status = statusComboBox.Text;
            String description = descriptionTextBox.Text;
            String fixer = fixerComboBox.Text;
            String codein = code_In_EnvironmentComboBox.Text;
            String programsforms = programs_FormsTextBox.Text;
            String timeestimate = time_EstimateTextBox.Text;
            String actualstart = actual_StartTextBox.Text;
            String actualfinish = actual_FinishTextBox.Text;
            String actualtime = actual_TimeTextBox.Text;
            String retestdate = retest_DateTextBox.Text;
            String tester = testerComboBox.Text;
            String comments = commentsTextBox.Text;


            OleDbCommand cmd = new OleDbCommand("INSERT into servicereq ([ServiceRequestNumber], DateLogged, LoggedBy, [Problem In Environment], Function, [Other Impacts], Account, [From], [To], Status, Description, Fixer, [Code In Environment], [Programs/Forms], [Time Estimate], [Actual Start], [Actual Finish], [Actual Time], [Retest Date], Tester, Comments) Values(@servreq, @datelogged, @loggedby, @problemfoundin, @function, @summary, @earningsaccount, @from, @to, @status, @description, @fixer, @codein, @programsforms, @timeestimate, @actualstart, @actualfinish, @actualtime, @requestdate, @tester, @comments)");
            cmd.Connection = conn;

            conn.Open();

            if (conn.State == ConnectionState.Open)
            {
                cmd.Parameters.Add("@servreq", OleDbType.VarChar).Value = servreq;
                cmd.Parameters.Add("@datelogged", OleDbType.VarChar).Value = datelogged;
                cmd.Parameters.Add("@loggedby", OleDbType.VarChar).Value = loggedby;
                cmd.Parameters.Add("@problemfoundin", OleDbType.VarChar).Value = problemfoundin;
                cmd.Parameters.Add("@function", OleDbType.VarChar).Value = function;
                cmd.Parameters.Add("@summary", OleDbType.VarChar).Value = summary;
                cmd.Parameters.Add("@earningsaccount", OleDbType.VarChar).Value = earningsaccount;
                cmd.Parameters.Add("@from", OleDbType.VarChar).Value = from;
                cmd.Parameters.Add("@to", OleDbType.VarChar).Value = to;
                cmd.Parameters.Add("@status", OleDbType.VarChar).Value = status;
                cmd.Parameters.Add("@description", OleDbType.VarChar).Value = description;
                cmd.Parameters.Add("@fixer", OleDbType.VarChar).Value = fixer;
                cmd.Parameters.Add("@codein", OleDbType.VarChar).Value = codein;
                cmd.Parameters.Add("@programsforms", OleDbType.VarChar).Value = programsforms;
                cmd.Parameters.Add("@timeestimate", OleDbType.VarChar).Value = timeestimate;
                cmd.Parameters.Add("@actualstart", OleDbType.VarChar).Value = actualstart;
                cmd.Parameters.Add("@actualfinish", OleDbType.VarChar).Value = actualfinish;
                cmd.Parameters.Add("@actualtime", OleDbType.VarChar).Value = actualtime;
                cmd.Parameters.Add("@requestdate", OleDbType.VarChar).Value = retestdate;
                cmd.Parameters.Add("@tester", OleDbType.VarChar).Value = tester;
                cmd.Parameters.Add("@comments", OleDbType.VarChar).Value = comments;

                try
                {
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("Form Submitted Successfully");
                    servicereqBindingSource.ResetBindings(true);
                    conn.Close();
                }
                catch (OleDbException ex)
                {
                    MessageBox.Show(ex.Message);
                    conn.Close();
                }
            }
            else
            {
                MessageBox.Show("Connection Failed");
            }
            this.servicereqTableAdapter1.Load(servicereq1DataSet2.servicereq, serviceRequestNumberTextBox.Text);
        }
    }

Answer 1

在这样的多用户场景中，正确的方法是将ServiceRequestNumber字段更改为AUTONUMBER字段。通过这种方式，数据库引擎将计算新的号码，并且您不会冒险将相同的号码分配给在同一数据库中工作但来自两台不同PC的不同用户。

当然，您不需要为这个字段在INSERT查询中传递任何内容，所以从INSERT查询文本中删除ServerRequestNumber及其参数占位符。

如果您想知道分配给您的记录的AUTONUMBER是什么，可以编写这个sql

using(OleDbConnection conn = new OleDbConnection(......))
using(OleDbCommand cmd = new OleDbCommand("SELECT @@IDENTITY", conn))
{
      conn.Open();
      int newServiceRequestNumber = Convert.ToInt32(cmd.ExecuteScalar());
      ....
}

请注意，您应该将连接和其他一次性对象保存在使用块中，以确保它们已被释放和关闭。

Answer 2

正如Steve在评论中所说的，最简单的方法是将那个colunm设置为数据库中的自动编号，并在添加新条目的表单中删除id的文本框。在您的INSERT命令中，不要为id提供任何值，数据库将自动为该新条目生成一个新的数字(最后一个id + 1)。