Create 1DataTable from a Select from DataTable

D

Doug Bell

Hi,
I am having problems trying to create a (temporary) DataTable from a
selection from a DataGrid (dgOrders).

dtOrdDetails is declared as a Public DataTable

Sub is:

Dim stFilter as String
Dim OrdersRows() as DataRow
'If OrderNo = "" then Order is an ASN
If gstOrderNo = "" Then
'ASN Selection

Else
'OrderNo Selection

'Create structure for dtOrderDetails
dtOrderDetails = dsPOs.Tables("PODetails")
dtOrderDetails.Clear()

'Set Filter - Column [PONo] = Selected Order No.
stFilter = "PONo = " & gstOrderNo
OrdersRows = dsPOs.Tables("PODetails").Select(stFilter)

'Populate dtOrderDetails
For i = 0 to OrdersRows.GetUpperBound(0)
dtOrderDetails.ImportRow(OrderRows(i))
Next i

End If

My problem is that OrderRows has a Row Count of 0 and yet I can see valid
records there.
I have also tried:
stFilter = "PONo = '" & gstOrderNo & "'"
stFilter = "PONO = " & gstOrderNo
stFilter = "PONO = " & gstOrderNo & "'"
Actual Alias Field Name is PONo but I have noticed the DB2 Database returns
Columns capitalised.
Field Type is Decimal 8,0

I can step through the DataTable "PODetails" and can see PONo = the Selected
Filter Number.

Thanks for any help,

Doug
 
C

Cor Ligthert

Doug,

Is there any reason that don't you use the currencymanager.position for your
start point and use the underlaying table from the datagrid to do the
filling of your new datatable until the rows to do are reached?

Cor
 
J

Jay B. Harlow [MVP - Outlook]

Doug,
My problem is that OrderRows has a Row Count of 0 and yet I can see valid
records there.
Click to expand...
dtOrderDetails = dsPOs.Tables("PODetails")
dtOrderDetails.Clear()
Click to expand...
You just cleared dsPOs.Tables("PODetails")!

DataTable is a reference type, when you assign one reference
variable/property to another you are making a copy of the reference. Both
variables (dtOrderDetails & dsPOs.Tables("PODetails")) refer to the exact
same DataTable object on the heap. If you call Clear on the one variable you
are also calling clear on the other variable.

Instead of the above two lines, you can use the following single line:
dtOrderDetails = dsPOs.Tables("PODetails").Clone()
Click to expand...

This creates an empty copy of the structure of your DataTable. If you used
..Copy() (as Cor mentioned in your earlier post) you would get the structure
& data of your DataTable.

The rest of your code should work. I normally use a For Each instead of a
For, mostly to avoid "off by one" problems, however that should not cause a
problem with your code.

For Each row As DataRow in OrdersRows
dtOrderDetails.ImportRow(row)
Next i

Hope this helps
Jay

Doug Bell said:
Hi,
I am having problems trying to create a (temporary) DataTable from a
selection from a DataGrid (dgOrders).

dtOrdDetails is declared as a Public DataTable

Sub is:

Dim stFilter as String
Dim OrdersRows() as DataRow
'If OrderNo = "" then Order is an ASN
If gstOrderNo = "" Then
'ASN Selection

Else
'OrderNo Selection

'Create structure for dtOrderDetails
dtOrderDetails = dsPOs.Tables("PODetails")
dtOrderDetails.Clear()

'Set Filter - Column [PONo] = Selected Order No.
stFilter = "PONo = " & gstOrderNo
OrdersRows = dsPOs.Tables("PODetails").Select(stFilter)

'Populate dtOrderDetails
For i = 0 to OrdersRows.GetUpperBound(0)
dtOrderDetails.ImportRow(OrderRows(i))
Next i

End If

My problem is that OrderRows has a Row Count of 0 and yet I can see valid
records there.
I have also tried:
stFilter = "PONo = '" & gstOrderNo & "'"
stFilter = "PONO = " & gstOrderNo
stFilter = "PONO = " & gstOrderNo & "'"
Actual Alias Field Name is PONo but I have noticed the DB2 Database
returns
Columns capitalised.
Field Type is Decimal 8,0

