CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 5 of 5

Thread: Bind Multiple Tables data in Datagrid

  1. #1
    Join Date
    Aug 2010

    Bind Multiple Tables data in Data Report

    Friends, I had designed an project using VB 6.0 and SQL 2008. Data Storage part is over. Now i am stuck up with display multiple table values in Data Report.

    I read some forum that can be achieved using DATASHAPE and I had tried as follows;


    SHAPE {select Emp_Code,Emp_Name,Place_Office,Designation,Scale,PAN,DOJ,Pay_Level,GPF_No,PRAN from Tbl_Employee where Emp_Code='P-160'} AS Command1 APPEND (( SHAPE {select Emp_Code,Head_Name FSEHN,FSE_Amount,Sal_Month from Tbl_FSE fse join Tbl_Head th on fse.Head_ID=th.Head_ID where Emp_Code='P-160'} AS Command2 APPEND ({select Emp_Code,Head_Name FSDHN,FSD_Amount,Sal_Month from Tbl_FSD fsd join Tbl_Head th on fsd.Head_ID=th.Head_ID where Emp_Code='P-160'} AS Command3 RELATE Emp_Code TO Emp_Code) AS Command3) AS Command2 RELATE emp_code to emp_code) AS Command2

    With Rpt_SS.Sections("Section6").Controls
    .Item("Text46").DataMember = ""
    .Item("Text46").DataField = "Emp_Code"
    .Item("Text6").DataMember = ""
    .Item("Text6").DataField = "Emp_Name"
    .Item("Text47").DataMember = ""
    .Item("Text47").DataField = "GPF_No"
    .Item("Text1").DataMember = ""
    .Item("Text1").DataField = "Place_Office"
    .Item("Text2").DataMember = ""
    .Item("Text2").DataField = "Designation"
    .Item("Text48").DataMember = ""
    .Item("Text48").DataField = "Scale"
    .Item("Text49").DataMember = ""
    .Item("Text49").DataField = "PAN"
    .Item("Text3").DataMember = ""
    .Item("Text3").DataField = "DOJ"
    .Item("Text55").DataMember = ""
    .Item("Text55").DataField = "Pay_Level"

    End With

    With Rpt_SS.Sections("Section1").Controls
    .Item("Text9").DataMember = "Command2"
    .Item("Text9").DataField = "FSEHN"
    .Item("Text4").DataMember = "Command2"
    .Item("Text4").DataField = "FSE_Amount"
    .Item("Text5").DataMember = "Command2"
    .Item("Text5").DataField = "FSDHN"
    .Item("Text7").DataMember = "Command2"
    .Item("Text7").DataField = "FSD_Amount"
    End With
    Error message coming as " Datafield Command2.FSD_Amount not found"

    Please guide me to fix the issue.

    Thank you.

    Last edited by gunapriyan; December 10th, 2018 at 10:46 PM. Reason: Title change

  2. #2
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008

    Re: Bind Multiple Tables data in Datagrid

    I personally do not use the VB report tool, I use Active Reports instead so can't be of any help with the report itself.

    What I have done on occasion in the past where I needed data from multiple tables for a report is use a temp table and populate it with the data required then point the report to that table. Probably not the most efficient way to do it but was the first method I came up with that managed to give the results I needed so may be something to consider.
    Always use [code][/code] tags when posting code.

  3. #3
    Join Date
    Aug 2010

    Re: Bind Multiple Tables data in Datagrid

    Thanks for your reply. My problem is different counts of rows in three tables. I shall be happy how to create temp table. Tjanks

  4. #4
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008

    Re: Bind Multiple Tables data in Datagrid

    I generally create the table in the designer though it could be done through code.
    I add the fields I will need for the report then populate that table through one or more queries and/or code as needed.
    I add a little pre-processor routine to my code that clears the table and places the required data into it prior to launching the report.

    The report itself is fairly simple then as you only have to point it to the one table.

    Now how you would get the data you want into that table is hard to say, looking at that query makes my head hurt
    Always use [code][/code] tags when posting code.

  5. #5
    Join Date
    Dec 2018

    Re: Bind Multiple Tables data in Datagrid

    You may try this code to bind tables.

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
    Dim sql As String = "SELECT TOP 10 c.CustomerId, c.ContactName, o.OrderId,"
    sql += " (e.FirstName + ' ' + e.LastName) EmployeeName"
    sql += " FROM Customers c INNER JOIN Orders o on c.CustomerId = o.CustomerId"
    sql += " INNER JOIN Employees e ON e.EmployeeId = o.EmployeeId"
    GridView1.DataSource = Me.GetData(sql)
    End If
    End Sub

    Private Function GetData(sql As String) As DataTable
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As New SqlConnection(constr)
    Using cmd As New SqlCommand(sql)
    Using sda As New SqlDataAdapter(cmd)
    cmd.Connection = con
    Dim dt As New DataTable()
    Return dt
    End Using
    End Using
    End Using
    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Windows Mobile Development Center

Click Here to Expand Forum to Full Width

On-Demand Webinars (sponsored)