Results 1 to 7 of 7

Thread: Adapter not filling datatable on second run

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    476

    Adapter not filling datatable on second run

    I am trying to learn how to properly work with adapters and I thought I had it working properly. The code below is triggered from a button on a form. The data in tblaccounts is pulled from a table in another database and massaged a little. Then the code below pulls that data and adds new information using GetChartOpeningBalances.

    It is then supposed to save the data with the Adapter.Update(dt2) command.

    When I first click the button, the data is updated properly and tblaccounts contains all the proper data. Therefore, the Fill and the Update commands are performed properly.

    When I immediately click the button again, the adapter.Fill(dt2) command does not fill the datatable properly. No error is generated (I set up a Try/Catch/End Try statement). I stopped the code mid-run and the connection is open, the SQL statement assigned to the adapter.SelectCommand.CommandText will run properly in SQLyog, but after the adapter.Fill(dt2) command, there are the proper columns in datatable but no rows.

    Can anyone give me an idea of what I might be doing wrong?


    VB.Net Code:
    1. Dim dt2 As New DataTable
    2. Dim strSQL As String = "SELECT * FROM `mydb`.`tblaccounts` ORDER BY `FullAccountNumber`"
    3.  
    4. Dim adapter As New MySqlDataAdapter()
    5.  
    6. adapter.SelectCommand = New MySqlCommand(strSQL, connection)
    7. adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
    8.  
    9. Dim cmdBuilder As New MySqlCommandBuilder(adapter)
    10.  
    11. adapter.Fill(dt2)
    12.  
    13. If blnBS Then
    14.    dt2 = GetChartOpeningBalances(dt2)
    15. End If
    16.  
    17. adapter.Update(dt2)

    TIA, rasinc

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Adapter not filling datatable on second run

    If the Fill call executes successfully then the SQL code you specified is executed over the connection you specified. That's it, that's all. If you're not seeing the result you expect then there are four possibilities:

    1. You specified the wrong SQL.
    2. You specified the wrong connection details.
    3. The database doesn't contain the data you think it does.
    4. Something is corrupt somewhere.

    We can't really help with any of those. As you have already said, the code you have works correctly.

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    476

    Re: Adapter not filling datatable on second run

    Quote Originally Posted by jmcilhinney View Post
    If the Fill call executes successfully then the SQL code you specified is executed over the connection you specified.....As you have already said, the code you have works correctly.
    But it only executes successfully right after starting the program. While the program is running, the concept is that the data can be updated in the original database from another program, so I've been asked to make sure the utility I am programming can refresh the data from the original and massage it for the reports the users want without having to restart the program. It's during the Refresh process that the adapter doesn't update.

    Quote Originally Posted by jmcilhinney View Post
    1. You specified the wrong SQL.
    2. You specified the wrong connection details.
    3. The database doesn't contain the data you think it does.
    4. Something is corrupt somewhere.
    1) It's the same SQL statement in my example running each time. I intentionally kept it simple for testing and it's really hard to go wrong with a "select all fields from one table in the database and sort it" type of a query. However, I had already validated that the select command text in the adapter is what I wrote and does work in SQLyog properly (similar to MySQL Workbench).

    2) I stopped the code mid-routine to check that the connection was open and which server, port and user name it was using. All are correct. And again, it ran the first time, the only difference is I am hitting the button again without starting the program over. When I restart the program, updates happen properly.

    3) Validated the data through SQLyog to make sure of what was in the table to start (nothing), part of the data part way through before the adapter.fill command, after the adapter.fill command I can see the proper columns in the datatable but no rows of data (inspected in VS and using a For each row..... I also created a Crystal Report on the data to make sure I was seeing the updates properly and had the same results.

    4) Quite possibly but I can't figure out where except with the Adapter.fill command.

    So I decided to go back to my previous methods and using the same SELECT command, and a MySQLDataReader to load the datatable to be used and creating my own INSERT command, it all works no matter how many times I run it. I validated again in SQLyog and Crystal and it all seems to work. I manually adjust the data in the database, rerun my code without restarting the program and it updates it back to the correct data again using my own INSERT command rather than the dataadapter.

    I had hoped to learn a bit more about data manipulation and your CodeBank submission explaining the use of an adapter and commandbuilder to create the UPDATE statement on a single table, seemed to be straight forward, however, I don't know where to look next. I would have thought there should be no problems running the same button code multiple times to get the same results but in my case, it only works properly after starting the program and not afterwards.

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Adapter not filling datatable on second run

    What number does the call to Fill return?

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    476

    Re: Adapter not filling datatable on second run

    Start program and On First hit of button

    Code:
    before line
    intCount = Adapter.Fill(dt2)
    
    intCount = 0
    dt2.Columns.Count = 0
    dt2.Rows.Count = 0
    
    After line
    intCount = Adapter.Fill(dt2)
    
    intCount = 161
    dt2.Columns.Count = 45
    dt2.Rows.Count = 161

    On Second hit of button right after hitting it the first time

    Code:
    before line
    intCount = Adapter.Fill(dt2)
    
    intCount = 0
    dt2.Columns.Count = 0
    dt2.Rows.Count = 0
    
    After line
    intCount = Adapter.Fill(dt2)
    
    intCount = 0
    dt2.Columns.Count = 45
    dt2.Rows.Count = 0

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Adapter not filling datatable on second run

    Either you're doing something wrong that we can't see from the code you posted or something somewhere is corrupt. There's nothing inherent in a data adapter that would cause that.

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    476

    Re: Adapter not filling datatable on second run

    Ok, I'll test this again another time on a different scenario when I have a bit more time. I've set it up to read the data with a MySQLDataReader that will load the datatable and created my own parameterised INSERT command that works.

    Thanks for the help over the weekend.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width