ThinkGeo.com    |     Documentation    |     Premium Support

ColumnValue should store DBNULL

#1

Hi,

how to store a DBNULL value in a columnvalue of a Feature?
When the column is type of Integer or Double/Float I get an error inserting the value DBNULL.Value or ‘’ (String.Empty).

What to do to store values like DBNULL?

Regards
Hardy

#2

Hi Hardy,

We build a sample to test that but failed to reproduce it, could you please modify our sample or let us know we missed any point?

We build a shape file, then insert string.empty or DBNULL.Value.ToString() into it, we hadn’t get any error when insert.

8700.zip (4.7 KB)

Regards,

Ethan

#3

Hi Ethan,

I’m using a PostgreSqlFeatureLayer or MsSql2008FeatureLayer.
Both Fails.

Regards
Hardy

#4

Hi Hardy,

Thanks to let us know that, our developer should look into that, any update I will let you know.

Regards,

Ethan

#5

Hi Hardy,

I think if you met problem when you insert dbnull, the workaround is you can insert them by sqlstatement, for example:

        string sql = "INSERT INTO testa (recid) VALUES (null)";
        postgre.FeatureSource.ExecuteNonQuery(sql);

And in fact when I tried to insert by BeginTransaction and CommitTransaction, I don’t get the error information also.

So could you please show us your code to help us reproduce the error?

8700.zip (4.8 KB)

Regards,

Ethan

#6

Hi Ethan,

my Methode storing the values is independent from the FeatureSource.
The following Code is extracted from my function:

Layer.EditTools.BeginTransaction()

            Dim theUpdateFeature As ThinkGeo.MapSuite.Core.Feature = MyMapItem.Feature

            For Each column As FeatureSourceColumn In Layer.FeatureSource.GetColumns
                                      Dim newColumnValue As String = String.Empty
                    Select Case column.TypeName
                            Case "int8", "int4", "int16"
                                newColumnValue = NewValues(column.ColumnName)
                            Case "float8", "float4", "float16"
                                newColumnValue = NewValues(column.ColumnName).Replace(",", ".")
                            Case Else
                                newColumnValue = NewValues(column.ColumnName)
                                If newColumnValue = "" Then
                                    newColumnValue = String.Empty
                                End If
                        End Select
                    theUpdateFeature.ColumnValues(column.ColumnName) = newColumnValue

Next

            Layer.EditTools.Update(theUpdateFeature)
            singleTransActionResult = Layer.EditTools.CommitTransaction()

            If singleTransActionResult.FailureReasons.Count > 0 Then
                For Each Reason As KeyValuePair(Of String, String) In singleTransActionResult.FailureReasons
                    msg &= Reason.Value & vbNewLine
                Next
            End If

I Need a solution storing values whitout checking what Kind of Feature I’m using…

Regards
Hardy

#7

Hi Hardy,

Our developer is checking this issue with you code, we will update the status here.

Thanks
Mark

#8

Hi Hardy,

Our developer had fixed that, please get this package:

And here is the code how to store the null, “null” or string.Empty into the Postgre database.

var connectionString = ConfigurationManager.ConnectionStrings["postgreConn"].ConnectionString;
PostgreSqlFeatureLayer postgreSqlFeatureLayer = new PostgreSqlFeatureLayer(connectionString, "testa", "gid",4326);
postgreSqlFeatureLayer.Open();
Feature feature = postgreSqlFeatureLayer.FeatureSource.GetAllFeatures(ReturningColumnsType.AllColumns)[0];
postgreSqlFeatureLayer.EditTools.BeginTransaction();
feature.ColumnValues["sqmi"] = null;
postgreSqlFeatureLayer.EditTools.Update(feature);
postgreSqlFeatureLayer.EditTools.CommitTransaction();

Regards,

Ethan

#9

Ethan,

thank you for the good News.
I Hope MS-SQL is fixed as well.

But we are using Version 9 and will update next year to 10. Maybe May or June.

Regards
Hardy

#10

Hi Hardy,

We will move this to V9 later, but we need more test.

And for MS-SQL our developer hadn’t reproduced that, could you please view our sample and see where is the problem?

8700.zip (4.5 KB)

Regards,

Ethan

#11

Hi Ethan,
your sample is Version 10.2.
We are using 9 so it does not make sense to me…
With 9 MS-SQL fails

Regards
Hardy

#12

Hi Hardy,

This bug has been fixed in MapSuite 9.0, you can download the latest version 9.0.867.0 or higher version from product center.

For MsSql2008FeatureLayer it need to save the null value like this:

sql2008Layer.Open();
sql2008Layer.EditTools.BeginTransaction();
features[0].ColumnValues[“SQMI”] = null;
sql2008Layer.EditTools.Update(features[0]);
sql2008Layer.EditTools.CommitTransaction();

For PostgreSqlFeatureLayer it received the null value like this:

var connectionString = ConfigurationManager.ConnectionStrings[“postgreConn”].ConnectionString;
PostgreSqlFeatureLayer postgreSqlFeatureLayer = new PostgreSqlFeatureLayer(connectionString, “testa”, “gid”,4326);
postgreSqlFeatureLayer.Open();
Feature feature = postgreSqlFeatureLayer.FeatureSource.GetAllFeatures(ReturningColumnsType.AllColumns)[0];
postgreSqlFeatureLayer.EditTools.BeginTransaction();
feature.ColumnValues[“sqmi”] = null;
postgreSqlFeatureLayer.EditTools.Update(feature);
postgreSqlFeatureLayer.EditTools.CommitTransaction();

Wish that’s helpful.

Thanks,

#13

Ethan,

it’s working fine.

Thanks for your Support.

Hardy

#14

Hi Hardy,

I am glad to hear that works.

Regards,

Ethan