Merge Table to Table

[ravenge]

Hi there. I have been trying to do this merge for a quite a while now and i think I am about to have enough. I have an access database that i want to connect to and want to merge a few columns from each record into another table with the same columns plus some more.

Eg. Table 1 fields: ID, aaa, bbb, ccc
Table 2 fields: ID, aaa, bbb, ccc, ddd, eee

Table 1 is the source table and table 2 is the new table that needs to have all fields from table 1 in it and then have the ability to add extra information into the other fields.

The code I have is as follows:

Dim conn As OleDbConnection = Me.DBConn 
conn.Open()
Dim ds As New DataSet
Dim da As OleDbDataAdapter = New OleDbDataAdapter("select ID, aaa, bbb, ccc from tbl1", conn)
da.Fill(ds, "tbl1")
Dim ds2 As New DataSet
Dim da2 As OleDbDataAdapter = New OleDbDataAdapter("select ID, aaa, bbb, ccc from tbl2", conn)
da2.Fill(ds2, "tbl2")
ds2.Merge(ds) ', True, MissingSchemaAction.Add)
ds2.AcceptChanges()
ds.AcceptChanges()
conn.Close()
'fill datagrid
dg.DataSource() = ds

This brings back no errors and updates the datagrid with the existing table but I have a feeling I am VERY close to being correct.

Please help on this as I am at wits end.

Thankyou very much in advance
John G

 

[kulrom]

Ok try this i hope you'll find sense ...

Dim parentCol as DataColumn
Dim childCol as DataColumn
Dim relCustOrder as DataRelation
 
dataadapter1_tbl1.Fill(dataset1,"tbl1")		' add "tbl1" in dataset
dataadapter2_tbl2.Fill(dataset1, "tbl2")	 ' add "tbl2" in dataset
 
' relations between tables:
parentCol = dataset1.Tables("tbl2").Columns("column/s")
childCol = dataset1.Tables("tbl1").Columns("column/s")
relCustOrder = New DataRelation("tbl1", parentCol, childCol)
dataset1.Relations.Add(relCustOrder)

Cheers

btw, welcome to the forum ... almost forgot

 

[ravenge]

Hi Kulrom and thanks for taking the tme to reply. Sorry I haven't replied sooner

I tried the code that you supplied but still did not have any success so I thought I would start from scratch and explain better what it is I am trying to accomplish.

I have a database (so.mdb) and have multiple tables in it. I am only working with 2 at the moment and they are "pos" and "so". The "pos" table is always being changed in the way of new records and changed records all the time with the running on the Point Of Sale software the store uses. The table "so" is a table that I have created because we need data from the "pos" table and also add some other information for our records. What I am trying to accomplish is when we load the small app that I am trying to create it runs through the "pos" table and adds any new records to the "so" table and also updates any changes made to the "pos" table back to the "so" table.

Main Database = Sales.mdb
Table = pos
Fields = line_id(key), status, stock_id, so_id <- there are more but dont need them

Table = so
Fields = id or line_id(key), status, stock_id, so_id, contacted and others that I add data to manually.


Is there an easy way to do this or is it really as hard as I am finding it?? Please let me know or even point me in the right direction. Thanks Everyone


I am sorry if I have confused anyone and if I have please let me know if you would like more information.
John G

p.s. Thanks heaps again Kulrom

 

[jmcilhinney]

First of all, it is bad practice to duplicate the data from your "pos" table in your "so" table. Just use the relationship between the two to join the tables in a select statement. That way, the information is actually coming from "pos" so it is always up to date. This means that your "pos" table contains its current fields and the "so" table only contains the extra fields. Your primary key value for the "so" table can be the same as that for your "pos" table.
Secondly, it seems to me that, unless some of the extra fields in the "so" table are not allowed to be null, there is no point having two tables. You could simply have the extra fields default to null, an empty string or some other desired value and use a single table. You just limit which fields you return when it is being used as the "pos" table and which fields can be updated when it is being used as the "so" table. If you really want to use two tables, you could insert a row to the "so" table with only the primary key whenever you insert a row to the "pos" table.

 