I can step through the DataTable "PODetails" and can see PONo = the
Selected
Filter Number.

Thanks for any help,

Doug
Click to expand...
 
C

Cor Ligthert

Dough,

I get from the message from Jay the idea that what I did below is something
as you wanted to do

\\\This sample needs a form with 2 datagrids
Private Sub Form1_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
DataGrid1.DataSource = createDS.Tables(0)
Dim mynewtable As DataTable = _
DirectCast(DataGrid1.DataSource, DataTable).Clone
Dim myrows As DataRow() = DirectCast(DataGrid1.DataSource, _
DataTable).Select("Numbers > 5")
For Each dr As DataRow In myrows
mynewtable.ImportRow(dr)
Next
DataGrid2.DataSource = mynewtable
End Sub
'Bellow only to create a datatable in a dataset
Public Function createDS() As DataSet
Dim ds As New DataSet
Dim dt As New DataTable
ds.Tables.Add(dt)
dt.Columns.Add("Numbers", GetType(System.Int32))
dt.Columns.Add("Characters")
For i As Integer = 0 To 10
ds.Tables(0).Rows.Add(ds.Tables(0).NewRow)
ds.Tables(0).Rows(i).ItemArray = _
(New Object() {i.ToString, ChrW(i + 65)})
Next
Return ds
End Function
///
I hope this helps?

Cor
 
D

Doug Bell

Cor,
I am not using the Currency Manager because I am filling the Details Form
from a temporary data source (DataTable). I am doing this so that the Users
can make changes to the Details Form and then if they decide not to proceed
they close the form and the changes are discarded. If the changes are valid
then they will be saved from the Temporary Table into a 3rd Table (DataSet),
saved to an XML File and then pushed up into several Tables in the DB2
Database.

Doug

Cor Ligthert said:
Doug,

Is there any reason that don't you use the currencymanager.position for your
start point and use the underlaying table from the datagrid to do the
filling of your new datatable until the rows to do are reached?

Cor


Doug Bell said:
Hi,
I am having problems trying to create a (temporary) DataTable from a
selection from a DataGrid (dgOrders).

dtOrdDetails is declared as a Public DataTable

Sub is:

Dim stFilter as String
Dim OrdersRows() as DataRow
'If OrderNo = "" then Order is an ASN
If gstOrderNo = "" Then
'ASN Selection

Else
'OrderNo Selection

'Create structure for dtOrderDetails
dtOrderDetails = dsPOs.Tables("PODetails")
dtOrderDetails.Clear()

'Set Filter - Column [PONo] = Selected Order No.
stFilter = "PONo = " & gstOrderNo
OrdersRows = dsPOs.Tables("PODetails").Select(stFilter)

'Populate dtOrderDetails
For i = 0 to OrdersRows.GetUpperBound(0)
dtOrderDetails.ImportRow(OrderRows(i))
Next i

End If

My problem is that OrderRows has a Row Count of 0 and yet I can see valid
records there.
I have also tried:
stFilter = "PONo = '" & gstOrderNo & "'"
stFilter = "PONO = " & gstOrderNo
stFilter = "PONO = " & gstOrderNo & "'"
Actual Alias Field Name is PONo but I have noticed the DB2 Database
returns
Columns capitalised.
Field Type is Decimal 8,0

I can step through the DataTable "PODetails" and can see PONo = the
Selected
Filter Number.

Thanks for any help,

Doug
Click to expand...
Click to expand...
 
D

Doug Bell

Hi Jay,
Thanks.
The problem does not seem to be the copy and clear as the original table
still has a row count of 428 and the array holding the rows has a count of
0. But the using clone looks much better. I thought that Clone would be like
creating a DAO Recordset clone (not what I wanted).

I will also use the For each loop.

The problem really seems to be in the Selection but if the syntax and
structure is right then I can't see why, I have found problems with the
Field Names being case sensitive and the DB returning them capitalised even
if they are aliased ( Select PORD AS PONo From... returns PONO).

I will make changes you advised and try some more.

Thanks

Doug

