CLR UDT query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

CLR UDT query

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"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources