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.

Improve this question
18
  • 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 chestysoft.com/asp-error-messages.asp Commented Jul 10, 2016 at 20:27
  • 1
    I assume "fy" is a variable which you've defined earlier in your code Commented Jul 10, 2016 at 20:30
  • 1
    @Noodles I don't agree, John is spot on they are different. 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. Commented Jul 11, 2016 at 2:16
  • 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. Commented Jul 11, 2016 at 7:22
  • 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. Commented Jul 12, 2016 at 3:47

2 Answers 2

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

3

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

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.