Find current Row in a DataTable
Find current Row in a DataTable
Find current Row in a DataTable
Find current Row in a DataTable
Find current Row in a DataTable
Find current Row in a DataTable Find current Row in a DataTable Find current Row in a DataTable Find current Row in a DataTable Find current Row in a DataTable Find current Row in a DataTable Find current Row in a DataTable Find current Row in a DataTable
Find current Row in a DataTable Find current Row in a DataTable
Find current Row in a DataTable
Go Back  Xtreme Visual Basic Talk > > > Find current Row in a DataTable


Reply
 
Thread Tools Display Modes
  #1  
Old 05-01-2007, 05:14 AM
mcdonnc2004's Avatar
mcdonnc2004 mcdonnc2004 is offline
Junior Contributor
 
Join Date: Dec 2003
Location: Liverpool, UK
Posts: 276
Default Find current Row in a DataTable


Hi all,

I am getting slightly frustrated with this problem now and everthing I try does not work! I have some code that selects data from a SQL table and stores it in a DataTable. I then loop through this DataTable to write out the data as CSV. The problem is that I have to put text qualifiers around certain values. Here is a sample of the CSV file I need to output:

Code:
Telephone Directory Assignment,vid7.23,7.0.5.3,,,,
0,1,2,3,4,5,6
Name,Number,Prime Name,Privacy,Cluster Element Id/Int/Ext/Speed Call,Department,Location
"Bloggs,Joe",9~12345678,No,No, ,,
"Beam,Jim",9~12345678,No,No, ,,
"Smith,John",9~12345678,No,No, ,,
"Doe,John",9~12345678,No,No, ,,
The CSV file is then uploaded to a server where it is converted into the server's address book format and sent out to all our VoIP phones.

I can output the CSV no problem but what I cannot do is put the text qualifiers starting from the 4th row onwards. When I output the CSV, every row in the first column has "" around it! Here is my code:

Code:
Private Sub ExportGridToCSV() Dim MyConnection As SqlConnection Dim MyDataAdapter As SqlDataAdapter Dim MyDataTable As New DataTable Dim strSQL As String = _ "SELECT [phonebook_Name], [phonebook_Number], [phonebook_Primename], [phonebook_Privacy], [phonebook_Cluster], [phonebook_Dept], [phonebook_Location] FROM [wb_TelephoneBook]" MyConnection = New SqlConnection( _ ConfigurationManager.ConnectionStrings("plistConnectionString").ConnectionString) MyConnection.Open() MyDataAdapter = New SqlDataAdapter(strSQL, MyConnection) MyDataAdapter.Fill(MyDataTable) MyConnection.Close() If IO.File.Exists("GridData.csv") Then IO.File.Delete("GridData.csv") Dim sw As StreamWriter = New StreamWriter(Server.MapPath("GridData.csv"), False) Dim iColCount As Integer = MyDataTable.Columns.Count Dim iRowCount As Integer = MyDataTable.Rows.Count Dim i As Integer = 0 For Each dr As DataRow In MyDataTable.Rows i = 0 Do While i < iColCount If (Not Convert.IsDBNull(dr(i))) Then If i = 0 Then ' checking for first column sw.Write("""" & dr(i).ToString() & """") Else sw.Write(dr(i).ToString()) End If End If If i < iColCount - 1 Then sw.Write(",") End If i += 1 Loop sw.Write(sw.NewLine) Next dr sw.Close() End Sub

I can check to see if I am on the first column but I cannot find a way to check what row I am currently looping through. Why is there no CurrentRow property!

If anyone has an idea on how I can check the row index (so I can apply the text qualifiers from row 4 onwards) then please post them.

Thanks.
Reply With Quote
  #2  
Old 05-01-2007, 07:46 PM
MKoslof's Avatar
MKoslofFind current Row in a DataTable MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

The row item array should correlate to the columns. For example if you have 5 columns, each row's itemArray() length is 5.

Therefore do a for each against all rows. Get the ItemArray() or collection of objects:

myObjectArray = row.ItemArray

Now iterate through myObjectArray until length -1 and you can start at position 4 or anywhere within that collection.

When you say you can't get the current "row" that is what the for each iteration does. If the Rows.Count is 50, your for each will loop through 5O rows, the ItemArray will get all values that correlate to COLUMNS for that row. Using a For Each and an integer that increments will return the numeric equivlent of what "row" you are on (start at 0 or 1, i++, continue) but I don't see why you care what row it is as long as you iterate through the entire collection and pull out the specific items you need from each row
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #3  
Old 05-02-2007, 05:12 AM
mcdonnc2004's Avatar
mcdonnc2004 mcdonnc2004 is offline
Junior Contributor
 
Join Date: Dec 2003
Location: Liverpool, UK
Posts: 276
Default

Hi MKoslof,

I decided that the code was not very effcient for writing out values that required text qualifiers so I looked at someones C# code that seemed a lot more efficient so I wrote a VB.NET version of that and it works a lot better:

Code:
Public Sub ExportCSV(ByVal dt As DataTable, ByVal OutputHeaders As Boolean) Dim FILENAME As String = Server.MapPath("GridData.csv") Dim sw As New StreamWriter(FILENAME) If OutputHeaders Then Dim arr As String() = New String(dt.Columns.Count - 1) {} For i As Integer = 0 To dt.Columns.Count - 1 arr(i) = dt.Columns(i).ColumnName arr(i) = OutPutWriteValues(arr(i)) Next i sw.WriteLine(String.Join(",", arr)) End If For j As Integer = 0 To dt.Rows.Count - 1 Dim dataArr As String() = New String(dt.Columns.Count - 1) {} For i As Integer = 0 To dt.Columns.Count - 1 Dim o As Object = dt.Rows(j)(i) dataArr(i) = OutPutWriteValues(o) Next i sw.WriteLine(String.Join(",", dataArr)) Next j sw.Close() End Sub Public Function OutPutWriteValues(ByVal dtInput As Object) As String If dtInput Is Nothing OrElse dtInput Is Convert.DBNull Then Return "" ElseIf dtInput.ToString().IndexOf(",") = -1 Then Return dtInput.ToString() Else Return """" & dtInput.ToString() & """" End If End Function

Thanks for the help.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Find current Row in a DataTable
Find current Row in a DataTable
Find current Row in a DataTable Find current Row in a DataTable
Find current Row in a DataTable
Find current Row in a DataTable
Find current Row in a DataTable Find current Row in a DataTable Find current Row in a DataTable Find current Row in a DataTable Find current Row in a DataTable Find current Row in a DataTable Find current Row in a DataTable
Find current Row in a DataTable
Find current Row in a DataTable
 
Find current Row in a DataTable
Find current Row in a DataTable
 
-->