What is the fastest way to update all rows of a table in a dataset

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Ado
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - Any way to make OleDb read an Excel file? With Visual Studio 2005 SP1 and DotNet Framework 2 SP1, Excel 2003 can read a file that was saved by Excel 2003, but the OleDb driver can't. Example: Excel row 2 maps to row 1 in OleDb, and Excel columns 1 to 24 (A to X) map to columns 0 to 23 in OleDb. Counting these in OleDb, row 1 columns 0 to 4 are OK, columns 5 and 6 get corrupted to DbNull, columns 7 to 12 are OK, columns 13 and 14 get corrupted to DbNull, etc. If I do Interop to Excel 2003 instead of using OleDb, it works. Counting these in Excel interop, row 2 columns 1 to 24 are entirely OK. Back to the OleDb problem. One worksheet has 40 columns instead of 24. The worksheet with 40 columns works. Several of the additional columns read as doubles instead of text but ToString() works on them. But all of the 24-column worksheets get corrupted by OleDb, and the corrupted column numbers vary. Does anyone know how to get OleDb to work? OleDbConnection connection = new OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HDR=NO'"); connection.Open(); DataTable schemaTable = connection.GetSchema("Tables"); DataRow[] schemaRows = schemaTable.Select(); foreach (DataRow schemaRow in schemaRows) { string tableName = schemaRow["TABLE_NAME"].ToString(); OleDbDataAdapter dataAdapter = new OleDbDataAdapter( "SELECT * FROM [" + tableName + "]", connection); int headerRowNum = 1; DataTable dataTable = new DataTable(tableName); dataAdapter.FillSchema(dataTable, SchemaType.Source); DataColumnCollection dataColumns = dataTable.Columns; int columnCount = dataColumns.Count; foreach (DataColumn dataColumn in dataColumns) { dataColumn.DataType = typeof(string); } dataAdapter.Fill(dataTable); int rowCount = dataTable.Rows.Count; if (!((rowCount > headerRowNum) && ((columnCount == 24) || (columnCount == 40)))) { dataTable.Dispose(); dataAdapter.Dispose(); continue; } DataRow headerRow = dataTable.Rows[headerRowNum]; // ... examine tableName in the debugger; it is correct including $ ... // ... examine headerRow in the debugger; some elements are null ... string x = (string)headerRow[5]; // crashes Using Odbc, besides the obvious changes to the DotNet class names and the connection string, one more change is this: int headerRowNum = 0; instead of int headerRowNum = 1; The reason of course is that HDR=NO in Odbc means HDR=YES. So with those changes, I read the Excel file in Odbc instead of OleDb. The corruption in Odbc is identical to the corruption in OleDb. Does anyone know how to get either OleDb or Odbc to read an Excel file? I think I have more news though. Although Excel 2003 saved the .xls file, now I guess that Excel 97 or 2000 or XP first created the .xls file. Here's the reason: In all of the cells which I'm trying to interpret, either the cell value was alphabetic, or the cell value had an apostrophe followed by digits, so the cell values were already text. For some other cells, Excel 2003 displays them with a little triangle in one corner, and a tooltip explains that the cell is text due to one of those reasons. However, for the cells which I'm trying to interpret, Excel 2003 doesn't display that triangle ... at first. However, if I put the mouse cursor in one of those cells and double-click, but do not type anything, and then move the mouse cursor to another cell and click, then suddenly Excel 2003 displays that little triangle in one corner of the cell that I double-clicked, and a tooltip explains that the cell is text due to one of those reasons. Furthermore, if I hit Ctrl+S to save the file, then suddenly OleDb starts correctly reading the cell that I double-clicked. So, was there a bug in Excel 97 or 2000 or XP, which corrupted the format when saving some cells which were text due to having apostrophe plus digits, but which saved some other cells correctly? And Excel 2003 contains a hack to read corrupted .xls files correctly? But OleDb is missing that hack so it gets all confused when reading files which were saved by Excel 97 or 2000 or XP (or saved by Excel 2003 without having double-clicked a corrupted cell)? Is there some way to get the same hack copied into OleDb so it will start working? Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143843
  - 2
    - Problem connecting to SQL Hello, I have several ASP.NET 2.0 sites That connect to a SQL 7 database. Up untill a few days ago they had been functioning just fine . For some reason all my SQL connections are returning the following error sporaticly: ---------------------------------------------------------- An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) ---------------------------------------------------------- This is not happening all the time but there have been an unusual amount of these errors as of late. I have searched the we web but so far I have been unable to find any documentation about this error that relates to SQL 7. Any help would be appreciated. -Scott Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143837
  - 3
    - Locating record on form by key Hi I have a winform db app that I have created by dragging fields on the form i.e. code has been generated by vs2008. How can I locate a record on the form by the record id? Many Thanks Regards Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143836
  - 4
    - DefaultValues I currently have some trouble with DataSets and DefaultValues. I have a DB on an MSSQL2000 Server with a table that contains several Columns with default values - e.g. defined like CREATE TABLE tbl_A ( [AGUID] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT [DF_tbl_A_AGUID] DEFAULT (newid()), [hidden] [bit] NOT NULL CONSTRAINT [DF_tbl_A_Hidden] DEFAULT (0) ) I did use the assistent to create a new data source for my project based on that DB. Unluckely the defaultvalues are not fetched from the DB. I could understand, that newid() was not fetched since it's a function - but at least the 0 should be set. Can anybody help me on how to get the DefaultValues from the DB into the DataSet? Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143832
  - 5
    - Faster .Compute() I have a class library that does calculations (usually AVERAGE) on (sometimes) large sets of data. The nature of the application is that it normally does several calculations (5-50 or more depending on user requests) on the same set of data, so the basic implementation is that I grab all the data out into a DataTable and then the library takes arguments and uses the .Compute() method of the DataTable to return results. In most cases, I'm doing an average of a column on a subset of rows. Subsequent calls do averages on different subsets of rows. This all works fine until the DataTable grows to 10k+ rows. Once I pass that threshold (or thereabouts), the performance goes into the toilet. It can literally take minutes to pull reports that would take < 10 seconds on a dataset half the size. The problem is that I don't want a single request to result in 50+ queries to my database, yet I must do 50+ calculations. I can add application servers pretty easily, but I only have the 1 database server. That's my scarce resource so I want to minimize his work (even though he's better at doing aggregation that my app server) and put my app servers to work. I am well aware of the limitations of the DataTable and I am also well aware that if I run these same queries directly against the database, I get much better performance (orders of magnitude better). I'm just not sure how to overcome this. So my question is how can I pull out possibly large sets of data and do calculations on the application servers so as not to overload my database server but avoid this huge performance penalty that I get when working with large DataTables? I'm willing to give up some performance for smaller sets of data if necessary (the tradeoff would be worth it, I'm sure, if I can figure out how to optimize the top end). I have a small benchmarking application that I'm working with and I have yet to find any method that out-performs DataTable.Compute(). I'm working under the assumption that I need to start w/ a DataTable because I will need to access the data several times (maybe I'm hosed right there - that's why I'm asking this question). Here is what I've tried thus far: 1) Manually call DataTable.Select() and get a DataRow[] object - brute force iterate and do the average 2) Use LINQ against an EnumerableRowCollection<DataRow> pulled from the DataTable and call .Average() 3) Brute force iterate over the DataTable myself and do the average 4) Call DataTable.CreateDataReader() and brute force iterate on the DataReader to do the average Of all of these methods, #1 results in the closest performance numbers to .Compute() (and it's pretty darn close) and #2 results in the worst (almost twice as bad) with #3 & #4 falling solidly in the middle of those two. Does anyone out there have an idea on how to solve this problem? Many thanks - Paul Prewett Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143831
  - 6
    - Renaming Excel Worksheets via ADO.NET I've written a program to extract information from Excel Worksheets over ado.net and most of it's working fine. Is there a way to change the sheetname via ado.net? Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143829
  - 7
    - Updating data adapters Hi I have several data adapters on my form. My question is, would a statement like Me.TableAdapterManager.UpdateAll(Me.MyDataSet) update all adapters or do I need to update each adapter individually? Thanks Regards Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143823
  - 8
    - Accessing data adapter in a dataset Hi I am trying to access the data adapter for a table in a dataset as below; MyDataSet.MyTable.Clear() MyDataSet.MyTableTableAdapter.Fill(MyDataSet.MyTable) But I am getting a 'MyDataSet.MyTableTableAdapter' is not a member of 'MyDataSet'. What is the problem and how can I access the underlying data adapter for the table MyTable? Thanks Regards Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143821
  - 9
    - Which is more expensive? Which is more expensive? Trapping an Exception to prevent duplicate entries in a database, or using IF EXISTS in T-SQL? Also, how would I go about testing which is more expensive myself? Thanks Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143817
  - 10
    - Performance implication of Parameters Hi. I was recently talking to a colleague who uses SQL Server as a backend to his applications. He mentioned that adding parameters causes SQL Server to determine the datatype and can cause performance issues, unless you explicitly specify the datatype. Does this also apply to Oracle, and if so, how can I specify it? Right now I am doing the following: int a = 4; String b = "test"; DateTime today = System.DateTime.Today; String sql = "insert into something (a, b, c) values (:a, :b, :c)"; OracleCommand aCommand = ... using above sql ... aCommand.Parameters.Add("a", a); aCommand.Parameters.Add("b", b); aCommand.Parameters.Add("c", c); Is there another way I could be doing this that would speed things up? Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143805
  - 11
    - How can I update a an entire table in a dataset? Hello, any help will be appreciated :) I have a dataset filled with data from a database table. Now I want to do an update command that will effect all rows in a specified table in this dataset (something like: "UPDATE TableName SET RowName = RowName + 10") How can I do it without executing it individually for each row? Thanks. Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143800
  - 12
    - TableAdapter and IN Statements Hi, I'm trying to configure a TableAdapter with an SQL statement fo the form: Select * from SomeTable where ID IN "1,2,3,4"; Can I do this with a tableadapter? Thanks in advance for your help! Terry Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143788
  - 13
    - insert many rows from tableadapter (vb2005) I have to insert a lot of rows from a tableadapter to a sql table. The operation is slow because table adptert send a single insert command for each row. Is there any way to have it with only one batch command? Or a faster solution? Thank you Alessandro Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143783
  - 14
    - Refilling dataset without changing context Hi I have a winform db app that is "bound" to a dataset...via data adpater, binding source and all that. As this is a multi-user app I need to refill dataset from time to time so the newest data is available for user to view. My problem is that I a) do not wish the app to become unresponsive while re-fill is being performed so need to do it as background task if possible. b) do not want the record context to change for user so user does not notice any disruption. My question is, are the above possible? If so, how? If not what is an alternate strategy? Thanks Regards Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143769
  - 15
    - Adding New Row, Set Value Hi, I am using the BindingNavigator control on my WindowsForm. When I click the '+' button, it appears that the program automatically adds another row to the datatable in the underlying typed dataset - fine. I need to set the value for one of the columns programmatically. How do I reference this newly created row? This must be done before I call Update to send it to the database. Private Sub bnEmploymentAddNewItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bnEmploymentAddNewItem.Click ??? = iPersonID End Sub Can someone help me with the syntax for referencing this newly created row so that I can set the value for the column? J Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143745
  - 16
    - Access to vb.net converter Hi Has any one checked this access to vb.net converter? http://www.microtools.us/anetvb7.aspx Any good? Thanks Regards Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143729
  - 17
    - System.Threading.ThreadAbortException: Thread was being aborted Hi, I am importing a flat file into a remote database using ADO.NET. First, I use a dataadapter to load the contents of the flat file into a datatable. Then, I use SqlCommandBuilder to update the data into a database table. I am doing this in development so the file and the script are local, while the database is remote. The flat file is only 16,236 rows, which I didn't think would be any trouble. But on the first run it thew this error: System.Threading.ThreadAbortException: Thread was being aborted. Each time I run it now, it imports fewer and fewer rows, until the last time, it only imported about 800 before it bombed out threw this error. I've updated the time outs in the .config files and in IIS. No change. If I limit the original select to 100 rows, it works fine. It seems like a timeout, yet it does not give a time out error. Any ideas? Thanks, Casey Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143728
  - 18
    - Getting connection information from vs 2008 generated code Hi I have developed a winform db app by dragging fields onto a form in vs 2008 i.e. I have not written code to open db connection myself and instead have let vs 2008 generate that code under the hood automatically. My question is, is there a way to get a handle to the db connection created by vs 2008? Thanks Regards Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143727
  - 19
    - Opening Access db via DAO in vb.net Hi In my vb.net app I am trying to open a db via DAO as below; Imports dao Dim dbSynch As dao.Database dbSynch = DBEngine(0).OpenDatabase("C:\MYDB.MDB") The problem is that on the last line I am getting the error 'DBEngine' is a type and cannot be used as an expression. What am I doing wrong? Many Thanks Regards Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143726
  - 20
    - Parameterised query with an ms access table Hi When using sql server, I can use a sql like below in my data adapter to send the sql a parameter value; SELECT <field list> FROM <table> WHERE Fieldx = @Para1 My question is, can I do something similar with an access db ie can I send a parameter to a data adaptor sql which is connected to an ms access table? If not is there another way to do this? Thanks Regards Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143717
  - 21
    - T-SQL Return row affected count in CLR proc? Is there a way to say to the caller just like an Insert/update/delete command would do from a T-SQL CLR Stored Procedure how many rows my procedure affected? I know I can push text down the SqlContext's Pipe as messages but how do you say here is my rows affected count? thanks! Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143706
  - 22
    - Problems on adding a new dataset using MySQL via ODBC Hi all! I am using Visual Studio 2008 and I am having problems on creating a dataset. I have a MySQL database accessing through ODBC. On adding a new data source, when I try to select a table on the database it gives the following error: <`database`..`table`> Could not get column information for database object named '`database`..`table`' Does anyone knows what is happening? Thanks in advance, Vanessa Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143700
  - 23
    - Total newbie, getting error "Reference to a non-shared member requires I'm trying to jump start with VB.Net/Ado.Net to get something done at work. I'm using the book "Programming Microsoft ADO.Net 2.0". One problem with this book is that his sample code does not spell out the imports. Usually somewhere in the paragraphs or pages before, it says "This assumes you're importing blah.blah.blah." Well, I'm not finding the reference. I've used the Data Source Configuration wizard to create BisDataSet. It's blowing up on line 9, on "BisDataSet" saying "Reference to a non-shared member requires an object reference." Trying to look that error message up on the Microsoft gives me a generic definition and little to go on. Here's my code: Imports Microsoft.Office.Interop.Excel Imports Microsoft.SqlServer.Server Imports System.Data.SqlClient Imports System.Data 1 Module Module1 2 3 Sub Main() 4 5 End Sub 6 Sub write_Scorecard_RRTP() 7 8 Dim RRTPRowset As BisDataSet.RRTP_ScorecardRow() 9 RRTPRowset = BisDataSet.RRTP_Scorecard(0) 10 11 End Sub 12 End Module Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143683
  - 24
    - Data table export to CSV? Is it possible to export a single (not related to anything else) data table's contents to a CSV file? I have XML export working fine, but they want the files in CSV format.. thanks! Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143673
  - 25
    - How do I stop VS2005 from backward-quoting identifiers on update I'm using the VS2005 wizard/design tool for generating typed datatables and adapters for a desktop application. At design-time I point the development environment at an MSJet database, but I've been careful to make all of the queries that I add to the adapters generic SQL which should work against most environments (or at least MS SQL Server, which I also want to target) BUT, the update/insert/delete statements that are being handily generated by the wizard are cloaking all of the identifiers with backward quotes which work OK with MSJet but not when I shift the connection to MS SQL Server at run time. Is there any way of stopping that backward-quoting behaviour at design time? I cannot see it... Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143667
