Can't Read & Write At The Same Time

I've got:
one database (local)
Two tables - both with hand-crafted DataAdapters & DataTables

I open the connection object and fill a DataReader from the SelectCommand object
I loop around this reader and in the process:
   Create an instance of a DataTable (via a function)
   Call the .NewRow() method several times adding data
   the call the .Update() method to commit the data

On calling .Update() I get the message "There is already an open DataReader associated with this Command which must be closed first."

I don't see how it can be the same command object as each is created within its own Make...DataAdapter() function using Dim'd variables to create the command and associate with the DataAdapter.

Hope this makes sense...

Cheers,

Roy

Answer this question

Can't Read & Write At The Same Time

Coriolan
There's quite a bit of code. I've uploaded what I believe to be the essential bits:

Basically the process is:

Loop through drCategories
Add rows to dtTracks

Module Main
   Private dtRacks As DataTable
   Private daTracks As SqlDataAdapter
   Private daCategories As SqlDataAdapter

   Sub Main()
      Dim diCategory As DirectoryInfo
      Dim drCategories As SqlDataReader
      Dim strSqlConnection As String = _
         "Data Source=.\SQLEXPRESS;AttachDbFilename=" + Chr(34) + _
         My.Computer.FileSystem.CurrentDirectory.ToString() + "\MusicManager.mdf" + Chr(34) + _
         ";Integrated Security=True;User Instance=True"

      ' Data Related Stuff
      Using connection As New SqlConnection(strSqlConnection)
         daCategories = MakeCategoriesAdapter(connection)
         daTracks = MakeTracksAdapter(connection)
         dtTracks = MakeTracksTable()

         connection.Open()

         ' Get the read-only data
         drCategories = daCategories.SelectCommand.ExecuteReader()

         While drCategories.Read
            diCategory = New DirectoryInfo(drCategories("RootPath"))

            ' Add data to another table in here
            AddFilesToLibrary(diCategory, drCategories("CategoryID"))
         End While
      End Using
   End Sub
End Module

' One MakeAdapter Function. The other is pretty much the same

Public Function MakeTracksAdapter(ByVal connection As SqlConnection) As SqlDataAdapter
   Dim daTracks As New SqlDataAdapter()

   ' Command Object To Retrieve Row From Table
   Dim cmdSelect As New SqlCommand("SELECT * FROM Tracks", connection)
   daTracks.SelectCommand = cmdSelect

   ' Command Object To Adding Rows In The Track Table
   Dim cmdInsert As New SqlCommand("INSERT INTO Tracks " + _
      "(CategoryID, Title, Artist, Album, Disc, TrackNo, Duration, Year, Genre, Variable, BitRate, " + _
      "Frequency, Filename, Size, DateCreated, DateLastModified) " + _
      "VALUES (@Category, @Title, @Artist, @Album, @Disc, @TrackNo, @Duration, @Year, @Genre, " + _
      "@Variable, @BitRate, @Frequency, @Filename, @Size, @DateCreated, @DateLastModified)", _
      connection)

   AddTrackParameters(cmdInsert)
   daTracks.InsertCommand = cmdInsert

   ' Command Object To Update Rows In The Track Table
   Dim cmdUpdate As New SqlCommand("", connection)
   AddTrackParameters(cmdUpdate)
   daTracks.UpdateCommand = cmdUpdate

   ' Command Object To Delete Row From The Track Table
   Dim cmdDelete As New SqlCommand("", connection)
   daTracks.DeleteCommand = cmdDelete

   Return daTracks
End Function
' And the function to add the rows to the other table
Private Sub AddFilesToLibrary(ByVal startDir As DirectoryInfo, ByVal intCategoryID As Integer)
   Dim arrFiles As System.Array
   Dim curFile As FileInfo
   Dim arrDirectories As System.Array
   Dim curSubDir As DirectoryInfo
   Dim fileTypes As String = My.Settings.FileTypes
   Dim intTracks, intAdded As Integer
   Dim mySorter = New NameSorter

   arrFiles = startDir.GetFiles()
   Array.Sort(arrFiles, mySorter)

   arrDirectories = startDir.GetDirectories()
   Array.Sort(arrDirectories, mySorter)

   Console.WriteLine(startDir.FullName)

   For Each curFile In arrFiles
      AddTrack(dtTracks, curFile, fileTypes, intCategoryID)
      Console.WriteLine(curFile.FullName)
   Next

   ' Save rows to table
   Try
      intTracks = dtTracks.Rows.Count
      intAdded = daTracks.Update(dtTracks)   <-- This is where I get the error
                                                                     I've recursed a couple of times when I reach this point

      ' Remove rows from DataTable
      dtTracks.Clear()

   Catch ex As SqlException
      Exit Try

   End Try

   For Each curSubDir In arrDirectories
      ' Resursion: Use same function to process sub folder
      AddFilesToLibrary(curSubDir, intCategoryID)
   Next
End Sub

Thanks,

Roy
mightypeo
can you please past your code in here
Can't Read & Write At The Same Time

Answer this question