locked
Array of ThreadClass object INSERT INTO TABLE not in sequence RRS feed

  • Question

  • Hi, I have the follow code, and it seems like the sequence of records being INSERT are in wrong order at random times. I am trying to figure what might caused it. Any ideas?

    To fix the issue, I might be able to retrieve a sequence value to indicate the sequence as it get inserted. Is the Array of ThreadClass messing things up? Any suggestion on how it should be done properly?

    For i = 0 To (Message.Result.CountOfRow - 1)
    selectString = "INSERT INTO [dbo].[Result] ([ReportID], [ServiceCode], [ServiceName], [ObservationCode], [ObservationName], [ObservationValue], [Units], [ReferenceRange], [TestingLocation], [AbnormalValue]) VALUES (" & intReportID & ", '" & Message.Report(0).ServiceCode & "', '" & ReplaceQuote(Message.Report(0).ServiceName) ',  '" & Message.Result(i).ObservationCode & "', '" & ReplaceQuote(Message.Result(i).ObservationName) & "', '" & ReplaceQuote(Message.Result(i).ObservationValue) ', '" & ReplaceQuote(Message.Result(i).Units) & "', '" & ReplaceQuote(Message.Result(i).ReferenceRange) & "', '" & GetLocationCode(Message.Result(i).TestingLocation) & "', '" & Message.Result(i).AbnormalValue & "')"
                        
    R(i) = New ThreadClass
    ThreadR(i) = New Thread(AddressOf R(i).ExecuteSQL)
    R(i).stCommandText = selectString
    ThreadR(i).Start()
    Next

    • Moved by Amanda Zhu Thursday, March 6, 2014 8:21 AM
    Wednesday, March 5, 2014 10:31 PM

Answers

  • Hi, I have the follow code, and it seems like the sequence of records being INSERT are in wrong order at random times. I am trying to figure what might caused it. Any ideas?

    To fix the issue, I might be able to retrieve a sequence value to indicate the sequence as it get inserted. Is the Array of ThreadClass messing things up? Any suggestion on how it should be done properly?

    For i = 0 To (Message.Result.CountOfRow - 1)
    selectString = "INSERT INTO [dbo].[Result] ([ReportID], [ServiceCode], [ServiceName], [ObservationCode], [ObservationName], [ObservationValue], [Units], [ReferenceRange], [TestingLocation], [AbnormalValue]) VALUES (" & intReportID & ", '" & Message.Report(0).ServiceCode & "', '" & ReplaceQuote(Message.Report(0).ServiceName) ',  '" & Message.Result(i).ObservationCode & "', '" & ReplaceQuote(Message.Result(i).ObservationName) & "', '" & ReplaceQuote(Message.Result(i).ObservationValue) ', '" & ReplaceQuote(Message.Result(i).Units) & "', '" & ReplaceQuote(Message.Result(i).ReferenceRange) & "', '" & GetLocationCode(Message.Result(i).TestingLocation) & "', '" & Message.Result(i).AbnormalValue & "')"
                        
    R(i) = New ThreadClass
    ThreadR(i) = New Thread(AddressOf R(i).ExecuteSQL)
    R(i).stCommandText = selectString
    ThreadR(i).Start()
    Next

    Threads are not guaranteed to run in any particular order.  Threads block each other to gain access to resources (IE: SQL server).  You could try using a Mutex but that still wouldn't guarantee which order things run in.

    In any event you should probably have some sort of created date in the insert so you can sort by the order it was created in.   Then it doesn't matter which order the records went in.

    • Marked as answer by Carl Cai Wednesday, March 12, 2014 10:10 AM
    Wednesday, March 5, 2014 10:40 PM
  • You really can't control the order data is written to a database.  You must use Order By in your queries when pulling the data out of a database to get data in the correct order.  SQL server stores the data in hash tables using multithreading processes.  Make sure you have a timestamp in the table so you can pull the data out in time order if required.

    jdweng

    • Marked as answer by Carl Cai Wednesday, March 12, 2014 10:10 AM
    Thursday, March 6, 2014 8:59 AM

All replies

  • Hi, I have the follow code, and it seems like the sequence of records being INSERT are in wrong order at random times. I am trying to figure what might caused it. Any ideas?

    To fix the issue, I might be able to retrieve a sequence value to indicate the sequence as it get inserted. Is the Array of ThreadClass messing things up? Any suggestion on how it should be done properly?

    For i = 0 To (Message.Result.CountOfRow - 1)
    selectString = "INSERT INTO [dbo].[Result] ([ReportID], [ServiceCode], [ServiceName], [ObservationCode], [ObservationName], [ObservationValue], [Units], [ReferenceRange], [TestingLocation], [AbnormalValue]) VALUES (" & intReportID & ", '" & Message.Report(0).ServiceCode & "', '" & ReplaceQuote(Message.Report(0).ServiceName) ',  '" & Message.Result(i).ObservationCode & "', '" & ReplaceQuote(Message.Result(i).ObservationName) & "', '" & ReplaceQuote(Message.Result(i).ObservationValue) ', '" & ReplaceQuote(Message.Result(i).Units) & "', '" & ReplaceQuote(Message.Result(i).ReferenceRange) & "', '" & GetLocationCode(Message.Result(i).TestingLocation) & "', '" & Message.Result(i).AbnormalValue & "')"
                        
    R(i) = New ThreadClass
    ThreadR(i) = New Thread(AddressOf R(i).ExecuteSQL)
    R(i).stCommandText = selectString
    ThreadR(i).Start()
    Next

    Threads are not guaranteed to run in any particular order.  Threads block each other to gain access to resources (IE: SQL server).  You could try using a Mutex but that still wouldn't guarantee which order things run in.

    In any event you should probably have some sort of created date in the insert so you can sort by the order it was created in.   Then it doesn't matter which order the records went in.

    • Marked as answer by Carl Cai Wednesday, March 12, 2014 10:10 AM
    Wednesday, March 5, 2014 10:40 PM
  • I actually do want the order maintain as sequence of the results being populate as it comes it. Preferbly first in first out, what would be the alternative to Thread?

    Thursday, March 6, 2014 2:38 AM
  • Hi,

    I have moved this thread to Visual Basic forum for better support.

    Thanks,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, March 6, 2014 8:22 AM
  • You really can't control the order data is written to a database.  You must use Order By in your queries when pulling the data out of a database to get data in the correct order.  SQL server stores the data in hash tables using multithreading processes.  Make sure you have a timestamp in the table so you can pull the data out in time order if required.

    jdweng

    • Marked as answer by Carl Cai Wednesday, March 12, 2014 10:10 AM
    Thursday, March 6, 2014 8:59 AM