# Adding features with MSSql2008FeatureLayer

**URL:** <https://community.thinkgeo.com/t/adding-features-with-mssql2008featurelayer/1242>\
**Category:** WinForms\
**Created:** [May 20, 2010, 3:44pm UTC](https://community.thinkgeo.com/t/adding-features-with-mssql2008featurelayer/1242 "2010-05-20T15:44:01Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Charles](https://community.thinkgeo.com/letter_avatar_proxy/v4/letter/c/6f9a4e/32.png) [@Charles](https://community.thinkgeo.com/u/Charles)\
**Post date:** [May 20, 2010, 3:44pm UTC](https://community.thinkgeo.com/t/adding-features-with-mssql2008featurelayer/1242/1 "2010-05-20T15:44:01Z")

</div>

I&nbsp;am&nbsp;having&nbsp;trouble&nbsp;adding&nbsp;features&nbsp;to&nbsp;an&nbsp;MsSql2008FeatureLayer.&nbsp;&nbsp;The&nbsp;code&nbsp;appears&nbsp;to&nbsp;run&nbsp;properly,&nbsp;but&nbsp;the&nbsp;record&nbsp;is&nbsp;not&nbsp;added&nbsp;to&nbsp;the&nbsp;feature&nbsp;table.&nbsp;&nbsp;Are&nbsp;there&nbsp;any&nbsp;known&nbsp;issues&nbsp;here?

&nbsp;

`   MsSql2008FeatureLayer MapLayer = new MsSql2008FeatureLayer(MapConnString, Tablename, "ID");   MapLayer.Srid = 0;   MapLayer.Open();   Dictionary<string, string> DictionaryValues = new Dictionary<string, string>(feature.ColumnValues);   DictionaryValues["Id"] = NextGenProject.DataInterface.GetNextID(MapLayer.TableName).ToString();   DictionaryValues["FeatureType"] = genFeature.GetWellKnownType().ToString();                   if (genFeature.GetWellKnownType() != WellKnownType.Point)   {       DictionaryValues["Angle"] = "";       DictionaryValues["FeatureText"] = "";       DictionaryValues["RScale"] = "";       DictionaryValues["DBText"] = "";       DictionaryValues["PSymbol"] = "";       DictionaryValues["TextJust"] = "";   }   DictionaryValues["ZoomLevel"] = i.ToString();   MapLayer.EditTools.BeginTransaction();   MapLayer.EditTools.Add(genFeature, DictionaryValues);   MapLayer.EditTools.CommitTransaction();   MapLayer.Close();`

---

<div class="post-metadata">

**Author:** ![Charles](https://community.thinkgeo.com/letter_avatar_proxy/v4/letter/c/6f9a4e/32.png) [@Charles](https://community.thinkgeo.com/u/Charles)\
**Post date:** [May 20, 2010, 3:47pm UTC](https://community.thinkgeo.com/t/adding-features-with-mssql2008featurelayer/1242/2 "2010-05-20T15:47:00Z")

</div>

Some&nbsp;code&nbsp;is&nbsp;missing.&nbsp;&nbsp;genFeature&nbsp;is&nbsp;new&nbsp;feature&nbsp;modified&nbsp;from&nbsp;an&nbsp;existing&nbsp;feature.&nbsp;&nbsp;I&nbsp;want&nbsp;to&nbsp;add&nbsp;it&nbsp;as&nbsp;a&nbsp;new&nbsp;feature.&nbsp;&nbsp;I&nbsp;have&nbsp;changed&nbsp;the&nbsp;Id&nbsp;field&nbsp;value&nbsp;(the&nbsp;FeatureID&nbsp;column).&nbsp;&nbsp;I&nbsp;know&nbsp;the&nbsp;feature&nbsp;is&nbsp;valid&nbsp;because&nbsp;I&nbsp;was&nbsp;able&nbsp;to&nbsp;add&nbsp;it&nbsp;throough&nbsp;SQL&nbsp;Server&nbsp;using&nbsp;the&nbsp;WellKnownText&nbsp;for&nbsp;the&nbsp;feature.&nbsp;  
&nbsp;&nbsp;  
&nbsp;Charles

---

<div class="post-metadata">

**Author:** ![ThinkGeo](https://community.thinkgeo.com/user_avatar/community.thinkgeo.com/thinkgeo/32/25029_2.png) [@ThinkGeo](https://community.thinkgeo.com/u/ThinkGeo)\
**Post date:** [May 20, 2010, 8:01pm UTC](https://community.thinkgeo.com/t/adding-features-with-mssql2008featurelayer/1242/3 "2010-05-20T20:01:10Z")

</div>

Charles,

Thanks&nbsp;for&nbsp;your&nbsp;post&nbsp;and&nbsp;code&nbsp;sample.  
&nbsp;  
So,&nbsp;the&nbsp;table&nbsp;should&nbsp;contains&nbsp;the&nbsp;following&nbsp;columns:&nbsp;ID,&nbsp;FeatureType&nbsp;and&nbsp;(&nbsp;Angle,&nbsp;FeatureText,&nbsp;RScale,&nbsp;DBText,&nbsp;PSymbol,&nbsp;TextJust)&nbsp;if&nbsp;not&nbsp;point.&nbsp;And&nbsp;the&nbsp;genFeature&nbsp;should&nbsp;a&nbsp;valid&nbsp;shape.&nbsp;Right?  
&nbsp;  
Try&nbsp;to&nbsp;notice&nbsp;the&nbsp;result&nbsp;when&nbsp;commit&nbsp;transaction.  
`TransactionResult result = MapLayer.EditTools.CommitTransaction();`  
&nbsp;  
Besides,&nbsp;you&nbsp;could&nbsp;hook&nbsp;up&nbsp;the&nbsp;event&nbsp;to&nbsp;see&nbsp;what&nbsp;the&nbsp;sql&nbsp;statement&nbsp;is&nbsp;to&nbsp;be&nbsp;excuted&nbsp;in&nbsp;the&nbsp;SQL&nbsp;Server.  
`((MsSql2008FeatureSource)(MapLayer.FeatureSource)).ExecutingSqlStatement += new EventHandler<ExecutingSqlStatementMsSql2008FeatureSourceEventArgs>(CreateAGraticuleAdornmentLayer_ExecutingSqlStatement);void CreateAGraticuleAdornmentLayer_ExecutingSqlStatement(object sender, ExecutingSqlStatementMsSql2008FeatureSourceEventArgs e){    if (e.ExecutingSqlStatementType == ExecutingSqlStatementType.CommitTransactionEx)    {        string sql = e.SqlStatement;    }}`  
&nbsp;  
Any&nbsp;more&nbsp;questions&nbsp;just&nbsp;feel&nbsp;free&nbsp;to&nbsp;let&nbsp;me&nbsp;know.  
&nbsp;  
Thanks.  
&nbsp;  
Yale

---

<div class="post-metadata">

**Author:** ![Charles](https://community.thinkgeo.com/letter_avatar_proxy/v4/letter/c/6f9a4e/32.png) [@Charles](https://community.thinkgeo.com/u/Charles)\
**Post date:** [May 21, 2010, 7:48am UTC](https://community.thinkgeo.com/t/adding-features-with-mssql2008featurelayer/1242/4 "2010-05-21T07:48:33Z")

</div>

Yale,&nbsp;  
&nbsp;&nbsp;  
&nbsp;Let&nbsp;me&nbsp;throw&nbsp;you&nbsp;a&nbsp;few&nbsp;curves.&nbsp;&nbsp;I&nbsp;have&nbsp;two&nbsp;fields&nbsp;in&nbsp;every&nbsp;geometry&nbsp;table&nbsp;called&nbsp;Id&nbsp;and&nbsp;FeatureId.&nbsp;&nbsp;The&nbsp;Id&nbsp;field&nbsp;is&nbsp;the&nbsp;primary&nbsp;key&nbsp;for&nbsp;the&nbsp;feature&nbsp;geometry&nbsp;table&nbsp;and&nbsp;the&nbsp;FeatureId&nbsp;field&nbsp;links&nbsp;the&nbsp;Id&nbsp;(primary&nbsp;key)&nbsp;field&nbsp;in&nbsp;the&nbsp;feature&nbsp;attribute&nbsp;table.&nbsp;&nbsp;We&nbsp;have&nbsp;used&nbsp;the&nbsp;field&nbsp;name&nbsp;“Id”&nbsp;for&nbsp;our&nbsp;primary&nbsp;key&nbsp;field&nbsp;since&nbsp;our&nbsp;product&nbsp;was&nbsp;originally&nbsp;written&nbsp;in&nbsp;C++&nbsp;and&nbsp;VB3&nbsp;(~1990).&nbsp;&nbsp;To&nbsp;make&nbsp;things&nbsp;even&nbsp;more&nbsp;interesting,&nbsp;the&nbsp;primary&nbsp;key&nbsp;field&nbsp;name&nbsp;is&nbsp;configurable.&nbsp;&nbsp;From&nbsp;all&nbsp;appearances,&nbsp;you&nbsp;use&nbsp;the&nbsp;field&nbsp;named&nbsp;FeatureId&nbsp;as&nbsp;the&nbsp;primary&nbsp;key&nbsp;by&nbsp;default.&nbsp;&nbsp;When&nbsp;I&nbsp;created&nbsp;the&nbsp;MsSql2008FeatureLayer&nbsp;I&nbsp;specified&nbsp;the&nbsp;field&nbsp;named&nbsp;Id&nbsp;as&nbsp;the&nbsp;“FeatureId”&nbsp;field.&nbsp;  
&nbsp;&nbsp;  
&nbsp;MsSql2008FeatureLayer&nbsp;MapLayer&nbsp;=&nbsp;new&nbsp;MsSql2008FeatureLayer(MapConnString,&nbsp;Tablename,&nbsp;“Id”);&nbsp;  
&nbsp;&nbsp;  
&nbsp;&nbsp;  
&nbsp;The&nbsp;SQL&nbsp;statement&nbsp;for&nbsp;the&nbsp;CommitTransaction&nbsp;operation&nbsp;came&nbsp;back&nbsp;as:&nbsp;  
&nbsp;&nbsp;  
&nbsp;“INSERT&nbsp;INTO&nbsp;AmpsGeometry&nbsp;(Location,Id,FeatureId,FeatureType,MapName,Layer,Angle,FeatureText,RScale,DBText,LineStart,LineEnd,PSymbol,TextJust,ZoomLevel)&nbsp;VALUES(Geometry::STGeomFromWKB(@Geometry,0),‘3358b86d-3e82-473b-9b12-670576b8064c’,‘3’,‘Polygon’,‘35761869.MSI’,‘736’,‘0’,’’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘11’);”&nbsp;  
&nbsp;&nbsp;  
&nbsp;If&nbsp;you&nbsp;count&nbsp;the&nbsp;fields&nbsp;listed&nbsp;there&nbsp;are&nbsp;15.&nbsp;&nbsp;This&nbsp;includes&nbsp;all&nbsp;of&nbsp;the&nbsp;fields&nbsp;in&nbsp;the&nbsp;geometry&nbsp;table.&nbsp;&nbsp;If&nbsp;you&nbsp;count&nbsp;values&nbsp;listed&nbsp;there&nbsp;are&nbsp;14.&nbsp;&nbsp;The&nbsp;statement&nbsp;left&nbsp;off&nbsp;the&nbsp;Id&nbsp;field&nbsp;value&nbsp;which&nbsp;should&nbsp;be&nbsp;‘36’&nbsp;in&nbsp;this&nbsp;case.&nbsp;  
&nbsp;&nbsp;  
&nbsp;This&nbsp;would&nbsp;seem&nbsp;to&nbsp;be&nbsp;a&nbsp;bug,&nbsp;don’t&nbsp;you&nbsp;think?&nbsp;  
&nbsp;&nbsp;  
&nbsp;Charles

---

<div class="post-metadata">

**Author:** ![ThinkGeo](https://community.thinkgeo.com/user_avatar/community.thinkgeo.com/thinkgeo/32/25029_2.png) [@ThinkGeo](https://community.thinkgeo.com/u/ThinkGeo)\
**Post date:** [May 24, 2010, 12:24am UTC](https://community.thinkgeo.com/t/adding-features-with-mssql2008featurelayer/1242/5 "2010-05-24T00:24:26Z")

</div>

Charles,

Thanks&nbsp;for&nbsp;your&nbsp;post.

Accroding&nbsp;to&nbsp;the&nbsp;SQL&nbsp;statement,&nbsp;the&nbsp;columns&nbsp;and&nbsp;values&nbsp;are&nbsp;as&nbsp;following&nbsp;which&nbsp;seems&nbsp;coreponded&nbsp;and&nbsp;correct.

Columns:

1)&nbsp;&nbsp;&nbsp;Location  
  
2)&nbsp;&nbsp;&nbsp;Id  
  
3)&nbsp;&nbsp;&nbsp;FeatureId  
  
4)&nbsp;&nbsp;&nbsp;FeatureType  
  
5)&nbsp;&nbsp;&nbsp;MapName  
  
6)&nbsp;&nbsp;&nbsp;Layer  
  
7)&nbsp;&nbsp;&nbsp;Angle  
  
8)&nbsp;&nbsp;&nbsp;FeatureText  
  
9)&nbsp;&nbsp;&nbsp;RScale  
  
10)&nbsp;DBText  
  
