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!
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.
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#)
Set par = cmd.CreateParameter("Par2", adDate, adParamInput, , #10/1/2004#)
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
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
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.
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
Set cnn = New ADODB.Connection
cnn.ConnectionString = strConn
cnn.CursorLocation = adUseClient 'necessary to work with hflexgrids
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandText = strQry
Set par1 = cmd.CreateParameter("startID", adVarChar, adParamInput, 10, strStart)
Set par2 = cmd.CreateParameter("endID", adVarChar, adParamInput, 10, strEnd)
'get a recordset
Set rst = New ADODB.Recordset
'populate the flexgrid
Set fgr1.DataSource = rst
Set rst = Nothing
Set par1 = Nothing
Set par2 = Nothing
Set cmd = Nothing
Set cnn = Nothing
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!
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.