Jay B. Harlow said:
Doug,
My problem is that OrderRows has a Row Count of 0 and yet I can see valid
records there.
Click to expand...
dtOrderDetails = dsPOs.Tables("PODetails")
dtOrderDetails.Clear()
Click to expand...
You just cleared dsPOs.Tables("PODetails")!

DataTable is a reference type, when you assign one reference
variable/property to another you are making a copy of the reference. Both
variables (dtOrderDetails & dsPOs.Tables("PODetails")) refer to the exact
same DataTable object on the heap. If you call Clear on the one variable you
are also calling clear on the other variable.

Instead of the above two lines, you can use the following single line:
dtOrderDetails = dsPOs.Tables("PODetails").Clone()
Click to expand...

This creates an empty copy of the structure of your DataTable. If you used
.Copy() (as Cor mentioned in your earlier post) you would get the structure
& data of your DataTable.

The rest of your code should work. I normally use a For Each instead of a
For, mostly to avoid "off by one" problems, however that should not cause a
problem with your code.

For Each row As DataRow in OrdersRows
dtOrderDetails.ImportRow(row)
Next i

Hope this helps
Jay

Doug Bell said:
Hi,
I am having problems trying to create a (temporary) DataTable from a
selection from a DataGrid (dgOrders).

dtOrdDetails is declared as a Public DataTable

Sub is:

Dim stFilter as String
Dim OrdersRows() as DataRow
'If OrderNo = "" then Order is an ASN
If gstOrderNo = "" Then
'ASN Selection

Else
'OrderNo Selection

'Create structure for dtOrderDetails
dtOrderDetails = dsPOs.Tables("PODetails")
dtOrderDetails.Clear()

'Set Filter - Column [PONo] = Selected Order No.
stFilter = "PONo = " & gstOrderNo
OrdersRows = dsPOs.Tables("PODetails").Select(stFilter)

'Populate dtOrderDetails
For i = 0 to OrdersRows.GetUpperBound(0)
dtOrderDetails.ImportRow(OrderRows(i))
Next i

End If

My problem is that OrderRows has a Row Count of 0 and yet I can see valid
records there.
I have also tried:
stFilter = "PONo = '" & gstOrderNo & "'"
stFilter = "PONO = " & gstOrderNo
stFilter = "PONO = " & gstOrderNo & "'"
Actual Alias Field Name is PONo but I have noticed the DB2 Database
returns
Columns capitalised.
Field Type is Decimal 8,0

I can step through the DataTable "PODetails" and can see PONo = the
Selected
Filter Number.

Thanks for any help,

Doug
Click to expand...
Click to expand...
 
D

Doug Bell

Jay,

Thanks again, it is working. It is just as you said, I was clearing out the
parent DataTable.
My messagebox was showing 428 records but I must have been testing that
before the clear statement.

Thanks
Doug

Jay B. Harlow said:
Doug,
My problem is that OrderRows has a Row Count of 0 and yet I can see valid
records there.
Click to expand...
dtOrderDetails = dsPOs.Tables("PODetails")
dtOrderDetails.Clear()
Click to expand...
You just cleared dsPOs.Tables("PODetails")!

DataTable is a reference type, when you assign one reference
variable/property to another you are making a copy of the reference. Both
variables (dtOrderDetails & dsPOs.Tables("PODetails")) refer to the exact
same DataTable object on the heap. If you call Clear on the one variable you
are also calling clear on the other variable.

Instead of the above two lines, you can use the following single line:
dtOrderDetails = dsPOs.Tables("PODetails").Clone()
Click to expand...

This creates an empty copy of the structure of your DataTable. If you used
.Copy() (as Cor mentioned in your earlier post) you would get the structure
& data of your DataTable.

The rest of your code should work. I normally use a For Each instead of a
For, mostly to avoid "off by one" problems, however that should not cause a
problem with your code.

For Each row As DataRow in OrdersRows
dtOrderDetails.ImportRow(row)
Next i

Hope this helps
Jay

Doug Bell said:
Hi,
I am having problems trying to create a (temporary) DataTable from a
selection from a DataGrid (dgOrders).

dtOrdDetails is declared as a Public DataTable

Sub is:

Dim stFilter as String
Dim OrdersRows() as DataRow
'If OrderNo = "" then Order is an ASN
If gstOrderNo = "" Then
'ASN Selection