11)&nbsp;LineStart  
  
12)&nbsp;LineEnd  
  
13)&nbsp;PSymbol  
  
14)&nbsp;TextJust  
  
15)&nbsp;ZoomLevel

VALUES

1)&nbsp;&nbsp;Geometry::STGeomFromWKB(@Geometry,0)  
  
2)&nbsp;'3358b86d-3e82-473b-9b12-670576b8064c'  
  
3)&nbsp;'3'  
  
4)&nbsp;'Polygon'  
  
5)&nbsp;'35761869.MSI'  
  
6)&nbsp;'736'  
  
7)&nbsp;'0'  
  
8)&nbsp;''  
  
9)&nbsp;'0',  
  
10)&nbsp;‘0'  
  
11)&nbsp;'0'  
  
12)&nbsp;'0'  
  
13)&nbsp;'0'  
  
14)&nbsp;'0'  
  
15)&nbsp;'11'

So&nbsp;if&nbsp;I&nbsp;am&nbsp;not&nbsp;misunderstanding&nbsp;anythingl,&nbsp;the&nbsp;problem&nbsp;is&nbsp;the&nbsp;SQL&nbsp;statement&nbsp;seems&nbsp;only&nbsp;insert&nbsp;a&nbsp;record&nbsp;with&nbsp;15&nbsp;columns&nbsp;and&nbsp;values&nbsp;while&nbsp;in&nbsp;fact,&nbsp;its&nbsp;table&nbsp;should&nbsp;contains&nbsp;16&nbsp;columns.

