We help IT Professionals succeed at work.

VB Script "Expected End of Statement"

logicsolutions
on
Hi Experts,

I am using the following code and it telling me there is an error on Line 1 indicating Expected end of Statement.

I have also tried seperating the Dim's at the start to read something like:

Dim n, nTotal  
Dim cnn, rst

but that did not work either.

Never used VB Script before but after doing my home work I have found there should be nothing wrong with the code.

Can anyone see any problems in the code below.
Dim n, nTotal  Dim cnn, rst
    Set cnn = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.Recordset")

strPathtoTextFile = "C:\Databases"

    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & strPathtoTextFile & ";" & _
          "Extended Properties=""text;HDR=YES;FMT=Delimited"""
    rst.Open "SELECT * FROM accounts.csv WHERE client_id=" 
         &  CInt(mailer.fields("client_id")), cnn
    n = 1
    nTotal = 0
    Do Until rst.EOF
        mailer.write("invoice" & n & ", ")
        mailer.write(rst("totalamount1") & ", ")
        mailer.write(rst("due"))
        if Len(rst("attach")) > 0 then mailer.attachFile(rst("attach"))
        mailer.write(vbCrLf)
        n = n + 1
        nTotal = nTotal + rst("amount")
        rst.MoveNext
    Loop
    rst.Close
    cnn.Close

Open in new window

Comment
Watch Question

Top Expert 2010

Commented:
>>Dim n, nTotal  Dim cnn, rst

You have to separate that into different lines like this:

Dim n, nTotal  
Dim cnn, rst

or get rid of the second Dim:

Dim n, nTotal, cnn, rst
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
You can also have multiple statements on the same line

Dim n, nTotal : Dim cnn, rst

I think you just hit the DEL/Backspace key without remembering it

Author

Commented:
Thanks guys. I am no longer getting the "Expected End of Statement"

Now I get another error that reads "Expected Statement" on Line 1
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
You have got it in a SUB right??

Sub X()
Dim n, nTotal  
Dim cnn, rst
    Set cnn = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.Recordset")

strPathtoTextFile = "C:\Databases"

    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & strPathtoTextFile & ";" & _
          "Extended Properties=""text;HDR=YES;FMT=Delimited"""
    rst.Open "SELECT * FROM accounts.csv WHERE client_id=" 
         &  CInt(mailer.fields("client_id")), cnn
    n = 1
    nTotal = 0
    Do Until rst.EOF
        mailer.write("invoice" & n & ", ")
        mailer.write(rst("totalamount1") & ", ")
        mailer.write(rst("due"))
        if Len(rst("attach")) > 0 then mailer.attachFile(rst("attach"))
        mailer.write(vbCrLf)
        n = n + 1
        nTotal = nTotal + rst("amount")
        rst.MoveNext
    Loop
    rst.Close
    cnn.Close
End Sub

Open in new window

Expert of the Quarter 2010
Expert of the Year 2010

Commented:
For what it's worth, you need to join lines 12 and 13 as well, e.g.

rst.Open "SELECT * FROM accounts.csv WHERE client_id=" _
         &  CInt(mailer.fields("client_id")), cnn
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Oops... working with Excel too much. Forget the comment about the SUB, but do read the next one.

Author

Commented:
Well, I am directly feeding this code into a program which converts it.

You can by there example code on the following page:

http://www.mach5.com/support/mailer/docs/script.html

The Mach5 application reads the <? and ?> as sub commands I think. There is a VBScript example on the page above.

Author

Commented:
Here is an example of there code:
Dear <? mailer.fields("customer_name")?> ,
Jobs currently in progress:
Job, Status,    Amount
===========================
<?    
	Dim cnn, rst    Dim n, nTotal
    Set cnn = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.Recordset")
    cnn.Open "Provider=MSDASQL.1;Data Source=jobs_odbc"
    rst.Open "SELECT * FROM jobs WHERE customer_id=" 
         &  CInt(mailer.fields("customer_id")), cnn
    n = 1
    nTotal = 0
    Do Until rst.EOF
        mailer.write("Job" & n & ", ")
        mailer.write(rst("status") & ", ")
        mailer.write(rst("amount"))
        if Len(rst("attach")) > 0 then mailer.attachFile(rst("attach"))
        mailer.write(vbCrLf)
        n = n + 1
        nTotal = nTotal + rst("amount")
        rst.MoveNext
    Loop
    rst.Close
    cnn.Close
?>   
===========================
<?
    mailer.write("Total: $" & nTotal & " in " & (n ? 1) & " jobs")
?>

Open in new window

Author

Commented:
I did the following change as indicated by Cyberwiki

