Script not working (Problems solved! Thanks.)

[LordBoling]

Hello everyone! Hope you are having great days.

I unfortunately am in need of assistance.

I am in the process of creating a program that will allow me to search 2 systems that my company uses that store customer info.

At this time if a customer places an order it could be in our online only system, our in-store system, or both at the same time. So instead of searching one manually, then the other, I decided to code a script that would look in both at the same time until it find the order. It has a few issues though.

Issue #1 (Solved, Thank you BrewManNH!): I have two GUIs that I created, but apparently created wrong as the first one never closes until the script ends completely or if they press the red X, but is supposed to close when the user presses okay, but not end the script. This GUI is called in my code by a function called RefGui().

Issue #2 (Solved): This same GUI is supposed to return a reference id entered by the user in an input field and what type of reference number it is by selecting a radio button. However, when used like it was intended it only returns the data from the input field. If nothing is put in the input field then it will return both the data in the input field and which radio selection is made.

I have other issues but will probably deal with them once I have those figured out. If someone could take a look at my code and give me some ideas I would appreciate it.

expandcollapse popup

#include "CSV.au3"
#include "Array.au3"
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <EditConstants.au3>
#include <StaticConstants.au3>
#include <ButtonConstants.au3>
Opt("WinWaitDelay",100)
Opt("WinDetectHiddenText",1)
Opt("MouseCoordMode",0)
Opt("SendCapslockMode",1)

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


;Open Connection to OsCommerce database
$oConn = ObjCreate("ADODB.Connection")
$oConn.Open(;Connection string to my first database)

;Open Connection to Mas90 database
$conn = ObjCreate("ADODB.Connection")
$DSN = ;Connection string for my second database
$conn.Open($DSN)

;Define var for reference type as global w/ initial value/Moved per BrewManNH's suggestion
Global $refType="Internet"
;Loop until user closes first GUI w/ close button
While 1
;~ ;Define var for reference type as global w/ initial value
;~ Global $refType="Internet"
;Ask for order number or invoice number: (This opens first gui)
$refNum=RefGui()
;Start text var to display message.
$OrderMsg=$refNum & @CRLF & $refType
;If user selected internet search OsCommerce. Otherwise this won't be necessarily.
If $refType="Internet" Then
;Query status and comments
$rs = $oConn.Execute("SELECT orders.orders_status,orders_status_history.comments FROM orders,orders_status_history WHERE orders.orders_id='"& $refNum &"' AND orders_status_history.orders_status_id=orders.orders_status AND orders_status_history.orders_id=orders.orders_id")
;Make sure a result was returned then get results
If IsObj($rs) And $rs.EOF=False Then
  $osStatus = $rs.GetRows()

  $rs = $oConn.Execute("SELECT orders_status_name FROM orders_status WHERE orders_status_id ='"& $osStatus[UBound($osStatus)-1][0] &"'")
  $temp=$rs.GetRows()
  $osStatus[UBound($osStatus)-1][0]=$temp[0][0]

  $OrderMsg+="OsCommerce:" & @CRLF
  $OrderMsg+=@TAB & "Status:" & $osStatus[UBound($osStatus)-1][0] & @CRLF
  $OrderMsg+=@TAB & "Comments:" & $osStatus[UBound($osStatus)-1][1] & @CRLF

Else
  $osStatus = 0
EndIf
EndIf
;If internet or sales were chosen then check if it has been put in Mas90 yet.
If $refType="Internet" Or $refType="Sales" Then
$rs = $conn.Execute("SELECT OrderType FROM SO_SalesOrderHeader WHERE (SalesOrderNo='"& $refNum &"')")
If IsObj($rs) And $rs.EOF=False Then
  $SOStatus = $rs.GetRows()

  $OrderMsg+="Sales Order:" & @CRLF
  $OrderMsg+=@TAB & "Status:" & $SOStatus[0][0] & @CRLF

  $rs = $conn.Execute("SELECT ItemCode,ItemCodeDesc,QuantityOrdered,QuantityBackordered FROM SO_SalesOrderDetail WHERE (SalesOrderNo='"& $refNum &"' AND ItemType='1')")
  If IsObj($rs) And $rs.EOF=False Then
   $SOItems = $rs.GetRows()
  
   $OrderMsg+=@TAB & "Items:" & @CRLF
  
   $i=0
   While $i<UBound($SOItems)-1
    $OrderMsg+=@TAB & @TAB & "(" & $SOItems[$i][0] & ") " & $SOItems[$i][1] & @CRLF
  
    $rs = $conn.Execute("SELECT TotalQuantityOnHand FROM CI_Item WHERE (ItemCode='"& $SOItems[$i][0] &"')")
    If IsObj($rs) And $rs.EOF=False Then
     $item = $rs.GetRows()
    
     $OrderMsg+=@TAB & @TAB & "Ordered/Available: " & $SOItems[$i][2] & "/" & $item[0][0] & @TAB & "Backordered: " & $SOItems[$i][3] & @CRLF
    EndIf
    $i+=1
   WEnd

  Else
   $SOItems = 0
  EndIf
