[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.

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")

---

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

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.

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.

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...

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.

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

---

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.

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

---

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.

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

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

Thank you.