2 Replies Latest reply on Sep 11, 2007 10:49 AM by Stevan2002

    Command Object Source

    Stevan2002
      I have recently moved from Dreamweaver MX (2003) to CS3. In MX, a recordset object was used to query the database. To troubleshoot a query,

      Dim SelectedPrimary
      Dim SelectedPrimary_numRows
      Set SelectedPrimary = Server.CreateObject("ADODB.Recordset")
      SelectedPrimary.ActiveConnection = MM_connClusterDB_STRING
      SelectedPrimary.Source = "SELECT O.OfficeID, O.ShortOffice FROM tbl_Offices O WHERE OfficeID = '" + Replace(SelectedPrimary__OffID, "'", "''") + "' ORDER BY OfficeName"
      SelectedPrimary.CursorType = 0
      SelectedPrimary.CursorLocation = 2
      SelectedPrimary.LockType = 1
      SelectedPrimary.Open()SelectedPrimary_numRows = 0

      before "SelectedPrimary.Open()" I would add...

      Response.Write SelectedPrimary.Source
      Response.End()

      run the page and copy the code ( SELECT O.OfficeID, O.ShortOffice FROM tbl_Offices O WHERE OfficeID = 7 ) into Query Analyzer

      Is there a similar function to write out the code to a page for troubleshooting a Command Object query that is used in Dreamweaver CS3?

      Other than repeatedly filling out the 4 items (Name, Type, Value and Default value) over and over,
      how do you make the Command Object to use the same variable, several times in one query?


      Dreamweaver CS3 | ASP | SQL SERVER

      -- Steve

        • 1. Re: Command Object Source
          Level 7
          Stevan2002 wrote:
          > before "SelectedPrimary.Open()" I would add...
          >
          > Response.Write SelectedPrimary.Source
          > Response.End()
          >
          > run the page and copy the code ( SELECT O.OfficeID, O.ShortOffice FROM
          > tbl_Offices O WHERE OfficeID = 7
          ) into Query Analyzer
          >
          > Is there a similar function to write out the code to a page for
          > troubleshooting a Command Object query that is used in Dreamweaver CS3?

          Unfortunately as far as I'm aware, with the command object and the parameters it uses you can't get the actual SQL that is submitted to the database, you can only get the SQL with the ? tokens where the values are supposed to be inserted. However, if you want to try to recreate the generated SQL, try something like the following. Look for this line of code:
          Set Recordset1 = Recordset1_cmd.Execute

          Just above it add the following (substitute your recordset name for Recordset1 below):
          ' Should work for Access and SQL Server, other DBs are questionable
          Dim cmd : Set cmd = Recordset1_cmd
          Dim cmdSQL: cmdSQL = cmd.CommandText
          Dim cmdParam

          for each param in cmd.Parameters
          val = param.Value
          If(param.Type = 200) Then ' If text
          val = """" & val & """"
          End If
          If(param.Type = 135) Then ' If date
          val = "'" & val & "'"
          End If

          cmdSQL = Replace(cmdSQL, "?", val, 1, 1)
          next
          Response.Write(cmdSQL &"<br />")


          Note this likely will only work with Dreamweaver parameters created within the Recordset dialog.


          > Other than repeatedly filling out the 4 items (Name, Type, Value and Default
          > value) over and over,
          > how do you make the Command Object to use the same variable, several times in
          > one query?

          Within the Dreamweaver UI, that's the way you do it. If you want to do it in the code, you can copy and paste the parameter code above the recordset, change the name slightly, like appending 2, 3, then 4. Then you'd need to add an extra Parameters and update the "param1" to 2, 3, and 4.


          --
          Danilo Celic
          | Extending Knowledge Daily : http://CommunityMX.com/
          | Adobe Community Expert
          • 2. Re: Command Object Source
            Stevan2002 Level 1
            Danilo,

            Thanks for the code. It works great.

            -- Steve