Else
  $SOStatus = 0
EndIf

$rs = $conn.Execute("SELECT InvoiceNo, SalesOrderNo FROM SO_InvoiceHeader WHERE (SalesOrderNo='"& $refNum &"')")
If IsObj($rs) And $rs.EOF=False Then
  $InvStatus = $rs.GetRows()

  $rs = $conn.Execute("SELECT InvoiceNo,ItemCode,ItemCodeDesc,QuantityOrdered,QuantityBackordered FROM SO_InvoiceDetail WHERE (InvoiceNo='"& $InvStatus[0][0] &"' AND ItemType=1)")
  If IsObj($rs) And $rs.EOF=False Then
   $InvItems = $rs.GetRows()
  Else
   $InvItems = 0
  EndIf
Else
  $InvStatus = 0
EndIf

$rs = $conn.Execute("SELECT InvoiceNo, SalesOrderNo FROM AR_InvoiceHistoryHeader WHERE (SalesOrderNo='"& $refNum &"')")
If IsObj($rs) And $rs.EOF=False Then
  $InvHst = $rs.GetRows()
  $rs = $conn.Execute("SELECT InvoiceNo,ItemCode,ItemCodeDesc,QuantityOrdered,QuantityBackordered FROM AR_InvoiceHistoryDetail WHERE (InvoiceNo='"& $InvHst[0][0] &"' AND ItemType=1)")
  If IsObj($rs) And $rs.EOF=False Then
   $InvItems = $rs.GetRows()
  Else
   $InvItems = 0
  EndIf
Else
  $InvHst = 0
EndIf

ElseIf $refType="Invoice" Then
$rs = $conn.Execute("SELECT InvoiceNo, SalesOrderNo FROM AR_InvoiceHistoryHeader WHERE (InvoiceNo='"& $refNum &"')")
If IsObj($rs) And $rs.EOF=False Then
  $InvHst = $rs.GetRows()
  $rs = $conn.Execute("SELECT InvoiceNo,ItemCode,ItemCodeDesc,QuantityOrdered,QuantityBackordered FROM AR_InvoiceHistoryDetail WHERE (InvoiceNo='"& $refNum &"' AND ItemType=1)")
  If IsObj($rs) And $rs.EOF=False Then
   $InvItems = $rs.GetRows()
  Else
   $InvItems = 0
  EndIf
Else
  $InvHst = 0
EndIf
EndIf
Gui($OrderMsg)

WEnd

;This is the second GUI that displays the results
Func Gui($txt)
    Local $myedit, $msg
    $hGUI=GUICreate("Details:", 500, 300, Default, Default,$WS_OVERLAPPEDWINDOW) ; will create a dialog box that when displayed is centered
$GUISize=WinGetClientSize($hGUI)
$myedit = GUICtrlCreateEdit($txt,-1, -1, $GUISize[0], $GUISize[1],$ES_READONLY)
GUICtrlSetResizing ( $myedit, $GUI_DOCKBORDERS )
    GUISetState()
DllCall("user32.dll","int","HideCaret","int",0)

Send("{END}")
    ; Run the GUI until the dialog is closed
    While 1
        $msg = GUIGetMsg()
        If $msg = $GUI_EVENT_CLOSE Then ExitLoop
    WEnd
    GUIDelete($hGUI)
EndFunc

