MSSQL Query issue

[devilyn]

Hi

i have a script that basically search an mssql DB

my problem is that the value i enter in the input field is missing from my query

please help

thanks in advance lior

expandcollapse popup

#include <GUIConstants.au3>
#include <GuiListView.au3>
#include <Process.au3>
#Include <String.au3>

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

$file = "\\blabla\san\Adic\blabla\open.m2v"

Dim $header = ""
Dim $quantity = 0
Dim $dsncount = 1
Dim $button3
Dim $Playb
Dim $list
Dim $Sbutton
Dim $input
Dim $SearchV 

; Use ONE of the next two query statements depending on whether or not you are using a text file
Dim $query = "select titles.title_id Title_ID,title Title_Name, classes.full_name Category, path+'\'+soundfiles.soundfile_name Proxy_File_Name from titles, soundfiles, titles_in_class, classes, soundfile_units where title = '"&$SearchV&"' and titles_in_class.title_id = titles.title_id and titles.soundfile_id = soundfiles.soundfile_id and titles.title_type_id = '13' and titles_in_class.class_id = classes.class_id and soundfiles.soundfile_unit_id = soundfile_units.soundfile_unit_id "; for all BUT text connections
;Dim  = "select Name from hosts order by Name "; for all BUT text connections
;Dim $query = "select * from filename.txt"; for text connections

; Simply un-remark ONE of the connectors below that you will be using
; Not all connectors require the UID and Pwd commands. Try without them first.
Dim $DSN = "Driver={SQL Server};Server=SQL1;Database=DB;Uid=sa;Pwd=BLabla;"; SQL connector
;Dim $DSN = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=drive:/folder;Extensions=asc,csv,tab,txt;Persist Security Info=False"; TXT file connector
;Dim $DSN = "Driver={Microsoft Access Driver (*.mdb)};Dbq=drive:/filename.mdb;Uid=UserID;Pwd=Password;"; Access file connector
;Dim $DSN = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=drive:/filename.xls;DefaultDir=drive:/folder;"; Excel file connector
;Dim $DSN = "Driver={Microsoft ODBC for Oracle};Server=TNSnames_ora;Uid=demo;Pwd=demo;"; Oracle connector

$out = getData($DSN)
ToolTip("")
WinSetTitle("TempWindowName", "", $out&" Rows fetched")

While 1
   $msg = GUIGetMsg()
   Select
    Case $msg = $button3
        ExitLoop
    Case $msg = $Playb
          $aItem = _GUICtrlListView_GetItem ($list,0,3)
         ;MsgBox(0,"",$aItem[3],-1)
          $rc = _RunDos("C:\DOCUME~1\xlberezi\Desktop\search\MPlayer-1.0rc2\mplayer.exe " & $file)
      Case $msg = $Sbutton
          $SearchV = GUICtrlRead($input)
          MsgBox(0,"",$SearchV,-1)
          MsgBox(0,"",$query,-1)
          getData($DSN)
   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("TempWindowName", 420, 300)
   $button3 = GUICtrlCreateButton("Quit", 80, 275, 70, 20)
   $Sbutton= GUICtrlCreateButton("GO", 320, 25, 40, 20)
   $Playb = GUICtrlCreateButton("Play Proxy", 250, 275, 70, 20)
   $label = GUICtrlCreateLabel("Search:",90, 25, 40, 20)
   $input = GUICtrlCreateInput("",140, 25, 170, 20)
   With $adoRs
     
   ; Get information about Fields collection
      For $n = 0 To .Fields.Count - 1
         $header = $header & .Fields ($n).Name & "|"
      Next
     
        $list = GUICtrlCreateListView(StringTrimRight($header, 1), 10, 70, 400, 200, BitOR($LVS_SHOWSELALWAYS, $LVS_EDITLABELS), BitOR($LVS_EX_GRIDLINES, $LVS_EX_HEADERDRAGDROP, $LVS_EX_FULLROWSELECT, $LVS_EX_REGIONAL))
      
     
      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, $list)
            $cmboVal = ""
            .MoveNext
         WEnd
         
         GUISetState()
         
      EndIf
     
     
   EndWith
   
   $adoCon.Close
   Return $count
EndFunc;==>getData