Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Thursday, February 4, 2010 10:59 AM
Hi Folks,
I have a data table in a loop that adds rows on each loop. During the loop i collect an array of unknown size. At the end of the loop i want to add a new row to the datatable with eachelement of the array as a field.
so if will look like this:
engineer name | array(0) | array(1) | array(2) | array(3)
I could hard code this if i knew how many item would be in the array but i dont...
Is there any way i can loop through the array and append the value to a row?
Many thanks,
Billy
All replies (8)
Friday, February 5, 2010 12:30 PM âś…Answered
Billy,
I think that your problem is that a DataRow is a type that has three main properties,
- relation
- items
- DataTable
In the DataTable are the columns described which can be used.
So it is impossible to create a datarow with unknown columns because that would exhoust the datatable.
You can however imagen that there can be 100 columns (more is not advisable in AdoNet because most methods are build around 100).
You can then get code like this, be aware it is only for one datarow, the datatable can be the one you got from your joins.
The columns are added to that. Be aware that adding of columns can in fact only one time, I don't know how to describe this, but I assume that you understand what I mean.
Dim dt As New DataTable
Dim myarray(100) As Integer
For i As Integer = 0 To 99
dt.Columns.Add(New DataColumn("Hours" & i.ToString))
Next
dt.Rows.Add(dt.NewRow)
dt.Rows.Add()
For i = 0 To myarray.Length - 2
dt.Rows(0).Item(i) = myarray(i)
Next
Success
Cor
Thursday, February 4, 2010 11:10 AM
Isn't it possible to use the length property or count property of an array you are using.
or you could determine the length of an array using ubound(array) in visual basic.
http://www.ishan-solution.blogspot.com
Thursday, February 4, 2010 11:57 AM
Isn't it possible to use the length property or count property of an array you are using.
or you could determine the length of an array using ubound(array) in visual basic.
I can get the length easy enough but am not sure on how to add each element to the row.
Thursday, February 4, 2010 3:19 PM
I never use the a value for the end even if I know that.
An Array has a length property (a list a count) so looping through an array is
For i = 0 to myArray.Length -2
do whatever you want
next
A little thing to know about is that Visual Basic is created compatible (Who know why for that part) with older versions, so for an array is always one extra created and then you can use the first as indexer.
for i = 1 to myArray.Length -1
do whatever you want
next
Success
Cor
Thursday, February 4, 2010 4:30 PM
I never use the a value for the end even if I know that.
An Array has a length property (a list a count) so looping through an array is
For i = 0 to myArray.Length -2
do whatever you want
nextA little thing to know about is that Visual Basic is created compatible (Who know why for that part) with older versions, so for an array is always one extra created and then you can use the first as indexer.
for i = 1 to myArray.Length -1
do whatever you want
nextSuccess
Cor
Hi Cor,
Cheers for the reply.
I know that to loop through an array is the answer but the part I'm stuck on is the adding to the table row.
How do i mix
for i = 1 to myArray.Length -1
do whatever you want
next
with
dtgrid.row.add(array(0), array(1), array(2))
Can I reference a particular row so that i can do
x = 0
for i = 1 to myArray.Length -1
dtgrid.row(0).add(array(x))
x = x + 1
next
Regards,
Billy
Thursday, February 4, 2010 8:31 PM
Can you describe you table structure a bit? How many elements are there is your input array and how many fields in your table? Alternatively, are the elements in the array just getting joined together with a delimiter and stored in a blob-ish field?jon.stromer.galley
Friday, February 5, 2010 8:44 AM
Can you describe you table structure a bit? How many elements are there is your input array and how many fields in your table? Alternatively, are the elements in the array just getting joined together with a delimiter and stored in a blob-ish field? jon.stromer.galley
Basically I have a checkbox list of resrouces (staff members) and a checkbox list of support ticket queues. I create a hash table of the resources checked and queues checked then create a gridview with the amount of hours each has spent on each queue. the structure is:
Dim dtQueue As New DataTable()
Dim dtGrid As New DataTable()
dtGrid.Columns.Add("Engineer", GetType(String))
'create table columns
For Each queue1 In queueHash
dtGrid.Columns.Add(queue1.value.ToString)
Next
Dim arraycount As Integer = queueHash.Count - 1
Dim hoursArray(arraycount)
Dim queueid As Integer
Dim resourceid As Integer
' for each checked resource
For Each item In resourceHash
'set resouce id to key of hash table item
resourceid = item.key.ToString
Dim x As Integer = 0
' for each queue selected get tickets based on queueid and resource id
For Each queue In queueHash
'assign queue id to the key of the hash item
queueid = queue.key.ToString
Dim connection As New SqlConnection("Data Source=Reports.Autotask.net,1433;Initial Catalog=xxxxxx;User ID=xxxxxxx;Password='xxxxxx';")
connection.Open()
Dim sqlCmd As New SqlCommand("SELECT wh_time_subitem.date_worked, wh_time_item.user_id, wh_time_subitem.hours_worked, wh_resource.first_name, wh_task.task_name, wh_task_type.task_type_name, wh_queue.queue_name, wh_queue.queue_id FROM wh_queue INNER JOIN (wh_task_type INNER JOIN ((wh_resource INNER JOIN (wh_time_subitem INNER JOIN wh_time_item ON wh_time_subitem.time_item_id = wh_time_item.time_item_id) ON wh_resource.resource_id = wh_time_item.user_id) INNER JOIN wh_task ON wh_time_item.task_id = wh_task.task_id) ON wh_task_type.task_type_id = wh_task.task_type_id) ON wh_queue.queue_id = wh_task.ticket_queue_id GROUP BY wh_time_subitem.date_worked, wh_time_item.user_id, wh_time_subitem.hours_worked, wh_resource.first_name, wh_task.task_name, wh_task_type.task_type_name, wh_queue.queue_name, wh_queue.queue_id HAVING(((wh_time_subitem.date_worked) ='02/02/2010') And ((wh_time_item.user_id) =" + item.key.ToString + ") And ((wh_queue.queue_id) =" + queue.key.ToString + ")) ORDER BY wh_resource.first_name", connection)
Dim sqlDa As New SqlDataAdapter(sqlCmd)
sqlDa.Fill(dtQueue)
If dtQueue.Rows.Count > 0 Then
'for each row returned
For i As Integer = 0 To dtQueue.Rows.Count - 1
'get values from recordset
Dim userid As Integer = dtQueue.Rows(i)("user_id")
Dim qid As Integer = dtQueue.Rows(i)("queue_id")
Dim hours_worked As Double = dtQueue.Rows(i)("hours_worked")
'Count up hours worked.
hourscount = hourscount + hours_worked
Next
End If
hoursArray(x) = hourscount
hourscount = 0
x = x + 1
Next
** Dim k As Integer = 0
For z = 1 To hoursArray.Length - 1
dtGrid.Rows.Add(item.value.ToString, hoursArray(k))
k = k + 1
Next
**
Next
DataGridChart.DataSource = dtGrid.DefaultView
DataGridChart.DataBind()
End Sub
Hopefully its clear what I'm trying to achieve from that. The text in bold is as close to filling the table as I've got.
Cheers,
Billy
Friday, February 5, 2010 3:56 PM
Billy,
I think that your problem is that a DataRow is a type that has three main properties,
- relation
- items
- DataTable
In the DataTable are the columns described which can be used.
So it is impossible to create a datarow with unknown columns because that would exhoust the datatable.You can however imagen that there can be 100 columns (more is not advisable in AdoNet because most methods are build around 100).
You can then get code like this, be aware it is only for one datarow, the datatable can be the one you got from your joins.
The columns are added to that. Be aware that adding of columns can in fact only one time, I don't know how to describe this, but I assume that you understand what I mean.Dim dt As New DataTable Dim myarray(100) As Integer For i As Integer = 0 To 99 dt.Columns.Add(New DataColumn("Hours" & i.ToString)) Next dt.Rows.Add(dt.NewRow) dt.Rows.Add() For i = 0 To myarray.Length - 2 dt.Rows(0).Item(i) = myarray(i) NextSuccess
Cor
Thanks cor dt.rows(o).item(i) was exactly what I needed! cheers!
Billy