Results 1 to 12 of 12

Thread: [RESOLVED] Datatable incorrectly loading from datasource.

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jan 2013
    Posts
    66

    Resolved [RESOLVED] Datatable incorrectly loading from datasource.

    Can anyone please tell me what im missing here. This code is supposed to make a dataset "ds", make three data tables (GOODS_RECEIPTS, GOODS_RECEIPTS_DG and STOCK_ADJUSTMENT_TABLE ). Then load the values of the select commands into the relevant data tables.

    But instead I end up with the results of the
    "daGOODS_RECEIPTS.UpdateCommand = New FbCommand("SELECT FIRST 1 * FROM GOODS_RECEIPTS WHERE RECORDSTATE = 1", connection)"
    command in both GOODS_RECEIPTS and GOODS_RECEIPTS_DG tables.

    Visual basic 2010.


    connection.Open() 'Open connection


    'create a new FB dataadapter daGOODS_RECEIPTS
    Dim daGOODS_RECEIPTS As FbDataAdapter = New FbDataAdapter("SELECT FIRST 1 * FROM GOODS_RECEIPTS WHERE RECORDSTATE = 1", connection)
    Dim fbCmdBuilder = New FbCommandBuilder(daGOODS_RECEIPTS) 'activate command builder


    'create a new dataset
    Dim ds As New DataSet("GOODS_RECEIPTS_DATASET") 'Create dataset ds
    Dim GOODS_RECEIPTS As DataTable = ds.Tables("GOODS_RECEIPTS")
    Dim GOODS_RECEIPTS_DG As DataTable = ds.Tables("GOODS_RECEIPTS_DG")
    Dim STOCK_ADJUSTMENT_TABLE As DataTable = ds.Tables("STOCK_ADJUSTMENT_TABLE")

    Try 'Fill data set but if a queiry returns nothing and we try to fill a datatable with nothing an error occurers

    'fill the dataset/table with GOODS RECEIPTS data
    daGOODS_RECEIPTS.UpdateCommand = New FbCommand("SELECT FIRST 1 * FROM GOODS_RECEIPTS WHERE RECORDSTATE = 1", connection)
    daGOODS_RECEIPTS.FillSchema(ds, SchemaType.Source, "GOODS_RECEIPTS")
    daGOODS_RECEIPTS.Fill(ds, "GOODS_RECEIPTS")
    daGOODS_RECEIPTS.Update(ds, "GOODS_RECEIPTS")


    'fill the dataset/table with GOODS RECEIPTS DG data
    daGOODS_RECEIPTS.UpdateCommand = New FbCommand("SELECT * FROM GOODS_RECEIPTS_DG WHERE GOODSRECEIPTSNUMBER = " & ds.Tables("GOODS_RECEIPTS").Rows(0).Item("SEQ") & "", connection)
    daGOODS_RECEIPTS.FillSchema(ds, SchemaType.Source, "GOODS_RECEIPTS_DG")
    daGOODS_RECEIPTS.Fill(ds, "GOODS_RECEIPTS_DG")
    daGOODS_RECEIPTS.Update(ds, "GOODS_RECEIPTS_DG")

    DataGridView1.DataSource = ds.Tables("GOODS_RECEIPTS")
    DataGridView2.DataSource = ds.Tables("GOODS_RECEIPTS_DG")
    DataGridView1.Refresh()
    DataGridView2.Refresh()

    'fill the dataset/table with STOCK ADJUSTMENT TABLE data
    daGOODS_RECEIPTS = New FbDataAdapter("SELECT * FROM STOCK_ADJUSTMENT_TABLE ", connection)
    daGOODS_RECEIPTS.FillSchema(ds, SchemaType.Source, "STOCK_ADJUSTMENT_TABLE")
    daGOODS_RECEIPTS.Fill(ds, "STOCK_ADJUSTMENT_TABLE")
    daGOODS_RECEIPTS.Update(ds, "STOCK_ADJUSTMENT_TABLE")



    Catch
    Timer1.Enabled = False
    connection.Close()
    Return
    End Try

    Thank you.

  2. #2
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: Datatable incorrectly loading from datasource.

    Change this:

    Code:
    'create a new dataset
    Dim ds As New DataSet("GOODS_RECEIPTS_DATASET") 'Create dataset ds
    Dim GOODS_RECEIPTS As DataTable = ds.Tables("GOODS_RECEIPTS")
    Dim GOODS_RECEIPTS_DG As DataTable = ds.Tables("GOODS_RECEIPTS_DG")
    Dim STOCK_ADJUSTMENT_TABLE As DataTable = ds.Tables("STOCK_ADJUSTMENT_TABLE")
    To this:

    Code:
    'create a new dataset
    Dim ds As New DataSet("GOODS_RECEIPTS_DATASET") 'Create dataset ds
    Dim GOODS_RECEIPTS As DataTable = ds.Tables.Add("GOODS_RECEIPTS")
    Dim GOODS_RECEIPTS_DG As DataTable = ds.Tables.Add("GOODS_RECEIPTS_DG")
    Dim STOCK_ADJUSTMENT_TABLE As DataTable = ds.Tables.Add("STOCK_ADJUSTMENT_TABLE")

  3. #3
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: Datatable incorrectly loading from datasource.

    Also, when posting code, enclose it in [code]'your code here[/code] tags and it'll retain its formatting...

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Jan 2013
    Posts
    66

    Re: Datatable incorrectly loading from datasource.

    Thank you for your prompt reply Paul.

    However your suggested changes do not appear to have altered the result.
    Values filled into GOODS_RECEIPTS datatable appear in GOODS_RECEIPTS_DG datatable as well.

    Code:
     connection.Open() 'Open connection
    
    
            'create a new FB dataadapter daGOODS_RECEIPTS
            Dim daGOODS_RECEIPTS As FbDataAdapter = New FbDataAdapter("SELECT FIRST 1 * FROM GOODS_RECEIPTS WHERE RECORDSTATE = 1", connection)
            Dim fbCmdBuilder = New FbCommandBuilder(daGOODS_RECEIPTS) 'activate command builder
    
    
            'create a new dataset
            Dim ds As New DataSet("GOODS_RECEIPTS_DATASET") 'Create dataset ds
            Dim GOODS_RECEIPTS As DataTable = ds.Tables.Add("GOODS_RECEIPTS")
            Dim GOODS_RECEIPTS_DG As DataTable = ds.Tables.Add("GOODS_RECEIPTS_DG")
            Dim STOCK_ADJUSTMENT_TABLE As DataTable = ds.Tables.Add("STOCK_ADJUSTMENT_TABLE")
    
            Try 'Fill data set but if a queiry returns nothing and we try to fill a datatable with nothing an error occurers 
    
                'fill the dataset/table with GOODS RECEIPTS data
                daGOODS_RECEIPTS.UpdateCommand = New FbCommand("SELECT FIRST 1 * FROM GOODS_RECEIPTS WHERE RECORDSTATE = 1", connection)
                daGOODS_RECEIPTS.FillSchema(ds, SchemaType.Source, "GOODS_RECEIPTS")
                daGOODS_RECEIPTS.Fill(ds, "GOODS_RECEIPTS")
                daGOODS_RECEIPTS.Update(ds, "GOODS_RECEIPTS")
    
    
                'fill the dataset/table with GOODS RECEIPTS DG data
                daGOODS_RECEIPTS.UpdateCommand = New FbCommand("SELECT * FROM GOODS_RECEIPTS_DG WHERE GOODSRECEIPTSNUMBER =  " & ds.Tables("GOODS_RECEIPTS").Rows(0).Item("SEQ") & "", connection)
                daGOODS_RECEIPTS.FillSchema(ds, SchemaType.Source, "GOODS_RECEIPTS_DG")
                daGOODS_RECEIPTS.Fill(ds, "GOODS_RECEIPTS_DG")
                daGOODS_RECEIPTS.Update(ds, "GOODS_RECEIPTS_DG")
    
                DataGridView1.DataSource = ds.Tables("GOODS_RECEIPTS")
                DataGridView2.DataSource = ds.Tables("GOODS_RECEIPTS_DG")
                DataGridView1.Refresh()
                DataGridView2.Refresh()
    
                'fill the dataset/table with STOCK ADJUSTMENT TABLE  data
                daGOODS_RECEIPTS = New FbDataAdapter("SELECT * FROM STOCK_ADJUSTMENT_TABLE ", connection)
                daGOODS_RECEIPTS.FillSchema(ds, SchemaType.Source, "STOCK_ADJUSTMENT_TABLE")
                daGOODS_RECEIPTS.Fill(ds, "STOCK_ADJUSTMENT_TABLE")
                daGOODS_RECEIPTS.Update(ds, "STOCK_ADJUSTMENT_TABLE")
    
    
               
            Catch
                Timer1.Enabled = False
                connection.Close()
                Return
            End Try
    Again thank you.

  5. #5
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Datatable incorrectly loading from datasource.

    You have some kind of misunderstanding going on there. You put a SELECT statement into the UpdateCommand, then call Fill, and immediately afterwards call Update. I'm not sure what you were thinking that would do, but you're wrong about it. Update is used to write changes from the datatable back to the database using the UPDATE command (along with INSERT and DELETE, if needed). However, you only filled the datatable in the line before you called the update, so there can't be any changes, in which case the call to Update will do nothing. That's good, because that is NOT an UPDATE query that you put into the UpdateCommand, so it would fail anyways.

    However, that isn't really the problem...at least not directly. Ultimately, the problem is the mistaken mental model. As I explained, the UpdateCommand is only used to by the Update call to push changes back to the DB, and there aren't any changes, so it won't be called at all. What IS called is the SELECT statement, which you set just the one time right off. So, you change the UpdateCommand, but that isn't used (and isn't right anyways). Instead, the dataadapter is just using the SELECT statement over and over, since you aren't changing that one.

    Part of this is because you used a form of the dataadapter constructor that takes the SQL for the SelectCommand property. That may have obscured from your notice the fact that there even IS a SelectCommand property. It is the SQL of that property you need to be changing, not the SQL of the UpdateCommand property.
    My usual boring signature: Nothing

  6. #6
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: Datatable incorrectly loading from datasource.

    I just spotted the first error. As i told you, it was difficult to read unformatted. If you use my changes and do as Shaggy suggested, it'll work...

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Jan 2013
    Posts
    66

    Re: Datatable incorrectly loading from datasource.

    Thank you both for your efforts. Yes had really made a dogs breakfast of that(add excuses here). Will redo and repost result.
    Again thank you both for your time.

  8. #8

    Thread Starter
    Lively Member
    Join Date
    Jan 2013
    Posts
    66

    Re: Datatable incorrectly loading from datasource.

    Thank you.

    Wasn't quit as bad as I first thought.

    Changed Update.Command to fdDataAdapter
    daGOODS_RECEIPTS.UpdateCommand = New FbCommand("SELECT FIRST 1 * FROM GOODS_RECEIPTS WHERE RECORDSTATE = 1", connection)

    To
    daGOODS_RECEIPTS = New FbDataAdapter("SELECT FIRST 1 * FROM GOODS_RECEIPTS WHERE RECORDSTATE = 1", connection)
    in each relevant line.

    and removed
    daGOODS_RECEIPTS.Update(ds, "GOODS_RECEIPTS")

    in each relevant line,

    Code:
     connection.Open() 'Open connection
    
    
            'create a new FB dataadapter daGOODS_RECEIPTS
            Dim daGOODS_RECEIPTS As FbDataAdapter = New FbDataAdapter("SELECT FIRST 1 * FROM GOODS_RECEIPTS WHERE RECORDSTATE = 1", connection)
            Dim fbCmdBuilder = New FbCommandBuilder(daGOODS_RECEIPTS) 'activate command builder
    
    
            'create a new dataset
            Dim ds As New DataSet("GOODS_RECEIPTS_DATASET") 'Create dataset ds
            Dim GOODS_RECEIPTS As System.Data.DataTable = ds.Tables.Add("GOODS_RECEIPTS")
            Dim GOODS_RECEIPTS_DG As System.Data.DataTable = ds.Tables.Add("GOODS_RECEIPTS_DG")
            Dim STOCK_ADJUSTMENT_TABLE As System.Data.DataTable = ds.Tables.Add("STOCK_ADJUSTMENT_TABLE")
    
            Try 'Fill data set but if a queiry returns nothing and we try to fill a datatable with nothing an error occurers 
    
                'fill the dataset/table with GOODS RECEIPTS data
                daGOODS_RECEIPTS = New FbDataAdapter("SELECT FIRST 1 * FROM GOODS_RECEIPTS WHERE RECORDSTATE = 1", connection)
                daGOODS_RECEIPTS.FillSchema(ds, SchemaType.Source, "GOODS_RECEIPTS")
                daGOODS_RECEIPTS.Fill(ds, "GOODS_RECEIPTS")
               
                'fill the dataset/table with GOODS RECEIPTS DG data
                daGOODS_RECEIPTS = New FbDataAdapter("SELECT * FROM GOODS_RECEIPTS_DG WHERE GOODSRECEIPTSNUMBER =  " & ds.Tables("GOODS_RECEIPTS").Rows(0).Item("SEQ") & "", connection)
                daGOODS_RECEIPTS.FillSchema(ds, SchemaType.Source, "GOODS_RECEIPTS_DG")
                daGOODS_RECEIPTS.Fill(ds, "GOODS_RECEIPTS_DG")
                                 
                'fill the dataset/table with STOCK ADJUSTMENT TABLE  data
                daGOODS_RECEIPTS = New FbDataAdapter("SELECT * FROM STOCK_ADJUSTMENT_TABLE ", connection)
                daGOODS_RECEIPTS.FillSchema(ds, SchemaType.Source, "STOCK_ADJUSTMENT_TABLE")
                daGOODS_RECEIPTS.Fill(ds, "STOCK_ADJUSTMENT_TABLE")
    
                DataGridView1.DataSource = ds.Tables("GOODS_RECEIPTS")
                DataGridView2.DataSource = ds.Tables("GOODS_RECEIPTS_DG")
                DataGridView1.Refresh()
                DataGridView2.Refresh()
    
               
            Catch
                Timer1.Enabled = False
                connection.Close()
                Return
            End Try

  9. #9
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Datatable incorrectly loading from datasource.

    You don't HAVE to create a new dataadapter each time, though I'm not sure that there is any good reason not to, in your case. You were changing the UpdateCommand originally. There is also a SelectCommand, and you could have simply changed the CommandText of that each time rather than creating a new object. On the other hand, the performance would be virtually identical.
    My usual boring signature: Nothing

  10. #10

    Thread Starter
    Lively Member
    Join Date
    Jan 2013
    Posts
    66

    Re: Datatable incorrectly loading from datasource.

    Thank you Shaggy. I am here to learn and take advise. I have adjusted the code to use SelectCommand.
    Is this what you meant ?

    Code:
     connection.Open() 'Open connection
    
    
            'create a new FB dataadapter daGOODS_RECEIPTS
            Dim daGOODS_RECEIPTS As FbDataAdapter = New FbDataAdapter()
            Dim fbCmdBuilder = New FbCommandBuilder(daGOODS_RECEIPTS) 'activate command builder
    
    
            'create a new dataset
            Dim ds As New DataSet("GOODS_RECEIPTS_DATASET") 'Create dataset ds
            Dim GOODS_RECEIPTS As System.Data.DataTable = ds.Tables.Add("GOODS_RECEIPTS")
            Dim GOODS_RECEIPTS_DG As System.Data.DataTable = ds.Tables.Add("GOODS_RECEIPTS_DG")
            Dim STOCK_ADJUSTMENT_TABLE As System.Data.DataTable = ds.Tables.Add("STOCK_ADJUSTMENT_TABLE")
    
            Try 'Fill data set but if a queiry returns nothing and we try to fill a datatable with nothing an error occurers 
    
                'fill the dataset/table with GOODS RECEIPTS data
    
                daGOODS_RECEIPTS.SelectCommand = New FbCommand("SELECT FIRST 1 * FROM GOODS_RECEIPTS WHERE RECORDSTATE = 1", connection)
                daGOODS_RECEIPTS.FillSchema(ds, SchemaType.Source, "GOODS_RECEIPTS")
                daGOODS_RECEIPTS.Fill(ds, "GOODS_RECEIPTS")
               
                'fill the dataset/table with GOODS RECEIPTS DG data
                daGOODS_RECEIPTS.SelectCommand = New FbCommand("SELECT * FROM GOODS_RECEIPTS_DG WHERE GOODSRECEIPTSNUMBER =  " & ds.Tables("GOODS_RECEIPTS").Rows(0).Item("SEQ") & "", connection)
                daGOODS_RECEIPTS.FillSchema(ds, SchemaType.Source, "GOODS_RECEIPTS_DG")
                daGOODS_RECEIPTS.Fill(ds, "GOODS_RECEIPTS_DG")
                                 
                'fill the dataset/table with STOCK ADJUSTMENT TABLE  data
                daGOODS_RECEIPTS.SelectCommand = New FbCommand("SELECT * FROM STOCK_ADJUSTMENT_TABLE ", connection)
                daGOODS_RECEIPTS.FillSchema(ds, SchemaType.Source, "STOCK_ADJUSTMENT_TABLE")
                daGOODS_RECEIPTS.Fill(ds, "STOCK_ADJUSTMENT_TABLE")
    
                DataGridView1.DataSource = ds.Tables("GOODS_RECEIPTS")
                DataGridView2.DataSource = ds.Tables("GOODS_RECEIPTS_DG")
                DataGridView1.Refresh()
                DataGridView2.Refresh()
    
               
            Catch
                Timer1.Enabled = False
                connection.Close()
                Return
            End Try

  11. #11
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Datatable incorrectly loading from datasource.

    That will work, but even simpler would be this:

    daGOODS_RECEIPTS.SelectCommand.CommandText = "SELECT FIRST 1 * FROM GOODS_RECEIPTS WHERE RECORDSTATE = 1"

    The first time you use the dataadapter you'd have to give it the full SelectCommand the way you are doing it, but the next times you use it all you have to do is change the CommandText property of the object.
    My usual boring signature: Nothing

  12. #12

    Thread Starter
    Lively Member
    Join Date
    Jan 2013
    Posts
    66

    Re: Datatable incorrectly loading from datasource.

    Yes. Good have changed that too, just because I can.

    daGOODS_RECEIPTS.SelectCommand.CommandText = "SELECT * FROM STOCK_ADJUSTMENT_TABLE "

    Thank you.

Tags for this Thread

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