Trying to update a database with the contents of a dataset

[AlexW]

Hi

I have a dataset which contains the entire contents of a database. During the course of my program some records are added. I am having difficulty trying to find an SQL command that will transfer the added rows in one go (without using a tableadapter which I'm told I can't define programmatically).

What is the quickest way to add all the added records at once? (can I even do this?) do I have to define an enumerator and add each row individually? Note that dataset contains exactly the same columns as the database. What's wrong with my SQL command? Do I need to change the SQL command to explicitly reference the datasetcolumns eg updtable.columns("PriceEach")??

        Dim updTable As DataTable = GblDataSet.Tables("Inventory")
 
        myDataAdapter.UpdateCommand = New OleDbCommand("INSERT INTO Inventory ('PriceEach', 'RetailPrice', 'Markup', 'Shipping(each)', 'Notes', 'POsUsed', 'PartID', 'None', 'NumOnOrder') VALUES ('PriceEach', 'RetailPrice', 'Markup', 'Shipping(each)', 'Notes', 'POsUsed', 'PartID', 'None', 'NumOnOrder')")
 
 
        myDataAdapter.Update(updTable.Select(Nothing, Nothing, DataViewRowState.Added))

Any help would be most appreciated.

[AlexW]

Do I Have to make a really long SQL string to update multiple records at once eg?

INSERT INTO tablename (blah) VALUES ('asdf'); INSERT INTO tablename (blah) VALUES ('asdf'); INSERT INTO tablename (blah) VALUES ('asdf'); INSERT INTO tablename (blah) VALUES ('asdf');

Can somebody tell me if there is a more elegant solution?

[Jim Doherty]

Do I Have to make a really long SQL string to update multiple records at once eg?

INSERT INTO tablename (blah) VALUES ('asdf'); INSERT INTO tablename (blah) VALUES ('asdf'); INSERT INTO tablename (blah) VALUES ('asdf'); INSERT INTO tablename (blah) VALUES ('asdf');

Can somebody tell me if there is a more elegant solution?

You can INSERT directly from a SELECT ie:

INSERT INTO tblBlah2 (field1,Field2,Field3)
SELECT (field1,field2,field3 FROM tblBlah1 WHERE Field3='Blahblahblah'

Jim :)

[AlexW]

Ok, but I still can't get the database to update. The code for my commit changes to database function is shown below. The problem arises when I start trying to update the database with the inserted rows (towards the end). You can see I've tried lots of different approaches (commented out) but am still getting nowhere.... still get message saying "syntax error in INSERT INTO" statement when I try and update the database. Can anyone help???

Public Function commitChangesToDatabase(ByVal tableName As String)
        Dim commandString As String
        Dim ConnectionString As String
        Dim myDataAdapter As OleDbDataAdapter
        Dim test As Object
        ' initialisation
        'connection string should point to the location of the inventory
        ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + "C:\Documents and Settings\User\Desktop\Devel\inventory analysis" + "\Inventorytest.mdb"
        commandString = "Select * From " + tableName
 
        'setting up the dataAdapter
        myDataAdapter = New OleDbDataAdapter(commandString, ConnectionString)
        Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(myDataAdapter)
 
        'filling the dataset using the dataAdapter
        Dim updTable As DataTable = GblDataSet.Tables("Inventory")
 
        ' First process deletes.
        myDataAdapter.Update(updTable.Select(Nothing, Nothing, DataViewRowState.Deleted))
 
        ' Next process updates.
        myDataAdapter.Update(updTable.Select(Nothing, Nothing, DataViewRowState.ModifiedCurrent))
 
        ' Finally, process inserts. Note that everything seems to work up to this point and That I have tried a few approaches here but most are commented out
        'myDataAdapter.UpdateCommand = New OleDbCommand("INSERT INTO Inventory (Select *)")
        'myDataAdapter.UpdateCommand = New OleDbCommand("INSERT INTO Inventory ('PriceEach', 'RetailPrice') select ('PriceEach', 'RetailPrice') from Inventory")
        'myDataAdapter.UpdateCommand = New OleDbCommand("INSERT INTO Inventory (PriceEach, RetailPrice) select (PriceEach, RetailPrice")
        'myDataAdapter.UpdateCommand.CommandText = "INSERT VALUES"
 
      'trying different methods of updating database
      'myDataAdapter.Update(updTable.Select(Nothing, Nothing, DataViewRowState.Added), "Inventory")
        'myDataAdapter.Update(updTable.DataSet, "Inventory")
        myDataAdapter.Update(GblDataSet, tableName)
 
    End Function

