Executing an SQL with a Long in it.

I'm having a bit of bother with some of my code, also this concerns ASP, VB, and SQL so I thought since it is mainly a VB problem I'd post it in the VB forum.

I have an ASP page that determines whether you are wishing to search by Name or Company.
The FindByX functions are in a DLL and are exactly the same except the variables that are passed are a String for the name (like "John Doe") or a Long (like "5") and the SQL for the search.

Here's the code that I use for determining which to use:



And the code for the FindByCompanyID - remember that FindByName is exactly the same except for the SQL and parameters.



However it doesn't work.
As it stands it returns an "Object variable or With block variable not set" error on Line 74 of the ASP.

A quick search on Google reveals that this occurs if: "You define a public variable in the COM component" and "The variable is of the Object type" and "You reference this COM component by using late binding."

Okay, my VB skills aren't that 1337 but I do know this: There are no public variables in my DLL. There are 3 methods and no properties (this is a simple test case to prove I can do what the prospective employer asks). The variable is of object type and I do bind it late. Switching to early binding does nothing to remedy the situation.

So I thought it might be that I might be passing it as a String to the function. So I wrap the parameter with a CInt() so the line becomes:

This gives me the same "Object variable or With block variable not set" that I had before. Okay, so the ASP isn't broken - must be something to do with the VB code.

Let's look at the function: All variables are Dim'd to begin with so that isn't it. Both early and late binding doesn't change anything. Numbers aren't wrapped by quotes when writing SQL statements but - hey - I'm stuck so I will change it. Still nothing.

So I am stuck and ask why is this? Is it my ASP or VB? Also I am not allowed to change the parameters.

If you want/need more information don't hesitate to ask.
Thanks, G.

I converted the CompanyID from a Long to a String and added the additional single quote to encase the string and it works fine. However the specs require it to be a Long.

Therefore I can safely (99.9%) assume that it is something to do with my VB function. But what?

Thanks.

Edit: Just realised that if it works as a string then CompanyID must be a string in the database. However I have it open in front of me and it is most definately not. An Int with a precision of 4.

Try using & for concatenation in your code, instead of +

Thanks - that works. Now that raises a couple of questions:

Firstly why would it work with the string as + but with numbers with &?

Secondly when I was using strings didn't SQL Server fire off an error telling me I am trying to insert a string into a number (or why did it convert it)?

Thanks for your help. On to the next problem(s):-)

The + operator will try to do math on the values before concatenating, where the & is only a string concatenation operator.