;This is the first GUI. It asks for the information required to look up the order
Func RefGui()
$RefGui = GUICreate("Reference#", 261, 156, -1, -1)
GUISetFont(10, 400, 0, "Verdana")
GUICtrlCreateLabel("Enter Reference:", 15, 12, 116, 20)
$RefNum = GUICtrlCreateInput("REFERENCE #", 136, 8, 110, 24, BitOR($GUI_SS_DEFAULT_INPUT,$ES_UPPERCASE))
$OK = GUICtrlCreateButton("OK", 176, 120, 75, 25, BitOR($BS_DEFPUSHBUTTON,$BS_NOTIFY))
GUICtrlCreateGroup("Select Ref Type:", 16, 40, 145, 105, BitOR($GUI_SS_DEFAULT_GROUP,$WS_CLIPSIBLINGS))
$Internet = GUICtrlCreateRadio("Internet Order", 24, 64, 113, 17)
GUICtrlSetState(-1, $GUI_CHECKED)
$Sales = GUICtrlCreateRadio("Sales Order", 24, 88, 113, 17)
$Invoice = GUICtrlCreateRadio("Invoice", 24, 112, 113, 17)
GUICtrlCreateGroup("", -99, -99, 1, 1)
GUISetState(@SW_SHOW)
While 1
  $nMsg = GUIGetMsg()
  If $nMsg = $GUI_EVENT_CLOSE Then Exit

  If $nMsg = $OK Then
   ExitLoop
  EndIf
WEnd
If GUICtrlRead($Internet) = 1 Then
  $refType="Internet"
ElseIf GUICtrlRead($Sales) = 1 Then
  $refType="Sales"
ElseIf GUICtrlRead($Invoice) = 1 Then
  $refType="Invoice"
EndIf

$temp=GUICtrlRead($RefNum)
GUIDelete($RefGui)
Return $temp
EndFunc

Func MyErrFunc()
$hexnum=hex($objErr.number,8)
Msgbox(0,"","We intercepted a COM Error!!"    & @CRLF               & @CRLF & _
             "err.description is: " & $objErr.description   & @CRLF & _
             "err.windescription is: " & $objErr.windescription & @CRLF & _
             "err.lastdllerror is: "   & $objErr.lastdllerror   & @CRLF & _
             "err.scriptline is: "   & $objErr.scriptline   & @CRLF & _
             "err.number is: "     & $hexnum               & @CRLF & _
             "err.source is: "     & $objErr.source     & @CRLF & _
             "err.helpfile is: "       & $objErr.helpfile     & @CRLF & _
             "err.helpcontext is: " & $objErr.helpcontext _
            )
exit
EndFunc

Here is just the code for the first GUI so you don't have to look through all that:

Func RefGui()
$RefGui = GUICreate("Reference#", 261, 156, -1, -1)
GUISetFont(10, 400, 0, "Verdana")
GUICtrlCreateLabel("Enter Reference:", 15, 12, 116, 20)
$RefNum = GUICtrlCreateInput("REFERENCE #", 136, 8, 110, 24, BitOR($GUI_SS_DEFAULT_INPUT,$ES_UPPERCASE))
$OK = GUICtrlCreateButton("OK", 176, 120, 75, 25, BitOR($BS_DEFPUSHBUTTON,$BS_NOTIFY))
GUICtrlCreateGroup("Select Ref Type:", 16, 40, 145, 105, BitOR($GUI_SS_DEFAULT_GROUP,$WS_CLIPSIBLINGS))
$Internet = GUICtrlCreateRadio("Internet Order", 24, 64, 113, 17)
GUICtrlSetState(-1, $GUI_CHECKED)
$Sales = GUICtrlCreateRadio("Sales Order", 24, 88, 113, 17)
$Invoice = GUICtrlCreateRadio("Invoice", 24, 112, 113, 17)
GUICtrlCreateGroup("", -99, -99, 1, 1)
GUISetState(@SW_SHOW)
While 1
  $nMsg = GUIGetMsg()
  If $nMsg = $GUI_EVENT_CLOSE Then Exit

  If $nMsg = $OK Then
   ExitLoop
  EndIf
WEnd
If GUICtrlRead($Internet) = 1 Then
  $refType="Internet"
ElseIf GUICtrlRead($Sales) = 1 Then
  $refType="Sales"
ElseIf GUICtrlRead($Invoice) = 1 Then
  $refType="Invoice"
EndIf

$temp=GUICtrlRead($RefNum)
GUIDelete($RefGui)
Return $temp
EndFunc

Edited May 16, 2012 by LordBoling

[BrewManNH]

First, your GUIDelete is after the Return statement so it's never going to get acted on, you'll need to move it to before the Return statement. BUT, and this is extremely important, first read the contents of the $refnum input box and assign it to a variable, and then use the variable in the Return statement. If you don't do this, there's no control to read from anymore, so the contents will be lost when you delete the GUI.

Second, you should probably move the declaration of the variables outside of the While loop, it can slow the script down, just move the Global $reftype line to before the While 1 line.

[LordBoling]

BrewManNH, Awesome!! Thank you, that solved the first issue perfectly, the RefGui now closes like it is supposed to. I knew I had to check the value before deleting the GUI but had forgotten that the Return keyword exits the function.

Unfortunately I still have the second issue. I predefined $refType as "Internet", but after RefGui() is called, it becomes blank. Anyone got any ideas on what is causing this? I did move the initial declaration outside of the loop as BrewManNH suggested but that did not affect what it is set to after the gui is called.

Updated code can be seen in the first post.

[Zedna]

If you need to reeturn more values from functions use ByRef parameters like this (tested,working):

expandcollapse popup

#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <EditConstants.au3>
#include <StaticConstants.au3>
#include <ButtonConstants.au3>

Global $RefNum, $RefType

RefGui($RefNum, $RefType)
MsgBox (0, 'RefGui result', $RefNum & ' - ' & $RefType)

Func RefGui(ByRef $num, ByRef $type)
    $RefGui = GUICreate("Reference#", 261, 156, -1, -1)
    GUISetFont(10, 400, 0, "Verdana")
    GUICtrlCreateLabel("Enter Reference:", 15, 12, 116, 20)
    $RefNum_id = GUICtrlCreateInput("REFERENCE #", 136, 8, 110, 24, BitOR($GUI_SS_DEFAULT_INPUT,$ES_UPPERCASE))
    $OK = GUICtrlCreateButton("OK", 176, 120, 75, 25, BitOR($BS_DEFPUSHBUTTON,$BS_NOTIFY))
    GUICtrlCreateGroup("Select Ref Type:", 16, 40, 145, 105, BitOR($GUI_SS_DEFAULT_GROUP,$WS_CLIPSIBLINGS))
    $Internet = GUICtrlCreateRadio("Internet Order", 24, 64, 113, 17)
    GUICtrlSetState(-1, $GUI_CHECKED)
    $Sales = GUICtrlCreateRadio("Sales Order", 24, 88, 113, 17)
    $Invoice = GUICtrlCreateRadio("Invoice", 24, 112, 113, 17)
    GUICtrlCreateGroup("", -99, -99, 1, 1)
    GUISetState(@SW_SHOW)
    While 1
      $nMsg = GUIGetMsg()
      If $nMsg = $GUI_EVENT_CLOSE Then Exit

      If $nMsg = $OK Then
       ExitLoop
      EndIf
    WEnd

    If GUICtrlRead($Internet) = 1 Then
      $Type="Internet"
    ElseIf GUICtrlRead($Sales) = 1 Then
      $Type="Sales"
    ElseIf GUICtrlRead($Invoice) = 1 Then
      $Type="Invoice"
    EndIf

    $num=GUICtrlRead($RefNum_id)
    GUIDelete($RefGui)
EndFunc

Also check if you don't have the same variable names more than once (local x global)

I see $RefNum is such case

[Zedna]

Also it's better (more safe) to use

If IsChecked($Internet) Then
      $Type="Internet"
    ElseIf IsChecked($Sales) Then
      $Type="Sales"
    ElseIf IsChecked($Invoice) Then
      $Type="Invoice"
    EndIf

Func IsChecked($control)
    Return BitAnd(GUICtrlRead($control),$GUI_CHECKED) = $GUI_CHECKED
EndFunc

Edited May 11, 2012 by Zedna

[BrewManNH]

What makes you think that $reftype is getting blanked after the function returns? I tested parts of your script and it worked for me, but I can't test all of it because the script won't run as written.

[LordBoling]

Sorry guys. My bad. It was not returning blank. I was appending it to other variables using += instead of &=. This caused some issues of course. Once I fixed that everything seems to be working great.

Thank you for all the help guys.