• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 890
  • Last Modified:

Convert an Append Query (MS Access) into a VB.Net SQL Statement.

Below is a SQL Append Query from MS Access. It compare records from 2 tables ( "PR Supplier Maste" & "PR Supplier Master - new") and append new record from "PR Supplier Master - new" to "PR Supplier Master" where the records does not exist in "PR Supplier Master"

INSERT INTO [PR Supplier Master] ( SuppCode, SuppName, CurrencyCode )
SELECT [PR Supplier Master - new].SuppCode, [PR Supplier Master - new].SuppName, [PR Supplier Master - org].CurrencyCode
FROM [PR Supplier Master - new] LEFT JOIN [PR Supplier Master] ON [PR Supplier Master - new].SuppCode = [PR Supplier Master].SuppCode
WHERE ((([PR Supplier Master].SuppCode) Is Null))
GROUP BY [PR Supplier Master - new].SuppCode, [PR Supplier Master - new].SuppName, [PR Supplier Master - new].CurrencyCode;

How can i convert it and use it in VB.net Program.
I did tried to compare 2 table using SELECT statement and use UPDATE Statement to update the records one by one. But it take a long long time (about 30 mins) to update 8000++ records.

Anyone here can help on this problem????
0
ian_khoo_sc
Asked:
ian_khoo_sc
  • 6
  • 4
1 Solution
 
Bob LearnedCommented:
1) .NET version?

2) Can you give me an example of what you attempted?

Bob
0
 
SanclerCommented:
It looks from your description as though your two tables are identical in structure and that SuppCode is the PrimaryKey (or at least constrained to be unique) in each.  If that is so, the following function should work

    Public Function Difference(ByVal tb1 As DataTable, ByVal tb2 As DataTable, ByVal pk As String) As DataTable

        'Assumes input datatables have same structure and that ...
        '... pk (which is a column/field name) is common to both ...
        '... and its values are unique within both.  It will ...
        '... normally be the name of the Primary Key field

        'NOTE: It is important in which order tables are put ...
        '... in arguments.  Function returns those rows in tb2 ...
        '... which do not appear in tb1

        Dim dra() As DataRow
        Dim dr As DataRow
        Dim ftr As String
        Dim result As New DataTable
        'give new table correct structure
        result = tb1.Clone
        'put pk values for one table into filter string
        For Each dr In tb1.Rows
            ftr = ftr & "'" & dr(pk) & "', "
        Next
        'tidy up and complete filter string
        ftr = pk & " NOT IN (" & ftr.Substring(0, ftr.LastIndexOf(",")) & ")"
        'filter other table with it
        dra = tb2.Select(ftr)
        'put results into new table
        For Each dr In dra
            result.ImportRow(dr)
        Next
        Return result

    End Function

You would use it on the following lines

    Private myConnection As OleDbConnection
    Private myOldAdapter, myNewAdapter As OleDbDataAdapter
    Private oldTableName, newTableName As String
    Private myOldTable, myNewTable, myDifferenceTable As DataTable

    Private Sub LoadTables()

        Dim connString As String = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = <Your database path goes here>;"
        myConnection = New OleDbConnection(connString)
        newTableName = "[PR Supplier Master - new]"
        oldTableName = "[PR Supplier Master]"
        Dim selectOldSQL As String = "SELECT * FROM " & oldTableName
        Dim selectNewSQL As String = "SELECT * FROM " & newTableName
        myOldAdapter = New OleDbDataAdapter(selectOldSQL, myConnection)
        myOldTable = New DataTable
        myOldAdapter.Fill(myOldTable)
        myNewAdapter = New OleDbDataAdapter(selectNewSQL, myConnection)
        myNewTable = New DataTable
        myNewAdapter.Fill(myNewTable)

    End Sub

    Private Sub doUpdate()

        myDifferenceTable = Difference(myOldTable, myNewTable, "SuppCode")
        myOldTable.Clear()
        For Each dr As DataRow In myDifferenceTable.Rows
            Dim nr As DataRow = myOldTable.NewRow
            nr.ItemArray = dr.ItemArray
            myOldTable.Rows.Add(nr)
        Next
        Dim cb As New OleDbCommandBuilder(myOldAdapter)
        myOldAdapter.Update(myOldTable)

    End Sub

A couple of comments.  I note that your current MS Access Query has a ref to [PR Supplier Master - org].CurrencyCode.  The "org" in that suggests that there might be yet another table.  If there is, then this approach may need modification (or may even not work at all).

The code in the doUpdate sub (a) clears the old table and (b) copies the rows from the difference table to it.  This is simply so that it can use the auto-generated update command of the existing data adapter for the old table.  Copying the data, rather than importing or merging the rows, makes sure that the row state of the new rows is "Added" so that the data adapter will recognise that they need to be inserted.  

