vbScript - vbRetry Question 1

[lewatkin]

Ok vbscript gurus, this is probably a simple question. This is my first attempt at vbscript. I am querying a database with input from the user, and when the records are returned to the message box, I have a retry button there. If the information that is returned is not what the user is looking for, I want them to be able to hit retry and "rerun" the vbscript. As it stands with the code below - you enter the parameter the first time, records are returned, you click retry and the input box form opens again, you enter your parameter, then it closes without returning anything the second time. If someone could point me in the right direction that would be awesome because I fear I have spent way to much time on this already. Thanks in advance for looking and extending me the understanding I am new at this . I have changed the server name and database name in the example.

________________________________________

Option Explicit

GetInfo()
Dim strInput, strGroup
Dim objCN, strConnection
Dim Result

Set objCN = CreateObject("ADODB.Connection")
strConnection = "Driver={SQL Server};Server=MyServer;Database=MyDatabase;Trusted_Connection=TRUE"
objCN.Open strConnection


If strinput = -1 or strinput = "" then
wscript.quit
End If

Dim strSQLQuery

strSQLQuery = "Select * from dbo.vw_script where responsibility like '%" & strinput & "%'"


Dim objRS
objRS=CreateObject("ADODB.Recordset")
Set objRS = objCN.Execute(strSQLQuery)


WHILE objrs.EOF <> TRUE
'access columns using objRS("column")
strGroup = strGroup & objRS.Fields("name") & " - " & objrs.fields("responsibility") & " - " & objrs.fields("con_type") & vbcrlf
objRS.MoveNext
WEND

Result = MsgBox(strGroup,vbretryCancel,"Information")

If Result = vbRetry then
GetInfo()
End If

objRS.Close
objCN.Close

Function GetInfo()
strinput = inputbox("Enter any part of responsibility", "Enter Information")
End function

 

[guitarzan]

Surround your code in a loop that only drops out when the Cancel button is pressed. Also be careful to put your Dim's outside the loop. Something like this:

Option Explicit
Dim Result
Dim objRS
Dim strSQLQuery
Dim strInput, strGroup
Dim objCN, strConnection


Do While Result <> vbCancel
   GetInfo()

   Set objCN = CreateObject("ADODB.Connection")
   strConnection = "Driver={SQL Server};Server=MyServer;Database=MyDatabase;Trusted_Connection=TRUE"
   objCN.Open strConnection

   If strinput = -1 or strinput = "" then
      wscript.quit
   End If

   strSQLQuery = "Select * from dbo.vw_script where responsibility like '%" & strinput & "%'"

   objRS=CreateObject("ADODB.Recordset")
   Set objRS = objCN.Execute(strSQLQuery)

   WHILE objrs.EOF <> TRUE
      'access columns using objRS("column")
      strGroup = strGroup & objRS.Fields("name") & " - " & objrs.fields("responsibility") & " - " & objrs.fields("con_type") & vbcrlf
      objRS.MoveNext
   WEND

   objRS.Close
   objCN.Close 	

   Result = MsgBox(strGroup,vbretryCancel,"Information")

Loop

Function GetInfo()
   strinput = inputbox("Enter any part of responsibility", "Enter Information")
End function

 

[lewatkin]

I fixed it. Just added a boolean in a loop that was false until the Cancel button was clicked on the Msgbox form. Maybe not the most efficient, but it works.

 

[lewatkin]

Thanks for your help! That's exactly what I did. have a star!!

 

[PHV]

Why keeping open and close the connection ?

Dim Result
Dim strSQLQuery
Dim strInput, strGroup
Dim objCN, strConnection, objRS

Set objCN = CreateObject("ADODB.Connection")
strConnection = "Driver={SQL Server};Server=MyServer;Database=MyDatabase;Trusted_Connection=TRUE"
objCN.Open strConnection
Set objRS = CreateObject("ADODB.Recordset")
Do While Result <> vbCancel
  strInput = GetInfo()
  If strInput = -1 Or strInput = "" Then
    WScript.Quit
  End If
  strSQLQuery = "Select * from dbo.vw_script where responsibility like '%" & strInput & "%'"
  Set objRS = objCN.Execute(strSQLQuery)
  strGroup = ""
  While objRS.EOF <> True
    'access columns using objRS("column")
    strGroup = strGroup & objRS.Fields("name") & " - " & objRS.Fields("responsibility") & " - " & objRS.Fields("con_type") & vbCrLf
    objRS.MoveNext
  Wend
  objRS.Close
  Set objRS = Nothing
  Result = MsgBox(strGroup, vbRetryCancel, "Information")
Loop
objCN.Close

Function GetInfo()
GetInfo = InputBox("Enter any part of responsibility", "Enter Information")
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lewatkin]

PH,

That is what I ended up doing. I saw the same thing you referenced and moved the Do loop below the connection string. Thanks for the reply!

Lee

 

[guitarzan]

PHV,

Yea it was my code that had that oversight, good catch