Variable not read right (syntax error)

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

Variable not read right (syntax error)

Logged in as: Guest

arrSession:exec spGetSession 2,2,37728

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> Variable not read right (syntax error)
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

Variable not read right (syntax error) - 9/13/2006 8:10:10 AM

user_s

Posts: 16
Score: 0
Joined: 9/13/2006
Status: offline

My goal is to query and display (html) some information from log files.
Everything's working except that when I use numbers in my variable (that I search for) the leading 0s get chopped off and letters are not taken at all. The latter produces "Syntax error (missing operator) in query expression 'Badge = 750D' Code: 80040E14"

I've researched the issue, but still can not see where I can force identify "Badge" as variable, and not integer. My code is below. It is mostly cobbled from different pieces of code, as this is my first fore into the VBScript world so please forgive the non-clarity of it.

I can also post the log.htm code if needed. I think the issue is probably with line 138 (if you c/p the code)

Thank you in advance for any/all help.

Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8

On Error Resume Next
Dim DateYear, DateMonth, DateDay, Badge, IE, path, filesys, oShell, shApp, x, bTest

path = WScript.ScriptFullName
path = Left(path, InstrRev(path, "..\"))

'Properties of the Web window
Set IE = WScript.CreateObject("InternetExplorer.Application")
IE.left = 200
IE.top = 200
IE.height = 350
IE.width = 700
IE.menubar = 0
IE.toolbar = 0
IE.statusbar = 1
IE.resizable = 1
IE.navigate path + "\\truepathremoved\log.htm"
IE.visible = 1

'Msg when user closes the window prematuresly
Do While (IE.Document.All.OKClicked.Value = "")
 If err <> 0 Then
'  Msgbox "Terminated by user..."
 Set IE = Nothing
 WScript.Quit
 Else
 WScript.Sleep 100
 End If 
Loop

'User inputted values defined here
DateYear = IE.Document.All.DateYear.Value
DateMonth = IE.Document.All.DateMonth.Value
DateDay = IE.Document.All.DateDay.Value
Badge = IE.Document.All.Badge.Value

'creating temp output file
set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists ("C:\temp\temp_Output.txt") Then
fso.DeleteFile ("C:\temp\temp_Output.txt")
End If

'copy amf file from L: to local if it exists, if not quit, AND restart the script
set filesys=CreateObject("Scripting.FileSystemObject")
If filesys.FileExists("L:\20" & DateYear & "-" & DateMonth & "-" & DateDay & "\truepathremoved\log." & DateYear & DateMonth & DateDay & "") Then
  filesys.CopyFile "L:\20" & DateYear & "-" & DateMonth & "-" & DateDay & "\truepathremoved\log." & DateYear & DateMonth & DateDay & "", "c:\temp\"
  Else
          Wscript.Echo "The file does not exist."
       IE.Quit
         Set IE = Nothing
       Set oShell = CreateObject("WScript.Shell")
       oShell.Run "wscript.exe \\truepathremoved\log_search.vbs", 1, True
       WScript.Quit
End If

'replace commas in name field with a space
Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("c:\temp\log." & DateYear & DateMonth & DateDay & "", ForReading)
Do Until objFile.AtEndOfStream
   strLine = objFile.ReadLine
   strLine = Replace(strLine, ",", " ")
   strNewText = strNewText & strLine & vbCrLF
Loop

objFile.Close

Set objFile = objFSO.OpenTextFile("c:\temp\log." & DateYear & DateMonth & DateDay & "", ForWriting)
objFile.WriteLine strNewText

objFile.Close

set tsFile = fso.OpenTextFile("C:\temp\temp_Output.txt", ForAppending, True)
Set objTextFile = fso.OpenTextFile("c:\temp\log." & DateYear & DateMonth & DateDay & "", _
   ForReading)

tsFile.WriteLine("Badge,Name,DeleteDate,Ignore,UserType,FirmID")

Do While objTextFile.AtEndOfStream <> True
   strLinetoParse = objTextFile.ReadLine
   strBadge = Mid(strLinetoParse, 1, 4)
   dtmName = Mid(strLinetoParse, 5, 15)
   intDeleteDate = Mid(strLinetoParse, 35, 8)    
   sngIgnore = Mid(strLinetoParse, 51, 1)
   sngUserType = Mid(strLinetoParse, 52, 1)
   dtmFirmID = Mid(strLinetoParse, 53, 4)
   
tsFile.WriteLine(strBadge & "," & dtmName & "," & intDeleteDate & "," & sngIgnore & "," & sngUserType & "," & dtmFirmID)
Loop
tsFile.Close

set tsFile = Nothing

If fso.FileExists ("\\truepathremoved\results.htm") Then
fso.DeleteFile ("\\truepathremoved\results.htm")
End If

set tsFile = fso.OpenTextFile("\\truepathremoved\results.htm", ForAppending, True)

tsFile.WriteLine("<html><head><title>Search results</title>")
tsFile.WriteLine("<script language=" & chr(34) & "javascript" & chr(34) & "type=" & chr(34) & "text/javascript" & chr(34) & ">")
tsFile.WriteLine("function runCmd(cmd) {")
tsFile.WriteLine("var shell=new ActiveXObject(" & chr(34) & "WScript.shell" & chr(34) & ");")
tsFile.WriteLine("shell.run(cmd,1); } </script>")
tsFile.WriteLine("<style type=" & chr(34) & "text/css"  & chr(34) & ">")
tsFile.WriteLine("")
tsFile.WriteLine("</style></head><body><center>")
tsFile.WriteLine("<table width=" & chr(34) & "100%" & chr(34) & " border=" & chr(34) & "1" & chr(34) & " cellpadding=" & chr(34) & "1" & chr(34) & ">")
tsFile.WriteLine("<tr><td height=" & chr(34) & "50" & chr(34) & ">Badge</td><td>Name</td><td>Date deleted </td><td>Ignore?</td><td>User type</td><td>Firm</td></tr>")

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

strPathtoTextFile = "C:\temp\"

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
   "Data Source=" & strPathtoTextFile & ";" & _
       "Extended Properties=""text;HDR=YES;FMT=Delimited"""

strBadge = Badge
strFile = "temp_Output.txt"

objRecordset.Open "Select * FROM " & strFile & " where Badge = " & strBadge & "", _
   objConnection, adOpenStatic, adLockOptimistic, adCmdText

Do Until objRecordset.EOF

tsFile.WriteLine("<tr><td>" & objRecordset.Fields.Item("Badge") & "</td><td>" & objRecordset.Fields.Item("Name") & "</td><td>" & objRecordset.Fields.Item("DeleteDate") & "</td><td>" & objRecordset.Fields.Item("Ignore") & "</td><td>" & objRecordset.Fields.Item("UserType") & "</td><td>" & objRecordset.Fields.Item("FirmID") & "</td></tr>")
    objRecordset.MoveNext
Loop

tsFile.WriteLine("</table><br>")
tsFile.WriteLine("<input type=" & chr(34) & "Button" & chr(34) &  "value=" & chr(34) & "Try again" & chr(34) & " onclick=" & chr(34) & "runCmd('\\\\truepathremoved\\log_search.vbs');" & chr(34) & ">  <input type=button value=" & chr(34) & "Exit" & chr(34) & "onClick=" & chr(34) & "javascript:window.close();" & chr(34) & ">")
tsFile.WriteLine("</center></body></html>")

tsFile.Close
set tsFile = Nothing
set fso = Nothing
IE.navigate path + "\\truepathremoved\results.htm"

Post #: 1

RE: Variable not read right (syntax error) - 9/14/2006 1:16:58 AM

ebgreen

Posts: 5246
Score: 31
Joined: 7/12/2005
Status: online

Does this work for you?

objRecordset.Open "Select * FROM " & strFile & " where Badge ='" & strBadge & "'", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to user_s)

Post #: 2

RE: Variable not read right (syntax error) - 9/14/2006 7:51:56 AM

user_s

Posts: 16
Score: 0
Joined: 9/13/2006
Status: offline

Ah, I've tried putting them virtually everywhere (but not where you mentioned)... thanks.

Unfortunately now I get the following error:

"Data type mismatch in criteria expression" (same line of code)
I'm currently looking into it.

Thanks much for your help.

(in reply to ebgreen)

Post #: 3

RE: Variable not read right (syntax error) - 9/14/2006 7:54:55 AM

ebgreen

Posts: 5246
Score: 31
Joined: 7/12/2005
Status: online

Hmmm...it looks like you are pulling this from a delimited text file? Is that the case? If so, I would expect all the fields to be type string. Curious.

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to user_s)

