Jump to content
Sign in to follow this  

MSSQL Query issue

Recommended Posts



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

#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)
WinSetTitle("TempWindowName", "", $out&" Rows fetched")

While 1
   $msg = GUIGetMsg()
    Case $msg = $button3
    Case $msg = $Playb
          $aItem = _GUICtrlListView_GetItem ($list,0,3)
          $rc = _RunDos("C:\DOCUME~1\xlberezi\Desktop\search\MPlayer-1.0rc2\mplayer.exe " & $file)
      Case $msg = $Sbutton
          $SearchV = GUICtrlRead($input)

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 & "|"
        $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 & "|"
            $cmboVal = StringTrimRight($cmboVal, 1) & @CR
            GUICtrlCreateListViewItem($cmboVal, $list)
            $cmboVal = ""
   Return $count

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  


Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.