Problem with connection to SQLExpress using MsSqlSpatial

Topics: Data Access
May 24, 2007 at 10:18 AM

I'm new to both Sharpmap & MsSqlSpatial. Still trying out small programs to learn.
Getting the map using shapefiles was ok. But I have a problem when using SQLExpress.
Using MsSqlSpatial, I have transfered the shapefile to SQLExpress database called testdb.
The tables was created as dbo.district.

I'm using SharpMap-21021 & MsSqlSpatial-22696 & SQLExpress(SQL Server 9.0.2047).

When I run the code, a NullReferenceException occurs at line 615 of MsSqlSpatial.cs (GetExtents() method).
I guess this is due to a problem in my connection.

My program code is as follow (the aspx contains an image as imgMap)

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Drawing;
using SharpMap;

public partial class _Default : System.Web.UI.Page
protected void Page_Load(object sender, EventArgs e)
//Initialize a new map of size 'imagesize'
SharpMap.Map myMap = new SharpMap.Map(new System.Drawing.Size((int)imgMap.Height.Value, (int)imgMap.Width.Value));

//Set up a district layer
SharpMap.Layers.VectorLayer layDistricts = new SharpMap.Layers.VectorLayer("Districts");
string ConnStr = @"Server=pcname\sqlexpress;Database=testdb;Uid=sa;Pwd=sa123";
layDistricts.DataSource = new SharpMap.Data.Providers.MsSqlSpatial(ConnStr, "district", "the_geom", "oid");

//Set layer styles
layDistricts.Style.Fill = new SolidBrush(Color.Lavender);
layDistricts.Style.Outline = System.Drawing.Pens.Blue;
layDistricts.Style.EnableOutline = true;


//This is the initial view of the map. Zoom to the extents of the map:

//Render map
System.Drawing.Image img = myMap.GetMap();
string imgID = SharpMap.Web.Caching.InsertIntoCache(1, img);
imgMap.ImageUrl = "getmap.aspx?ID=" + HttpUtility.UrlEncode(imgID);

Could someone please help me with what I'm doing wrong here?
May 24, 2007 at 4:57 PM
Please, could you post the full exception call stack?

Best regards,
Ricardo Stuven.
May 25, 2007 at 4:24 AM
Hi Ricardo,

Here are the full exception details,

System.NullReferenceException was unhandled by user code
Message="Object reference not set to an instance of an object."
at SharpMap.Data.Providers.MsSqlSpatial.GetExtents()
at SharpMap.Layers.VectorLayer.get_Envelope()
at SharpMap.Map.GetExtents()
at SharpMap.Map.ZoomToExtents()
at Default.PageLoad(Object sender, EventArgs e) in d:\Trials\TestMSSQLSpatial\MSSQLSpatial\Default.aspx.cs:line 33
at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)
at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)
at System.Web.UI.Control.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Thank you.
May 30, 2007 at 12:10 PM
you can add source code instead of compiled libraries and debug them.

It seems that your SqlConnection object might be null, check your connection string. According to they are
"Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;" or
"Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;TrustedConnection=False;", try to add "TrustedConnction=False;"

In addition, I have noticed that shapefiles imported by a MsSqlSpatial have different names of envelope columns than sharpmap expects.

May 30, 2007 at 11:51 PM
Oops... jnouza is right. Check out the connection string. It seems it should be:
string ConnStr = @"Data Source=pcname\sqlexpress;;Initial Catalog=testdb;User Id=sa;Password=sa123";
If the problem persists, please copy also the file SharpMap.pdb to the directory where SharpMap.dll is located and give us the full exception again (this will include source code line numbers of the call stack).

Best regards,
Ricardo Stuven.
Jun 5, 2007 at 11:16 AM
Edited Jun 5, 2007 at 11:46 AM
(Sorry, I've accidentally posted same twice. So see post below.)
Jun 5, 2007 at 11:35 AM

Thanks for the suggestions. I've tried it, however, I still get the error.

Exception details are as follows.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

Line 32:
Line 33: //This is the initial view of the map. Zoom to the extents of the map:
Line 34: myMap.ZoomToExtents();
Line 35:
Line 36: //Render map

Source File: d:\Trials\TestMSSQLSpatial\MSSQLSpatial\Default.aspx.cs Line: 34

Stack Trace:
NullReferenceException: Object reference not set to an instance of an object.
SharpMap.Data.Providers.MsSqlSpatial.GetExtents() in D:\backup\h\5-18\NNSS\map\sharpmap\SharpMap-21021\SharpMap\Data\Providers\MsSqlSpatial.cs:615
SharpMap.Layers.VectorLayer.get_Envelope() in D:\backup\h\5-18\NNSS\map\sharpmap\SharpMap-21021\SharpMap\Layers\VectorLayer.cs:299
SharpMap.Map.GetExtents() in D:\backup\h\5-18\NNSS\map\sharpmap\SharpMap-21021\SharpMap\Map\Map.cs:376
SharpMap.Map.ZoomToExtents() in D:\backup\h\5-18\NNSS\map\sharpmap\SharpMap-21021\SharpMap\Map\Map.cs:197
Default.PageLoad(Object sender, EventArgs e) in d:\Trials\TestMSSQLSpatial\MSSQLSpatial\Default.aspx.cs:34
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +31
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +68
System.Web.UI.Control.OnLoad(EventArgs e) +88
System.Web.UI.Control.LoadRecursive() +74
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3035

Thank you,
Jun 5, 2007 at 4:38 PM
The error spot is in these lines of SharpMap.Data.Providers.MsSqlSpatial:
    using (SqlConnection conn = new SqlConnection(_ConnectionString))
        string strSQL = string.Format("SELECT ST.AsBinary(ST.EnvelopeQueryWhere('{0}', '{1}', '{2}'))", this.Table, this.GeometryColumn, this.DefinitionQuery.Replace("'", "''"));
Looking at the code, the only possible method call to a null reference is "this.DefinitionQuery.Replace", and looking at the rest of the class, it seems it's never properly initialized (shame of me! :-). Please, could you try to set layDistricts.DataSource.DefinitionQuery = string.Empty; and tell us how it works?

Best regards,
Ricardo Stuven.
Jun 27, 2007 at 9:49 AM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.