Last post Nov 13, 2013 09:28 AM by delux269
Nov 12, 2013 04:25 PM|delux269|LINK
I have an ASP.NET page with C# code behind. All I ahve is a simple SQL statement in a SQLDataSource, with two parameters, one that is an integer and one that is a string. The integer parameter works find but I can't do anything to get the string parameter
to work. I have tried using the equals sign "=" and the LIKE keyword with the % sign after still with no success. I know the query is correct as it is simple and if I hard code in a value for the string, the query executes successfully. Any ideas? Here
is the simple SQL query inside the SQLDataSource:
SELECT * FROM table1 WHERE ProjectId = @ProjectId and EmployeeId = @EmployeeId
(EmployeeId is the string value that is giving me trouble)
Nov 12, 2013 04:42 PM|Boris Pazin|LINK
Do you get any error?
Are you sure that @EmployeeID has value?
What is type of EmployeeID field in database?
Nov 12, 2013 04:44 PM|smirnov|LINK
If you cannot make it working with the parameter, try to hardcode the value which should work
e.g. WHERE ProjectId = @ProjectId and EmployeeId = 'john'
and see if it works.
It might be that either your data or parameter has leading/trailing spaces in it, so when you try to search for 'john ' nothing works. In this case you can try to use
WHERE ProjectId = @ProjectId and EmployeeId = LTrim(RTrim(@EmployeeId))
Nov 12, 2013 06:04 PM|oned_gk|LINK
Nov 12, 2013 11:21 PM|delux269|LINK
Nov 12, 2013 11:59 PM|delux269|LINK
Nov 13, 2013 12:21 AM|NPurohit|LINK
You have to match the datatypes of parameters in your query, if your parameter's datatype is int then you can only match int value with it.
Nov 13, 2013 01:56 AM|smirnov|LINK
The column employeeid is of type nchar(10), so any vLues not having a length of 10 will contain trailing white spaces. Knowing this, I used the RTRIM() function with no success
Where did you use RTRIM()?
If employeed is nchar(10) and has values like
and @EmployeeId is
Then rtrim must be in the query
SELECT * FROM table1 WHERE ProjectId = @ProjectId and RTRIM(EployeeId) = @EmployeeId
Nov 13, 2013 02:27 AM|Altaf_Ksa|LINK
Please try to use below mention pattern. It will work as its working on my side.
SELECT * FROM EMPLOYEE WHERE employeeid LIKE '%'+@employeeid+'%'
Nov 13, 2013 02:35 AM|oned_gk|LINK
SELECT * FROM table1 WHERE ProjectId = @ProjectId and CAST(EployeeId as INT) = CAST(@EmployeeId as INT)
Nov 13, 2013 08:35 AM|delux269|LINK
I cannot cast the field EmployeeId nor the parameter @EmployeeId as an INT type, because the variable can contain letters as well as numbers.
Nov 13, 2013 08:41 AM|smirnov|LINK
Nov 13, 2013 08:42 AM|delux269|LINK
Whenever I hard code the value in for @EmployeeId in the query it works fine whether I simply use @employeeid, '%' + @employeeid + '%' or RTRIM(employeeid) = @employeeid. So I believe the issue is coming from the value of the parameter. The value of the
parameter comes from a HiddenField element, which give the SqlDataSource the Value attribute for the parameter, which is by definition of String type, so I am confused as to what the issue could be. I also performed a Trim() on the HiddenField after its value
is set so that there are no spaces before or after, which still does not work.
Nov 13, 2013 08:49 AM|delux269|LINK
Tried this with no success. This is becoming very baffling. I believe the issue lies with the value of the parameter @EmployeeId somehow, since when I hard code in the value for @EmployeeId it works just fine.
Nov 13, 2013 09:12 AM|smirnov|LINK
If you think it is due to parameter value then it makes no sense to discuss sql query.
Share your code of SqlDataSource and related parameters.
Also using nchar(10) for values that are less then 10 in length is a kind of bad idea.
Nov 13, 2013 09:28 AM|delux269|LINK
EmployeeId value which would set that EmployeeId value into a HiddenField element. Turned out my issue was that I was using the Str() function when passing the value. The value was already of string type so I removed the Str() function, and now the query
works properly. Thank you to everyone who has contributed on this issue.