The Old Joel on Software Forum: Part 3 (of 5)

Windows Script Help

I need to extract data from our SQL Server using VBScript instead of my regular tool. I'm not real familiar with VBScript but I figured there must be a million and one examples on the web. I got the following example straight from Microsoft but when I paste it into Test.vbs I get the following error:

Line: 1
Char: 9
Error: Expected end of statement

The code came from here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnado/html/msdn_workshp2.asp

Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection

' Open a connection by referencing the ODBC driver.
cnn.ConnectionString = "driver={SQL Server};" & _
"server=rgreennt;uid=sa;pwd=;database=pubs"
cnn.Open

' Find out if the attempt to connect worked.
If cnn.State = adStateOpen Then
MsgBox "Welcome to Pubs!"
Else
MsgBox "Sorry. No Pubs today."
End If

' Close the connection.
cnn.Close

WTF?
Monday, January 5, 2004

You can't declaire varible types in VBScript. Dim can be used to explicitly declaire varable names, but not types. Try changing the first line to just:

Dim cnn

and procede from there.

nalyd
Monday, January 5, 2004

In VBScript all variables are Variant type.
This means that accessing non-default interfaces on COM objects is a real pain (it can be done http://dev.luca.minudel.it/dynamiccast.htm has an object to do it)
It also means some stuff works, other stuff doesn't. Occasionally you may need to cast variables when calling COM objects which expect to see non-variants as their parameters.

i.e. if the component programmer wrote in VB
Public MyFunc(i as integer)
instead of
Public MyFunc(i as variant)

then you'd need to write
Dim x
MyClass.MyFunc(CInt(x))
To call it successfully (hope that makes sense)

The whole thing is a mess in this area. Most of the time it just works, but when it breaks you need quite a bit of knowledge of COM to figure out what VBScript is actually doing.

Peter Ibbotson
Monday, January 5, 2004

Another reason you can't declare a statement such as:

Dim cnn As ADODB.Connection

is because the script you're running never references the ADODB type library, so it never knows exactly where to find ADODB.Connection. Instead, since everything in VBScript is late bound, you have to use the CreateObject function, which I think would go like:

Dim cnn
Set cnn - CreateObject("ADODB.Connection")

You can see a list of available COM classes by going to the Registry and looking under the HKEY_CLASSES_ROOT node.

Ankur
Monday, January 5, 2004

>>
Try changing the first line to just:
Dim cnn
and procede from there.
<<

Thanks, that got me past line 1. Now I get "Class Not Defined: 'ADODB' " on line 2.

I guess MS must not have meant this sample code to be called from a script but rather from an actual VB app. Still, I thought VB/VBA/VDScript was supposed to be compatible.

WTF?
Monday, January 5, 2004

Thanks Ankur, you replied while I was composing my last post. I changed line 2 and it works now . . . sort of. I always get "Sorry. No Pubs today." message, meaning the connection is not open. I guarantee that I am using a valid userid/password combination. Is "cnn.State" another example of a command that works only in VB, not VBScript?

Last question: can anyone recommend a thorough VBScript tutorial that lists the syntax and describes some of the quirks like the one I just experienced? Would also appreciate pointers to a complete object hierarchy if one exists.

WTF?
Monday, January 5, 2004

"Still, I thought VB/VBA/VDScript was supposed to be compatible."

VB is to VBScript as Java is to JavaScript.

Brad Wilson (dotnetguy.techieswithcats.com)
Monday, January 5, 2004

>VDScript

An unfortunate typo but I'm beginning to feel it's appropriate. :-)

WTF?
Monday, January 5, 2004

Is variable declaration even necessary in VBScript? The scripts seem to work with or without the DIM statements.

WTF?
Monday, January 5, 2004

No, it's not.

Brad Wilson (dotnetguy.techieswithcats.com)
Monday, January 5, 2004

VDScript (VisualDialog Script) was pretty good while it lasted. I won't be surprised if a few people are still using it today.

Indian Developer in India
Monday, January 5, 2004

Visual Dialogscript was great.

Ged Byrne
Monday, January 5, 2004

iI believe t all depends if you put Option Explicit at the head of the script - as it does in VB proper.

Stephen Jones
Monday, January 5, 2004

Option explicit will force you to declare a variable in VBScript, but you still can't assign a type.

In VB you can write

Dim S as String
Dim V

V will be a variant.

In VBScript Dim S as String is not available, only Dim V.

VBScript is a subset of VB.

