CODEWITHSUNDEEP
sql-servervb6
LenPayne
LenPayne

Reputation: 105

How do I use a datatype-based SQL Stored Procedure when using Visual Basic 6?

I'm adding features to an old VB6 application. We're making it more geo-spatial friendly. I've completed the database-end changes by upgrading to SQL Server 2012 Express and am trying to use the geography datatype.

In VB6, I have a piece of code that looks like this (and yes, I know Latitude is spelled wrong):

rsGeoref![Lattitude] = IIf(dblLatitude <> "", Val(dblLatitude), Null)
rsGeoref![LatDirection] = IIf(strLatDirection = "", Null, strLatDirection)
rsGeoref![LatDegrees] = IIf(dblLatDegrees <> "", Val(dblLatDegrees), Null)

This is modifying a RecordSet that is then pushed back to the Database later on using:

rsGeoref.Update

So I now have a field called GeogSpatial that uses the geography datatype from SQL Server 2012. I want to update it the same way. This doesn't work:

rsGeoref![GeogSpatial] = "geography::STGeomFromText('POINT(" + CStr(dblSignedLong) + " " + CStr(dblSignedLat) + ")', 4326)"

I believe the error comes from using the SQL stored procedure geography::STGeomFromText. If so, is there a better way for me to do this without having to re-write the entire section as a monolithic SQL query?

Upvotes: 0

Views: 311

Answers (1)

Jason
Jason

Reputation: 1383

I am not certain you can set the value of a field in a recordset to a string literal that represents a call to a SQL stored procedure/function and have that execute as SQL code. What it will attempt to do is just set that field to the string literal and most likely you will get a type mismatch error at runtime if the string can not be cast to the type of the field.

I tested this theory with an equivalent setup, but using a call to GetDate() assigned to a datetime field instead of STGeomFromText. This failed as I imagined since the text 'GetDate()' cannot be assigned to a datetime.

Upvotes: 1

Related Questions