How to write to Sql server from MapBasic and insert rows

Question

I have created a table in Sql server 2014 with the columns: Id (int), Geom (geometry), RouteId (int), Desc (varchar(MAX)) I have succeeded in making the table mappable through MapInfo I have checked .TAB file for the table, that it has IsReadOnly = false

Stil when i try to execute an insert query on the table I get the error messsage that the table is read only. Anyone knows how to fix this?

Also I cannot figure out how to write a correct insert query I have tried doing that on a copied version of the table code is as follows

Dim rId As Integer
Dim oPline as Object
rId = 20
Dim t As String
t = "test"
Create pline into variable oPline 2 (1,2)(1,3)

Insert into TestGeom1 (Geom, RouteId, Desc) VALUES (oPline, rId, t)

Writing like above I get the error messsage field Geom does not exist in table TestGeom1 If i replace Geom with obj or just remove it completely i get the error Expression does not evaluate to a column or a table name. So please who can tell me what is the correct syntax for this?

Answer 1

1. Make sure that you have specified a unique primary key in the table in SQL Server. Also you could take advantage of the IDENTITY type so that you don't have to manage the ID's yourself.

2. Make sure your text columns are defined as varchar and that they aren't wider than 254. Otherwise you might have an issue editing the data

3. Once the table is opened in MapInfo Professional your spatial column is always called OBJ. So use this insert statement:

   Insert into TestGeom1 (OBJ, RouteId, Desc) VALUES (oPline, rId, t)

If you have a statement that works in the MapBasic window but doesn't work in the MapBasic application, you should have a look at your variable and function names. Maybe you have a variable using the same name as a table or column.

Also try to create the table in the database from MapInfo Professional using File > New, when specifying the location of the table, pick an open connection to your database. In this way you can create a new table directly in your database

If you use the Per Row Style option, the width of the column might prevnet you from editing the object. If the column is wider than 254, MapInfo Pro can't edit the column and so the spatial object also gets read-only