Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2004
    Posts
    8

    Unanswered: Pass Variable In Dataenvironment Sql Child Cmd For Mshflexgrid

    I am using VB 6.0 and an Access 2002 database.

    I have a form that has an MSHFLEXGRID (2 bands to support hierarchical parent-child relationship) and 2 DTpicker fields. On the data side, there are two dataenvironment1 command entries. One is for the parent table, table "A" ("customer"), which populates band 0 of the grid and includes aggregates for calculated totals from table "B", the child table. A child command was added to Table "A" in dataenvironment1 for Table "B" ("custSched"), which populates (subordinate) band 1 of the grid.

    Works fine with all rows, but I need to limit retrieval for table "B" to rows that have date values within the range the user enters in the 2 DTpicker fields.

    How do I change the DE child command entry to an sql statement that will accept the 2 date values as variables? I managed to come up with an sql statement that has a hard-coded date range, but need to take it a step further so that the user's date values are referenced instead.

    What I have now is: Select * from CustSched Where FeedPlanFrDate Between #09/01/2004# and #09/05/2004#

    I've researched parameter queries which may be my answer but don't know how to use them. Any help or potential alternative is greatly appreciated!

  2. #2
    Join Date
    Sep 2004
    Posts
    15
    change the query to something like this:

    WHERE FeedPlanFrDate BETWEEN [StartDate] AND [EndDate]

    then do this to the appropriate command object:

    Dim cmd As New ADODB.Command
    Dim par As New ADODB.Parameter

    Set par = cmd.CreateParameter("StartDate", adDate, adParamInput, , #9/1/2004#)

    cmd.Parameters.Append par

    'use this to check if u like:
    Debug.Print cmd.Parameters.Count
    Debug.Print cmd.Parameters(0).Value

    hope this helps

    Kuma

  3. #3
    Join Date
    Sep 2004
    Posts
    15
    Ummm:

    two things:

    if the query wont work like this:

    do it with questionmarks: ...BETWEEN ? AND ?

    replace the date with a variable here of course
    Dim datStart As Date
    datStart = #9/1/2004# 'fill in the value of ur datepicker thingy here

    Set par = cmd.CreateParameter("StartDate", adDate, adParamInput, , datStart)

    just got up...still a bit slow...

    Kuma

  4. #4
    Join Date
    Sep 2004
    Posts
    8
    First, many thanks for the very prompt reply...much appreciated! I followed the steps as best I could but was unsure about some of them, primarily because I've used the Data Environment Designer for data specifics and am not sure how certain elements relate to your instructions.

    Using the DE Designer, I modified the underlying sql associated with the CustSched (child) command object per suggestion 1 (var name in brackets) via the properties window. Upon doing that, I had to specify parameter properties on the parameters screen, and change the required field from true to false to eliminate an error message - "all required parameters must be linked with a parent field". After that, I got a bit lost. My problem is I'm not sure how my existing command object, Dataenvironment1.cust, relates to the new command object referenced in your reply. Can you elaborate? In the meantime, I will pursue as best I can.

    Regarding the set par = statement...can you tell me how to modify it for two parameters vs. one?

    Again, many thanks for your help and patience with my fragmented knowledge of VB.

  5. #5
    Join Date
    Sep 2004
    Posts
    15
    if you have a command object already, then u dont have to do the dim cmd as new.... part.

    simply use the name of the command u already have.

    u can add many parameters. simply go on with the par thing


    Set par = cmd.CreateParameter("Par1", adDate, adParamInput, , #9/1/2004#)

    cmd.Parameters.Append par

    Set par = cmd.CreateParameter("Par2", adDate, adParamInput, , #10/1/2004#)

    cmd.Parameters.Append par


    and so on.

    I'm not so sure about those DataEnvironment things, because I never work with them. I tried once but didnt like the way they behave. Somehow less control over what happens with more fuss creating what u want.

    Instead i simply get a connection like this:

    dim cnn as adodb.connection

    set cnn = new adodb.connection

    cnn.connectionstring = strYourConnectionString
    (to get connectionstrings, see the examples on microsoft.com or
    www.connectionstrings.com, or use ur DataEnvironment and do a
    debug.print DataEnvironment1.Connection1.ConnectionString)


    Then I use a cmd object like that:

    dim cmd as adodb.command

    set cmd = new adodb.command

    set cmd.activeconnection = cnn
    cmd.commandtext = strYourSQLQuery

    add parameters if needed.

    get a recordset like this

    dim rst as adodb.recordset

    ...

    set rst = cmd.execute

    hope that sheds some light on the whole thing.

    Kuma

  6. #6
    Join Date
    Sep 2004
    Posts
    15
    Since I wait for an answer atm myself and have nothing better to do...

    Here a little example to retrieve some data from NWind DB.

    Throw on HFlexgrid, two textboxes and one button on a form,
    make sure u have a reference to MS ActiveX Data Objects 2.8 (or 2.5, 2.6, 2.7) and play around.

    Code:
    Private Sub btnPop_Click()
    
    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim par1 As ADODB.Parameter
    Dim par2 As ADODB.Parameter
    Dim rst As ADODB.Recordset
    
    Dim strConn As String
    Dim strQry As String
    Dim strStart As String
    Dim strEnd As String
    
    
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\NWind\Nwind2002.mdb;User Id=admin;Password=;"
    strQry = "SELECT * FROM Customers WHERE CustomerID BETWEEN [startID] AND [endId];"
    
    strStart = Me.txtStart.Text
    strEnd = Me.txtEnd.Text
    
    'open connection
    Set cnn = New ADODB.Connection
    cnn.ConnectionString = strConn
    cnn.CursorLocation = adUseClient 'necessary to work with hflexgrids
    cnn.Open
    
    'prepare command
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = cnn
    cmd.CommandText = strQry
    
    'add parameters
    Set par1 = cmd.CreateParameter("startID", adVarChar, adParamInput, 10, strStart)
    cmd.Parameters.Append par1
    Set par2 = cmd.CreateParameter("endID", adVarChar, adParamInput, 10, strEnd)
    cmd.Parameters.Append par2
    
    'get a recordset
    Set rst = New ADODB.Recordset
    rst.Open cmd
    
    'populate the flexgrid
    Set fgr1.DataSource = rst
    
    'clean up
    Set rst = Nothing
    Set par1 = Nothing
    Set par2 = Nothing
    Set cmd = Nothing
    cnn.Close
    Set cnn = Nothing
    
    End Sub
    hth

    Kuma

  7. #7
    Join Date
    Sep 2004
    Posts
    8
    WOW...thanks for all the detailed information, expecially the example! I agree with you that some control is lost when using the DataEnvironment and I have been gradually moving away from it, but it's a great way to get started with limited coding expertise. In this particular case, I thought it could help with defining the shape of the multi-band hierarchical grid, an object which I've never used before. Anyway, I'll try your suggestions and will let you know how it goes. Thanks again!

  8. #8
    Join Date
    Sep 2004
    Posts
    8
    Hello again. I reviewed the info you provided me and tried an example using NWind db as you suggested...worked like a charm! However, I ran into a problem when using date values for parameters. I tried a test using the Employees table in NWind, with sql select where clause ..."Where HireDate Between [startDT] AND [endDT];"
    I changed adVarChar in the par statements to adDate (also tried adDBDate). The parameters are set to DTpicker1.value and DTpicker2.value which are inputted on my form. Every range I entered resulted in 0 records returned. I also tried hardcoding the date values in the par statements, for ex, #01/01/2002# AND #01/01/2004#, to no avail. In any case, I suspect I just have to play around with the format until I get it right. If you have any suggestions, they're more than welcome. In the meantime, I will browse through other entries in the forum along with some other reference material as I'm sure this has been encountered by others in the past. I just wanted to let you know my results and to once again extend my thanks for all your help.

Posting Permissions

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