locked
Insert Linestring in SQL Server 2008 RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I'm trying to insert a linestring in SQL Server via C#. I tried this way, but it doesn't work ('udttypename property must be set for udt parameters'). Does somebody now how to insert this linestring?:

    SqlConnection con = connection;
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "INSERT INTO Fietsnetwerk (tid, regio, land, afstand, kleur, data) VALUES (@id,'Vlaanderen',1,0,1,@linestring)";
            cmd.Parameters.AddWithValue("@id", tracknode.getTrackId());
            cmd.Parameters.AddWithValue("@linestring", SqlGeometry.STGeomFromText(new System.Data.SqlTypes.SqlChars("LINESTRING (3.788 50.899, 4.743 51.4333)"), 4326)); 
            cmd.Connection = con;
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            con.Close();

    • Moved by Ricky_Brundritt Saturday, March 10, 2012 1:07 PM (From:Bing Maps: Map Control and Web services Development)
    Friday, January 27, 2012 1:14 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Pretty much exactly as the error states, you need to specify the UdtTypeName for UDT types in SQL Server (geography and geometry are implemented as CLR datatypes just like UDTs). Try this:

    SqlConnection con = connection;
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO Fietsnetwerk (tid, regio, land, afstand, kleur, data) VALUES (@id,'Vlaanderen',1,0,1,@linestring)";
cmd.Parameters.AddWithValue("@id", tracknode.getTrackId());
SqlParameter param =  cmd.Parameters.AddWithValue("@linestring", SqlGeometry.STGeomFromText(new System.Data.SqlTypes.SqlChars("LINESTRING (3.788 50.899, 4.743 51.4333)"), 4326));
param.UdtTypeName = "geography";
cmd.Connection = con;
cmd.ExecuteNonQuery();
cmd.Dispose();
con.Close();

    If data is a geometry column, then change to param.UdtTypeName = "geometry"; instead

    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Friday, January 27, 2012 1:30 PM