Results 1 to 8 of 8

Thread: shape statement?

  1. #1

    Thread Starter
    Fanatic Member noielen's Avatar
    Join Date
    Nov 2005
    Location
    Cebu, Phil.
    Posts
    680

    shape statement?

    for example i have these records:

    Table Name: Department
    Name
    I.T. Dept.
    Finance Dept.

    Table Name: Employee
    Name - Department
    Carl - I.T. Dept.
    Noie - I.T. Dept.
    Doye - I.T. Dept.
    Shar - Finance Dept.

    Table Name: Calls
    Date - Caller - Duration - Cost
    2/2/2006 - Noie - 00:34:12 - 23.75
    2/2/2006 - Carl - 01:02:55 - 55.00
    2/2/2006 - Carl - 00:22:03 - 12.75
    2/2/2006 - Noie - 03:00:11 - 76.25

    For example i want to display all I.T. Department's calls?
    Using Shape Statement??
    Any help really appreciated.
    noister
    <advertising link removed by moderator>

  2. #2
    Fanatic Member eimroda's Avatar
    Join Date
    Jul 2000
    Location
    Philippines
    Posts
    642

    Re: shape statement?

    i learned the structure of SHAPE commands from the Data Environment. if you dont have one, add it to your project, make a connection then make two commands, one is being the child of the other command. set the Relationship well. then RIGHT-CLICK the parent command and click Show Shape Command (im not sure if thats the right words in the menu, it is in the bottom of the shortcut menu) i have no VB here and im getting old...

    BTW, i dont recommend the use of DE here, you can remove it after you get the syntax of the SHAPE command...
    On Error GoTo Hell

    Hell:
    Kill Me


    Food For Thought:

    - Do not judge a book... if you're not a judge!


  3. #3
    Addicted Member jeanette_db's Avatar
    Join Date
    Oct 2005
    Location
    DC, Phil
    Posts
    216

    Re: shape statement?

    shape { select * from department }
    append ( ( Shape { select * from employee }
    append ( {select * from calls }
    relate caller to name ))
    relate department to department)

    this code groups records by department..
    then by name (caller)...
    try revising this....

  4. #4

    Thread Starter
    Fanatic Member noielen's Avatar
    Join Date
    Nov 2005
    Location
    Cebu, Phil.
    Posts
    680

    Re: shape statement?

    VB Code:
    1. 'declare ------------------------------
    2. 'Dim cmdrep As New ADODB.Command
    3. Dim strsql As String
    4. Dim repcon As New ADODB.Connection
    5. Dim reprs As New ADODB.Recordset
    6. Dim param As New ADODB.Parameter
    7. Dim param2 As New ADODB.Parameter
    8. Dim cmdrep As New ADODB.Command
    9. 'end declare --------------------------
    10.  
    11. repcon.CursorLocation = adUseClient
    12. repcon.Provider = "MSDataShape.1"
    13. repcon.ConnectionString = "Data Provider=Microsoft.Jet.OLEDB.4.0;" & _
    14. "Data Source=" & App.Path & "\CASdb.mdb;Persist Security Info=False"
    15. repcon.Open
    16. cmdrep.CommandText = " shape { select * from department } " & _
    17.                      " append ( ( Shape { select * from employee } " & _
    18.                      " append ( {select * from calls } " & _
    19.                      " relate caller to name )) " & _
    20.                      " relate department to department) "
    21.  
    22. cmdrep.ActiveConnection = repcon
    23. cmdrep.CommandType = adCmdText
    24. Set reprs = cmdrep.Execute
    25. Set datareportdepartment.DataSource = reprs
    Error occur: Column Name does not exist in the appropriate rowset.
    noister
    <advertising link removed by moderator>

  5. #5
    old fart Frans C's Avatar
    Join Date
    Oct 1999
    Location
    the Netherlands
    Posts
    2,926

    Re: shape statement?

    I am not very good in shape commands, but shouldn't it be relate department to name, because the department table only has a name field, and not a department field?
    Frans

  6. #6

    Thread Starter
    Fanatic Member noielen's Avatar
    Join Date
    Nov 2005
    Location
    Cebu, Phil.
    Posts
    680

    Re: shape statement?

    Quote Originally Posted by Frans C
    I am not very good in shape commands, but shouldn't it be relate department to name, because the department table only has a name field, and not a department field?
    k ill try it first...
    noister
    <advertising link removed by moderator>

  7. #7

    Thread Starter
    Fanatic Member noielen's Avatar
    Join Date
    Nov 2005
    Location
    Cebu, Phil.
    Posts
    680

    Re: shape statement?

    Frans C: same thing happened, i mean the same error.

    Here's my latest code:
    VB Code:
    1. cmdrep.CommandText = " SHAPE {SELECT name FROM Department WHERE status = 1} " & _
    2. " AS Department APPEND (( SHAPE {SELECT EMPLOYEE.NAME, " & _
    3. " EMPLOYEE.DEPARTMENTID, " & _
    4.     " DEPARTMENT.NAME AS DEPTNAME " & _
    5.     " From EMPLOYEE, DEPARTMENT " & _
    6.     " WHERE EMPLOYEE.DEPARTMENTID = DEPARTMENT.NAME}  AS Employee " & _
    7. " APPEND ({SELECT CALLS.CALLER, CALLS.TOTAL, CALLS.DURATION, " & _
    8.     " EMPLOYEE.name " & _
    9.     " From CALLS, EMPLOYEE " & _
    10.     " WHERE EMPLOYEE.NAME = CALLS.CALLER}  AS Calls RELATE ) AS Calls) " & _
    11. " AS Employee RELATE ) AS Employee "
    attached file is the error
    Attached Images Attached Images  
    Last edited by noielen; Apr 26th, 2006 at 04:53 AM.

  8. #8
    old fart Frans C's Avatar
    Join Date
    Oct 1999
    Location
    the Netherlands
    Posts
    2,926

    Re: shape statement?

    I just made one in the dataenvironment, and came to this shape command:

    Code:
    SHAPE {select * from department}  AS Department APPEND (( SHAPE {select * from employee}  AS Employee APPEND ({select * from calls }  AS Calls RELATE 'Name' TO 'Caller') AS Calls) AS Employee RELATE 'Name' TO 'Department') AS Employee
    In your code you had the relation missing (after RELATE)
    Frans

Posting Permissions

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



Click Here to Expand Forum to Full Width