crystal reports SQL Command
I have an sql command that works, but when I try to add parameters it doesn't. I get Failed to retrieve data from Database. Invalid Column Name E. I tried putting quotes around the employee # when I entered it, Then I got invalid column name E-004525 (the no I entered.
I have the parameter created as EmpNo String. I intended to also create a paramter for fromYear, I had already tried it with both, but it was not working so I was trying one at a time. This is something I have done before without a problem. This is Crystal XI.
from-Crystal-Command.txt
I have the parameter created as EmpNo String. I intended to also create a paramter for fromYear, I had already tried it with both, but it was not working so I was trying one at a time. This is something I have done before without a problem. This is Crystal XI.
from-Crystal-Command.txt
ASKER
It is a command. I am adding the parameter with Create in the command window.
When you save and close the command, it asks for values for the parameter, does that work?
How did you use the parameter in the command?
mlmcc
How did you use the parameter in the command?
mlmcc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK, I will try that. Any idea as to why this is suddenly an issue. I have used commands with parameters quite a lot, and not had this problem.
ASKER
It worked. But I am still wondering why tis was an issue, since I have used commands with paramters before without having to do the quotes.
Is the field a text field of numbers?
If so it was probably interpreted as comparing a string to a number
'123' = 123
mlmcc
If so it was probably interpreted as comparing a string to a number
'123' = 123
mlmcc
It depends on the type of value in the parameter, and how you're using it.
If you don't put the quotes around a string parameter and you enter a number, then you won't get an error, but the parameter may or may not work.
For example, if you enter 123, SQL will just see that as a number. If you're comparing that to a string column, SQL will just do an implicit conversion, so you won't get an error, but you may or may not get the results that you're looking for. If SQL converts the column to a number, then 123 would match rows with values like '123', '123.0' and '0123' in that column. OTOH, if SQL converts the number from the parameter (123) to a string ('123'), then that would only match the rows that had '123' in that column (and not '123.0' or '0123').
But if you don't put quotes around the parameter and you enter something like E-004525, you end up with
set @EmpNo = E-004525
and SQL doesn't know what E-004525 is.
James
If you don't put the quotes around a string parameter and you enter a number, then you won't get an error, but the parameter may or may not work.
For example, if you enter 123, SQL will just see that as a number. If you're comparing that to a string column, SQL will just do an implicit conversion, so you won't get an error, but you may or may not get the results that you're looking for. If SQL converts the column to a number, then 123 would match rows with values like '123', '123.0' and '0123' in that column. OTOH, if SQL converts the number from the parameter (123) to a string ('123'), then that would only match the rows that had '123' in that column (and not '123.0' or '0123').
But if you don't put quotes around the parameter and you enter something like E-004525, you end up with
set @EmpNo = E-004525
and SQL doesn't know what E-004525 is.
James
How are you adding the parameter?
mlmcc