mysql Null field replacement not working

Hello guys,

I just imported some data from SQLite database to MySQL server and it seems that all the empty fields were replaced by NULL , now i did write some code to replace those but unfortunately does not work , it works for the first 2 records and then it closes like it finished the records, so in total i have like 123 of them, where i`m doing wrong ? thanks .

[code] query = “SELECT * FROM test”

rs = app.sqlBase.SQLQuery(query)

if rs <> Nil Then
  
  'MsgBox(rs.RecordCount.ToText)
  
  While Not rs.EOF
    
    If Not app.sqlBase.Error Then
      
      
      rs.Edit
      
      If rs.Field("qa").Value.IsNull OR rs.Field("qa").StringValue = "" Then
        rs.Field("qa").StringValue = "test"
      End If
      
      rs.Update
      
      If app.sqlBase.Error Then
        
        MsgBox("DB Error : " + app.sqlBase.ErrorMessage)
        
      End If 
      
      rs.MoveNext
      
    Else
      MsgBox("DB Error: " + app.sqlBase.ErrorMessage)
    End If
    
  Wend
  rs.Close
End If [/code]

so it sees all the records and once it reaches to the second one it goes to rs.close like it finished the loop.

why not just do it the easy way?

SQL="UPDATE test SET QA='test' WHERE QA IS NULL or QA=''"

Trying to tell from your code above, I’d say that is not possible. The While loop does not have any exit point, it will loop over all records in the record set and then close will be called. So most probably only two records are returned.

What happens when you step through the code in the debugger? How many records are returned? How many times does the loop iterate?

Hello Eli,

Well why you say that ? as far as i remember you have the start, While Not rs.EOF then it edits the record, then it update it and then it moves next rs.MoveNext then in the end you have the Wend for While Not rs.EOF and then once exited has a rs.Close so it does the proper loop and closes, what i don`t realize is why it close it to fast and where i do the mistake .

[quote=276270:@Dave S]why not just do it the easy way?

SQL="UPDATE test SET QA='test' WHERE QA IS NULL or QA=''" [/quote]

Thanks Dave, i already tested your solution and i guess it will be the last resort, i wanted to use the original code for some reasons but if i don`t figure out the logics i will have to go with your answer .

As I said, most probably only two records are returned from the database and not 123. So there is no error in your code.

Maybe in your SQL statement?