Quick VB.net Q

I

initroot

Senior Member
Joined
Jul 30, 2011
Messages
898
Reaction score
45
Location
Cape Town
Hi,
So ive checked that the database doesn't replace the one in build when running. "Copy if newer" is selected.
I have a datatable in memory loaded with info. Ive connected a database (access) to my project, datasource, setup the dataset etc etc.

Problem is im trying to copy the rows from the datatable in memory to the datatable in my dataset. (identical structure)

Code: 
For Each dr As DataRow In dtExcelImportedData.Rows
                PlanningDBDataSet.DataImported.ImportRow(dr)
            Next

Im trying to save these data in the dataset using:

Code: 
Me.PlanningDBDataSet.DataImported.Clear()
           Me.Validate()
           Me.DataImportedBindingSource.EndEdit()
           Me.TableAdapterManager.UpdateAll(Me.PlanningDBDataSet)

This code runs fine but never updates my database.
If I use datagrid dragged via datasource toolbox onto the form, and manually enter data and use the save code, it works just fine.

The data is being imported correctly in memory. Ive hooked the datatable from my dataset to datagrid and refresh after the copy is done and all +30k records is loaded.

Any help on what I might be missing here? Suggestions to improve is welcomed as always.
 
Without really having code in my possession, but aren't you clearing the imported data set with .Clear();? I assume you have a BeginEdit as well?
 
Wouldnt that only be required when using the datagridview. In the code im copying directly into the datatable in memory.
 
Its been a while since ive used those components, but still what about the Clear()?
 
Is it possible for you to zip up the project and send it to me? Would be easier:P
 
I haven't worked with DataSets in years, but I think you issue relates to ImportRow maintaining RowState. When a TableAdapter runs an update, it gets the changes rows (Same as dataTable.GetChanges()). Those changes are then applied to the database.

When you manually add rows using NewRow and Add(row), it sets the RowState, but if you are importing from a DataTable that effectively has not changes, it will maintain that RowState.

Try:

Code: 
For Each dr As DataRow In dtExcelImportedData.Rows
                dr.SetAdded()
                PlanningDBDataSet.DataImported.ImportRow(dr)
            Next

The example above will create effect changes in dtExcelImportedData rows as well. If you want to avoid this, you could also do:

Code: 
For Each dr As DataRow In dtExcelImportedData.Rows
                PlanningDBDataSet.DataImported.ImportRow(dr)
            Next
            
            For Each dr As DataRow In PlanningDBDataSet.DataImported.Rows
                dr.SetAdded()
            Next

It's an additional loop, but I assume the ImportRow function clones the Row - although that is just an assumption.
 
me_ said:
I haven't worked with DataSets in years, but I think you issue relates to ImportRow maintaining RowState. When a TableAdapter runs an update, it gets the changes rows (Same as dataTable.GetChanges()). Those changes are then applied to the database.

When you manually add rows using NewRow and Add(row), it sets the RowState, but if you are importing from a DataTable that effectively has not changes, it will maintain that RowState.

Try:

Code: 
For Each dr As DataRow In dtExcelImportedData.Rows
                dr.SetAdded()
                PlanningDBDataSet.DataImported.ImportRow(dr)
            Next

The example above will create effect changes in dtExcelImportedData rows as well. If you want to avoid this, you could also do:

Code: 
For Each dr As DataRow In dtExcelImportedData.Rows
                PlanningDBDataSet.DataImported.ImportRow(dr)
            Next
            
            For Each dr As DataRow In PlanningDBDataSet.DataImported.Rows
                dr.SetAdded()
            Next

It's an additional loop, but I assume the ImportRow function clones the Row - although that is just an assumption.
Click to expand...

Thanks going to try this out when I get home!
 
Fransh said:
Thanks works great!!
Another question, why on earth is it so slow. Takes over 3min just to copy 28 000 records
Click to expand...

I generally avoid DataSets and TableAdapters because they are generally quite slow, but that seems slow even for a DataSet. Have you put timers in to see which steps are taking so long?
Is it the Update statement that takes so long, the ImportRow or reading the Excel sheet?
What database are you using and are you using the native driver - SQL Native is a lot quicker than ODBC.
 
Its just for local desktop, so im using access. Don't want to setup sql for just that and can't go sql lite cause I'll lose the stored procedures.

It's the update statement, already did the timing. Modifying the data once it is in the database is quick since I create the stored procedures in access in order to be more "native" to the access. So yes im using ODBC.
 
Fransh said:
Its just for local desktop, so im using access. Don't want to setup sql for just that and can't go sql lite cause I'll lose the stored procedures.

It's the update statement, already did the timing. Modifying the data once it is in the database is quick since I create the stored procedures in access in order to be more "native" to the access. So yes im using ODBC.
Click to expand...

Access is dog slow. I'm not surprised then. You can always look at SQL Express or SQL Compact.
You can however upgrade to OLEDB which should be faster than ODBC. ODBC is meant for maximum compatibility, not performance.
 
SQL Express and Compact doesn't support stored procedures i've read? Did I maybe understand wrong, thanks moved over to OLEDB and definitely better.
 
Fransh said:
SQL Express and Compact doesn't support stored procedures i've read? Did I maybe understand wrong, thanks moved over to OLEDB and definitely better.
Click to expand...

SQL Express definitely supports stored procs. Not sure about compact - probably not there. SQL Express has most of the features of standard, but with a limitation of 10GB databases and only 1GB RAM per instance. This is plenty for most small applications.
The only issue with it is distributing it with an application. It is a bit heavy to install and setup in comparison to an Access DB.
 
me_ said:
SQL Express definitely supports stored procs. Not sure about compact - probably not there. SQL Express has most of the features of standard, but with a limitation of 10GB databases and only 1GB RAM per instance. This is plenty for most small applications.
The only issue with it is distributing it with an application. It is a bit heavy to install and setup in comparison to an Access DB.
Click to expand...
Then its prob best not to go with, since my app has to be very portable. Thanks for the info, will def come in handy when I start a new project.
At the moment it runs under 1min using the oledb. The big db transfer is only used every 3 months to move bulk data, so it should be just fine.
 
Fransh said:
Then its prob best not to go with, since my app has to be very portable. Thanks for the info, will def come in handy when I start a new project.
At the moment it runs under 1min using the oledb. The big db transfer is only used every 3 months to move bulk data, so it should be just fine.
Click to expand...

If your app needs to be portable then look at sqlite as your database. It's a PITA to install sql express on client machines just to get your app to run. if you control the setup, then that's fine I guess, but it takes away from the "very" part of your "portable" requirement :) sqlite is way better than ms access and works almost exactly like sql server/ms tech that you are used to.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X