5

I'm trying to secure some legacy code written in what I guess is VB or asp(Not really sure if there is a difference). When I try to execute the statement the page gets an internal server error. I'm convinced this is a result of the connection but I don't know the language well enough to know how to troubleshoot it.

What I know of the language and the ADODB library has come from W3Schools documentation and this post.

Here is the code I have written (Identifying information redacted)

SET Conn=server.CreateObject("adodb.connection")
Conn.Open "Provider=sqloledb;SERVER=I;DATABASE=Hate;UID=My;PWD=Life;"

SET countCmd = createobject("adodb.command")
countCmd.ActiveConnection = Conn
countCmd.commandText = "SELECT COUNT(*) FROM [table1] WHERE FY=@fy"

countCmd.Parameters.Append countCmd.createparameter("@fy", 200, 1, 255, fy)
SET pcount = countCmd.Execute() 'This is where the error happens

My end goal is not just to get a count from this table but to understand th adodb library well enough that I could continue parameterizing all of the queries in this legacy code base that need it.

I appreciate any help and would love a detail explanation.

EDIT

I wish I could accept both of these answers as the accepted answer because together I think they are the perfect answer. I ended up using both so upvote these guys por favor.

Community
  • 1
  • 1
HopAlongPolly
  • 1,347
  • 1
  • 20
  • 48
  • That is not VB.NET code. Not likely asp.net either. Could it be classic asp? – Ňɏssa Pøngjǣrdenlarp Jul 10 '16 at 19:46
  • It very well could be. I've worked here for 5 years and never seen this stuff till today. – HopAlongPolly Jul 10 '16 at 19:50
  • 2
    It's Classic ASP and the language is VBScript. VBScript and VB are different just as Java and Javascript are different. At first glance I think your query. The first thing which strikes me is that your query line should be `countCmd.commandText = "SELECT COUNT(*) FROM [table1] WHERE FY=?"` ie replace "@fy" with a "?". I always recommend following the steps on this page for debugging Classic ASP http://www.chestysoft.com/asp-error-messages.asp – John Jul 10 '16 at 20:27
  • Good call on switching to a ```?``` but when I try to reference pcount now I get errors. – HopAlongPolly Jul 10 '16 at 20:28
  • 1
    I assume "fy" is a variable which you've defined earlier in your code – John Jul 10 '16 at 20:30
  • Yea. I'm starting to think maybe it's how I use the recordset instead. – HopAlongPolly Jul 10 '16 at 20:34
  • ASP hosts VBScript. Internet Explorer hosts VBScript. Windows Scripting Host also hosts VBScript. It takes a minimum of 3 lines of code to add VBScript to a program as a macro language (see `createObject("MSScriptControl.ScriptControl",SC)`. Also VBScript can be pasted in VB/VBA. It is also pastable into VB.NET with minor changes (mainly adding some brackets). The thing to keep in mind there are objects provided by the hosts (Wscript object, Application objects, Internet Explorer objects, etc) and the language. Hosts top level objects are GLOBAL. –  Jul 10 '16 at 22:29
  • @Noodles what are you on about, is any of this applicable to the OPs question? – user692942 Jul 10 '16 at 22:33
  • From his/her post. *I guess is VB or asp(Not really sure if there is a difference).* And the comment *VBScript and VB are different*. Legal VBScript is legal VB (which is a host for the VBA language). –  Jul 10 '16 at 22:46
  • 1
    @Noodles I don't agree, John is spot on [they are different](https://msdn.microsoft.com/en-us/library/ms970436.aspx). Both VBA and VBScript are both based on the VB programming language but each is different, nor more so then VBScript which is a scripting language. It may share VB syntax but there are key differences such as VBScript is typeless while VBA and VB are not. If anything VB and VBA have more in common with each other then they do with VBScript. You just confuse things further by going on about IE and ScriptControl. They are all subsets of VB that is where their similarities end. – user692942 Jul 11 '16 at 02:16
  • VB hosts VBA. VBA can also be typeless. –  Jul 11 '16 at 03:29
  • 1
    @Noodles it's a compiler option, yes. But not recommended, where as VBScript is typeless by default and can be nothing else. To be honest this conversation is pointless and was from the very start. – user692942 Jul 11 '16 at 07:22
  • It's not a compiler option - VBS can be pasted without changes. When VBA was first released MS said you should only do typeless programming (the way of the future etc) and the typing was for legacy code. It's a core part of it that VBS actually realises the design goals of VBA. Everyone laughed at MS going "but I don't want slow code". They changed their tune within a month. –  Jul 11 '16 at 07:39
  • @Lankymart. I.ve been using basic for 42 years and 5 months. –  Jul 12 '16 at 02:36
  • @Noodles....Good lord that language is older than I am....why are we still using that. – HopAlongPolly Jul 12 '16 at 02:40
  • 1
    It's loosely based on Fortran - a scientific programming language. It's easy to learn. It allows one to concentrate on solving real world problems (ie accounting or science) without having to deal with how CPUs work and how the computer is architectured (as do most modern languages to varying degrees). Todays basic is very different to yesterdays basic though the very basics of doing something is the same. **Interesting Fact**. All Microsoft Basics from the 1970s have the same error code written by Bill Gates and Paul Allen. `5 Invalid procedure call or argument` is one of Bill's legacies. –  Jul 12 '16 at 03:47
  • @Noodles thanks for the history lesson, but how is any of this relevant to the question? – user692942 Jul 12 '16 at 08:26
  • I reapeat - From his/her post. I guess is VB or asp(Not really sure if there is a difference). And the comment VBScript and VB are different. Legal VBScript is legal VB (which is a host for the VBA language). –  Jul 12 '16 at 09:17

2 Answers2

4

To use named parameters you need to enable NamedParameters.

countCmd.NamedParameters = True

But there's a limitation that affects you.

In Adodb.Command, named parameters only work with stored procedures.

For an ordinary query like yours, you need to use question mark placeholders instead of named ones.

Then you can omit or specify a rubbish value for first parameter of the CreateParameter method.

countCmd.NamedParameters = False
countCmd.CommandText = "SELECT COUNT(*) FROM [table1] WHERE FY=?"
countCmd.Parameters.Append countCmd.createparameter(, 200, 1, 255, fy)
'countCmd.Parameters.Append countCmd.createparameter("@blablabla", 200, 1, 255, fy) 'this also works
Kul-Tigin
  • 16,728
  • 1
  • 35
  • 64
4

When using a CommandType of adCmdText the placeholder expected by ADODB is ? and trying to passed named parameters like @fy in the CommandText will fail. It is an unfortunate failing in ADODB that

countCmd.NamedParameters = True

only works with a CommandType of adCmdStoredProc and only with certain providers.

However there is a simple workaround for SQL Server (and possibly other providers depending on what they support) which is to build the named parameters in the CommandText like so;

countCmd.commandText = _
    "DECLARE @fy AS VARCHAR(255);" & vbCrLf & _
    "SET @fy = ?;" & vbCrLf & _
    "SELECT COUNT(*) FROM [table1] WHERE FY=@fy;"

Useful Links

Community
  • 1
  • 1
user692942
  • 16,398
  • 7
  • 76
  • 175