Else
'OrderNo Selection

'Create structure for dtOrderDetails
dtOrderDetails = dsPOs.Tables("PODetails")
dtOrderDetails.Clear()

'Set Filter - Column [PONo] = Selected Order No.
stFilter = "PONo = " & gstOrderNo
OrdersRows = dsPOs.Tables("PODetails").Select(stFilter)

'Populate dtOrderDetails
For i = 0 to OrdersRows.GetUpperBound(0)
dtOrderDetails.ImportRow(OrderRows(i))
Next i

End If

My problem is that OrderRows has a Row Count of 0 and yet I can see valid
records there.
I have also tried:
stFilter = "PONo = '" & gstOrderNo & "'"
stFilter = "PONO = " & gstOrderNo
stFilter = "PONO = " & gstOrderNo & "'"
Actual Alias Field Name is PONo but I have noticed the DB2 Database
returns
Columns capitalised.
Field Type is Decimal 8,0

I can step through the DataTable "PODetails" and can see PONo = the
Selected
Filter Number.

Thanks for any help,

Doug
Click to expand...
Click to expand...
 
D

Doug Bell

Hi Cor,
'Doug' no 'h'
Thanks
I have it working this morning with Jay & your assistance.
Delivery is overdue and I am slowly coming to grips with Dot Net.

Doug

Cor Ligthert said:
Dough,

I get from the message from Jay the idea that what I did below is something
as you wanted to do

\\\This sample needs a form with 2 datagrids
Private Sub Form1_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
DataGrid1.DataSource = createDS.Tables(0)
Dim mynewtable As DataTable = _
DirectCast(DataGrid1.DataSource, DataTable).Clone
Dim myrows As DataRow() = DirectCast(DataGrid1.DataSource, _
DataTable).Select("Numbers > 5")
For Each dr As DataRow In myrows
mynewtable.ImportRow(dr)
Next
DataGrid2.DataSource = mynewtable
End Sub
'Bellow only to create a datatable in a dataset
Public Function createDS() As DataSet
Dim ds As New DataSet
Dim dt As New DataTable
ds.Tables.Add(dt)
dt.Columns.Add("Numbers", GetType(System.Int32))
dt.Columns.Add("Characters")
For i As Integer = 0 To 10
ds.Tables(0).Rows.Add(ds.Tables(0).NewRow)
ds.Tables(0).Rows(i).ItemArray = _
(New Object() {i.ToString, ChrW(i + 65)})
Next
Return ds
End Function
///
I hope this helps?

Cor

Doug Bell said:
Hi,
I am having problems trying to create a (temporary) DataTable from a
selection from a DataGrid (dgOrders).

dtOrdDetails is declared as a Public DataTable

Sub is:

Dim stFilter as String
Dim OrdersRows() as DataRow
'If OrderNo = "" then Order is an ASN
If gstOrderNo = "" Then
'ASN Selection

Else
'OrderNo Selection

'Create structure for dtOrderDetails
dtOrderDetails = dsPOs.Tables("PODetails")
dtOrderDetails.Clear()

'Set Filter - Column [PONo] = Selected Order No.
stFilter = "PONo = " & gstOrderNo
OrdersRows = dsPOs.Tables("PODetails").Select(stFilter)

'Populate dtOrderDetails
For i = 0 to OrdersRows.GetUpperBound(0)
dtOrderDetails.ImportRow(OrderRows(i))
Next i

End If

My problem is that OrderRows has a Row Count of 0 and yet I can see valid
records there.
I have also tried:
stFilter = "PONo = '" & gstOrderNo & "'"
stFilter = "PONO = " & gstOrderNo
stFilter = "PONO = " & gstOrderNo & "'"
Actual Alias Field Name is PONo but I have noticed the DB2 Database
returns
Columns capitalised.
Field Type is Decimal 8,0

I can step through the DataTable "PODetails" and can see PONo = the
Selected
Filter Number.

Thanks for any help,

Doug
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Removing rows from a DataTable is VERY slow 32
how to create table in sql server from either a datagridview or excelfile 1
How to Create a new contacts folder with a rule to select from the main contacts 1

Top