Need String help

[Teldin]

How can I make the following a proper Autoit string?

SELECT "Order"."Setup Date", "Order"."No_", "Order"."Order Date", "Order"."Location Code", "Order"."Document Type"

FROM "DB1"."dbo"."Order" "Order"

WHERE "Order"."Document Type"=2 AND ("Order"."Order Date">={ts '2007-12-13 00:00:00'} AND "Order"."Order Date"<{ts '2007-12-14 00:00:00'}) AND "Order"."Setup Date">={ts '2007-12-14 00:00:00'} AND "Order"."Location Code"='SF'

It has to keep the exact same format with the single and double quotes or else it does not work.

Here is the code I'm trying to use it with: (credits go to scriptkitty and others)

expandcollapse popup

$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")

#include <GUIConstants.au3>

opt("TrayIconDebug", 1)       ;0=no info, 1=debug line info
opt("ExpandEnvStrings", 1)    ;0=don't expand, 1=do expand
opt("ExpandVarStrings", 1)    ;0=don't expand, 1=do expand

Dim $ueberschriften = ""
Dim $anzahl = 0
Dim $dsncount = 1
Dim $DSN = "DB1"
Dim $button3

$test = "SELECT NMR_Rental_Header.Setup Date, NMR_Rental_Header.No_, NMR_Rental_Header.Order Date, NMR_Rental_Header.Location Code, NMR_Rental_Header.Document Type FROM NMRDB40.dbo.NMR$Rental Header WHERE NMR_Rental_Header.Document Type=2 AND (NMR_Rental_Header.Order Date>={ts 2007-12-13 00:00:00} AND NMR_Rental_Header.Order Date<{ts 2007-12-14 00:00:00}) AND NMR_Rental_Header.Setup Date>={ts 2007-12-14 00:00:00} AND NMR_Rental_Header.Location Code=SF"

GUICreate("Enter your sql-query", 420, 250)
$edit1 = GUICtrlCreateEdit($test, 10, 10, 400, 200)
$button2 = GUICtrlCreateButton("OK", 180, 220, 70, 20)
$query = GUICtrlRead($edit1)
GUISetState()

While 1
   $msg = GUIGetMsg()
   Select
      Case $msg = $button2
         $query = GUICtrlRead($edit1)
         ExitLoop
   EndSelect
WEnd

$out = getData($DSN)
ToolTip("")
MsgBox(0, "Rows fetched", $out)

While 1
   $msg = GUIGetMsg()
   Select
      Case $msg = $button3
         ExitLoop
   EndSelect
WEnd

Func getData($DSN)
  ; some things are a bit harder to understand in this part
  ; .Fields("address").Value is the Value of the Field named "address"
  ; access database would be the same, but you should call out the full table as well
  ; like .Fields("Tablename.Fieldname").Value
  ; and EOF is universal for End Of File
   $cmboVal = ""
   $adoCon = ObjCreate ("ADODB.Connection")
   $adoCon.Open ($DSN)
   $adoRs = ObjCreate ("ADODB.Recordset")
   
   $adoSQL = $query
   
   
   $adoRs.CursorType = 2
   $adoRs.LockType = 3
   $adoRs.Open ($adoSql, $adoCon)
   
   GUICreate("Results", 420, 250)
   $button3 = GUICtrlCreateButton("OK", 180, 220, 70, 20)
   
   With $adoRs
      
     ; Get information about Fields collection
      For $n = 0 To .Fields.Count - 1
         $ueberschriften = $ueberschriften & .Fields ($n).Name & "|"
      Next
      
      $liste = GUICtrlCreateListView(StringTrimRight($ueberschriften, 1), 10, 10, 400, 200, $LVS_REPORT, $LVS_EX_GRIDLINES)
      
      
      If .RecordCount Then
         $count = 0
         While Not .EOF
            $count = $count + 1
            ToolTip("record search #" & $count, 0, 0)
            For $colum = 0 To .Fields.Count - 1
               $cmboVal = $cmboVal & "" & .Fields ($colum).Value & "|"
            Next
            $cmboVal = StringTrimRight($cmboVal, 1) & @CR
            
            GUICtrlCreateListViewItem($cmboVal, $liste)
            $cmboVal = ""
            .MoveNext
         WEnd
         
         GUISetState()
         
      EndIf
      
      
   EndWith
   
   $adoCon.Close
   Return $count
EndFunc  ;==>getData



; AT THE END OF SCRIPT
Func MyErrFunc()
  $HexNumber=hex($oMyError.number,8)
  Msgbox(0,"AutoItCOM Test","We intercepted a COM Error !"       & @CRLF  & @CRLF & _
             "err.description is: "    & @TAB & $oMyError.description    & @CRLF & _
             "err.windescription:"     & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "         & @TAB & $HexNumber              & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "     & @TAB & $oMyError.scriptline     & @CRLF & _
             "err.source is: "         & @TAB & $oMyError.source         & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile       & @CRLF & _
             "err.helpcontext is: "    & @TAB & $oMyError.helpcontext _
            )
  SetError(1)  ; to check for after this function returns
Endfunc

P.S. anyone know what ueberschriften and anzahl mean?

[Skruge]

How can I make the following a proper Autoit string?

SELECT "Order"."Setup Date", "Order"."No_", "Order"."Order Date", "Order"."Location Code", "Order"."Document Type"

FROM "DB1"."dbo"."Order" "Order"

WHERE "Order"."Document Type"=2 AND ("Order"."Order Date">={ts '2007-12-13 00:00:00'} AND "Order"."Order Date"<{ts '2007-12-14 00:00:00'}) AND "Order"."Setup Date">={ts '2007-12-14 00:00:00'} AND "Order"."Location Code"='SF'

...

P.S. anyone know what ueberschriften and anzahl mean?

You need double-double or double-single quotes, depending on the start/end quotes.

You could also replace doublequotes with Chr(34) but I find that it makes the lines less legible.

$test = "SELECT ""Order"".""Setup Date"", ""Order"".""No_"", ""Order"".""Order Date"", ""
Order"".""Location Code"", ""Order"".""Document Type""
FROM ""DB1"".""dbo"".""Order"" ""Order""
WHERE ""Order"".""Document Type""=2 AND (""Order"".""Order Date"">={ts '2007-12-
13 00:00:00'} AND ""Order"".""Order Date""<{ts '2007-12-14 00:00:00'}) AND ""Ord
er"".""Setup Date"">={ts '2007-12-14 00:00:00'} AND ""Order"".""Location Code""=
'SF'"

Ueberschriften = Headings

Anzahl = Number

[Teldin]

That did the trick. Thanks!!