2. ASPRunner.NET General questions
  3. [SOLVED]  Why doesn't this work (SQL variables)?
This topic is locked
[SOLVED]

 Why doesn't this work (SQL variables)?

 		9/16/2019 10:50:17 AM
ASPRunner.NET General questions
Pete K author

I have a table, [RequestOral] (actually a custom query) with a field [Language] and another field, [Interpreter]. Interpreter is configured as a lookup field, related to the table [Interpreters]. That part works fine. When the user selects an interpreter from the DDL, I want it to display only interpreters where [Interpreter].[Language] = [RequestOral].[Language] or 44. Here is how I coded the where clause for the lookup wizard:

(Language = :Language) OR (Language = 44)


The problem is that [size="2"]:Language is not getting replaced with the appropriate field value from RequestOral. It's actually yellow-screening on that page and when I look at SQL Profiler I see that the where clause getting passed to SQL Server includes this: [/size]

(Language = ) OR (Language = 44)


I even tried qualifying the SQL variable by using both the custom view ([size="2"]:[/size][size="2"]RequestOral.[/size][size="2"]Language[/size][size="2"]) and the underlying table ([/size][size="2"]:Requests.Language[/size][size="2"]). That didn't work either.[/size]
[size="2"]My fear is that this is a situation where SQL variables won't work on a custom view page. I sure hope that isn't the case, or I'm going to have to recreate a bunch of pages. But maybe I'm doing something wrong. [/size]
[size="2"]EDIT: I finally hit on the idea of recreating the scenario on a page based on the table, Requests, and the result was the same. So the custom query is not the issue. Either this is a bug or (more likely) I'm doing something wrong...
[/size]

[size="2"]Ideas? [/size]
admin 9/16/2019

Table prefix is not required but SQL variables are case sensitive so maybe you need to use :language instead of :Language?
Pete K author 9/16/2019



Table prefix is not required but SQL variables are case sensitive so maybe you need to use :language instead of :Language?


Thanks for the suggestion, Sergey, but the field is capitalized in the table.

Pete K author 9/16/2019

Hey Sergey, I think this is specific to version 10.2. Just for fun, I opened a previous version of the project in 10.1, added the where clause with the SQL variable and it works exactly as expected.
Pete K author 9/25/2019

I accidentally marked this solved but I meant to mark a different thread. This is still an issue for me. I will submit a support ticket.
Pete K author 9/26/2019

I did submit this to support and this is the response I got:

thank you for pointing me to this problem.

We'll make SQL Variables apply on the List page as well,and we'll make it ignore SQL errors if there are any.
The updated ASPRunner will be available next week, withthe release of ASPRunner.NET 10.3
In the mean time change your WHERE expression this way toget your page working:
--------------------------
CONVERT( varchar, Language ) = ':Language'


That workaround does indeed work. Once again the support team comes through.