- Next
  - 1
    - Oracle connection Fails Hi All, I have built a simple .Net console application. In which i am trying to connect to the oracle database by using Oracle data provider for .Net from ORACLE site and it works fine on my machine. I have installed the same on my main server but when i run this application from Our main server i am getting an exception. The exception is as follows. 'Oracle.DataAccess.Client.OracleException at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 rrCode, OracleConnection conn, IntPtr opsErrCt x, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure) at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, Oracle Connection conn, IntPtr opsErrCtx, Object src) at Oracle.DataAccess.Client.OracleConnection.Open() at TestODACWithWindows2000.Program.Main(String[] args)' I am not able to trace out the exact cause why it is failing from that server. While the oracle server is pingable from that server. My application is as follows using System; using System.Collections.Generic; using System.Text; using Oracle.DataAccess.Client; using System.Data.SqlClient; using System.Xml; using System.Data; using System.Xml.XPath; using System.Xml.Xsl; using System.IO; namespace TestODACWithWindows2000 { class Program { static void Main(string[] args) { Console.WriteLine("Opening........"); string ConnectionString = "Data Source=(DESCRIPTION=" + "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=Host)(PORT=1521)))" + "(CONNECT_DATA=(SERVER=DEDICATED)(SID = SID)));" + "User Id=username;Password=password;"; OracleConnection ora = new OracleConnection(ConnectionString); try { ora.Open(); } catch (Exception ee) { Console.WriteLine("{0} Exception caught.", ee); Console.ReadLine(); } } } } Please help me out.. Its urgent. Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143666
  - 2
    - Programmatically create Access 2007 database Greetings... From this: http://support.microsoft.com/default.aspx?scid=kb;en-us;317881 article I've knew how to create access 2000 databases. But what with 2007? Best regards... Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143662
  - 3
    - Trouble Using DataAdapter.Update() Hi, and thanks in advance for any help :-) From a high level, heres what I'm trying to do. I have a flat comma delimited file that I need to import into a database, but after some of the data is massaged and mingled with data in the destination table. So, I first import the flat file data into a dataset using one dataadapter and one dataset. Then, I use another dataadapter and dataset to import the current data from the eventual destination table. Then, I do the logic and place the resulting data into the *second* dataset. So far so good. Now, all I have left to do is update the database table with the new data. Easy, right? But I can't make it happen. I know it's a lot of code, but here's what I've got: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ' First, import the flat file data Dim TextConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & "C:\Inetpub\wwwroot\GrandCentralCoupons\comissionjunction\" & ";" & _ "Extended Properties=""Text;HDR=Yes;FMT=Delimited;""" Dim TextConn As New System.Data.OleDb.OleDbConnection(TextConnectionString) TextConn.Open() Dim dad1 As New System.Data.OleDb.OleDbDataAdapter("Select top 5 * from cj.txt", TextConn) Dim dsSource As DataSet = New DataSet dad1.Fill(dsSource) TextConn.Close() dad1.Dispose() ' Verify that the data is corect by binding to a gridview for visual display GridView1.DataSource = dsSource GridView1.DataBind() ' Now, get the currentdata from the destination database table Dim dbConn As New SqlConnection(WebConfigurationManager.ConnectionStrings("GCC_Remote").ConnectionString) Dim dad2 As New SqlDataAdapter() dad2.SelectCommand = New SqlCommand("SELECT * FROM import_temp_cj2", dbConn) Dim cb As SqlCommandBuilder = New SqlCommandBuilder(dad2) dbConn.Open() Dim dsDestination As DataSet = New DataSet dad2.Fill(dsDestination, "temp_import_cj") ' verify the data is correct by binding to a second gridview for display GridView2.DataSource = dsDestination GridView2.DataBind() ' mix. mingle, manage the data form the two datasets '/* lots of code snipped for easy of viewing */ ' verify the new data is correct by binding to a third gridview for display GridView3.DataSource = dsDestination GridView3.DataBind() ' update the database table dad2.Update(dsDestination, "temp_import_cj") '<--- NOT WORKING!!! dbConn.Close() ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ All three gridviews display exactly what they should be displaying. No errors at compile or runtime. But, the new data just never makes it into the db table. I have a hunch I am missing something really simple... but I can't pick it out. This is the article I am using as a guide: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlcommandbuilderclasstopic.asp Thanks! Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143659
  - 4
    - Vengadeshwaraa Astrologers Vision To be the leader in the field of Astrology, to be the best Astrological service providers and to be ranked as the pioneer Astrologer in the world. To deliver various aspects and knowledge bases of Sri Lanka to the whole world and to be the Astrological force that binds all human beings together, wherever they be. Mission To become an Astrologically meaningful resource and be a vital part of lives of our members and users. Providing efficient & trusted service 24 hrs a day, 7 days a week. Our endeavor has become fruitful in making solutions for users to shop on line with direct delivery facilities while offering you the opportunity to know yourself, to grow and to improve your life. Shree Vengadeshwaraa Astrologers founded on 08th April 1972 has since gained numerous audience due to the untiring efforts by us and we believe that no other Astrology service can provide you with the value that Vengadeshwaraa can. So much so, our service is backed by an unconditional guarantee.We have predicted thousands and thousands of horoscopes and have never failed.We want this science to be taken to all mankind irrespective of religion. This is based on mathematical calculations and in no way concerned to the religion. We provide a pleasant, satisfying and superior Astrology experience to our customers while protecting their privacy and security. Shree Vengadeshwaraa Astrologers consist of Sri Lankan and Indian Astrologers and count over 35 years of dedicated and friendly service out of which 20 years has been spent on research alone.We have a very eminent team of Astrologers who are well educated and traditionally inherited this divine/legendary art of science.Our Astrologers are not only educated but also traditionally qualified. Our Astrologers are supposed to be best in this profession. Shree Vengadeshwaraa Astrologers has 03 branches worldwide namely, Sri Lanka, India and Germany. Head of Sri Lanka branch Brahmashree V.S.Sharma is a very popular Astrologer in Jaffna, Sri Lanka Head of India branch Padmanaapa Saastrikal Head of Germany branch Brahmashree V.Mahadeva Sharma Our consultant service to International customers are absolutely free of charge. We have geared ourselves to launch our new branches in London, Canada, Singapore, Malaysia and Australia next year. Shree Vengadeshwaraa also offers services in the field of Matrimonial, Hindu Religion Development and Hindu Brahmins social network. We take pride in our endeavour for the introduction of "TOP 100 ASTROLOGERS RATING PROGRAM" for the first time. Visit vengadeshwaraa.com Please take some time to get to know us better through the links that we have provided on this page and enjoy very competitive product price, special promotions and of course 100% satisfaction on accurate calculation of Horoscopes. Our Astrological and sincere thanks for visit Now:http:// www.vengadeshwaraa.com Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143658
  - 5
    - Insert c# DateTime object to Oracle Table using OleDbDataAdapter I have a table defined as: create table product_price ( productId int, price number(20,10), price_date date, primary key (productId) ); I am reading through a price file in c# and I would like to insert a row into the table for every price+date+productId combination. I am using the DataAdapter insertCommand and Table.Rows.Add(newRow) method but it fails when setting the date of the new row. // Setting the insert command priceDA.InsertCommand = priceDA.SelectCommand.Connection.CreateCommand (); priceDA.InsertCommand.CommandText = "insert into product_price (productId, price, price_date)" + "values (@productId, @price, TO_DATE('@priceDate', 'YYYYMMDD'))"; // Adding the row private void addPriceRow (int prodId, decimal price, DateTime priceDate, DataTable priceTable) // priceDate and other params defined elsewhere DataRow newPriceRow = priceTable.NewRow(); newPriceRow["productId"] = prodId; newPriceRow["price"] = prodPrice; newPriceRow["price_date"] = priceDate; priceDA.Update(priceDS, "product_price"); // Dies here with an oracle error ORA-00936 missing expression priceDS.AcceptChanges(); I think this is some sort of date format error, so I tried: newPriceRow ["price_date"] = priceDate.ToString("yyyyMMdd"); but that line fails at runtime with: "String was not recognised as a valid DateTime" Any ideas how I can save this date to the table? Many thanks in advance. -- JohnH Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143657
  - 6
    - Inserting records from one database into identical table in another datrabase Hi! I'm transferring the records in a table from one database to another. I create a data adapter using a connection from the first database to fill a dataset. I add an InsertCommand with a different connection to the second database, and add the necessary parameters. I hoped that when I called the update method, the records in the dataset added from the first database would be inserted into the identical table in the second database. This didn't work, but no exception was thrown. Just no records were added. My code is like this modified code from msdn - should this work? Private Sub AdapterUpdate(ByVal connectionStringA As String, ByVal connectionStringB As String) dim connectionA As SqlConnection = New SqlConnection( _ connectionStringA) dim connectionB As SqlConnection = New SqlConnection( _ connectionStringB) Dim adapter As SqlDataAdapter = New SqlDataAdapter( _ "SELECT CategoryID, CategoryName FROM dbo.Categories", _ connectionA) adapter.InsertCommand = New SqlCommand( _ "INSERT Categories (CategoryName) values (@CategoryName), connectionB) adapter.UpdateCommand.Parameters.Add( _ "@CategoryName", SqlDbType.NVarChar, 15, "CategoryName") Dim categoryTable As New DataTable adapter.Fill(categoryTable) adapter.Update(categoryTable) End Sub Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143654
  - 7
    - SQL CLR Sproc isn't running right I have a stored procedure for SQL Server 2005 which is done with the CLR... when its executed as a admin it works. when its executed as a non-admin it doesn't work... any ideas? the user has permission to execute the procedure on the server... seems to be something with the .NET SPROC's that is going wierd Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143652
  - 8
    - list box primary key vb .net Does anyone know how to populate a list box using SqlDataReader. I am using VB .NET 2005. Along with the list box value that is visible I need it's primary key value that is not visible. Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143646
  - 9
    - Error Messages? Currently, I tend to return @@ERROR in my stored procedures so that the calling code can test for error conditions. But I'm wondering: When @@ERROR is non-zero, is there a way for ADO.NET to detect any specific error messages? Thanks. -- Jonathan Wood SoftCircuits Programming http://www.softcircuits.com Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143638
  - 10
    - Can't save renamed DataColumn Hello. I experience a problem with columns in a DataSet. I need to add, remove, and rename columns in one table. I'd like to avoid changing the columns, but unfortunately I can't. The table is bound to a DataGridView. Adding and removing columns is not a problem, this works perfectly. The problem is what to do when I need to rename a column. I tried to rename the column in the DataTable and change the DataPropertyName property of the corresponding DataGridViewColumn. Everything seems to work fine. Then I want to save the DataSet to a .mdb file, so I do the following: 1. remove the column that was renamed, 2. add a new one with the correct name, 3. mark all unchanged rows in the DataTable as modified, 4. rebuild the insert, delete, and update commands using the CommandBuilder, and 5. call TableAdapter.Update(...); This routine works perfectly if I add some new columns and/or remove a few of them only. If I try to use this with a renamed column that was already present in the DataSet, it fails. Although it does not throw an exception, the saved file contains blank fields instead of the values in the renamed column. It confuses me a lot. What should I do to make this work? Any help would be much appreciated. Best regards, nvx Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143632
  - 11
    - Calling a stored procedure with a output parameter I've been testing a stored procedure that has an output parameter. When I add parameters to the sqlcommand this way, the output parameter returned is always what I pass in: With InsertLeg .Transaction = mySQLTrans .CommandType = CommandType.StoredProcedure .Parameters.Add(New SqlParameter("@Load_Num", SqlDbType.Int)) .Parameters.Add(New SqlParameter("@RC", SqlDbType.Int, 0, ParameterDirection.Output)) End With If I declare the sqlcommand this way, it works, the output parameter from the stored procedure is returned in the @RC parameter. With InsertLeg .Transaction = mySQLTrans .CommandType = CommandType.StoredProcedure .Parameters.Add(New SqlParameter("@Load_Num", SqlDbType.Int)) Dim Param As New SqlParameter("@RC", SqlDbType.Int) Param.Direction = ParameterDirection.Output .Parameters.Add(Param) End With Can someone tell me why these declarations are treated differently? Thanks, Kevin Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143631
  - 12
    - Problem using DbProviderFactory with a Access 2000 database Hi; I can't seem to open an Access 2000 database with the following dbprovider factory code : In my source : Code SnippetDim dp As String = ConfigurationManager.AppSettings("provider") Dim cnStr As String = _ ConfigurationManager.ConnectionStrings("AFIDConnectionString").ConnectionString ' The factory provider Dim df As DbProviderFactory = DbProviderFactories.GetFactory(dp) ' Now make the connection object Dim conn As DbConnection = df.CreateConnection() In my App config file I have : Code Snippet<configuration> <appSettings> <add key="provider" value="System.Data.OleDb" /> </appSettings> <connectionStrings> <add name="AFIDConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\wmcrystalt\sysData\FTPFiles\databases\FTPFilesRpts.mdb;" /> </connectionStrings> When I step through the code in my locals windows I notice that df {System.Data.OleDb.OleDbFactory} System.Data.Common.DbProviderFactory + System.Data.OleDb.OleDbFactory {System.Data.OleDb.OleDbFactory} System.Data.OleDb.OleDbFactory CanCreateDataSourceEnumerator False Boolean Can't you connect to an Access database i.e. MDB through the dbProvider object ? Thanks for any insights, -- Gordon Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143618
  - 13
    - Displaying Data Table Queries I have several basic queries that seem to add up to a very intricate query. I need to create a report to display the results of these queries, but I don't know the best way. The old VB5 program this new application is replacing wrote the document in a very painful, line-by-line format to a VSPrinter object. Should I do this again, using the PrintDocument object instead? I can't get Crystal Reports to display data for me, so I guess it is out. I've asked questions on how to use it, but I don't get replies. I guess others don't use it either. I suppose I could use an HTML page. This seems the simplest, but also would not give nice headers/footers between page breaks. Any thoughts? Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143617
  - 14
    - TransactionManagerCommunicationException when transaction is escalated Hello, I have an application which uses the System.Transactions.TransactionScope class to manage transactions. Transactions are often escalated to distributed transactions, and normally this works fine. With one particular installation I have a problem where as soon as the transaction is escalated I get the following error: System.Transactions.TransactionManagerCommunicationException: Communication with the underlying transaction manager has failed. ---> System.Runtime.InteropServices.COMException (0x80004005): Error HRESULT E_FAIL has been returned from a call to a COM component. at System.Transactions.Oletx.IDtcProxyShimFactory.ReceiveTransaction(UInt32 propgationTokenSize, Byte[] propgationToken, IntPtr managedIdentifier, Guid& transactionIdentifier, OletxTransactionIsolationLevel& isolationLevel, ITransactionShim& transactionShim) at System.Transactions.TransactionInterop.GetOletxTransactionFromTransmitterPropigationToken(Byte[] propagationToken) --- End of inner exception stack trace --- at System.Transactions.TransactionInterop.GetOletxTransactionFromTransmitterPropigationToken(Byte[] propagationToken) at System.Transactions.TransactionStatePSPEOperation.PSPEPromote(InternalTransaction tx) at System.Transactions.TransactionStateDelegatedBase.EnterState(InternalTransaction tx) at System.Transactions.EnlistableStates.Promote(InternalTransaction tx) at System.Transactions.Transaction.Promote() at System.Transactions.TransactionInterop.ConvertToOletxTransaction(Transaction transaction) at System.Transactions.TransactionInterop.GetExportCookie(Transaction transaction, Byte[] whereabouts) at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx) at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx) at System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction) at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() I have tried using DTCPing to check the link between the machine and the database server and the finds no problems. Help! Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143613
  - 15
    - dataset error Hi, I've a requirement where in I need to read the data from a csv file and load the data into the sql database. The file has around 1 lakh records. I'm reading 300 records at a time from the file, loading into a dataset. From the dataset, I'm reading the rows sequentially,and updating one by one in to the database. This happens with a sql trnasaction. The problem is here with the performance.. The file to be loaded exists on one server and the database exists on another server. The loading process is too slow and it breaks with the below error 'The SqlTransaction is closed;it is no longer usable'. I've searched on the google..but couldn't get appropriate resolution. Can someone help me out? Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143612
  - 16
    - Pasting from excel to DataGrid Hello guys I'm having a winform application. I can copy from the datagrid and it can paste to excel, however I cannot copy from excel and paste to the datagrid. Is this doable? if so, how? Thanks HS Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143606
  - 17
    - Size of Table Hi EveryBody: How much data can be saved in the tabel in the database ? In another way can the size of table be 100 GB ? any help will be appreciated regard's Husam Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143601
  - 18
    - Moving from recordsets to datasets... Been writing VB6 for years and am now getting up to speed with .NET. In VB6 I used to create classes for each logical grouping of tables, then have a recordset in that class for each table. Then I'd have functions that would populate the different recordsets. I had a function like this: "getRecordset($SQLStr) as ADODB.Recordset" that would take care of the connection, opening the recordset, then just return a disconnected recordset from that query. Then I had a function called "updateRecordset(RS as ADODB.Recordset)" which just stored the changes. Everything was self contained and very easy to deal with. Now in VB.NET, I see that the dataset is essentially doing something very similar to what I was doing in VB6. A couple problems I'm having though. 1) When I finished making changes to a recordset in VB6, I'd just call my updateRecordset function, which would reconnect the recordset, call RS.UpdateBatch and take care of all the inserts, deletes, updates for me. In VB.NET it looks like I have to use the same SqlDataAdapter that I used to read the data to update the data in order to save my changes, and I have to write INSERT and UPDATE code for it? Am I correct, or is there any way to use this the same way as I was using recordsets? 2) With recordsets, I could specify the read type when I opened the recordset, which allowed me to set this programatically as needed but I would always be dealing with the RecordSet type regardless of how I read the data. With VB.NET, I have to use a dataReader if I want read only. Is there anyway to programatically set a dataTable to read only forward-looking, so that it works the same way as a dataReader and I only have to deal with one data type in my code? 3) I looked at the typed-datasets, but I really hate using wizards or GUIs to do my code. I like being able to see it all at once and copy and paste functions if needed. I also need to be able to change the connection string on the fly, because I will be using different servers and database names. Is there any way to create typed-datasets programatically? Any suggestions on guides to help me design a good back end for my program? Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143596
  - 19
    - Recommended db app strategy Hi I have an access backend database to which I need to give a vb.net front end. I created db connection and data source in vs 2008., I then dragged the fields onto a form which created the app for me. The app works ok but my question is; is this the correct/acceptable way to cerate an app or should I be using another technique? Thanks Regards Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143593
  - 20
    - Recommended db app strategy Hi I have an access backend database to which I need to give a vb.net front end. I created db connection and data source in vs 2008., I then dragged the fields onto a form which created the app for me. The app works ok but my question is; is this the correct/acceptable way to cerate an app or should I be using another technique? Thanks Regards Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143592
  - 21
    - Inserting from a asp:table in an asp:formview I am trying to insert from fields in a asp:table wrapped in a formview i am not sure if the fields are bound to the data source as when I do a view in browser the form view is blank I dont see the asp:table or asp:textboxes in the asp:table <asp:FormView ID="FormView1" runat="server" DataSourceID="SqlDataSource1" AllowPaging="True" GridLines="None" HeaderStyle-BackColor="#909090" HeaderStyle-ForeColor="#FFFFFF" HeaderStyle-Font-Bold="True" HeaderStyle-HorizontalAlign="Center"> <HeaderTemplate> <asp:Label ID="Label1" Text='<%# Eval("Agency_ID") %>' Runat="Server"/> </HeaderTemplate> <ItemTemplate>  <asp:Table ID="Table2" runat="server" AccessKey='<%# Bind("Asset_Tag") %>' BackColor="#C0C0FF" Enabled='<%# Eval("Agency_ID") %>' Width="411px" Visible="true" > <asp:TableRow ID="TableRow1" runat="server"> <asp:TableCell ID="TableCell1" runat="server" Text="Asset_ID"></asp:TableCell> <asp:TableCell ID="TableCell2" runat="server"> :<asp:TextBox ID="TextBox1" runat="server" Text='<%# Eval("Asset_ID") %>'></asp:TextBox> </asp:TableCell> <asp:TableCell ID="TableCell3" runat="server" Text="Agency_ID" ></asp:TableCell> <asp:TableCell ID="TableCell4" runat="server"> :<asp:TextBox ID="TextBox2" runat="server" Text='<%# Eval("Agency_ID") %>' ></asp:TextBox> </asp:TableCell> </asp:TableRow> . . . <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Insert Record" Width="157px" /> when the button is clicked this runs in the code behind protected void Button1_Click(object sender, EventArgs e) { SqlDataSource1.Insert(); } Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143585
  - 22
    - Re-added row Hello there, I need an advice on "re-adding the deleted rows" in a DataTable. The situation is as follows: 1. I have a data-bound DataGridView. 2. User wants to delete a row with a key value "KEY", therefore this row gets deleted using DataRow row = dataSet.Tables["data"].Rows.Find("KEY"); row.Delete(); 3. The deleted row is still in the DataTable, but with DataRowState.Deleted. 4. Now, user decides to add a row with the same key value again, so he types it into the DataGridView. At this moment, there are TWO rows with the same key value "KEY": one with DataRowState.Deleted and one with DataRowState.Added. 5. User wants to save the data into the .mdb file, therefore tableAdapter.Update(...) is called ending up with a concurrency error saying that the UpdateCommand event caused 0 out of 1 records to update (or something similar, I unfortunately don't have an English version of .NET). What should I do with these two DataRows to be able to use the Update(...) command later on? Any help will be much appreciated. Best regards, nvx Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143582
  - 23
    - Using Visual Studio Queries In my C# project, I have a Data Set called Employee and an EmployeeTableAdapter. The EmployeeTableAdapter created a "Fill,GetData()" Query for itself, and it uses this to display data to my DataGridView. The properties of the Fill Query are: CommandText = SELECT ... CommandType = Text ExecuteMode = Reader Whenever my Form loads, it calls: this.EmployeeTableAdapter.Fill(this.Jp2DataSet.Employee); I have created additional queries to return select pieces of data. These are: FillMethodName: FillAssignmentList CommandText = dbo.AssignmentListProcedure CommandType = StoredProcedure ExecuteMode = Reader FillMethodName: FillGroup CommandText = dbo.GroupSelect CommandType = StoredProcedure ExecuteMode = Reader With this information set, how would I now go about calling one of these stored procedures? Is using Stored Procedures this way good or bad? If I switched my CommandType to Text, what would change in the way I called the query? I've looked on the MSDN site, but the many links I've found don't seem to answer how I would programatically call queries and procedures that I have added to my table adapters. Thanks for your help, and your time. Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143578
  - 24
    - oleDB hangs on ExecuteNonQuery The line insertStatus = cmdInsert.ExecuteNonQuery() is hanging; although the INSERT is working, because after I kill the application, I can see the new row. The OLE DB provider is set to IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider. Private Function VS_Insert(ByVal pndAmt As Decimal) As Boolean Dim currTime As String = Format(DateTime.Now, "HH.mm.ss") Dim currDate As String = Format(DateTime.Now, "yyyy-MM-dd") Dim success As Boolean = False Dim cnInsert As New OleDb.OleDbConnection(My.Settings.connAS400) Dim cmdInsert As New OleDb.OleDbCommand Dim insertStatus As Integer = 0 cmdInsert.Parameters.Clear() Dim query As String = "INSERT INTO PRODUCTION.DVABDAPV " & _ "(PONBR, LINENR) " & _ "VALUES (?,?)" With cmdInsert .CommandText = query .CommandType = CommandType.Text .Parameters.Add("?", OleDb.OleDbType.Char).Value = PONr .Parameters.Add("?", OleDb.OleDbType.Char).Value = lineNr .Connection = cnInsert End With handleConnection(cnInsert) insertStatus = cmdInsert.ExecuteNonQuery() If Not insertStatus = 0 Then MsgBox("The select failed") success = False Else MsgBox("The select WORKED") success = True End If handleConnection(cnInsert) Return (success) Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143575
  - 25
    - Calling DataAdapter.Update() on disposed DataSet? Hey, Some one gave a new developer of ours a project to make sure our core library funcitons are properly cleaning up resources (that's a good thing!), but I had consernse about one thing the developer was doing which sparked a debate with my co-worker. Some of these functions were returning DataSet objects, and the developer was calling the Dispose() function on the datasets inside the finally portion of a try block. For me, this kind of raised a red flag, because it seems to me the funciton is then returning an object which it has already "cleaned up". In some cases an "end programmer" might even want to take this dataset, modify datarow values and call a DataAdapter.Update() on it. It just doesn't seem like a very safe practise to me. Am I being overly cautious here? I guess I just have a problem with returning "cleaned up" object in general, to me it seems like no good can come from that. Thanks for your thoughts, Carlo Tag: What is the fastest way to update all rows of a table in a dataset Tag: 143574

Hello and thanks for your help :)

I have uploaded an xml into a dataset so now it contains a single table
filled with the xml values. I need to update one of the fields in all the
rows with a constant value.

What is the fastest way of doing it

Btw: I don not have a database to which I can save the table and then
execute an update command.

Any ideas?

Top

RE: What is the fastest way to update all rows of a table in a dataset by KerryMoorman

KerryMoorman
Wed Feb 06 11:43:06 CST 2008

Stren,

Haven't you already received answers to this question?

You must update the appropriate field for each row in the datatable, using a
loop. You cannot execute an sql statement to update the entire datatable.

Kerry Moorman

"Stren Assaf" wrote:

> Hello and thanks for your help :)
>
> I have uploaded an xml into a dataset so now it contains a single table
> filled with the xml values. I need to update one of the fields in all the
> rows with a constant value.
>
> What is the fastest way of doing it
>
> Btw: I don not have a database to which I can save the table and then
> execute an update command.
>
> Any ideas?
>

Top