[ravenge]

Hi there jmcilhinney and thankyou very much for your time.

I have read through your response and looked at it on my end and had been working on it for a while now but i am stumped again so if anyone can help this is my question.

How do I add multiple records to the "so" table so I can use the primary keys to have a relationship?

Meaning.. Say in the "so" table I have 20 records and the id field values are 1 to 20 and in the "pos" table there is 24 records. How do I add 4 new records with default values. The main problem I have is the "pos" table has records deleted if the sale was never completed(this is done by the POS software and I think is silly). The main problem here is although there may be 24 records the value of the latest id field may be 29. So i would have to have 29 records in my new "so" table

My Question is: How do I lookup the highest value in the id field of "pos" and then the number of records in the "so" table and then add the required number of rows?

(line_id of "pos" = 29) - (quantity of records in "so" = 20) = 9 rows to add to "so"

I would also like to read through "pos" and for all the recordes that have been deleted then change a field value corresponding to that value in "so"

I am really sorry if I have confussed you in any of this but I think this is the best i can describe it.

Thanks Heaps to anyone that can help here
John G

 

[jmcilhinney]

It would appear from your most recent post that, due to restraints of existing software, you are forced to have the extra table. One thing I'm not sure of is whether, if rows are deleted from the "pos" table, are the same rows to be deleted, or not added as the case may be, from the "so" table? From your post it sounds like the rows missing from the "pos" table will still exist in the "so" table. This would preclude you from using the primary key of the "so" table as a foreign key from the "pos" table.

To get the highest ID value from a table create a (OleDb/Sql/...)Command with CommandText similar to:

SELECT MAX(ID) FROM Table

and then call ExecuteScaler on the command. This function will return a single value which you can cast as an Integer and assign to a variable.

To get the number of records in a table do the same with the following SQL statement:

SELECT COUNT(*) FROM Table

If you only want rows in "so" that correspond to rows in "pos" you will have to select every row from both, although not necessarily every column, and then check whether each ID in "pos" exists in "so" and, if not, insert a row.

I've tried to explain this clearly but it may still be a bit wordy. Of course, feel free to ask further questions if required.

 

[ravenge]

Thanks again jmcilhinney.

Yeah I do need to have another table because of the software that I am using but thats ok.

I am fairly new to vb.net so sorry for all the questions. Ultimataly what I would like to do is search the "pos" table and add any new records (line_id field) to the "so" table and then go through the "so" table and delete any records that do not exist from the "pos" table. I am sure that I cant use relationships if a corresponding record does not exist in one of the tables and will only cause problems.

What would be the best way to do this? would you mind pasting some starter code or links to the info I am after. I have been working on this problem for ages and about to beat the crap out of my computer. I have already dented the case to my computer from kicking it

Thanks for any help.
John G

 

[jmcilhinney]

I would suggest that the primary key for your "so" table be of the same type and hold the same values as the primary key for your "pos" table, but there should not be a relationship between the two. I guess you know this much already.

One way you can achieve your aim would be to select all the IDs for each table into their own DataTable. You could then check for the existence in the "so" table of each ID from the "pos" table and insert a row for each that is not found. You would then check for the existence in the "pos" table of each ID from the "so" table and delete the row of each that was not not found. Actually you would do these two the other way around. This would get fairly time consuming as the tables grew though. I'm sure there must be a more efficient way to accomplish this using more complex SQL statements. It's 1 AM in Sydney so I'll pack up for tonight and have another look tomorrow. Anyone else's input would be welcome in the mean time.

 

[ravenge]

Thanks Heaps again jmcilhinney, I really do appriciate your advice and will sit down tomorrow and play with some ideas and see what i can come up with by myself and then ask some more questions. It 12:30 in Adelaide and I am hitting the sack too and will look tomorrow.
Thanks Heaps Again!

 

[jmcilhinney]

Hey! A fellow Aussie! I didn't realise. My ex-flatmate and good friend is from Adelaide so perhaps there are less than six degrees of separation. Anyway, I had a small epiphany of sorts so I thought I would keep going. I think you should find that the following SQL will give you all the rows of the "pos" table where the ID does not exist in the "so" table:

SELECT * FROM pos WHERE ID NOT IN (SELECT ID FROM so)

You can change this to return just the ID if you want. Then you know all the IDs you have to add to the "so" table. You would reverse the table order to get all the rows from the "so" table where the ID does not exist in the "pos" table. These would be the rows you need to delete. I've never used a subquery in anything other than a select statement before but I don't see why you shouldn't be able to use this principle to perform at least the delete without ever bringing any data into your app. You could create a command and call ExecuteNonQuery using SQL like the following:

DELETE FROM so WHERE ID NOT IN (SELECT ID FROM pos)

 

[ravenge]

Hiya jmcilhinney, thanks heaps for your last bit of info. I have had a look at it and looked around the net but having problems. I thought I will post my bit of code and see where I am going wrong.

		Dim con As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\Trial.mdb;Persist Security Info=False;"
		Dim dbconn As New OleDbConnection(con)
		dbconn.Open()

		Dim sql1 As String
		sql1 = "select id from tbl1 where id not in (select id from tbl2)"
		Dim cmd1 As New OleDbCommand(sql1, dbconn)
		Dim da1 As New OleDbDataAdapter(cmd1)
		Dim ds1 As New DataSet
		da1.Fill(ds1, "tbl1")
		dg1.DataSource() = ds1

		dbconn.Close()
		dbconn.Dispose()

I am getting the error on the line da1.fill(ds1, "tbl1") and the error is: "An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll"

I also have this at the top of my page:
Imports System.Data.OleDb
Imports System.Data.SqlClient

But still no luck. where am i going wrong?

Thanks a Heap!
John G

 

[jmcilhinney]

I'm not sure what the issue is because your code works fine for me. Are you sure you got the table and field names correct?
Also, it won't do any harm but you don't need to import the System.Data.SqlClient namespace unless you are using SQL Server. All the objects are equivalent to those in the OleDb namespace but for use with different databases.

 

[ravenge]

Hi there again jmcilhinney,
Thanks heaps for you previous help but I am stuck again.

The code you helped me with earlier works fine. Even the Delete Command you suggested. I have been playing with this all day and have had no luck yet.

What I was wondering is I have seen a book (Some Wrox book) that you can call an insert statement that inserts directly from one table to the other. It is actually written like this in the book

INSERT INTO tbl2
(SELECT * FROM tbl1)

I have called the executenonquery on this but cant get it to work. I have taken your advice and looked into the help of VB.NET but cant find anything. Can anyone help me with this?

If this is not possible how is it easiest to to insert a dataset into a database. I mean the original code that you provided works great and i can read it into a dataset and view it in a datagrid but stuffed if I can update it into the database.

Thanks for all your help or anyone else that can help.
John G

 

[jmcilhinney]

I've never tried to insert using this method but I have read about it. I'm not sure if this is the issue but I would recommend always including the field list in an insert statement. It might not be a bad idea, at least while you're testing, to include the field list in the select statement as well. That way you know for sure that you have the correct field correspondence.

Given that you are using Access, it is quite possible that Access doesn't support this syntax. If that is true then you will have to retrieve the data from one table into a DataTable and then save it to the other table using a data adapter. If it comes to that, each row will need to have a RowState of Added to be inserted into second table. To achieve this, I recently discovered from another member that you need to set the AcceptChangesDuringFill property of the data adapter to False.

 

[ravenge]

Thanks once again jmcilhinney, I will have a play with this over the next few days and post a solution here (if I find one)