Post #: 4

RE: Variable not read right (syntax error) - 9/14/2006 8:04:45 AM

user_s

Posts: 16
Score: 0
Joined: 9/13/2006
Status: offline

The original log is not comma delimited... I parse it (know exactly the length of each filed) into a comma delimited .txt with a header.

One clue to go by is that when I finally display that parse data, all my numerical values have trailing 0s cut off (ex: 0024 is 24). The values are fine in the parsed .txt

Thanks again!

(in reply to ebgreen)

Post #: 5

RE: Variable not read right (syntax error) - 9/14/2006 8:06:20 AM

user_s

Posts: 16
Score: 0
Joined: 9/13/2006
Status: offline

Sorry.. it's been a long day.. above post should say:

"The original log is not comma delimited... I parse it (know exactly the length of each "field") into a comma delimited .txt with a header.

One clue to go by is that when I finally display that parse data, all my numerical values have initial 0s cut off (ex: 0024 is 24). The values are fine in the parsed .txt

Thanks again!

(in reply to user_s)

Post #: 6

RE: Variable not read right (syntax error) - 9/14/2006 8:07:04 AM

ebgreen

Posts: 5246
Score: 31
Joined: 7/12/2005
Status: online

Could you post a mock up of the file with good values in it?

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to user_s)

Post #: 7

RE: Variable not read right (syntax error) - 9/14/2006 8:18:49 AM

user_s

Posts: 16
Score: 0
Joined: 9/13/2006
Status: offline

Here you go, thanks for taking the time.

STEP1
Please create temp_Output.txt in your c:\temp\ with the following text:

STEP2
Paste this into a new .hta file (ex: test.hta)

<html>
<head>
<title>Log Search v0.1</title>
<SCRIPT LANGUAGE="JScript">
function myClose() {
   close();}
</SCRIPT>

<SCRIPT language="VBScript">
<!--
Sub RunScripts
   main_script
   'script2
End Sub

Sub main_script
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8

'creating temp output file
set fso = CreateObject("Scripting.FileSystemObject")

objRecordset.MoveNext
Loop

End Sub
-->

</SCRIPT>

<hta:application 
  id="MyApp"
  applicationName="My Application"
  border="thin"
  borderStyle="sunken"
  caption="yes"
  contextMenu="yes"
  icon="search.ico"
  innerBorder="no"
  maximizeButton="yes"
  minimizeButton="yes"
  navigable="yes"
  scroll="yes"
  scrollFlat="no"
  selection="yes"
  showInTaskBar="yes"
  singleInstance="no"
  sysMenu="yes"
  version="0.1"
  windowState="normal"
>
</head>

<BODY>

