# Use SQL Query to display data on map

**URL:** <https://community.thinkgeo.com/t/use-sql-query-to-display-data-on-map/4232>\
**Category:** MVC\
**Created:** [September 23, 2014, 7:30am UTC](https://community.thinkgeo.com/t/use-sql-query-to-display-data-on-map/4232 "2014-09-23T07:30:09Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Vivek6](https://community.thinkgeo.com/letter_avatar_proxy/v4/letter/v/f04885/32.png) [@Vivek6](https://community.thinkgeo.com/u/Vivek6)\
**Post date:** [September 23, 2014, 7:30am UTC](https://community.thinkgeo.com/t/use-sql-query-to-display-data-on-map/4232/1 "2014-09-23T07:30:09Z")

</div>

Hi,  
  
  
  
I&nbsp;need&nbsp;to&nbsp;use&nbsp;some&nbsp;SQL&nbsp;functions&nbsp;like&nbsp;LTrim,&nbsp;RTrim,&nbsp;Round&nbsp;etc.  
  
  
  
I&nbsp;am&nbsp;using&nbsp;below&nbsp;code:  
  
  
  
Dim&nbsp;dynamicOverlay&nbsp;As&nbsp;LayerOverlay&nbsp;=&nbsp;DirectCast(map.CustomOverlays("Layer"),&nbsp;LayerOverlay)  
Dim&nbsp;mssqlFeatureLayer&nbsp;As&nbsp;MsSql2008FeatureLayer&nbsp;=&nbsp;DirectCast(dynamicOverlay.Layers("Layer"),&nbsp;MsSql2008FeatureLayer)  
mssqlFeatureLayer.Open()  
Dim&nbsp;d&nbsp;As&nbsp;New&nbsp;DataTable()  
d&nbsp;=&nbsp;mssqlFeatureLayer.QueryTools.ExecuteQuery("SELECT&nbsp;ROUND(X,0)&nbsp;AS&nbsp;XCrd&nbsp;FROM&nbsp;Table")  
For&nbsp;Each&nbsp;row&nbsp;As&nbsp;DataRow&nbsp;In&nbsp;d.Rows  
&nbsp;mssqlFeatureLayer.ZoomLevelSet.ZoomLevel01.DefaultTextStyle&nbsp;=&nbsp;TextStyles.CreateSimpleTextStyle(row("XCrd").ToString(),&nbsp;"Arial",&nbsp;9,&nbsp;DrawingFontStyles.Italic&nbsp;Or&nbsp;DrawingFontStyles.Bold,&nbsp;GeoColor.GetRandomGeoColor(RandomColorType.All))&nbsp;  
&nbsp;Next  
  
mssqlFeatureLayer.ZoomLevelSet.ZoomLevel01.ApplyUntilZoomLevel&nbsp;=&nbsp;ApplyUntilZoomLevel.Level20  
mssqlFeatureLayer.Close()  
  
  
  
But&nbsp;it&nbsp;not&nbsp;display&nbsp;anything&nbsp;on&nbsp;map.  
  
  
  
So&nbsp;Is&nbsp;something&nbsp;wrong&nbsp;in&nbsp;this&nbsp;code?

---

<div class="post-metadata">

**Author:** ![Ethan](https://community.thinkgeo.com/user_avatar/community.thinkgeo.com/ethan/32/19964_2.png) [@Ethan](https://community.thinkgeo.com/u/Ethan)\
**Post date:** [September 25, 2014, 1:34am UTC](https://community.thinkgeo.com/t/use-sql-query-to-display-data-on-map/4232/2 "2014-09-25T01:34:55Z")

</div>

Hi&nbsp;Vivek,&nbsp;  
&nbsp;&nbsp;  
&nbsp;It&nbsp;looks&nbsp;you&nbsp;just&nbsp;set&nbsp;the&nbsp;text&nbsp;style&nbsp;for&nbsp;the&nbsp;layer.&nbsp;  
&nbsp;&nbsp;  
&nbsp;So&nbsp;if&nbsp;the&nbsp;text&nbsp;style&nbsp;for&nbsp;label&nbsp;has&nbsp;any&nbsp;problem&nbsp;you&nbsp;cannot&nbsp;get&nbsp;the&nbsp;result.&nbsp;  
&nbsp;&nbsp;  
&nbsp;Could&nbsp;you&nbsp;please&nbsp;make&nbsp;sure&nbsp;the&nbsp;items&nbsp;as&nbsp;below&nbsp;for&nbsp;find&nbsp;the&nbsp;problem:&nbsp;  
&nbsp;&nbsp;  
&nbsp;1.&nbsp;Add&nbsp;a&nbsp;break&nbsp;point&nbsp;and&nbsp;make&nbsp;sure&nbsp;the&nbsp;"d"&nbsp;contains&nbsp;the&nbsp;value&nbsp;you&nbsp;want.&nbsp;  
&nbsp;2.&nbsp;Set&nbsp;the&nbsp;style&nbsp;for&nbsp;your&nbsp;shape&nbsp;(AreaStyle,&nbsp;LineStyle&nbsp;or&nbsp;PointStyle)&nbsp;and&nbsp;see&nbsp;whether&nbsp;they&nbsp;are&nbsp;rendered&nbsp;correct.&nbsp;  
&nbsp;3.&nbsp;Double&nbsp;check&nbsp;whether&nbsp;you&nbsp;assign&nbsp;correct&nbsp;connect&nbsp;string,&nbsp;table&nbsp;name&nbsp;and&nbsp;column&nbsp;id.&nbsp;  
&nbsp;&nbsp;  
&nbsp;Regards,&nbsp;  
&nbsp;&nbsp;  
&nbsp;Don

---

<div class="post-metadata">

**Author:** ![Vivek6](https://community.thinkgeo.com/letter_avatar_proxy/v4/letter/v/f04885/32.png) [@Vivek6](https://community.thinkgeo.com/u/Vivek6)\
**Post date:** [September 25, 2014, 4:26am UTC](https://community.thinkgeo.com/t/use-sql-query-to-display-data-on-map/4232/3 "2014-09-25T04:26:52Z")

</div>

Hi&nbsp;Don.&nbsp;  
  
  
  
Thanks&nbsp;for&nbsp;Reply.&nbsp;  
  
  
  
I&nbsp;have&nbsp;checked&nbsp;above&nbsp;points&nbsp;you&nbsp;have&nbsp;mentioned&nbsp;and&nbsp;all&nbsp;it&nbsp;fine,&nbsp;but&nbsp;still&nbsp;not&nbsp;data&nbsp;display&nbsp;on&nbsp;map.&nbsp;  
  
  
  
If&nbsp;I&nbsp;have&nbsp;use&nbsp;simple&nbsp;X&nbsp;column&nbsp;&nbsp;which&nbsp;is&nbsp;from&nbsp;that&nbsp;table&nbsp;like&nbsp;below&nbsp;then&nbsp;it&nbsp;display&nbsp;data&nbsp;on&nbsp;map,  
  
For&nbsp;Each&nbsp;row&nbsp;As&nbsp;DataRow&nbsp;In&nbsp;d.Rows  
&nbsp;mssqlFeatureLayer.ZoomLevelSet.ZoomLevel01.DefaultTextStyle&nbsp;=&nbsp;TextStyles.CreateSimpleTextStyle( **“X”** ,&nbsp;“Arial”,&nbsp;9,&nbsp;DrawingFontStyles.Italic&nbsp;Or&nbsp;DrawingFontStyles.Bold,&nbsp;GeoColor.GetRandomGeoColor(RandomColorType.All))&nbsp;  
&nbsp;Next  
  
  
  
and&nbsp;If&nbsp;I&nbsp;have&nbsp;use&nbsp;**row(“XCrd”)**&nbsp;this&nbsp;then&nbsp;it&nbsp;can’t&nbsp;display&nbsp;data&nbsp;on&nbsp;map  
  
  
Is&nbsp;this&nbsp;due&nbsp;to&nbsp;for&nbsp;loop?&nbsp;or&nbsp;can&nbsp;I&nbsp;use&nbsp;customStyle&nbsp;for&nbsp;this?&nbsp;  
  
  
  
Can&nbsp;you&nbsp;give&nbsp;me&nbsp;demo&nbsp;related&nbsp;to&nbsp;this?

---

<div class="post-metadata">

**Author:** ![Troy](https://community.thinkgeo.com/letter_avatar_proxy/v4/letter/t/85f322/32.png) [@Troy](https://community.thinkgeo.com/u/Troy)\
**Post date:** [September 28, 2014, 6:42am UTC](https://community.thinkgeo.com/t/use-sql-query-to-display-data-on-map/4232/4 "2014-09-28T06:42:51Z")

</div>

Hi&nbsp;Vivek,  
  
  
  
If&nbsp;what&nbsp;I&nbsp;understand&nbsp;is&nbsp;correct,&nbsp;you&nbsp;want&nbsp;to&nbsp;format&nbsp;the&nbsp;text&nbsp;label&nbsp;and&nbsp;if&nbsp;the&nbsp;label&nbsp;is&nbsp;numeric&nbsp;type,&nbsp;then&nbsp;round&nbsp;it,&nbsp;if&nbsp;string,&nbsp;then&nbsp;we&nbsp;trim&nbsp;it.&nbsp;If&nbsp;yes,&nbsp;I&nbsp;think&nbsp;the&nbsp;textFormat&nbsp;and&nbsp;NumericFormat&nbsp;property&nbsp;should&nbsp;be&nbsp;fit&nbsp;for&nbsp;this&nbsp;case&nbsp;Following&nbsp;code&nbsp;snippet&nbsp;will&nbsp;show&nbsp;you&nbsp;how&nbsp;to&nbsp;round&nbsp;the&nbsp;‘X’&nbsp;column&nbsp;to&nbsp;0&nbsp;decimal:&nbsp;

> `Dim mssqlFeatureLayer As New MsSql2008FeatureLayer(connectionString, "layer", "ID")`  
> ` ``mssqlFeatureLayer.ZoomLevelSet.ZoomLevel01.DefaultTextStyle = TextStyles.CreateSimpleTextStyle("x", "Arial", 9, DrawingFontStyles.Italic Or DrawingFontStyles.Bold, GeoColor.GetRandomGeoColor(RandomColorType.All))`  
> `mssqlFeatureLayer.ZoomLevelSet.ZoomLevel01.DefaultTextStyle.TextFormat = "{0:F0}"`  
> ` ``mssqlFeatureLayer.ZoomLevelSet.ZoomLevel01.ApplyUntilZoomLevel = ApplyUntilZoomLevel.Level20`

  
As&nbsp;for&nbsp;the&nbsp;custom&nbsp;style,&nbsp;most&nbsp;of&nbsp;the&nbsp;cases&nbsp;are&nbsp;when&nbsp;we&nbsp;want&nbsp;to&nbsp;display&nbsp;multi-column&nbsp;labels&nbsp;for&nbsp;one&nbsp;feature.  
  
If&nbsp;there&nbsp;is&nbsp;any&nbsp;thing&nbsp;misunderstanding,&nbsp;please&nbsp;feel&nbsp;free&nbsp;to&nbsp;correct&nbsp;me.  
  
Thanks.  
  
Troy

---

<div class="post-metadata">

**Author:** ![Vivek6](https://community.thinkgeo.com/letter_avatar_proxy/v4/letter/v/f04885/32.png) [@Vivek6](https://community.thinkgeo.com/u/Vivek6)\
**Post date:** [September 29, 2014, 2:38am UTC](https://community.thinkgeo.com/t/use-sql-query-to-display-data-on-map/4232/5 "2014-09-29T02:38:25Z")

</div>

Thanks&nbsp;Troy,&nbsp;  
&nbsp;&nbsp;  
&nbsp;I&nbsp;need&nbsp;to&nbsp;do&nbsp;this&nbsp;using&nbsp;SQL&nbsp;query.&nbsp;I&nbsp;also&nbsp;need&nbsp;to&nbsp;use&nbsp;other&nbsp;SQL&nbsp;functions&nbsp;like&nbsp;Trim,&nbsp;Rtrim&nbsp;etc…&nbsp;  
&nbsp;So&nbsp;at&nbsp;present&nbsp;I&nbsp;am&nbsp;not&nbsp;able&nbsp;to&nbsp;display&nbsp;data&nbsp;on&nbsp;map&nbsp;using&nbsp;code&nbsp;I&nbsp;have&nbsp;post,&nbsp;so&nbsp;Is&nbsp;anything&nbsp;wrong&nbsp;in&nbsp;my&nbsp;code?&nbsp;

---

<div class="post-metadata">

**Author:** ![Troy](https://community.thinkgeo.com/letter_avatar_proxy/v4/letter/t/85f322/32.png) [@Troy](https://community.thinkgeo.com/u/Troy)\
**Post date:** [September 29, 2014, 9:56pm UTC](https://community.thinkgeo.com/t/use-sql-query-to-display-data-on-map/4232/6 "2014-09-29T21:56:05Z")

</div>

Hi&nbsp;Vivek,  
  
  
  
I&nbsp;think&nbsp;you&nbsp;can&nbsp;try&nbsp;to&nbsp;register&nbsp;the&nbsp;Formatting&nbsp;event&nbsp;to&nbsp;custom&nbsp;your&nbsp;label&nbsp;string,&nbsp;the&nbsp;below&nbsp;codes&nbsp;show&nbsp;it:

> `Dim customStringStyle As TextStyle = TextStyles.Interstate3("CNTRY_NAME")`  
> `customStringStyle.Formatting += customStringStyle_Formatting`  
> `Dim customNumStyle As TextStyle = TextStyles.Interstate3("POP_CNTRY")`  
> `customNumStyle.XOffsetInPixel = 30`  
> `customNumStyle.YOffsetInPixel = 30`  
> `customNumStyle.Formatting += customNumStyle_Formatting`  
> &nbsp;  
> `Dim connectString As String = "User ID=thinkgeo;Password=123456789;Data Source=192.168.0.80;Initial Catalog=InternalDB;"`  
> `Dim sql2008Layer As New MsSql2008FeatureLayer(connectString, "cntry02", "ID")`  
> `sql2008Layer.ZoomLevelSet.ZoomLevel01.CustomStyles.Add(AreaStyles.Country2)`  
> `sql2008Layer.ZoomLevelSet.ZoomLevel01.CustomStyles.Add(customStringStyle)`  
> `sql2008Layer.ZoomLevelSet.ZoomLevel01.CustomStyles.Add(customNumStyle)`  
> &nbsp;  
> `sql2008Layer.ZoomLevelSet.ZoomLevel01.ApplyUntilZoomLevel = ApplyUntilZoomLevel.Level20`  
> &nbsp;  
> `…`  
> `Private Sub customNumStyle_Formatting(sender As Object, e As FormattingPositionStyleEventArgs)`  
> `    ``e.Text = Math.Round((Decimal.Parse(e.Text)), 2) + String.Empty`  
> `End Sub`  
> &nbsp;  
> `Private Sub customStringStyle_Formatting(sender As Object, e As FormattingPositionStyleEventArgs)`  
> `    ``e.Text = e.Text.Trim()`  
> `End Sub`

  
In&nbsp;your&nbsp;codes,&nbsp;we&nbsp;can’t&nbsp;use&nbsp;the&nbsp;loop&nbsp;for&nbsp;the&nbsp;style&nbsp;setting&nbsp;and&nbsp;the&nbsp;TextStyle’s&nbsp;constructor&nbsp;parameter&nbsp;columnname&nbsp;also&nbsp;can’t&nbsp;be&nbsp;custom.  
  
Please&nbsp;let&nbsp;us&nbsp;know&nbsp;if&nbsp;it&nbsp;is&nbsp;fit&nbsp;for&nbsp;you.  
  
Thanks,  
  
Troy