1

I'm working on a quick ASP Classic form that calls a TSQL proc. The first one that I created works great since it doesn't send any values in. Now I'm working on my second one and it looks a bit like this:

exec update_allocation(@Anum='164360',@mTeam='5',@Team='9',@Perc='14',@Bill=140000,@Mons=164360)

Also tried as: 

exec update_allocation('164360','5','9','14',140000,164360)

First one gives me an error of:

Microsoft OLE DB Provider for SQL Server error '80040e14' Incorrect syntax near '@Anum'.

The second gives me:

Microsoft OLE DB Provider for SQL Server error '80040e14' Incorrect syntax near '164360'.

I'm not sure what to make of these errors. The issue must be the parameters, but not sure how they should be sent in.

Fuzzy
  • 3,810
  • 2
  • 15
  • 33
yoelbenyossef
  • 393
  • 1
  • 7
  • 26
  • Fix your formatting. Don't be sloppy! – Amit Feb 29 '16 at 21:00
  • Why are you sending in strings for some parameters and numerics for others? The first error must come from the stored procedure code itself since @subsnum is not one of your input parameters (assuming the proc signature is correct). The second error could also be from the proc code. – squillman Feb 29 '16 at 21:12
  • 1
    First - use exec update_allocation @Anum='164360',@mTeam='5',@Team='9',@Perc='14',@Bill=140000,@Mons=164360 ( i.e. without parentheses ) – DimaSUN Feb 29 '16 at 21:15
  • Second - check proc code near to @subsnum ( or send piece of code here ) – DimaSUN Feb 29 '16 at 21:20
  • You should read about and utilize parameterized queries. This like it is potentially vulnerable to sql injection. Here is an example of how to do this in your code. http://stackoverflow.com/questions/7654446/parameterized-query-in-classic-asp – Sean Lange Feb 29 '16 at 21:35
  • @SeanLange While I agree these are just T-SQL calls no Classic ASP code is shown so we are just hypothesising that it's vulnerable to SQL Injection, but it is definitely a likely possibility if this is the call used by ADODB. – user692942 Mar 01 '16 at 10:13
  • @yoel did any of the suggestions help? – user692942 May 04 '16 at 13:42

1 Answers1

2

The comments are correct but here is a bit of explanation as to why that is the case.

Both errors;

Microsoft OLE DB Provider for SQL Server error '80040e14' Incorrect syntax near '@Anum'.

and

Microsoft OLE DB Provider for SQL Server error '80040e14' Incorrect syntax near '164360'.

are actually quite descriptive and pinpoint where the issue is. Usually in a situation like this an error is highlighting a problem just before the word or phrase in an error description in these cases the @Anum parameter and the 164360 value. If we follow this through the previous character in both cases is an open bracket (.

Unlike functions in T-SQL, Stored Procedures do not require brackets around their parameters and doing so will raise an error (like the ones above) to correct this remove the brackets from both statements.

With named parameters;

EXEC update_allocation @Anum='164360', @mTeam='5', @Team='9', @Perc='14', @Bill=140000, @Mons=164360

Without named parameters;

EXEC update_allocation '164360', '5' ,'9' ,'14' ,140000 ,164360

Either method is acceptable syntax but personally I find it more useful to use the named parameter approach as that allows you to pick and choose what parameters to pass and even exclude ones that have defaults. You can still do this with the nameless approach but ordinal position of the values is more important.

@sean-lange makes a valid point here about SQL Injection vulnerability, consider using the ADODB.Command object to execute stored procedures rather then just calling the Execute() method on a ADODB.Connection object.

Here is an example of calling Stored Procedures using Classic ASP and the ADODB.Command object.

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