I need help with SQL

[KenNichols]

This query works fine

$Item = InputBox("Input Item Number","")
$sqlCon = ObjCreate("ADODB.Connection")
        $sqlCon.Open("Driver={SQL Server};SERVER=Test;DATABASE=Test;UID=;PWD=;")
        $query = "SELECT Item_ID,Item_Desc1 FROM Item Where Item_ID = '" & $Item & "'"
        $rs = $sqlCon.EXECUTE($query)
        If $rs.EOF Then
            MsgBox(48, "Error: ID number not found!", "Error: ID number " & $Item & " Does not exist!")
            $sqlCon.Close
        Else
            $QItem = $rs("Item_ID").Value
            $QDesc1 = $rs("Item_Desc1").Value
            $sqlCon.close
            MsgBox(0,"",$QItem & " " & $QDesc1)
        EndIf

This query gives me an error

$WHID = InputBox("Input Warehouse Number","")
$sqlCon = ObjCreate("ADODB.Connection")
        $sqlCon.Open("Driver={SQL Server};SERVER=Test;DATABASE=Test;UID=;PWD=;")
        $query = "SELECT Item_ID,Item_Desc1,WH_ID,ItemWH_OnHand FROM Item JOIN ItemWH ON Item.Item_ID=ItemWH.Item_ID Where WH_ID = '" & $WHID & "'"
        $rs = $sqlCon.EXECUTE($query)
        If $rs.EOF Then
            MsgBox(48, "Error: ID number not found!", "Error: ID number " & $WHID & " Does not exist!")
            $sqlCon.Close
        Else
            $QItem = $rs("Item_ID").Value
            $QDesc1 = $rs("Item_Desc1").Value
            $QItemOnHand = $rs("ItemWH_OnHand").Value
            $QWHID = $rs("WH_ID").Value
            $sqlCon.close
            MsgBox(0,"",$QItem & " " & $QDesc1 & " " & $QItemOnHand & " " & $QWHID)
        EndIf

What am I missing?

Edited June 23, 2009 by KenNichols

[spudw2k]

just a shot in the dark here....you are trying to select WH_ID, but do not call WH in your FROM clause. Do you mean ItemWH_ID perhaps?

Edited June 23, 2009 by spudw2k

[KenNichols]

I got this to work but I am SURE there is a better way!

I modified cdkid's code to get this far.

expandcollapse popup

#Include <Array.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#Include <GuiListView.au3>
$FrmReport = GUICreate("Inventory Report", 793, 568, -1, -1)
$ListHeaders = "Part Number|Part Description|Qty OnHand"
$ListViewReport = GUICtrlCreateListView($ListHeaders, 8, 8, 770, 476)

;_GUICtrlListView_SetColumnWidth($hWnd, $iCol, $iWidth)
_GUICtrlListView_SetColumnWidth($ListViewReport, 0, 125)
_GUICtrlListView_SetColumnWidth($ListViewReport, 1, 200)
_GUICtrlListView_SetColumnWidth($ListViewReport, 2, 75)
$WHID = InputBox("Input Warehouse Number","Input Warehouse Number", "3009")
WinSetTitle ( $FrmReport, "", "Inventory Report for " & $WHID )
$sqlCon = ObjCreate("ADODB.Connection")
        $sqlCon.Open("Driver={SQL Server};SERVER=CAPA;DATABASE=nextgen;UID=;PWD=;")
;==================================================Part Number
        Dim $PartNumber[1]
        $query = "SELECT ItemWH.Item_ID FROM Item JOIN ItemWH ON Item.Item_ID=ItemWH.Item_ID Where WH_ID = '" & $WHID & "'"
        $rs = $sqlCon.EXECUTE($query)
        With $rs
            While Not .EOF
                ReDim $PartNumber[UBound($PartNumber, 1) + 1]
                $PartNumber[UBound($PartNumber, 1) - 1] = .Fields (0).value
                .MoveNext
            WEnd
        EndWith
        $PartNumber[0] = UBound($PartNumber, 1) - 1
        ConsoleWrite($PartNumber[1] & @CRLF)
        $ArrayString = _ArrayToString($PartNumber)
        ConsoleWrite($ArrayString & @CRLF)
        $Split = StringSplit($ArrayString,"|")
    For $i = 2 To UBound($Split) -1
        _GUICtrlListView_AddItem($ListViewReport, $Split[$i])
    Next
    ;==================================================Part Desc
    Dim $PartDesc[1]
    $query = "SELECT Item_Desc1 FROM Item JOIN ItemWH ON Item.Item_ID=ItemWH.Item_ID Where WH_ID = '" & $WHID & "'"
        $rs = $sqlCon.EXECUTE($query)
        With $rs
            While Not .EOF
                ReDim $PartDesc[UBound($PartDesc, 1) + 1]
                $PartDesc[UBound($PartDesc, 1) - 1] = .Fields (0).value
                .MoveNext
            WEnd
        EndWith
        $PartDesc[0] = UBound($PartDesc, 1) - 1
        $ArrayString = _ArrayToString($PartDesc)
        ConsoleWrite($ArrayString & @CRLF)
        $Split = StringSplit($ArrayString,"|")
    For $i = 2 To UBound($Split) -1
        _GUICtrlListView_AddSubItem($ListViewReport, $i-2, $Split[$i], 1)
    Next
;==================================================Qty OnHand
    Dim $OnHand[1]
    $query = "SELECT ItemWH_OnHand FROM Item JOIN ItemWH ON Item.Item_ID=ItemWH.Item_ID Where WH_ID = '" & $WHID & "'"
        $rs = $sqlCon.EXECUTE($query)
        With $rs
            While Not .EOF
                ReDim $OnHand[UBound($OnHand, 1) + 1]
                $OnHand[UBound($OnHand, 1) - 1] = .Fields (0).value
                .MoveNext
            WEnd
        EndWith
        $OnHand[0] = UBound($OnHand, 1) - 1
        $ArrayString = _ArrayToString($OnHand)
        ConsoleWrite($ArrayString & @CRLF)
        $Split = StringSplit($ArrayString,"|")
    For $i = 2 To UBound($Split) -1
        _GUICtrlListView_AddSubItem($ListViewReport, $i-2, $Split[$i], 2)
    Next
;=================================================Close
    $sqlCon.close
GUISetState()


While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit

    EndSwitch
WEnd

Edited June 24, 2009 by KenNichols