Could&nbsp;you&nbsp;call&nbsp;the&nbsp;following&nbsp;API&nbsp;&nbsp;to&nbsp;query&nbsp;the&nbsp;columns&nbsp;to&nbsp;see&nbsp;if&nbsp;it&nbsp;is&nbsp;15&nbsp;columns&nbsp;or&nbsp;16&nbsp;columns:  
  
sqlFeatrueLayer.Open();  
  
Collection\<FeatureSourceColumn\>&nbsp;allColumns&nbsp;=&nbsp;sqlFeatrueLayer.QueryTools.GetColumns();

Is&nbsp;it&nbsp;possible&nbsp;to&nbsp;send&nbsp;me&nbsp;a&nbsp;backup&nbsp;of&nbsp;the&nbsp;SQL&nbsp;data&nbsp;and&nbsp;I&nbsp;will&nbsp;try&nbsp;to&nbsp;recreaet&nbsp;the&nbsp;problem?

Any&nbsp;more&nbsp;questions&nbsp;just&nbsp;feel&nbsp;free&nbsp;to&nbsp;let&nbsp;me&nbsp;know.

Thanks.

Yale

&nbsp;

---

<div class="post-metadata">

**Author:** ![Charles](https://community.thinkgeo.com/letter_avatar_proxy/v4/letter/c/6f9a4e/32.png) [@Charles](https://community.thinkgeo.com/u/Charles)\
**Post date:** [May 24, 2010, 8:41am UTC](https://community.thinkgeo.com/t/adding-features-with-mssql2008featurelayer/1242/6 "2010-05-24T08:41:34Z")

</div>

Yale,  
  
  
  
Sorry,&nbsp;my&nbsp;mistake&nbsp;there&nbsp;are&nbsp;15&nbsp;fields&nbsp;and&nbsp;values.&nbsp;&nbsp;The&nbsp;columns&nbsp;are&nbsp;correct.&nbsp;&nbsp;I&nbsp;didn't&nbsp;recognize&nbsp;'3358b86d-3e82-473b-9b12-670576b8064c'&nbsp;as&nbsp;a&nbsp;possible&nbsp;value&nbsp;for&nbsp;the&nbsp;Id&nbsp;field.&nbsp;&nbsp;If&nbsp;I&nbsp;look&nbsp;at&nbsp;the&nbsp;values&nbsp;in&nbsp;DictionaryValues&nbsp;I&nbsp;see&nbsp;DictionaryValues["Id"]&nbsp;=&nbsp;'36'.&nbsp;&nbsp;the&nbsp;data&nbsp;type&nbsp;for&nbsp;the&nbsp;Id&nbsp;field&nbsp;is&nbsp;int.&nbsp;&nbsp;It&nbsp;is&nbsp;primary&nbsp;key.&nbsp;&nbsp;There&nbsp;is&nbsp;a&nbsp;foreign&nbsp;key&nbsp;on&nbsp;the&nbsp;FeatureId&nbsp;field&nbsp;of&nbsp;the&nbsp;AmpsGeometry&nbsp;table&nbsp;linking&nbsp;it&nbsp;to&nbsp;the&nbsp;Id&nbsp;field&nbsp;of&nbsp;the&nbsp;Amps&nbsp;table&nbsp;and&nbsp;causing&nbsp;a&nbsp;cascading&nbsp;delete&nbsp;if&nbsp;I&nbsp;delete&nbsp;the&nbsp;Amps&nbsp;record.  
  
  
  
&nbsp;

`using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Data.OleDb;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using ThinkGeo.MapSuite.Core;public partial class Form1 : Form{    public Form1()    {        InitializeComponent();    }    private void button1_Click(object sender, EventArgs e)    {        string ConnString = @"Server=Mapcom061\SQLEXPRESS2008;Database=NGRichDemo;Trusted_Connection=Yes;";        MsSql2008FeatureLayer MapLayer = new MsSql2008FeatureLayer(ConnString, "AmpsGeometry", "Id");        MapLayer.Open();        PolygonShape polygon = new PolygonShape(@"POLYGON((11803825.47 3738681.07, 11803825.47 3738671.07,11803725.47 3738681.07,11803725.47 3738671.07,11803925.47 3738671.07,11803925.47 3738681.07,11804025.47 3738671.07,11804025.47 3738681.07,11803825.47 3738681.07))");        int i = 11;        Dictionary<string, string> DictionaryValues = new Dictionary<string, string>();        DictionaryValues["Id"] = GetNextID(MapLayer.TableName).ToString();        DictionaryValues["FeatureId"] = "3";        DictionaryValues["FeatureType"] = "POLYGON";        DictionaryValues["MapName"] = "35761869.MSI";        DictionaryValues["Layer"] = "736";        DictionaryValues["Angle"] = "0";        DictionaryValues["FeatureText"] = "";        DictionaryValues["RScale"] = "0";        DictionaryValues["DBText"] = "0";        DictionaryValues["LineStart"] = "0";        DictionaryValues["LineEnd"] = "0";        DictionaryValues["PSymbol"] = "0";        DictionaryValues["TextJust"] = "0";        DictionaryValues["ZoomLevel"] = i.ToString();        MapLayer.EditTools.BeginTransaction();        MapLayer.EditTools.Add(polygon, DictionaryValues);        TransactionResult result = MapLayer.EditTools.CommitTransaction();        MapLayer.Close();    }    private int GetNextID(string Tablename)    {        string SQLText = "SELECT Max(ID) AS MaxID FROM " + Tablename;        OleDbConnection conn = new OleDbConnection(@"Provider=SQLOLEDB;Server=Mapcom061\SQLEXPRESS2008;Database=NGRichDemo;Trusted_Connection=Yes;");        conn.Open();        OleDbDataAdapter DA = new OleDbDataAdapter(SQLText, conn);        DataSet DS = new DataSet();        DA.Fill(DS);        DA.Dispose();        conn.Close();        int nextid = 1;        if (DS.Tables[0].Rows.Count > 0)        {            DataRow DR = DS.Tables[0].Rows[0];            if (DR["MaxID"] != DBNull.Value)                nextid = Convert.ToInt32(DR["MaxID"]) + 1;        }        DS.Dispose();        return nextid;    }}`&nbsp;

&nbsp;

I&nbsp;have&nbsp;attached&nbsp;a&nbsp;backup&nbsp;of&nbsp;a&nbsp;truncated&nbsp;data&nbsp;set.&nbsp;&nbsp;I&nbsp;get&nbsp;the&nbsp;same&nbsp;error&nbsp;with&nbsp;this&nbsp;code.  
  
  
  
Charles

[2088-NGRichDemoConverted.zip](https://community.thinkgeo.com/uploads/default/original/2X/1/140ba00759eff8c63b2bbb8119e1c5580d9ffa41.zip) (192 KB)

---

<div class="post-metadata">

**Author:** ![ThinkGeo](https://community.thinkgeo.com/user_avatar/community.thinkgeo.com/thinkgeo/32/25029_2.png) [@ThinkGeo](https://community.thinkgeo.com/u/ThinkGeo)\
**Post date:** [May 25, 2010, 8:46pm UTC](https://community.thinkgeo.com/t/adding-features-with-mssql2008featurelayer/1242/7 "2010-05-25T20:46:07Z")

</div>

Charles,

Thanks&nbsp;for&nbsp;your&nbsp;post&nbsp;and&nbsp;data.  
&nbsp;  
Try&nbsp;following&nbsp;code&nbsp;which&nbsp;set&nbsp;the&nbsp;polygon&nbsp;id&nbsp;to&nbsp;a&nbsp;correct&nbsp;value&nbsp;explicitly&nbsp;before&nbsp;commit&nbsp;transaction&nbsp;should&nbsp;fix&nbsp;your&nbsp;problem.  
  
&nbsp;  
`MapLayer.EditTools.BeginTransaction(); polygon.Id = DictionaryValues["Id"];MapLayer.EditTools.Add(polygon, DictionaryValues);TransactionResult result = MapLayer.EditTools.CommitTransaction();MapLayer.Close();`  
  
Any&nbsp;more&nbsp;questions&nbsp;just&nbsp;feel&nbsp;free&nbsp;to&nbsp;let&nbsp;me&nbsp;know.  
&nbsp;  
Thanks.  
&nbsp;  
Yale

---

<div class="post-metadata">

**Author:** ![Charles](https://community.thinkgeo.com/letter_avatar_proxy/v4/letter/c/6f9a4e/32.png) [@Charles](https://community.thinkgeo.com/u/Charles)\
**Post date:** [May 26, 2010, 7:14am UTC](https://community.thinkgeo.com/t/adding-features-with-mssql2008featurelayer/1242/8 "2010-05-26T07:14:31Z")

</div>

Yale,&nbsp;  
&nbsp;&nbsp;  
&nbsp;That&nbsp;seems&nbsp;to&nbsp;have&nbsp;done&nbsp;the&nbsp;trick.&nbsp;&nbsp;Thanks!&nbsp;  
&nbsp;&nbsp;  
&nbsp;Charles

---

<div class="post-metadata">

**Author:** ![ThinkGeo](https://community.thinkgeo.com/user_avatar/community.thinkgeo.com/thinkgeo/32/25029_2.png) [@ThinkGeo](https://community.thinkgeo.com/u/ThinkGeo)\
**Post date:** [May 26, 2010, 7:18pm UTC](https://community.thinkgeo.com/t/adding-features-with-mssql2008featurelayer/1242/9 "2010-05-26T19:18:29Z")

</div>

Charles,&nbsp;  
&nbsp;&nbsp;  
&nbsp;Thanks&nbsp;for&nbsp;letting&nbsp;me&nbsp;know&nbsp;the&nbsp;status.&nbsp;  
&nbsp;&nbsp;  
&nbsp;Any&nbsp;more&nbsp;questions&nbsp;just&nbsp;feel&nbsp;free&nbsp;to&nbsp;let&nbsp;me&nbsp;know.&nbsp;  
&nbsp;&nbsp;  
&nbsp;Thanks.&nbsp;  
&nbsp;&nbsp;  
&nbsp;Yale