SYntax error

[Lipjam]

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 OLEDB:Database 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

[SYNACK]

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

' COPY OLD MARKS TABLE TO BACKUP
SQLStatement = "SELECT * INTO Marks_BAK_" & replace(date(),"/","_") & "_" & replace(time(),":","_") & " FROM Marks"
[color=red]msgbox SQLStatement[/color]
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.

[Lipjam]

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.

[Lipjam]

??? no help?