In VB.NET 2005 (I think, although I haven't used it yet) you can programmatically change the rowstate of imported rows with the DataRow.SetAdded method.  So there are obviously other ways to do this, and they might be more efficient.  But at least I know that this one works - for me.  And I reckon it would be (much) quicker than what you've tried so far.

Roger
0
 
SanclerCommented:
I was thinking about this while walking the dog.  Of course it can be made more efficient.

If this line

        myNewAdapter.AcceptChangesDuringFill = False

is added immediately after this line

        myNewAdapter = New OleDbDataAdapter(selectNewSQL, myConnection)

in the LoadTables sub, it will ensure that the row states of all rows in myNewTable are "Added".  Which means that the doUpdate sub can be as simple as

        myOldTable = Difference(myOldTable, myNewTable, "SuppCode")
        Dim cb As New OleDbCommandBuilder(myOldAdapter)
        myOldAdapter.Update(myOldTable)

Roger
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
SanclerCommented:
Another performance tweak - given the number of records you might be dealing with - would be to use a StringBuilder, rather than a String, in the Difference function.  I just copied and pasted that from one of my Utility modules but, after thinking about it and doing some testing, I've now made that change in my module anyway.  Here's the revised version.

    Public Function Difference(ByVal tb1 As DataTable, ByVal tb2 As DataTable, ByVal pk As String) As DataTable

        'Assumes input datatables have same structure and that ...
        '... pk (which is a column/field name) is common to both ...
        '... and its values are unique within both.  It will ...
        '... normally be the name of the Primary Key field

        'NOTE: It is important in which order tables are put ...
        '... in arguments.  Function returns those rows in tb2 ...
        '... which do not appear in tb1

        Dim dra() As DataRow
        Dim dr As DataRow
        Dim sb As New System.Text.StringBuilder(tb1.Rows.Count * 8)
        '... the * 8 is arbitrary, but it's unlikely that ...
        '... any pk value will be shorter than 4 and then ...
        '... there's the quotes, comma and space
        Dim ftr As String
        Dim result As New DataTable
        'give new table correct structure
        result = tb1.Clone
        'collect pk values for one table for filter string
        For Each dr In tb1.Rows
            sb.Append("'" & dr(pk) & "', ")
        Next
        'use string builder result to make filter string
        ftr = pk & " NOT IN (" & sb.ToString.Substring(0, sb.ToString.LastIndexOf(",")) & ")"
        'filter other table with it
        dra = tb2.Select(ftr)
        'put results into new table
        For Each dr In dra
            result.ImportRow(dr)
        Next
        Return result

    End Function

I've did my tests trying to replicate your conditions.  I used dummy data with 7900 3-field records in the old table and 8000 in the new one.  Absolute times will obviously depend on the system but, for the previous version of the Difference function my time was ~29 seconds.  With the above amendment to that it was ~22 seconds.

That is somewhat better than 30 minutes ;-)

Roger
0
 
ian_khoo_scAuthor Commented:
Thanks for the feedback...

When i tried to run the code provided, there was an error on the following line:

myOldAdapter.Update(myOldTable)

It is complaining --> Syntax error in INSERT INTO statement.
   at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
   at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)

Do i need to use a DataSet?
Thanks
0
 
SanclerCommented:
>>
Do i need to use a DataSet?
<<

No, that shouldn't be necessary.

The best idea is to check to see what the INSERT statement is.  Put these lines, temporarily

        Debug.WriteLine(cb.GetInsertCommand.CommandText)
        For Each param As OleDbParameter In cb.GetInsertCommand.Parameters
            With param
                Debug.WriteLine(.ParameterName & "; " & .OleDbType.ToString & "; " & .SourceColumn)
            End With
        Next

immediately after

        Dim cb As New OleDbCommandBuilder(myOldAdapter)

and see if that reports anything odd.

Roger

 
0
 
ian_khoo_scAuthor Commented:
This is what the debug.writeLine written:

INSERT INTO PR Supplier( SuppCode , SuppName , CurrencyCode ) VALUES ( ? , ? , ? )
@p1; VarWChar; SuppCode
@p2; VarWChar; SuppName
@p3; VarWChar; CurrencyCode

I using the step in method, i was able to see the data was read into the array...
But i got no idea why the insert statement will pass in the value with "?".
0
 
SanclerCommented:
>>
But i got no idea why the insert statement will pass in the value with "?".
<<

That's the way OleDb works with MS Access.  For that, what matters is the ORDER in which the parameters appear in the CommandText.  Parameter NAMES, even if they are given, are ignored.

So far as I can see the problem is that the table name - PR Supplier - has a space in it.  As I never uses spaces in my Access table names I hadn't come across this before.  I would have expected that, if a table name was passed into a SELECT command with square brackets round it because it has a space in it those square brackets would persist into the INSERT command, too.  But, having tested, I see that that is not so.  The ideal solution would, I think, be to change the table name in Access to get rid of the space.  E.g. change it from PR Supplier to PR_Supplier.  Would that be possible?

If not, we'll have to see if a work-around in code is possible.

Roger
0
 
ian_khoo_scAuthor Commented:
Ya....you are right. I taken away the space from the table name and it is WORK...
It is very difficult for me to change the table name, coz it will affect my other program.

Please suggest some work-around code...
Thanks...
0
 
SanclerCommented:
I think that, rather than messing with the insert command automatically built by the CommandBuilder, it would be better just to code your own.  I reckon it will be along these lines

        myOldTable = Difference(myOldTable, myNewTable, "MyID")
        Dim cmd As New OleDbCommand
        With cmd
            .CommandText = "INSERT INTO [PR Supplier]( SuppCode , SuppName , CurrencyCode ) VALUES ( ? , ? , ? )"
            .CommandType = CommandType.Text
            .Connection = myConnection
            .Parameters.Add("@p1", OleDbType.VarWChar, 50, "SuppCode")
            .Parameters.Add("@p2", OleDbType.VarWChar, 50, "SuppName")
            .Parameters.Add("@p3", OleDbType.VarWChar, 50, "CurrencyCode")
        End With
        myOldAdapter.InsertCommand = cmd
        myOldAdapter.Update(myOldTable)

I'm not sure about the size - the "50"s - in the code for the parameters, as I didn't ask you to get the Debug to write those out.  But if you don't know them you can check using the previous code what those are by adding .Size to the parameters Debug print out.  If they are not 50, substitute the appropriate numbers in the above code.

I've just tested code like that - although with differences in names as my Access table is not identical to yours - and it works OK for me with a space in the table name.

Roger
0
 
ian_khoo_scAuthor Commented:
This work perfectly!!!
Thanks a lot for help...:D
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now