<p align="left" class="style3"><strong>Year </strong><span class="style1">(##):</span><strong>
<input type="text" name="DateYear" value="06" size="2" maxlength="2">
&nbsp;&nbsp;Month</strong><span class="style1"> (##):</span><strong>
<input type="text" name="DateMonth" value="09" size="2" maxlength="2">
&nbsp;&nbsp;Day</strong><span class="style1"> (##):</span><strong>
<input type="text" name="DateDay" value="07" size="2" maxlength="2"><br><br>
Badge: <input type="text" name="Badge" value="999T" size="4" maxlength="4"></strong>
&nbsp;&nbsp;&nbsp;&nbsp;
<BUTTON onclick="RunScripts();">Refresh</BUTTON></p>
<br>
<span id = "DataArea"></span>

</body>
</html>

STEP3 Run the hta, putting the following values into the "Badge" field:
First "1234" ...perfect result
Second "0055" ...you see how it cut it down to "55"... but still works so ok
Lastly "999T" ...therein lies the problem.

(in reply to ebgreen)

Post #: 8

RE: Variable not read right (syntax error) - 9/15/2006 2:30:17 AM

ebgreen

Posts: 5246
Score: 31
Joined: 7/12/2005
Status: online

Ok, this works (it fixess the problem with 0055 too). Use this code:

<html>
<head>
<title>Log Search v0.1</title>
<SCRIPT LANGUAGE="JScript">
function myClose() {
  close();}
</SCRIPT>

<SCRIPT language="VBScript">
<!--
Sub RunScripts
  main_script
  'script2
End Sub

Sub main_script
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8

'creating temp output file
set fso = CreateObject("Scripting.FileSystemObject")

'read and output text to the screen
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
strPathtoTextFile = "C:\temp\"
' objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
'    "Data Source=" & strPathtoTextFile & ";" & _
'        "Extended Properties=""text;HDR=YES;FMT=Delimited"""
strConn = "DRIVER={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\Temp;"
strBadge = CStr(Badge.Value)
strFile = "temp_Output.txt"
'strSQL = "Select * FROM [" & strFile & "] where Badge=" & strBadge & ""
strSQL = "SELECT * FROM [" & strFile & "]"
objRecordset.Open strSQL, strConn, adOpenStatic, adLockOptimistic, adCmdText
objRecordSet.movefirst
objRecordSet.Filter = "Badge=" & strBadge & ""
Set oSpan = document.getelementbyid("DataArea")
Set oTable = document.CreateElement("TABLE")
oSpan.AppendChild(oTable)
Do Until objRecordset.EOF
'DataArea.InnerHTML = "<br><table width=" & chr(34) & "100%" & chr(34) & " border=" & chr(34) & "1" & chr(34) & " cellpadding=" & chr(34) & "1" & chr(34) & ">" & "<tr><td height=" & chr(34) & "50" & chr(34) & ">Badge</td><td>Name</td><td>Date deleted</td><td>Ignore?</td><td>User type</td><td>Firm</td></tr>" & "<tr><td>" & objRecordset.Fields.Item("Badge") & "</td><td>" & objRecordset.Fields.Item("Name") & "</td><td>" & objRecordset.Fields.Item("DeleteDate") & "</td><td>" & objRecordset.Fields.Item("Ignore") & "</td><td>" & objRecordset.Fields.Item("UserType") & "</td><td>" & objRecordset.Fields.Item("FirmID") & "</td></tr>"
   Set oRow = oTable.InsertRow()
   For Each oField In objRecordSet.Fields
       Set oCell = oRow.InsertCell()
       oCell.InnerHTML = objRecordSet(oField.Name)
   Next
   'oRow.InnerHTML = "<td>" & objRecordset.Fields.Item("Badge") & "</td><td>" & objRecordset.Fields.Item("Name") & "</td><td>" & objRecordset.Fields.Item("DeleteDate") & "</td><td>" & objRecordset.Fields.Item("Ignore") & "</td><td>" & objRecordset.Fields.Item("UserType") & "</td><td>" & objRecordset.Fields.Item("FirmID") & "</td>"
   objRecordset.MoveNext
Loop

End Sub
-->

</SCRIPT>

<hta:application 
 id="MyApp"
 applicationName="My Application"
 border="thin"
 borderStyle="sunken"
 caption="yes"
 contextMenu="yes"
 icon="search.ico"
 innerBorder="no"
 maximizeButton="yes"
 minimizeButton="yes"
 navigable="yes"
 scroll="yes"
 scrollFlat="no"
 selection="yes"
 showInTaskBar="yes"
 singleInstance="no"
 sysMenu="yes"
 version="0.1"
 windowState="normal"
>
</head>

<BODY>

</body>
</html>

You will also need to put a Schema.ini file in the same directory as the data file. The Schema.ini file needs to contain:

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to user_s)

Post #: 9

RE: Variable not read right (syntax error) - 9/15/2006 4:54:07 AM

user_s

Posts: 16
Score: 0
Joined: 9/13/2006
Status: offline

Thank you VERY much.

That gives me a working point.

(in reply to ebgreen)

Post #: 10

RE: Variable not read right (syntax error) - 9/15/2006 8:19:52 AM

user_s

Posts: 16
Score: 0
Joined: 9/13/2006
Status: offline

One more (hopefully last) question.

I'm trying to work with more then one tempOutput.txt files (temp_Output.txt, temp_Output2.txt, temp_Output3.txt, etc.)

These files are same instructure but have different header names.

Would it still be possible to incorporate pulling data from multiple output files using your method? I've dropped the .ini and just pull values thusly:

I can't seem to however incoporate using variable (let's say ABC from the Output2.txt).... objRecordset.Fields.Item("ABC")

Thanks in advance.

(in reply to user_s)

Post #: 11

RE: Variable not read right (syntax error) - 9/15/2006 8:35:57 AM

ebgreen

Posts: 5246
Score: 31
Joined: 7/12/2005
Status: online

You could easily pull from multiple files. I would suggest using an ini file though because it will make sure your data is handled correctly. You can just copy the ini block for temp_Output.txt in the ini and change the header for the block to the name of another file that you want to use. As an aside, using an ini file would let you avoid parsing the data into a temp file in the first place since it would support fixed length fields.

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to user_s)

Post #: 12

RE: Variable not read right (syntax error) - 9/18/2006 4:21:11 AM

user_s

Posts: 16
Score: 0
Joined: 9/13/2006
Status: offline

Yes thank.. I ran into that issue and took your advice. Works now. :) :)

(in reply to ebgreen)

Post #: 13

RE: Variable not read right (syntax error) - 9/20/2006 3:43:37 AM

user_s

Posts: 16
Score: 0
Joined: 9/13/2006
Status: offline

Another question.

If I do

And later

For some reason my <td>s do not seem to take, meaning the overall boarder is seen but not the border lines between tds. BUT this is only the case while viewing the HTA. If I save the dataarea to an .html file, there I can see a normal table (with all the lines, etc)

Thanks in advance for your help.

< Message edited by user_s -- 9/20/2006 4:22:27 AM >

(in reply to user_s)

Post #: 14

If you found our site useful please link to us <a href="http://www.visualbasicscript.com">VisualBasicScript.com</a>.

All Forums >> [Scripting] >> WSH & Client Side VBScript >> Variable not read right (syntax error)

Page: [1]

Jump to:

New Messages	No New Messages
Hot Topic w/ New Messages	Hot Topic w/o New Messages
Locked w/ New Messages	Locked w/o New Messages

Post New Thread

Reply to Message

Post New Poll

Submit Vote

Delete My Own Post

Delete My Own Thread

Rate Posts