Results 1 to 6 of 6

Thread: [RESOLVED] Simple SQL on Data Environment / Report [help please]

  1. #1

    Thread Starter
    Member
    Join Date
    Apr 2005
    Posts
    38

    Resolved [RESOLVED] Simple SQL on Data Environment / Report [help please]

    Hey,

    I currently have a Data Report, linked to a Data Environment, that displays the following fields:

    ProductID
    Cost
    Price
    Profit

    It currently displays all of the records from the Product Table. Is there any way so that at run-time when I display the report, it only displays one record, hence only one ProductID? For example:
    VB Code:
    1. Private Sub CommandBox_Click()
    2. Load ProductReport Where ProductID= " & txtProductID & "
    I'm not sure if this can be done this way (coding in command box), or whether to use the 'SQL Statement' box in the Command Properties of the Data Environment.

    Any pointers would be greatly appreciated.
    With many thanks!

  2. #2
    PowerPoster
    Join Date
    Nov 2002
    Location
    Manila
    Posts
    7,629

    Re: Simple SQL on Data Environment / Report [help please]

    You can create a command object in the data environment that requires parameters which can also define in the data environment.

    Although I would suggest you try not using the data environment in the future... its not scalable and consumes lots of memory (after all, your not using all of those tables/views at the same time)

  3. #3

    Thread Starter
    Member
    Join Date
    Apr 2005
    Posts
    38

    Re: Simple SQL on Data Environment / Report [help please]

    Quote Originally Posted by leinad31
    You can create a command object in the data environment that requires parameters which can also define in the data environment.

    Although I would suggest you try not using the data environment in the future... its not scalable ad consnumes lots of memory (after all, your not using all of those tables/views at the same time)
    Thanks for the reply, but I'm not really sure how to create a command object nor do I know anything about parameters. Is it possible you could give me a quick break-down that I could look up or work on? I'm new to VB, hence the confusion.

    Thanks

    I was told to use Data Environment, so didn't really have a choice. But, I'll look out next time.

  4. #4
    PowerPoster
    Join Date
    Nov 2002
    Location
    Manila
    Posts
    7,629

    Re: Simple SQL on Data Environment / Report [help please]

    Use it for now, but when you've become accustomed to using ADO (the reference not the data control) then you can move past the data environment.

    In the data enrironment's GUI/IDE window/tab simply right click the data environment object in the tree view, and select ADD COMMAND in the popup menu. Open the created command object to view its settings/properties window.

    You will then set the properties of the command object; 1) table/view name or your own SQL, 2) the parameters and their data types, 3) etc

  5. #5

    Thread Starter
    Member
    Join Date
    Apr 2005
    Posts
    38

    Re: Simple SQL on Data Environment / Report [help please]

    I got you, but one thing:

    Sorry if I sound like a dumbass, but how do I create parameters? I don't think I've ever used one - is there a sample parameter code that could guide me?

  6. #6

    Thread Starter
    Member
    Join Date
    Apr 2005
    Posts
    38

    Re: Simple SQL on Data Environment / Report [help please]

    I took your advice and scrapped the data environment and through a little searching on the net managed to find a straight-forward code to do the job. Many thanks for your help

    If anyone is interested, the code:
    VB Code:
    1. Private Sub Command1_Click()
    2. Call connectDB 'procedure defining connection, conn, recordset, etc.
    3. rs.Open "SELECT * FROM PRODUCTTABLE WHERE ProductID=' " & txtProduct & " ' "
    4. Set DataReport1.DataSource = rs
    5. DataReport1.Show
    6. End Sub

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