More information on variants can be found here:
http://www.mvps.org/vb/hardcore/html/ashorthistoryofbasictypes.htm

Ged Byrne
Monday, January 5, 2004

"Thanks Ankur, you replied while I was composing my last post. I changed line 2 and it works now . . . sort of. I always get "Sorry. No Pubs today." message, meaning the connection is not open. "

The problem is in the if statement:
If cnn.State = adStateOpen Then

var adStateOpen is 0 in your script.

The original example assumes you included the file with adState enums. In the include file adStateOpen is 1.

.
Monday, January 5, 2004

I had to reluctantly learn VBscript as PowerDesigner is scripted in VBScript and i do a lot of add-on work with powerdesigner.

I happily paid the $5 for devgurus reference even at the unfavourable exchange rate.

Karel
Monday, January 5, 2004

> VB is to VBScript as Java is to JavaScript.

No, no, no!

VBScript is a simple subset of the VB language -- one of our design criteria was that a VBScript program be a legal VB program (not vice versa, and there are some exceptions, but more or less that's the case).

JavaScript is not a subset of Java at all. It's an entirely different language with a superficially similar syntax. Remember, JavaScript was originally called LiveScript, and Netscape changed the name because (among other reasons) they believed that they could capitalize on the incredible hype surrounding Java.

Pretty much everything about Java and JavaScript that makes them interesting languages are different -- the type system, exception system, class system, libraries, compilation model... all radically different.

Eric Lippert
Wednesday, January 7, 2004

> Is variable declaration even necessary in VBScript?

Nope.

The question then is "if it is optional, why would you want to declare your variables"?

A few reasons come to mind:

1) You're worried about catching spelling mistake bugs, and have therefore turned on Option Explicit. This is pretty much the best reason. The earlier bugs are found, the better.

If "Option Explicit" is on, non-declared variables are run-time errors. That's as opposed to compile-time errors in VB. I've written an article on the reasons why here:

http://blogs.gotdotnet.com/ericli/PermaLink.aspx/35b975ac-c90d-4f3a-baa4-79c7bb4320f4

2) You're writing a big, complicated program and you want to use declarations as lexical cues to the maintenance programmer. If the variable is declared then the maintenance programmer knows whether its local, global, etc.

Note that I would recommend against using VBScript for this kind of programming-in-the-large in the first place. I wrote an article on why that's a bad idea, which can be found here:

http://blogs.gotdotnet.com/ericli/PermaLink.aspx/9437ee08-c618-4f09-a7da-e4390fbbecb9

So proceed with caution -- if the program is complex enough to require things like declarations, it is probably complex enough to benefit from features like types, information hiding, etc, and should therefore be implemented in C#, Java, VB, C++, etc.

3) You're a hard-core bit twiddling performance wonk. Declared variables are accessed entire microseconds faster than undeclared variables.

This is a TERRIBLE reason. If you are a hard code bit twiddling performance wonk, we have a language for you -- it's called "C".

An article I wrote on why this is a terrible reason can be found here:

http://blogs.gotdotnet.com/ericli/PermaLink.aspx/bfd7cd56-caf1-47e5-b94a-0c812b1be28e

I'm sure that was more information than you wanted, but I hope that helps.

Eric Lippert
Wednesday, January 7, 2004

Thanks for the info, Eric. I asked because I like to eliminate unnecessary code, even something as innocuous as a declaration that is ignored by the interpreter.

I'm having a tough time wrapping my brain around VBScript as I'm accustomed to strongly-typed, rigid languages like Delphi and C. VB is far too loosey-goosey (?) and random for my tastes but I have no choice in the matter.

Great article links by the way.

WTF?
Thursday, January 8, 2004

You're welcome.

And just to be nit-picky, the declaration is not ignored. Declarations do cause changes in the generated code, though in most cases the effects of the changes are pretty subtle.

Nit picks aside, the important thing to keep in mind is that most code is written for two audiences -- the runtime engine and the future maintenance programmers. The runtime engine doesn't really care about declarations, comments, formatting, etc. The maintenance programmers might!

And as for getting used to the weakly typed, late bound world of script languages -- you'll get used to it. The language is simple -- the challenging part is learning what object models to glue together to get stuff done.

Eric Lippert
Thursday, January 8, 2004

Can someone out there help me. I'm still fairly new to script and need the following:

I need a VBS script that will calculate the age of person when they input their age on a webpage. Any assistance appreciated.

Owen Michaels
Tuesday, February 10, 2004