How to go through a datatable row by row and column by column

Hello, I have a dataset with a datatable and I need to insert each row from the datatable on my sql table, anyway there may be errors, so some rows may not be inserted because primary key constraints,  So I need to show the rows that are not inserted in a gridview, the hard thing to do is to go through all the datatable,  is there an wasy way to do it?
11/22/2005 1:18:33 PM 72751 articles. 3 followers. Follow

8 Replies

Similar Articles

[PageSpeed] 26

Start by looking here

Perform an update of your dataset, and if a row fails, you can mark it as invalid.

You can then easily create a view from the datatable containing only rows that have failed

If it was easy, everybody would be doing it.
11/22/2005 3:16:54 PM
I think I need to make the following

1.  Insert the info from the excel file into the dataset.
2. Define a dataadapter.
3. Define the insert command of the dataadapter.
4. Put the update inside a try catch
5. Inside the catch which exception should I use to see if there was a row that could not be inserted?
6. How can I mark a row as invalid?


Is this how this must be done ? Or do you recommend me another way ?
11/22/2005 3:33:19 PM
And just for reference on the looping:

Dim dt1 As DataTable = ds1.Tables(0)
For Each dr As DataRow in dt1.Rows
        For i As Integer = 0 to (dt1.Columns.Count -1)
                dr.Item(i) <<<<<  Current column value going from left to right
                dt1.Columns(i).ColumnName <<<<<  if you need the name of the column

"If you make it idiot proof, they'll build a better idiot"
11/22/2005 4:12:37 PM

Have a look at the following for data errors, adapters, and tables.,vs.80).aspx

What you need to do is work with the data adapter events.

If you use a database that supports transactions, you can attempt an update inside a transacrion, get the errors, roll back, then report the rows in error back to the user to correct, then try again, etc.

You can also write a DTS job in SQL Server to import the data direct from an external source such as Excel, rather than write code.  You can pick up rows in error from teh DTS job into another table, and write some .Net code to handle just the errors and subsequent updates


If it was easy, everybody would be doing it.
11/23/2005 9:03:12 AM
I cant use DTS because its Sql Express.
11/23/2005 12:29:05 PM
DTS isn't part of SQL Server.  It's a separate application that SQL Server uses and hosts, so you don't need SQL Server at all (you can program DTS classes via COM using (Microsoft.SqlServer.Dts.Runtime).  You can write DTS packages and create a file that can be run by the DTS executable, maybe developing  them on a SQL Server proper, and simply adding the DTS package to the project.

DTS is now Notification Services, which I haven't had time to look at yet

As DTS might be a bit over the top for your needs, I would suggest looking into to data adapter route

If it was easy, everybody would be doing it.
11/23/2005 4:24:10 PM
Dts is now Integration Services not Notification Services.

Anyway I will use the data adapter route, I hate COM   
11/23/2005 4:29:11 PM
Sorry about that - My fingers didn't type what I was thinking!

Let us know when you get it working.  Good luck

If it was easy, everybody would be doing it.
11/23/2005 6:14:58 PM

Similar Artilces:

computing the difference of a column in the current row from the column of the preceding row
Hello, a user asked me if we can solve following problem: we want to compute the difference (delta) of a column in the current row from the column of the preceding row. My first solution signs where: create a computed field (small_function_result) that takes the value from the preceding row: small(value, value, cf_getrow for all); where cf_getrow is a computed field like this: if(getrow()=1,getrow(),getrow()-1) create a computed field (Diff) that computes the difference: value-small_function_result My results should look like this: Row Value cf_getrow small_func...

GridView.Rows(Column=ID_PK) to GridView.Rows(Column=DropDownListTeamplteField)
Hi Guys,I wonder how I can assign the PrimaryKey in each GridView Row to the template field in the same row. whereas the template field is filled with a dropdownlist thatshould show the values for retrieved in a different table through the GridView Row primary key (which serves as the SELECT parameter to get these values) The GridView looks like this:<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="C_ID" DataSourceID="GetC_IDs"> <Columns> <asp:BoundField D...