Note that I'm trying to copy the added rows in the inventory table of my dataset to the inventory table of the database

[Jim Doherty]

Ok, but I still can't get the database to update. The code for my commit changes to database function is shown below. The problem arises when I start trying to update the database with the inserted rows (towards the end). You can see I've tried lots of different approaches (commented out) but am still getting nowhere.... still get message saying "syntax error in INSERT INTO" statement when I try and update the database. Can anyone help???

Public Function commitChangesToDatabase(ByVal tableName As String)
Dim commandString As String
Dim ConnectionString As String
Dim myDataAdapter As OleDbDataAdapter
Dim test As Object
' initialisation
'connection string should point to the location of the inventory
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + "C:\Documents and Settings\User\Desktop\Devel\inventory analysis" + "\Inventorytest.mdb"
commandString = "Select * From " + tableName
 
'setting up the dataAdapter
myDataAdapter = New OleDbDataAdapter(commandString, ConnectionString)
Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(myDataAdapter)
 
'filling the dataset using the dataAdapter
Dim updTable As DataTable = GblDataSet.Tables("Inventory")
 
' First process deletes.
myDataAdapter.Update(updTable.Select(Nothing, Nothing, DataViewRowState.Deleted))
 
' Next process updates.
myDataAdapter.Update(updTable.Select(Nothing, Nothing, DataViewRowState.ModifiedCurrent))
 
' Finally, process inserts. Note that everything seems to work up to this point and That I have tried a few approaches here but most are commented out
'myDataAdapter.UpdateCommand = New OleDbCommand("INSERT INTO Inventory (Select *)")
'myDataAdapter.UpdateCommand = New OleDbCommand("INSERT INTO Inventory ('PriceEach', 'RetailPrice') select ('PriceEach', 'RetailPrice') from Inventory")
'myDataAdapter.UpdateCommand = New OleDbCommand("INSERT INTO Inventory (PriceEach, RetailPrice) select (PriceEach, RetailPrice")
'myDataAdapter.UpdateCommand.CommandText = "INSERT VALUES"
 
'trying different methods of updating database
'myDataAdapter.Update(updTable.Select(Nothing, Nothing, DataViewRowState.Added), "Inventory")
'myDataAdapter.Update(updTable.DataSet, "Inventory")
myDataAdapter.Update(GblDataSet, tableName)
 
End Function

Note that I'm trying to copy the added rows in the inventory table of my dataset to the inventory table of the database

OK lets work through some of your failures then you will see where it is you have gone wrong

Line 25 insertion syntax is incorrect

"INSERT INTO Inventory (Select *)"

You are not telling it where to insert from in other words (select *) but from what which table FROM? In addition remove those brackets surrounding it the string merely needs to be commenced with a speechmarks and end with a speechmark drop the bracketing around it

Line 26 insertion syntax is incorrect

I know this is not what you had in mind but If you had wanted an insert of the literal words 'PriceEach' and 'RetailPrice' then this is what you would use and here you would not need to reference a table FROM because the words are literal and not being fetched from a table in that case if you understand me.

"INSERT INTO Inventory ( PriceEach, RetailPrice )
SELECT 'PriceEach' AS PriceEach, 'RetailPrice' AS RetailPrice;"

Your mention wanting to insert field data back into itself from the same table referenced thats ok.

This is what you need plain and simple nothing extra no extra bracketing

 
"INSERT INTO Inventory (PriceEach, RetailPrice)
SELECT Inventory.PriceEach, Inventory.PriceEach
FROM Inventory;"
 

Again other than the brackets laid out my sample above you don't need any more than that to be synxtax correct for an insert derived from a select in Access

As an aside if you are unsure of the textual requirements for SQL its a simple thing to actually open access and let access do it for you by simply playing with the query grid and that inventory table you might particularly want to look at how 'Access' creates an 'append' query to cater for your requirement to insert data from inventory back into itself. You will be able to see the SQL it generates by looking in the SQL window (query design....menubar...SQL view) You could paste that into your application code and surround it with speechmarks to make it a string


Hope this helps you

Jim :)