rst.Open "SELECT * FROM accounts.csv WHERE client_id=" _
         &  CInt(mailer.fields("client_id")), cnn

Now I get Syntax Error
Getting closer I think.
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
[
I did the following change as indicated by Cyberwiki

rst.Open "SELECT * FROM accounts.csv WHERE client_id=" _
         &  CInt(mailer.fields("client_id")), cnn

Now I get Syntax Error
Getting closer I think.
]

Hi there,

Unfortunately, it's getting further. I've never worked with Mach5, but it looks like it supports multi-line statements.  Adding the _ causes a syntax error which stops it cold.

Good luck.

Author

Commented:
The picture below is a screen grab of the code and the error when previewing.

Any help would be much appreciated. I have already tired to contact them directly. Due to time zone differences the communication is difficult.
error-mach5.JPG
error-mach5-1.JPG

Commented:
Try


Dim n, nTotal
Dim cnn, rst

Set cnn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")

strPathToTextFile = "C:\Databases"

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
         "Data Source=" & strPathToTextFile & ";" & _
         "Extended Properties=""text;HDR=YES;FTM=Delimited"""

rst.Open "SELECT * FROM accounts.csv WHERE client_id=" & CInt(mailer.fields("client_id")), cnn

n = 1
nTotal = 0

Do Until rst.EOF
        mailer.write("invoice" & n & ", ")
        mailer.write(rst("totalamount1") & ", ")
        mailer.write(rst("due"))
        if Len(rst("attach")) > 0 then mailer.attachFile(rst("attach"))
        mailer.write(vbCrLf)
        n = n + 1
        nTotal = nTotal + rst("amount")
        rst.MoveNext
Loop

rst.Close
cnn.Close

Open in new window

Author

Commented:
Hi Harris,

I get a "Syntax Error"

Commented:
I have a copy of the mailer but I cannot run the vbscript.  I downloaded a free trial.

What line are you getting the error?

Author

Commented:
The first Line. I even tried copying and pasting there script and even there script gives errors.

Even tried using JScript example they provided. Is there a basic vbscript command I can run to test functionality?

Commented:
C:\cscript    myscript.vbs

I tried running the code without the headers and it worked fine.

You mIght want to call them up - I cannot help you with the TRIAL I downloaded.
You need to separate the two dims you have on the first line and you neeed to join line 12 and 13 as the code below.

Dim n, nTotal
 Dim cnn, rst
    Set cnn = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.Recordset")

strPathtoTextFile = "C:\Databases"

    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & strPathtoTextFile & ";" & _
          "Extended Properties=""text;HDR=YES;FMT=Delimited"""
    rst.Open "SELECT * FROM accounts.csv WHERE client_id="_ 
         &  CInt(mailer.fields("client_id")), cnn
    n = 1
    nTotal = 0
    Do Until rst.EOF
        mailer.write("invoice" & n & ", ")
        mailer.write(rst("totalamount1") & ", ")
        mailer.write(rst("due"))
        if Len(rst("attach")) > 0 then mailer.attachFile(rst("attach"))
        mailer.write(vbCrLf)
        n = n + 1
        nTotal = nTotal + rst("amount")
        rst.MoveNext
    Loop
    rst.Close
    cnn.Close

Open in new window

Author

Commented:
Sorry. That did not work either.

Got a Syntax Error.

Author

Commented:
OK Think I am making some head way here.

I tried running my script outside the Mach5 mailer program using the following command from the Windows command line.

cscript test.vbs

Where test.vbs contains my original code. I get the same error from the command line as well as Mach5.

All the modifications highlighted by all users above get the same errors in the command line as they do in the Mach5 application. So I am sure now the problem is with my code.

Any suggestions?
Hi Experts,

Resolved it using the following code:
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

Dim n, nTotal 
Dim cnn, rst
    Set cnn = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.Recordset")

strPathtoTextFile = "C:\databases\"
strFile = "[csvfile.csv]"
username = mailer.fields("CustomField1")

    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & strPathtoTextFile & ";" & _
          "Extended Properties=""text;HDR=Yes;FMT=Delimited"""
rst.Open "SELECT * FROM " & strFile _
& " where Field2 = 'O' and Field1 = '" & username & "'", _
cnn, adOpenStatic, adLockOptimistic, adCmdText
    n = 1
    nTotal = 0
    Do Until rst.EOF
        mailer.write(rst("Field4") & "                        ")
        mailer.write(rst("Field3") & "                   $")
        mailer.write(rst("Field5") & "                            $")
        mailer.write(rst("Field6"))
        mailer.write(vbCrLf)
        n = n + 1
        nTotal = nTotal + rst("Field5")
        rst.MoveNext
    Loop
    rst.Close
    cnn.Close

Open in new window