SQL: mutiple rows one column to multiple columns one row
In InfoMaker SQL can you take a table with multiple rows for the same key and take a column from the rows and create mutiple columns with one report line with the key(no duplicate keys) on a report? For example Table books (key) (book title) 123 misery 123 titanic 123 roots 456 war 456 1984 456 giant The report I need would look like this Key title1 title2 title 3 123 misery titanic roots 456 war 1984 giant Thanks in advance. -- Kaye Hendry HealthInsight ...

How to divide 1 GridView Row with 1 column in 2 Rows with 5 columns?
Hi; I have a GridView with 10 rows and 1 column. How can I do to this GridView show the same data, but with 2 rows and 5 columns? In classic ASP, I made it this way: While (NOT rsFotos.EOF) if i mod 5 = 0 then Response.Write "</tr><tr>" end if ... Someone can help? :)  You have to use a custom template and lay the table out the way you want, either dropping controls in the template or creating a custom ITemplate that takes care of it for you. L...

is it possible to turn 1 column,many rows into one row,many columns in sql ?
 Hi AllI have the following query:Select CategoryName From Category Where CategoryId In(Select SkillId From SpecialitySkills Where CopywriterId = 13) It gives me the correct results but not in the form that i want, i would like it to be in one row, many columns. its in 1 column, many rows.Any ideas?  You must be talking about displaying it on a web page? Just create a loop that runs through the rows of the data that is returned then just make html table:dim tablestring as string = "<table><tr>" for i as integer = 0 to dt.rows.count - 1 tablestring += &...

How can I display multiple rows w/8 columns (4 columns editable on each row)?
Hello, Please - Can anyone suggest the best way to display a joined dataset with 4 read-only columns, 4 editable columns and a checkbox column? The content for this display currently has < 100 rows - but could approach 1000 in the future. I currently have this setup with a Datagrid (working) - but I would like to be able to edit the 4 columns of ALL rows in the displayed list instead of one row at a time as the Datagrid allows. After editing, I use an update function to traverse the modified list/table and save all values that have the Checkbox clicked. Could a list with 10...

Calculated column from columns in same row??
Hi everyone - Is there a way to display a calculated value in a column based on values from the same row? Using a datagrid thanks tony Sure. Have a function declared for your OnItemDataBound: Sub CalculateValues(ByVal sender As Object, ByVal e As DataGridItemEventArgs) dim total as Integer = CType(e.Item.FindControl("col1"), Integer) + CType(e.Item.FindControl("col2"), Integer) e.Item.FindControl("totalCOlumn").Text = total End Sub I think it would be something like that.Sara If yo...

Arranging data on multiple rows into a sigle row (converting rows into columns)
Hello, I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row: user1   answer1user1   answer2user1   answer3user2   answer1user2   answer2user2   answer3 For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so that each user will be on a single row with all of that user's answers on...

Merging DataTables with multiple columns
I have 2 datatablesTable 0:Col Time    Col A    Col BTable 1:Col Time    Col C    Col DDesired Merged Table Result (final Table 0):Cole Time  Col A  Col B  Col C  Col D(with all same Time rows in the same row)   Via merge, I would like a merged table where all rows from either table with the same time (my PK column) all appear on the same row.  The table structure does get output correctly, but those rows with identical times from Table 1 (and 2, 3, etc.)  appear as an additional row...

Is there a control that offers the same data presentation as a GridView, but allows users to copy and paste many rows/columns at once?
 Hi Is there a control that offers the same data presentation as a GridView, but allows users to copy and paste many rows/columns at once onto the grid? What I mean is some grid that 's as flexible as MS Excel grid for example.  Any suggestions will be very much appreciated.E The Repeater control is a highly customisable data presentation controlRohin.NET developerNever trust a computer you can't throw out a window. - Steve WozniakPlease remember to click on 'Mark as answer', if this post helped you......

