为什么我的SqlDataReader更新了2个表行而不是1个？ [英] Why is my SqlDataReader updating 2 table rows instead of 1?

问题描述

我的sqldatareader在退出while之前读取并更新表中的2行，它应该只读取并更新1行。谁能告诉我我失踪了什么？我需要读取行，检查值是否为我的字符串，将该值更新为新值然后退出while。

my sqldatareader reads and updates 2 rows in the table before exiting the while, it should only read and update 1 row. Can anyone tell me what I am missing? I need to read the rows, check if a value = my string, update that value to a new value then exit the while.

Dim lrd As SqlDataReader = cmd11.ExecuteReader()
       Dim Lstat As String = "Ready"
       While lrd.Read()
           If lrd("TNumStatus") = Lstat Then

               Dim NID As String = lrd.GetValue(0)
               Dim NVal As String = lrd.GetValue(1)
               Dim NStat As String = lrd.GetValue(2)

               Dim ho As String = "Hold"

               Dim cmd111 As New SqlCommand
               Dim con111 As New SqlConnection
               con111.ConnectionString = SqlDataSource2.ConnectionString
               con111.Open()
               cmd111.Connection = con111
               cmd111.CommandText = "UPDATE TNum SET TNumStatus = '" & ho & "' WHERE TNumID = '" & NID & "'"
               cmd111.ExecuteNonQuery()
               con111.Close()
               Exit While
               lrd.Close()
           Else

           End If
       End While

它应该只读取和更新一行。

It should only read and update one row.

推荐答案

我认为你可以改变下面的代码所以你可以确定更新的行数。如果它首先执行查询，则允许退出。

I think you can change the code like below So you can identify the number of rows update. And allow to exit from the while if it Execute the query at first.

Dim UpdateRwCnt=cmd111.ExecuteNonQuery()
'View the number of rows changed
Debug.Print(UpdateRwCnt)
If UpdateRwCnt>0 Then   
   Exit While
End If
con111.Close()
lrd.Close()

您好..



您的代码很好并且也正常工作。

问题是您只想更新一个数据表，但是当数据读取器获取数据时，您编写代码来更新所有数据，因此如果您只想更新一个数据，那么为此设置代码。

Hello..

Your code is fine and its working too.
the problem is that you want to update only one data in the table but you write the code to update all the data when data-reader gets the data, so if you want to update only one data then set the code for that .

While lrd.Read()
If lrd("TNumStatus") = Lstat Then

这里你的代码得到这些条件为真2次，这就是为什么它的更新为2行。



如果你认为这不是问题然后使用标志并调试代码并检查。

here your code getting these condition true for 2 times thats why its updation the 2 row.

If you think this is not the Problem then use a flag and debug the code and check.

Dim lrd As SqlDataReader = cmd11.ExecuteReader()
Dim Lstat As String = "Ready"
While lrd.Read()
   Dim i as int32 =1
   If lrd("TNumStatus") = Lstat Then
      if i==1 then
         Dim NID As String = lrd.GetValue(0)
         Dim NVal As String = lrd.GetValue(1)
         Dim NStat As String = lrd.GetValue(2)

         Dim ho As String = "Hold"

         Dim cmd111 As New SqlCommand
         Dim con111 As New SqlConnection
         con111.ConnectionString = SqlDataSource2.ConnectionString
         con111.Open()
         cmd111.Connection = con111
         cmd111.CommandText = "UPDATE TNum SET TNumStatus = '" & ho & "' WHERE TNumID = '" & NID & "'"
         cmd111.ExecuteNonQuery()
         con111.Close()
         Exit While
         lrd.Close()
         i=i+1
      End if
   Else

   End If
End While

这篇关于为什么我的SqlDataReader更新了2个表行而不是1个？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！