Archive for September, 2013

Extracting latitude and longitude from the SQL geometry data type

If that title is confusing, let me explain what this post is about: Extracting latitude and longitude from the SQL geometry data type.

SELECT PlaceName, geometry::STGeomFromText(CONVERT(nvarchar(50),geo), 0).STX,
geometry::STGeomFromText(CONVERT(nvarchar(50),geo), 0).STY
FROM Location
WHERE PlaceName ‘Dulles International Airport’

So I convert the Geometry field in my database (the field name is “geo”, like the “Team Umizoomi” character) to an nvarchar. With that done, I call the STGeomFromText function, a built-in procedure for the geometry data type. And the final step is to call the STX (for latitude) and STY (for longitude).

Advertisements

Leave a comment

Procedure or function expects parameter which was not supplied

I had some fairly simple database code to call a stored procedure:

using (SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings[“Scott”].ConnectionString))             {                 using (SqlCommand Comm = new SqlCommand(“ImportStuff”, Conn))
{
SqlParameter param = new SqlParameter(“@Name”, SqlDbType.VarChar, 10);
param.Value = “Scott”
Comm.Parameters.Add(param);

param = new SqlParameter(“@Quest”, SqlDbType.VarChar, 50;
param.Value = “Write a blog post”;
Comm.Parameters.Add(param);

param = new SqlParameter(“@FavoriteColor”, SqlDbType.VarChar, 10);
param.Value = “Green”;
Comm.Parameters.Add(param);

Conn.Open();
Comm.ExecuteNonQuery();
}
}

No problem. However, when I ran it, I got there exception:

Procedure or function ‘ImportStuff’ expects parameter ‘@Name’, which was not supplied.

Hmm, everything seemed OK in the stored procedure – @Name was there. And as you can see, it was defined in my C# code. So what is the problem?

I would like to preface my answer by saying I’ve done .NET development for twelve years. Tens of thousands of lines of code, good and bad, have come from my fingers. And in this instance, I missed a very simple line:

Comm.CommandType = CommandType.StoredProcedure;

Yes, I had not told the SqlCommand object that I was calling a procedure than text. And after I fixed it, I hoped that was the dumbest mistake I would make that day.

1 Comment