How to update columns from row to another row? (thank you)
 assume my Table's Columns looks like below1  ,   Annie,   History, pass, null 2  ,   Colin,   Math   , Pass, null7  ,   Annie,   History, Fail, 111 ,   Colin,   Math   , Fail, 2When Delete 7  ,   Annie,   History, Fail, 111 ,   Colin,   Math   , Fail, 2using the last column to check which row is reference with itso when delete Row 7 which reference with 1  ,   Annie,   History, pass, null w...

Column Name in datastore ( invalid row/column ....)
--------------A15DFE08B1A7F5CB43BA4D7D Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit I have a problem with a script, when i reference the column name of a datastore with GetItem.... function. I'm using PB 5.03 and MS SQL 6.5. This is the sql syntax for generating datastore. sql_syntax_ac = "SELECT tabmer.mer_code, tabmer.mer_desc, tabreg.reg_code, tabreg.reg_desc, "+& "Sum(tabarc.arc_rine - tabarc.arc_reso) 'fatturato', "+& "Sum(tabarc.arc_cove - tabarc.arc_core ) 'costo...

Adding a row to the last row column in Gridview ?
Hello, I have a gridview and it displays data in row column and I want to add a row across in the bottom of the last row for the total value.  How do I do it?  Thanks Here is my Gridview <Columns> <asp:BoundField DataField="Information" HeaderText="Information" SortExpression="Information" HeaderStyle-Width = "600" HeaderStyle-Wrap = "true"/> </Columns>   use footer? something like maybe.    use TemplateField   <Columns> <asp:BoundField DataField="Infor...

Hide column data on a row by row basis. How?
I'm sure there is a way to hide/show data of column on a row by row basis. I've done the following for the admin users, but it displays the whole column. GridView1.Columns[1].Visible = true; Let's say I have the following GridView: Listing Address Sale Price End Date Tuscany Village SW I-29 169 Hwy   12/31/2007 Plaza Corporate Center 800 W 47th St   12/31/2007 Ward Parkway Plaza 1414 W 85th St   03/12/2007 Pinnacle Corporate Centre II 11460 Tomahawk Creek Pky   03/12/2007 Corporate Medical Plaza 10777 Nall Ave   03/26/2007...

Web resources about - How to go through a datatable row by row and column by column -

Stack Overflow
Stack Exchange log in - careers - chat - meta - about - faq Questions Tags Users Badges Unanswered Ask Question Top Questions interesting 325 ...

University of Southampton - Wikipedia, the free encyclopedia
The University of Southampton is a British Russell Group university located in the city of Southampton , United Kingdom . The origins of the ...

London Metropolitan University - Wikipedia, the free encyclopedia
London Metropolitan University (London Met), located in London , England , was formed on 1 August 2002 by the amalgamation of the University ...

Google Chart Tools — Google Developers
This page has moved to Google Developers. Welcome! Learn more Dismiss Home Products Events Showcase Google Chart Tools Overview Hello Charts! ...

Google Chart Tools — Google Developers
Powerful, simple to use, and free.

AribaWeb ( - Google Groups
Search Images Maps Play YouTube News Gmail Documents More Calendar Translate Mobile Books Offers Wallet Shopping Blogger Reader Finance Photos ...

Linux Syscall Reference
Generated from Linux kernel using Exuberant Ctags , Python , and DataTables . Developed and maintained by Greg Ose .

... over one weekend where the author had a bit of extra time on his hands. Built with the help of the following tools: Bootstrap jVectorMap DataTables ...

A Map for the Microsoft Application Development Platform
... Localization N-Tier Remote Data Security Silverlight SQL Azure SQL Server Streaming XML Features ADO.NET DataSet ADO.NET DataTable ...

Application Development Framework
Articles: Application Development Framework

Resources last updated: 1/10/2016 3:42:02 PM