fpspread Grouping

Posted by: redahashem-fp on 8 September 2017, 2:06 pm EST

  • Posted 8 September 2017, 2:06 pm EST

    i use fpspread Grouping to show invoice head and invoice details its works fine but i want to Filter the tables by  invoice date or invoice number


    i use this code and give me wrong and error


    ========================


    Dim cmd As New OleDbCommand()


    Dim da As New OleDbDataAdapter()


    Dim ds As New DataSet()


    cn.open


    With cmd


    .Connection = cn


    .CommandType = CommandType.TableDirect


    .CommandText = "select code,invoice_no,my_date,invoice_date,suplier_no,suplier_name,total,fara3_name,closed from tbl_invoice_buy_cash_head where(tbl_invoice_buy_cash_head.my_date=#" & DateEdit1.Text & "#)"


    End With


    da.SelectCommand = cmd


    da.Fill(ds, "tbl_invoice_buy_cash_head")


    cmd.CommandText = "select ivoice_no,item_no,item_name,quantity,price,total_price from tbl_invoice_buy_cash_details"


    da.SelectCommand = cmd


    da.Fill(ds, "tbl_invoice_buy_cash_details")


    ds.Relations.Add("Root", ds.Tables("tbl_invoice_buy_cash_head").Columns("code"), ds.Tables("tbl_invoice_buy_cash_details").Columns("ivoice_no"))


    FpSpread1.DataSource = ds


     thanks 4 help


    Smile Smile Smile Smile


     


     

  • Replied 8 September 2017, 2:06 pm EST

    it doent work if i have more than dates in my table and this is the error message-This constraint cannot be enabled as not all values have corresponding parent values.


    Reeva Dhingra:


    In addition to this, if you want to use the filtering feature of fpSpread with grouping feature (as is evident from the subject line of Post), then please note that Grouping and Filtering do not work together. If you want to use grouping, you should not use filtering and you should clear the filter under the Grouping event.


     


     


    Reeva Dhingra


    GrapeCity Farpoint


     



    your sample using to filter the child table- and me not   i want to filter parent table


     


    thanks 4 help

  • Replied 8 September 2017, 2:06 pm EST

    Hi Redahashem,


     


    In your code there is data type mismatch in expression criteria. For Date/ Time data type, you need to use the DateValue function. The DateValue function converts a string to a date. String can be a date ranging from January 1, 100 to December 31, 9999. DateValue also recognizes unambiguous dates that contain month names, either in long or abbreviated form.


     


    Here is the sample code to achieve the required functionality of filtering the tables by InvoiceDate OR InvoiceNumber.


     


    Private Sub GroupingAndFiltering_Load(sender As Object, e As EventArgs)


       Dim ds As New DataSet()


       Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb"


       Dim con As New OleDbConnection(connectionString)


       Dim qstringCustomers As String = "SELECT * FROM Customers"


       Dim qstringOrders As String = "SELECT OrderID, CustomerID, EmployeeID, InvoiceDate, RequiredDate, ShippedDate, InvoiceNumber FROM Orders WHERE InvoiceDate = DateValue ('04-Jul-1996') OR InvoiceNumber = 3"


         Dim adapterCustomer As New OleDbDataAdapter(qstringCustomers, con)


       Dim adapterOrders As New OleDbDataAdapter(qstringOrders, con)


       adapterCustomer.Fill(ds, "Customers")


       adapterOrders.Fill(ds, "Orders")


       Dim customerToOrders As New DataRelation("customerToOrder",   ds.Tables("Customers").Columns("CustomerID"), ds.Tables("Orders").Columns("CustomerID"))


       ds.Relations.Add(customerToOrders)


       fpSpread1.DataSource = ds


    End Sub 'GroupingAndFiltering_Load


     


    In addition to this, if you want to use the filtering feature of fpSpread with grouping feature (as is evident from the subject line of Post), then please note that Grouping and Filtering do not work together. If you want to use grouping, you should not use filtering and you should clear the filter under the Grouping event.


     


     


    Reeva Dhingra


    GrapeCity Farpoint


     


    2009/10/FpSpreadGrouping.zip
  • Replied 8 September 2017, 2:06 pm EST

    i hve one problem with  relation grouping when primary key contains from 2 columns gives error


    i use this code


    =========================


    Dim cmd As New OleDbCommand()


    Dim da As New OleDbDataAdapter()


    Dim ds As New DataSet()


    Call cnopen()


    Dim sql1 As String


    sql1 = "select*from tbl_invoice_sale_barcode_head "


    Dim sql2 As String = "select* from tbl_invoice_sale_barcode_details"


    Dim head As New OleDbDataAdapter(sql1, cn)


    Dim details As New OleDbDataAdapter(sql2, cn)


    head.Fill(ds, "tbl_invoice_sale_barcode_head")


    details.Fill(ds, "tbl_invoice_sale_barcode_details")


    Dim parentColumns() As DataColumn


    Dim childColumns() As DataColumn


    parentColumns(0) = ds.Tables("tbl_invoice_sale_barcode_details").Columns("doc_no")


    parentColumns(1) = ds.Tables("tbl_invoice_sale_barcode_details").Columns("casher_no")


     


    childColumns(0) = ds.Tables("tbl_invoice_sale_barcode_details").Columns("doc_no")


    childColumns(1) = ds.Tables("tbl_invoice_sale_barcode_details").Columns("casher_no")


     


    Dim bConstraints As Boolean = True


    ' Create DataRelation.


    Dim CustOrderRel As DataRelation = New DataRelation( _


    "CustomersOrders", parentColumns, childColumns, _


    bConstraints)


    ' Add the relation to the DataSet.


    ds.Relations.Add(CustOrderRel)


    FpSpread1.DataSource = ds


     

  • Replied 8 September 2017, 2:06 pm EST

    thank you so 100000000000000000000000000000000000000000000000000000000 much


    it works good


     

  • Replied 8 September 2017, 2:06 pm EST

    Redahashem,


    For this you need to set constraint in DataRelation class to false.


    ds.Relations.Add(CustomerToOrder, parentCol, childCol, false)


    By default, when you create a relationship, it enforces foreign key constraints, by setting to false, you are telling it that you dont want to enforce the relationship.


     Reeva Dhingra


    GrapeCity FarPoint

  • Replied 8 September 2017, 2:06 pm EST

    Hello Redahashem,


    You need to set your code to this:



                parentColumns(0) = ds.Tables("tbl_invoice_sale_barcode_details").Columns("doc_no")
                parentColumns(1) = ds.Tables("tbl_invoice_sale_barcode_details").Columns("casher_no")
                childColumns(0) = ds.Tables("tbl_invoice_sale_barcode_head").Columns("doc_no")
                childColumns(1) = ds.Tables("tbl_invoice_sale_barcode_head").Columns("casher_no")


    instead of the code given below in order to make it work i.e. the parentColumns(0) and childColumns(0) should contain same columns but from different tables so that they can be mapped together and same for the parentColumns(1) and childColumns(1).



                parentColumns(0) = ds.Tables("tbl_invoice_sale_barcode_details").Columns("doc_no")
                parentColumns(1) = ds.Tables("tbl_invoice_sale_barcode_details").Columns("casher_no")
                childColumns(0) = ds.Tables("tbl_invoice_sale_barcode_details").Columns("doc_no")
                childColumns(1) = ds.Tables("tbl_invoice_sale_barcode_details").Columns("casher_no")


    Hope this will help you. Thanks.

  • Replied 8 September 2017, 2:06 pm EST

    thanks so much 4 help it works
  • Replied 8 September 2017, 2:06 pm EST

    Hello Redahashem,


    Please find the attched zipped project wherein the code mentioned above is working fine.


    Please let us know your further observations for the same.


    Thanks.


    2010/02/FpSpreadGrouping.zip
  • Replied 8 September 2017, 2:06 pm EST

    i  Modified the cod to this


    -----------------


                parentColumns(0) = ds.Tables("tbl_invoice_sale_barcode_details").Columns("doc_no");
                parentColumns(1) = ds.Tables("tbl_invoice_sale_barcode_details").Columns("casher_no");
                childColumns(0) = ds.Tables("tbl_invoice_sale_barcode_head").Columns("doc_no");
                childColumns(1) = ds.Tables("tbl_invoice_sale_barcode_head").Columns("casher_no");


    -------------------------------------------------------------------------------------------------------------------------------------------


    but still give error do u try it with my database it didnt work with me


     

  • Replied 8 September 2017, 2:06 pm EST

    Hello,


    You can not set the relations by setting the ParentColumns(0) and ChildColumns(0) of same table only, they has to be mapped from different tables. Hence, while setting the childColumns(0) and childColumns(1) the Table has to be tbl_invoice_sale_barcode_head instead of tbl_invoice_sale_barcode_details.


    Hope this will help you, else you can get back to us. Thanks.

  • Replied 8 September 2017, 2:06 pm EST

    so how can i do to show grouping if primary key in database contains from 2 columns - cols[0]+cols[1] in Parent table  relations with cols[0]+cols[1] in Child table 


    this link for my database


    http://www.4shared.com/file/224165628/7dbebcd/Database1.html


     would you Modified this code to fit me


    Dim cmd As New OleDbCommand()


    Dim da As New OleDbDataAdapter()


    Dim ds As New DataSet()


    Call cnopen()


    Dim sql1 As String


    sql1 = "select*from tbl_invoice_sale_barcode_head "


    Dim sql2 As String = "select* from tbl_invoice_sale_barcode_details"


    Dim head As New OleDbDataAdapter(sql1, cn)


    Dim details As New OleDbDataAdapter(sql2, cn)


    head.Fill(ds, "tbl_invoice_sale_barcode_head")


    details.Fill(ds, "tbl_invoice_sale_barcode_details")


     


     


     


    Dim headTodetails As New DataRelation("headTodetails", ds.Tables("tbl_invoice_sale_barcode_head").Columns("doc_no "), ds.Tables("tbl_invoice_sale_barcode_details").Columns("doc_no"), False)


    FpSpread1.DataSource = ds

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels