Well i have a rather unsual case and i can't think of anything .
The situation is like this
I have a nice Access query that gathers info from various tables .SO far so good.The problem is that this queryis base for another query that has a calculated column that works with a custom function i have made in vba.
Lets assume the base query give this data
--------------------------------------------------
Client Order | Create Something with Code | Client
1x......................... | x01....................... | A
2x......................... | x01...................... |A
3x......................... | x02...................... |A
4x......................... | x03...................... |B
the calculated goes like this
--------------------------------------------------
Client OrderCalc | Create Something with Code | Client
1x / 2x ............| x01 ..................................| A
3x ..................| x02 ..................................|A
4x ..................| x03 ..................................|B (the dots are for "trying" to give proper column effect)
As u can see since the product i am going to make is the same in 2 orders i will make it once but i want the information that 2 client orders need it
Well right in VB i have a datagridview wich has source a dataset which is filled from a query which gets column(Create Something with code and Client,3 rows as u see in the calculated query but missing the 1st column) and i am trying to find a way to retrieve the 1st column.
I know it is a bit complicated and i am stuck.
P.S the custom function i use in VBA goes like this
For every row (the create something with code is Unique) in the calculated query it searches the base query for matching client orders and outputs a string with concatenated the clientOrders
Last edited by masteripper; Aug 28th, 2009 at 12:00 PM.
Well i was pretty that it is somewhat beyond the usual but not even one response.What you would do if you a situation you needed the result of a custom function on a datagrid depending on a column of the datagrid.
If it is only possible with LINQ(?) please inform.
Can you post your Function Code, and can you post the SQL used in both of your queries?
It should be relatively easy the grab the value out of a DGV column for use in your calculation. However, it would work event BETTER if your wrote the SQL to perofrm the calculation prior to populating the DGV in the first place . . .
You probably haven't gotten a response because it is unclear where your answer lies. We don't have enough information . . .
I don't think is that great code but here how it goes
There is one complete query that has all the results and one that returns only the unique values(there is a join).What i do is that for every row of the query with the Unique values i search the Complete for occurences of the "client order" and what i find i concatenate it in one single string.
e.g the client asked for the same thing twice so we will manufacture it only once but the quantity will be the sum of the two orders....but the guy who takes the manufacturing is good to know it belongs to 2 client orders due to logistics .
If it is still too hard to picture it please tell if it is possible for a DGV to have a column calculated by a custom function(not the usual aggregate functions but a really custom function)
OK. So, you still didn't leave me much to go on, so I took some guesses.
THis may not be what you are looking for, and there are probably better ways to do it even if it is, but here goes . . .
As often when I post here, this is something of a "brute-force" approach to your problem. There are DEFINITLEY more efficient and/or elegant ways to do what you are trying to do (or what I THINK you are tring to do . . .).
I DId not do this with a designer-bound DGV control, although you can proably take what I have done and accomplish that, if you choose. I DID retrieve the data programmatically, and THEN bound it to the control.
I have attached a hacked together Access .mdb file, and a vs2008 Project file. Check it out.
The vs Project file has a connection string set in the Project Settings file that will look for this .mdb at the root level of C Drive, so copy the .mdb to C:\
Here is the code I threw together:
Code:
Imports System.Data
Imports System.Data.OleDb
Public Class Form2
'Form-Level DataTables to work with:
Private dtGroupCustomers As DataTable
Private dtOrders As DataTable
'Call when you need to Load or refresh the data in the DataTables:
Public Sub LoadData()
Dim strCustomerOrders As String = "" & _
"SELECT tblCustomer.CustomerID, tblCustomer.Customer " & _
"FROM tblCustomer INNER JOIN tblOrder ON tblCustomer.CustomerID = tblOrder.CustomerID " & _
"GROUP BY tblCustomer.CustomerID, tblCustomer.Customer"
Dim strOrders As String = "" & _
"SELECT tblOrder.ItemID, tblOrder.OrderID, tblOrder.CustomerID " & _
"FROM tblOrder"
'Create a connection object:
Using cn As New OleDbConnection(My.Settings.cxnOrderSample)
'Create a commmand to retrieve records for all of the
'customers who have Orders:
Using cmdUnique As New OleDbCommand(strCustomerOrders, cn)
cmdUnique.CommandType = CommandType.Text
Dim dr As OleDbDataReader
dtGroupCustomers = New DataTable
Try
cn.Open()
dr = cmdUnique.ExecuteReader()
dtGroupCustomers.Load(dr)
Catch ex As Exception
'Something has gone horribly Wrong!
If Not cn.State = ConnectionState.Closed Then
cn.Close()
End If
End Try
Using cmdOrders As New OleDbCommand(strOrders, cn)
cmdOrders.CommandType = CommandType.Text
dtOrders = New DataTable
Try
'The connection is already open:()
dr = cmdOrders.ExecuteReader
dtOrders.Load(dr)
Catch ex As Exception
'Something has gone horribly Wrong!
MsgBox(ex.Message)
If Not cn.State = ConnectionState.Closed Then
cn.Close()
End If
End Try
End Using
End Using
End Using
End Sub
'Call to load the data in the DataTables into the DGV control:
Public Sub LoadDGV()
'Initialize a DataGridViw Variable to work with
'and assign it to the DGV on your Form:
Dim DGV As DataGridView = Me.DataGridView1
'Set the datasource to the DataTable containing records
'for Customers with Orders:
DGV.DataSource = dtGroupCustomers
'Add a column to hold the funky concatenated Order info:
DGV.Columns.Add("OrderID", "OrderID")
'Because this DGV is displaying the odd concatenation, we don't want user's
'trying to add rows. I assume the data-entry happens elsewhere, and that this
'display if for the manufacturing guy, who will not be updating it?
DGV.AllowUserToAddRows = False
'Assign the HeaderText property to
'the Name Property for each column so you can reference the columns by name
'later. This is not done when you set the datasource, apparently:
For Each dgvColumn As DataGridViewColumn In DGV.Columns
dgvColumn.Name = dgvColumn.HeaderText
Next
'Iterate through the rows in the DataTable of Customers who have
'Orders, and use the local function "CustomerItemOrders"
'to populate the "OrderID" column we added:
For Each dgvRow As DataGridViewRow In DGV.Rows
'The function requires a customer ID to filter the data:
Dim iCustomerID As Integer = dgvRow.Cells("CustomerID").Value
'Create a String variable to return the data from the function:
Dim str As String = CustomerItemOrders(iCustomerID)
'Assign the value to the cell in the current DGV Row:
dgvRow.Cells("OrderID").Value = str
Next
End Sub
'a Function that performs the concatenation you want and returns a String:
Private Function CustomerItemOrders(ByVal CustomerID As String)
Dim strOrders As String
Dim strFilter As String
'Create a filter to use on the dataset to
'show only record for the customer ID passed in:
strFilter = "CustomerID = " & CustomerID
'A dataView to hold the filter results:
Dim drv As DataView
drv = dtOrders.DefaultView
drv.RowFilter = strFilter
If drv.Count > 0 Then
'Grab the FIRST value, 'cuz we don't want to append a
'comma to the front of it . . .
strOrders = "Order# " & drv(0).Item("OrderID")
'When doing concatenation, it is better to use
'a StringBuilder Object than the actual String (Look it up,
'it REALLY is . . .):
Dim sb As New System.Text.StringBuilder(strOrders)
'Then interate thru the Dataveiw . . .
For Each dr As DataRowView In drv
'But ignore the first row, because we already have the in our string
'Note: you can't use "=" operator, you need to compare object references for equality):
If Not ReferenceEquals(dr, drv.Item(0)) Then
'Append a comma, followed by the OrderID (Or whatever other
'Order info you want to include):
sb.Append(", Order #" & dr("OrderID"))
End If
Next
'NOW store the contents of the StringBuilder Object into the String variable:
strOrders = sb.ToString
Else
'Just in case:
strOrders = "None"
End If
'Return the String as the value of the function:
Return strOrders
End Function
'The load sequence:
Private Sub Form2_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
LoadData()
LoadDGV()
End Sub
End Class
Well thanks for the solution but i solved it myself a few minutes ago....believe it or not....i spend a whole afternoon but the solution was so easy with the iterations of datagridview row.Thanks very much for your time and with a glance at your code looks much cleaner and i will use it..especially the filtering of the dataview is rocking.
Again thanks..
Last edited by masteripper; Aug 30th, 2009 at 03:04 PM.
It's good that you struggled and figured it out on your own, because THAT is how you learn this stuff . . .
That's why I put in a little time on some of these things for folks here. It is good problem-solving practive, even though some of the solutions are not optimal at first (at least, as far as I am concerned-the code I posted is something of a hack-way to do it), but sometimes, you just make it WORK first, and THEN worry about getting "elegant!"
[RESOLVED] Complex Issue with DataSet/Access
Reply With Quote
