Problems in Dataset Update
-
I am having problem with dataset update! I am using a DataAcesTier to access data and update the data and presentation tier is used to do all the manipulation! Presentation Tier uses DataAcessTier to retrieve the data but when i try to update the database with dataset , NOTING HAPPENS! NO ERROR but NO UPDATES too.. Here is how it goes!!! I have a class with the name AccessData in my DataAccessTier and it have somehow this sort of code public sdaMajorDataAdapter as new SqlDataAdapter public strAllDBTables(50) as string 'strAllDBTables Array is inialized with table names with a sub routine Public Function CreateDataset() As DataSet Dim dsMajorDataSet As New DataSet OpenDbConnection() ' some code for dbcommand and dbconnection variables etc 'For loop used to loop through the table names For iCounter = 0 to strAllDBTables.length(0) - 1 sdaMajorDataAdapter.Fill(dsMajorDataSet, strAllDBTables(iCounter)) Next 'I have populated the dataset with all the DB tables supposedly CloseDBConnection() 'this DS is returned to the class from where its accessed return dsMajorDataSet End Function ====== Similary in the same class i have a Function for DB update Public Sub UpdateDataSet(ByVal updateDS As DataSet, ByVal strTableName As String) OpenDbConnection() dim dsUpdate as new dataset 'dbCommand.Connection = dbConnection 'dbCommand.CommandText = "Select * from " & strTableName 'sdaUpdate.SelectCommand = dbCommand 'sdaMajorDataAdapter.Fill(dsUpdate, strTableName) 'I have tried to use dataadapter to fill a new dataset and then try to update .no gain sdaMajorDataAdapter.Update(updateDS, strTableName) 'Which DataAdapter is to be used here ... or do i need to add update queries 'with adapter?, if YES , how to add it? ' There is no error here but NO UPDATES too? CloseDBConnection() End Sub ======== 'i am accessing these functions in Presentation Tier Class with the name AdminControlPanel Class 'here i have a variable dim dsControlPanel as new dataset dim dsRead as new AccessData ' an object of AccessData Class dsControlPanel = dsRead.CreateDataSet() ' than all the manipulation is done here and after that i return the DS to data access class for updation like this dsRead.UpdateDataSet(dsControlPanel, "Name of the table to be updated") 'No Error but nothing happens? i guess i am doing something wrong as to which dataAdapter is to be
-
I am having problem with dataset update! I am using a DataAcesTier to access data and update the data and presentation tier is used to do all the manipulation! Presentation Tier uses DataAcessTier to retrieve the data but when i try to update the database with dataset , NOTING HAPPENS! NO ERROR but NO UPDATES too.. Here is how it goes!!! I have a class with the name AccessData in my DataAccessTier and it have somehow this sort of code public sdaMajorDataAdapter as new SqlDataAdapter public strAllDBTables(50) as string 'strAllDBTables Array is inialized with table names with a sub routine Public Function CreateDataset() As DataSet Dim dsMajorDataSet As New DataSet OpenDbConnection() ' some code for dbcommand and dbconnection variables etc 'For loop used to loop through the table names For iCounter = 0 to strAllDBTables.length(0) - 1 sdaMajorDataAdapter.Fill(dsMajorDataSet, strAllDBTables(iCounter)) Next 'I have populated the dataset with all the DB tables supposedly CloseDBConnection() 'this DS is returned to the class from where its accessed return dsMajorDataSet End Function ====== Similary in the same class i have a Function for DB update Public Sub UpdateDataSet(ByVal updateDS As DataSet, ByVal strTableName As String) OpenDbConnection() dim dsUpdate as new dataset 'dbCommand.Connection = dbConnection 'dbCommand.CommandText = "Select * from " & strTableName 'sdaUpdate.SelectCommand = dbCommand 'sdaMajorDataAdapter.Fill(dsUpdate, strTableName) 'I have tried to use dataadapter to fill a new dataset and then try to update .no gain sdaMajorDataAdapter.Update(updateDS, strTableName) 'Which DataAdapter is to be used here ... or do i need to add update queries 'with adapter?, if YES , how to add it? ' There is no error here but NO UPDATES too? CloseDBConnection() End Sub ======== 'i am accessing these functions in Presentation Tier Class with the name AdminControlPanel Class 'here i have a variable dim dsControlPanel as new dataset dim dsRead as new AccessData ' an object of AccessData Class dsControlPanel = dsRead.CreateDataSet() ' than all the manipulation is done here and after that i return the DS to data access class for updation like this dsRead.UpdateDataSet(dsControlPanel, "Name of the table to be updated") 'No Error but nothing happens? i guess i am doing something wrong as to which dataAdapter is to be
You've told the DataAdapter how to retrieve the columns, well, all of them, from the table, but you never told it how to update the database with changes. It's much better practice to change your SQL to retrieve the columns to want instead of using the * specifier. This prevent your from making a change to the table schema and breaking your code doing it. In most case, you're end up returning far more data than you actually need, thereby killing your SQL Server performance, and if the dataset is transferred over a network, transferring way too much information and bogging down the network. How you've written your data layer in not a good idea. You're creating a generic method that returns a DataAdapter, but that DataAdapter doesn't have a clue about what columns it's supposed to
SELECT IdColumn, SomeColumn, SomeOtherColumn FROM TableNameWhen you create the DataAdpater, you need to tell it how to update the coumns in the tables. To do this, you either have to supply the DataAdapter with the SQL commands for UPDATE, INSERT, and DELETE, or you have to use an SqlCommandBuilder object to do it for you:
Dim conn As New SqlConnection(_connectionString_) Dim comm As New SqlCommand("SELECT IdColumn, SomeColumn FROM SomeTable", conn) Dim da As New SqlDataAdapter(comm) Dim cb As New SqlCommandBuilder(da) Dim ds As New DataSet() da.Fill(ds) Return dsWarning: This could be a little off. I wrote it from memory, and I'm half asleep right now...
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007 -
You've told the DataAdapter how to retrieve the columns, well, all of them, from the table, but you never told it how to update the database with changes. It's much better practice to change your SQL to retrieve the columns to want instead of using the * specifier. This prevent your from making a change to the table schema and breaking your code doing it. In most case, you're end up returning far more data than you actually need, thereby killing your SQL Server performance, and if the dataset is transferred over a network, transferring way too much information and bogging down the network. How you've written your data layer in not a good idea. You're creating a generic method that returns a DataAdapter, but that DataAdapter doesn't have a clue about what columns it's supposed to
SELECT IdColumn, SomeColumn, SomeOtherColumn FROM TableNameWhen you create the DataAdpater, you need to tell it how to update the coumns in the tables. To do this, you either have to supply the DataAdapter with the SQL commands for UPDATE, INSERT, and DELETE, or you have to use an SqlCommandBuilder object to do it for you:
Dim conn As New SqlConnection(_connectionString_) Dim comm As New SqlCommand("SELECT IdColumn, SomeColumn FROM SomeTable", conn) Dim da As New SqlDataAdapter(comm) Dim cb As New SqlCommandBuilder(da) Dim ds As New DataSet() da.Fill(ds) Return dsWarning: This could be a little off. I wrote it from memory, and I'm half asleep right now...
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007I was just trying to get all the data at front end in dataset! Thats why populated it with all the tables!!! So, I have to populate my dataset with a single table if i intend to use SqlCommandBuilder! I was trying to use it too but even this was not working, probrably becasue of multiple tables in dataset !!!.. Thanks for the advice sir!