+ Post New Thread
Results 1 to 4 of 4
Scripts Thread, SYntax error in Coding and Web Development; Hi I am not a coder so looking at syntax for me I might as well be looking at a ...
  1. #1
    Lipjam's Avatar
    Join Date
    May 2007
    Location
    Moreton
    Posts
    18
    Thank Post
    4
    Thanked 2 Times in 1 Post
    Blog Entries
    2
    Rep Power
    15

    SYntax error

    Hi

    I am not a coder so looking at syntax for me I might as well be looking at a hole in the ground. I debugged this script and found an error at line 94,5 (see below line is red bold). I commented out the line and the script runs fine. I am just concerned that I am leaving out something important that will catch up on me so would like you opinions on this line what it refers to and if you can spot the error. I have taken out any real info on paths and passwords so don't be concerned about them.

    Many thanks in advance.

    PS. I notice that a pice of the code comes out as a smiley that is the work of edugeek and is just a 0 and a ; in the script.

    Reported error:
    -Tracking-FixedMarks.vbs(94, 5)
    Microsoft JET Database Engine: Syntax error in INSERT INTO statement.


    Script

    ' MAP DRIVE X TO THE ADMIN SERVER
    '--------------------------------
    Option Explicit
    Dim strUser, strPassword, strDriveLetter, strHomeServer, strProfile
    Dim objNetwork, objPopUp
    Set objNetwork = CreateObject("WScript.Network")
    Set objPopUp = CreateObject("WScript.Shell")

    strDriveLetter = "b:"
    strHomeServer = "\\servername"
    strProfile = "False"
    strUser = "domain\user"
    strPassword = "password"
    objNetwork.MapNetworkDrive strDriveLetter, strHomeServer, strProfile, strUser, strPassword

    ' WAIT FOR CONNECTION
    '--------------------
    WScript.sleep 3000


    ' COPY DESIRED XML FILE
    '-----------------------
    Dim objFSO, objFileCopy, aFile
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set aFile = objFSO.GetFile("c:\Tracking-FixedMarks.xml")
    aFile.Copy("c:\Tracking-FixedMarks.xml")

    ' WAIT FOR FILE COPY
    '-------------------
    WScript.sleep 3000

    ' DISCONNECT DRIVE X
    '-------------------
    objNetwork.RemoveNetworkDrive "b:"

    ' OPEN XML
    '---------
    Dim xmlDoc
    Set xmlDoc = CreateObject("Microsoft.XMLDOM")
    xmlDoc.Async = "False"
    xmlDoc.Load("c:\Tracking-FixedMarks.xml")

    Dim strQuery, colItem, objItem, SQLStatement, conntemp, myconnect, rstemp
    strQuery = "/SuperStarReport/Record"
    Set colItem = xmlDoc.selectNodes(strQuery)

    set conntemp = CreateObject("adodb.connection")
    myconnect="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=\\server\Tracking.mdb;Jet OLEDBatabase Password=password;"
    conntemp.Open myconnect

    'DELETE OLD DATA
    '----------------
    SQLStatement = "DELETE * FROM MarksImporter WHERE MarksImporter.SetID IN(SELECT SetID FROM MarkSets WHERE ReadOnly=True)"
    set rstemp=conntemp.execute(SQLStatement)

    For Each objItem in colItem
    Dim myVal, thisItem , varUniqueID, varSetID, varMark, varDate

    set myVal = objItem.selectNodes("AdmissionNumber")
    For Each thisItem in myVal
    varUniqueID = right(thisItem.text,5)
    Next

    set myVal = objItem.selectNodes("AspectDescription")
    For Each thisItem in myVal
    varSetID = thisItem.text
    Next

    set myVal = objItem.selectNodes("Result")
    For Each thisItem in myVal
    varMark = thisItem.text
    Next

    set myVal = objItem.selectNodes("ResultDate")
    For Each thisItem in myVal
    varDate = thisItem.text
    Next

    'FORMAT DATE
    '-----------
    Dim varDateYear, varDateMonth, varDateDay
    varDateYear = left(varDate,4)
    varDateMonth = right(left(varDate,7),2)
    varDateDay = right(left(varDate,10),2)
    varDate = varDateDay& "/" & varDateMonth & "/" & varDateYear

    SQLStatement = "INSERT INTO MarksImporter (UniqueID,SetID,Mark,MarkDate) VALUES (" & varUniqueID & "," & replace(varSetID,"#","") & ",'" & varMark & "','" & varDate & "')"
    WScript.Echo SQLStatement

    ' UPDATE DATABASE
    '
    '----------------
    set rstemp=conntemp.execute(SQLStatement)

    Next

    ' POST IMPORT ACTIVITY
    ' ----------------------

    ' COPY OLD MARKS TABLE TO BACKUP
    SQLStatement = "SELECT * INTO Marks_BAK_" & replace(date(),"/","_") & "_" & replace(time(),":","_") & " FROM Marks"
    set rstemp=conntemp.execute(SQLStatement)
    wScript.Echo "Backed up Marks table..."

    ' CLEAR MARKS TABLE
    SQLStatement = "DROP Table Marks"
    set rstemp=conntemp.execute(SQLStatement)
    wScript.Echo "Clearing Marks data..."


    ' COPY NEW MARKS TABLE TO MARKS
    SQLStatement = "SELECT * INTO Marks FROM MarksImporter"
    set rstemp=conntemp.execute(SQLStatement)
    wScript.Echo "Updated Marks with new data..."

    conntemp.Close

    WScript.Quit

  2. #2

    SYNACK's Avatar
    Join Date
    Oct 2007
    Posts
    11,084
    Thank Post
    853
    Thanked 2,679 Times in 2,272 Posts
    Blog Entries
    9
    Rep Power
    769
    Code:
    ' POST IMPORT ACTIVITY
    ' ----------------------
    
    ' COPY OLD MARKS TABLE TO BACKUP
    SQLStatement = "SELECT * INTO Marks_BAK_" & replace(date(),"/","_") & "_" & replace(time(),":","_") & " FROM Marks"
    msgbox SQLStatement
    set rstemp=conntemp.execute(SQLStatement)
    wScript.Echo "Backed up Marks table..."
    Add the red line into your script and post the resulting text from the messagebox that pops up back here. It looks like there in an error in the tSQL INSERT statment that is being used so there may be bad data or a strange error when the string is put together.

  3. #3
    Lipjam's Avatar
    Join Date
    May 2007
    Location
    Moreton
    Posts
    18
    Thank Post
    4
    Thanked 2 Times in 1 Post
    Blog Entries
    2
    Rep Power
    15
    IT ECHOS WHAT IS DISPLAYED IN THE cmd WINDOW AS FOLLOWS:

    INSERT INTO Marksimporter (UniqueID,SetID,Mark,MarkDate) VALUES (14283,18325,'C','07/09/2010')
    INSERT INTO Marksimporter (UniqueID,SetID,Mark,MarkDate) VALUES (14283,18309,'B','07/09/2010')
    INSERT INTO Marksimporter (UniqueID,SetID,Mark,MarkDate) VALUES (14283,18326,'B','07/09/2010')
    Etc...
    As I've stated if I comment out line 95 (highlighted red above) the script will continue as above (INSERT INTO etc..) ad-nauseam until the script completes. If I remove the comment on line 95 I get six lines as above and then the following error:
    -Tracking-FixedMarks.vbs(95, 5)
    Microsoft JET Database Engine: Syntax error in INSERT INTO statement.

  4. #4
    Lipjam's Avatar
    Join Date
    May 2007
    Location
    Moreton
    Posts
    18
    Thank Post
    4
    Thanked 2 Times in 1 Post
    Blog Entries
    2
    Rep Power
    15
    ??? no help?

SHARE:
+ Post New Thread

Similar Threads

  1. xcopy syntax
    By leco in forum How do you do....it?
    Replies: 2
    Last Post: 28th August 2010, 10:37 AM
  2. Replies: 9
    Last Post: 1st April 2010, 03:15 PM
  3. [Fog] Syntax for specifying OU in AD
    By birchanger in forum O/S Deployment
    Replies: 3
    Last Post: 24th July 2009, 04:02 PM
  4. SQL Syntax?
    By ScottStevinson in forum How do you do....it?
    Replies: 2
    Last Post: 8th September 2008, 01:01 PM
  5. Grep syntax for multiple srtings?
    By ChrisH in forum *nix
    Replies: 17
    Last Post: 14th October 2006, 08:20 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •