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>0I 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 = conngetRect.CommandType = CommandType.TextDim Adapter As New SqlDataAdapter("get_data", conn)getRect.CommandText = "select * from grid where RECT.LonLow <='" & Convert.ToString(point_obj.X) & "'"Adapter.SelectCommand = getRectAdapter.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 writeselect * from grid where RECT.LonLow>0No 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 thisgetRect.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 1980blog: 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" |
 |
|
|