Why is my SqlDataReader updating 2 table rows instead of 1?

Question

1.00/5 (1 vote)

See more:

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.

VB

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.

Posted 29-Oct-14 14:55pm

PTG.Jake

Add a Solution

Comments

BacchusBeale 29-Oct-14 22:55pm

I can't see an error in this code so maybe your SQL query for cmd11 is not correct?

DamithSL 29-Oct-14 23:21pm

have you debug line by line and check what exactly happening? you need to check whether your logic/loop executing as expected or not and also what is the sql statement generated at runtime. you can check that statement in your database and confirm the result.

PTG.Jake 29-Oct-14 23:49pm

I have debugged and found everything is fine until the update statement. I just don't know why it's updating 2 rows instead of the 1????

ATjong 30-Oct-14 1:21am

Did you check with sql profiler which query gets executed? is TNumID your primary key? Is it possible that there is a row where TNumID = null?

Shweta N Mishra 30-Oct-14 5:49am

Can you share the sample records of your table before and after the change.

PTG.Jake 31-Oct-14 8:51am

Sample records before update:

tnumid, tnum, tnumstatus
1, 1000, ready
2, 1001, ready

table after update:
tnumid, tnum, tnumstatus
1, 1000, Hold
2, 1001, Hold

instead of stopping on the first value for tnumstatus and updating it then exiting the while, it updates the first value and second value...

Yes ATjong TNumID is primary key

I should also mention this is happening in a private sub dothis()

If Not IsPostBack Then
dothis()
Else

End If

Shweta N Mishra 31-Oct-14 9:04am

Looking at your records and code it seems that while loop is not breaking but code seems to be correct or possibly you are not getting ID from you SQL query at first. Debug Your code at each point and first check that are you getting tnumid or it is null.

Maciej Los 31-Oct-14 14:58pm

Why to do that in loop? Isn't better to update values using SQL command?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Senthil Sambandam · Answer 1 · 2014-11-15T04:23:00

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.

VB

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()

/\jmot · Answer 2 · 2014-11-15T11:52:00

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 .

VB

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

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.

VB

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

Why is my SqlDataReader updating 2 table rows instead of 1?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0