使用ID列的简单增量时遇到问题 [英] Having problems using a simple increment of ID column

问题描述

所以我的问题是，当我做初始添加记录时，我正在制作的程序会自动跳过ID为1的第一个并开始像2,3,4,5,6那样添加。



这是我的代码：

 Dim i As Integer = 0 
 I + = 1 
 
 
尝试
 Dim sqlcon As New SqlConnection（mysqlstring）
 Dim sqladapt = New SqlDataAdapter（Select * from [Table]，sqlcon）
 
 sqlcon.Open（）
 Dim cmd As SqlClient.SqlCommand 
 Dim sql As String =insert into [Table] values（@ ID，@ Question，@ Answers，@ Howtowork，@章节
 cmd =新的SqlClient.SqlCommand（sql，sqlcon）
 
'确保所有表格都有相同类型的信息可以输入，如果不是，你会收到错误。 
 cmd.Parameters.AddWithValue（@ ID，DataGridView1.Rows.Count + I）
 cmd.Parameters.AddWithValue（@ Question，TextBox2.Text）
 cmd.Parameters。 AddWithValue（@ Answers，TextBox3.Text）
 cmd.Parameters.AddWithValue（@ Howtowork，TextBox4.Text）
 cmd.Parameters.AddWithValue（@ Chapter，TextBox5.Text）
 
 cmd.ExecuteNonQuery（）
 sqlcon.Close（）
 
 MessageBox.Show（新记录已添加）
 
 Catch ex As例外
 
 
结束尝试
 

我认为它可能很容易解决我没有看到。我想要做的就是使用1的增量添加到ID唯一列。我的数据库必须从1开始并从那里开始。



我是什么尝试过：



所以我试着在调试开始前手动将它添加到表中来放入第一条记录。这就是1,3,4,5,6发生的事情。无论我在哪里尝试开始记录，它总是跳过一个并跳过它之后就好了。

解决方案

问题是你让用户输入记录的ID号。你永远不会这样做。



ID列应该是自动编号或自动增量列。你让数据库生成并分配ID值。



让你的代码执行它的问题是，如果有多个用户同时运行代码，更多一个用户可以查询数据库以查找使用的最大ID值。多个客户端可以获得相同的价值。然后这些客户端都增加ID值并尝试为两个不同的记录分配相同的ID值。这真的很糟糕。



所以，你不这样做。你让数据库处理生成ID值，绝不是客户端。

阅读Dave的解决方案并检查出来： SQL AUTO INCREMENT a Field [ ^

So my problem is when I do the initial add record the program I am making automatically skips the first which is ID 1 and starts adding like so 2, 3, 4, 5, 6.

here is my code:

Dim i As Integer = 0
      I += 1


      Try
          Dim sqlcon As New SqlConnection("mysqlstring")
          Dim sqladapt = New SqlDataAdapter("Select * from [Table]", sqlcon)

          sqlcon.Open()
          Dim cmd As SqlClient.SqlCommand
          Dim sql As String = "insert into [Table] values(@ID,@Question,@Answers,@Howtowork,@Chapter)"
          cmd = New SqlClient.SqlCommand(sql, sqlcon)

          ' Make sure that all tables have the same type of information that can be entered, if not you will recieve an error.
          cmd.Parameters.AddWithValue("@ID", DataGridView1.Rows.Count + I)
          cmd.Parameters.AddWithValue("@Question", TextBox2.Text)
          cmd.Parameters.AddWithValue("@Answers", TextBox3.Text)
          cmd.Parameters.AddWithValue("@Howtowork", TextBox4.Text)
          cmd.Parameters.AddWithValue("@Chapter", TextBox5.Text)

          cmd.ExecuteNonQuery()
          sqlcon.Close()

          MessageBox.Show("New Record Added")

      Catch ex As Exception


      End Try

I figured that it's probably an easy fix that I am not seeing. All I am trying to do is add to the ID unique column using increments of 1. my database must start with 1 and go from there.

What I have tried:

So I tried to put in the first record by just adding it to the table manually before the start of my debugging. This is what happens 1, 3, 4, 5, 6. no matter where I try to start a record it always skips one and after the skip its fine.

解决方案

The problem is that you're letting users type in the ID numbers for records. You NEVER do that.

The ID column should be an auto-numbering or auto-increment column. You let the database generate and assign ID values.

The problem with letting your code do it is that if you have multiple users running the code at the same time, more than one user can query the database to find the maximum ID value that was used. More than one client can get the same value. Then those clients both increment the ID value and try assigning two different record the same ID value. That's really bad.

So, you don't do that. You let the database handle generating ID values, NEVER the client.

Read Dave's solution and check this out: SQL AUTO INCREMENT a Field[^]

这篇关于使用ID列的简单增量时遇到问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！