SqlException (0x80131904) in SqlMap

1

I have this sql mapping to an xml file where the sql query is.
In the statement, I needed to get an additional column value from another unrelated table. Like tables A and B are not related (so no way of joining).
Therefore, my statement is like:

SELECT ax, ay, az, bm
FROM A, B
WHERE bn = 'p'

It runs fine in SQL query window.
But when I run it from my program - I get this error:
SqlException (0x80131904) in SqlMap: Incorrect syntax near the keyword 'WHERE'.

Anybody any ideas?
Thanks.

2

Looks like the problem is with the dynamic mapping of the sql statement. I can't directly put the where with the statement.
I need to use mapping like:

<dynamic prepend="WHERE">
<isEqual prepend="AND" property="StatusCode" compareValue="O">
    StatusCode = 'O'
	</isEqual>
</dynamic>

But I still did not figure out the right syntax. Working on it.

3

You could try this as it's equivalent to what you have and maybe the other program will like it better:
SELECT ax, ay, az, bm
FROM A
CROSS JOIN B
WHERE bn = 'p'

But I'm not convinced that a cartesian product is what you want here. How should the data line up if the tables aren't related?

4

We need to grab this one piece of information from the other table (a date which will be same for each row), so it doesn't return any extra records. But the problem is - because we are using dynamic sql, it's just not simple to put a simple where clause, because the field from the other table is not a property of the class.

5

Can you load the value into a variable first?

declare @date datetime

select @date = datecolumn
from table1

select column1, column2, @date
from table2
where yadayadayada

6

Probably not, as the where clause needs to be here which I am having problem with putting into the dynamic sql statement. But will let you know.

7

Yeah, that ran into complication - so I found simpler way to add the condition at the end of others.
Thanks Tara.

1 Like