| Author |
Topic |
|
stathis30_2000
Starting Member
23 Posts |
 Posted - 2007-08-27 : 04:57:18
|
Hi,
I am facing a strange problem, I have created a CLR UDT called 'rectangle'. My udt has four properties : lonlow, lonhigh, latlow ,lathigh which are double numbers. When I try to execute the following query from the management studio everything work fine :
select * from grid where RECT.LonLow>0
I try to execute the same query from a CLR stored procedure but an exception occurs. The exception message says that a varchar cannot be converted to float. My code is :
Dim getRect As New SqlCommand()
getRect.Connection = conn
getRect.CommandType = CommandType.Text
Dim Adapter As New SqlDataAdapter("get_data", conn)
getRect.CommandText = "select * from grid where RECT.LonLow <='" & Convert.ToString(point_obj.X) & "'"
Adapter.SelectCommand = getRect
Adapter.Fill(dsRectangle, "rects")
Thank you
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-27 : 05:17:51
|
quote:
Originally posted by stathis30_2000
When I try to execute the following query from the management studio everything work fine :
select * from grid where RECT.LonLow>0
I doubt that.
In your query you SELECT FROM the GRID table using the RonLow Column greater than 0.
BUT... you have prefixed the LonLow column with the RECT table name.
E 12°55'05.25"
N 56°04'39.16" |
 |
|
|
stathis30_2000
Starting Member
23 Posts |
Posted - 2007-08-27 : 05:45:53
|
RECT is not a table, is my CLR UDT column. My udt has four properties like :
Public Property LonLow() As Double
Get
Return (Me.lon_low)
End Get
Set(ByVal value As Double)
Me.lon_low = value
End Set
End Property
So RECT.LonLow just returns the property LonLow of the rectangle record .
Thanks |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-27 : 06:09:45
|
Also there is a difference between first query you write
select * from grid where RECT.LonLow>0
No single quotes there. But here you have!
getRect.CommandText = "select * from grid where RECT.LonLow <='" & Convert.ToString(point_obj.X) & "'"
Drop the single quotes, like this
getRect.CommandText = "select * from grid where RECT.LonLow <= " & Convert.ToString(point_obj.X)
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
stathis30_2000
Starting Member
23 Posts |
Posted - 2007-08-27 : 06:28:34
|
Quotes work fine in management studio. I guess that it does not take RECT.LonLow as a value. It takes it as a string.
If a drop the quotes then an incorrect syntax error occurs.
I appreciate your help !
|
|
|
|
stathis30_2000
Starting Member
23 Posts |
Posted - 2007-08-27 : 06:59:45
|
| I think the problem is the function Convert.ToString(point_obj.X) ... I replaced it with a single value and it worked fine.. I cannot explain this problem! I have used it several times .. |
|
|
|
stathis30_2000
Starting Member
23 Posts |
Posted - 2007-08-27 : 07:22:16
|
After hours of trying I found the cause I think..It looks more like a bug to me, I used Convert.ToString(test).Replace(",", ".").
It seems that Convert.ToString(Double) produces something like 12,34 instead of 12.34 ...
|
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-08-27 : 09:43:51
|
convert.ToString takes a decimal based on your system locale.
so if you use a , as a decimal separator it will use that.
of course sql server only takes . as a decimal separator.
this is why it's good to use paramteres and not just contencate sql strings.
_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-27 : 09:54:39
|
The fastest way to solve these kind of problems is
1) DEBUG.PRINT getRect.CommandText to verify the expected query.
2) Ask for help here at SQLTeam if previous step is not obvious.
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
|
CLR UDT query