StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Cannot Create Insert command

Cannot Create Insert command

Michael Reese — Thu, 31 Jan 2013 18:12:54 GMT

I am getting an error adding a record to a table that does not have a primary key. The message states that I cannot insert a command because PrimaryKeyisAutoincremented = true and the PrimaryKeyField is not a valid auro-increment data type.

The table does not have a Primary Key or autoincrementing primary key set to Auto increment.

Any Ideas?

Sample code below.

                                '-- Create a new record
                                .NewRow()
                                'Save Log Record
                                LocHL7Data.MessageID = System.Guid.NewGuid.ToString() 'Loc_PID_SEG_BO.MessageID
                                'LocHL7Data.MessageSize = oMsg.
                                LocHL7Data.VendorVersion = oMsg.VendorVersion
                                LocHL7Data.VendorName = oMsg.VendorName
                                LocHL7Data.MsgControl = sCrlID
                                LocHL7Data.PartnerAPP = ""
                                LocHL7Data.DateLoaded = System.DateTime.Now.ToString
                                LocHL7Data.HL7Message = oMsg.HL7
                                LocHL7Data.SegmentCount = oMsg.SegmentCount
                                LocHL7Data.MsgType = sMsgType1

                                '-- Save the record
                                If .Save() <> MicroFour.StrataFrame.Data.SaveUndoResult.Success Then
                                    MsgBox("Save Failed...")
                                End If
                            End With

RE: Cannot Create Insert command

Edhy Rijo — Thu, 31 Jan 2013 18:12:54 GMT

Mi Michael, Ben,

You are welcome.

RE: Cannot Create Insert command

Michael Reese — Thu, 31 Jan 2013 13:50:51 GMT

Thanks so much guys, I went down that road yesterday and all is well!

RE: Cannot Create Insert command

StrataFrame Team — Thu, 31 Jan 2013 08:33:18 GMT

Good call, Edhy. A bulk insert into SQL is always the fastest way to go. It's not worth the energy unless you're inserting 50+ records at a time, but it's an order of magnitude faster when you're inserting 1000+ records at a time.

Michael might be just inserting the records as the HL7 messages are received, in which case the bulk operation wouldn't be worth the energy, but if it's a batch operation that inserts 100+ messages at the same time and he ends up with 1000+ NTE segments that need to be inserted, then a bulk copy is worth considering.

RE: Cannot Create Insert command

Edhy Rijo — Thu, 31 Jan 2013 02:09:22 GMT

Hi Michael,

If you are parsing millions of record in the BO and then want to do an insert to the table, instead of using the BO to insert each record which could take a long time, you can use a combination of the BO datatable and SQLBulkCopy to speed up the process of the inserting the records.
Check out this code:
[quote][code]
'' Instead of saving the records, lets use sqlBulkCopy to speed up the import to SQL process
Using destinationConnection As SqlClient.SqlConnection = _
    New SqlClient.SqlConnection(MicroFour.StrataFrame.Data.DataLayer.DataSources(Me.BizCustomersItems1.DataSourceKey).ConnectionString)
    destinationConnection.Open()

    Using bulkCopy As SqlClient.SqlBulkCopy = New SqlClient.SqlBulkCopy(destinationConnection)
        Try
            bulkCopy.DestinationTableName = Me.BizCustomersItems1.TableNameAndSchema
            bulkCopy.BatchSize = 50
            bulkCopy.BulkCopyTimeout = 0

            '-- Create a column mapping with all fields in the BO
            For Each fieldName As String In .AllFieldsList()
                bulkCopy.ColumnMappings.Add(fieldName, fieldName)
            Next

            bulkCopy.WriteToServer(Me.BizCustomersItems1.CurrentDataTable)
            bulkCopy.Close()

            ''-- Accept the changes to the data table to avoid message asking to save records.
            Me.BizCustomersItems1.CurrentDataTable.AcceptChanges()

        Catch ex As Exception
            MsgBox(ex.ToString)

        Finally
            Me.WaitWindow1.HideWaitWindow()
        End Try
    End Using
End Using
[/code][/quote]

Basically I am importing some records in the Me.BizCustomersItems1 business object, since this import can have several thousands of records, I use the SQLBulkCopy passing the BO.CurrentDataTable and then accepting the changes to the Me.BizCustomersItems1 bo to avoid SF to complaint about those records, or you can also do a Me.BizCustomersItems1.Clear()

Hope this is clear and can help you in any way.

RE: Cannot Create Insert command

Michael Reese — Wed, 30 Jan 2013 10:26:45 GMT

Thanks , I'll give is a shot. I want to do as much as I can through the SF BOs.

I'm rewriting (2 years old application) a solution and was wondering how I successfully pulled it off before and then checked my my code and realized that I used a ADOSqlServer connection on these tables.

Michael

RE: Cannot Create Insert command

StrataFrame Team — Wed, 30 Jan 2013 10:02:35 GMT

HL7 you probably never update the records. If you delete an entire message, then you also delete all of the children with the same MessageID, yes?

So, the children all have a foreign key to the MessageID of the MSH... it would make sense for that to be the clustered index of the table.

I think the best way of doing this would be to leave the PrimaryKeyIsAutoIncremented set to false, that lets you assign the primary key and tells SF not to negative increment the column. Then, move the Public Overrides ReadOnly Property PrimaryKeyFields As String() from the designer file to your main file and return "MessageID" or whatever field name you need in the string array rather than an empty string array. Moving this to the main code file will keep it from being overriden when you re-map your business objects. Instead, when you rebuild the partial classes, it will regenerate the PrimaryKeyFields property and you will need to go delete it from the designer file before you can build (better than figuring out at runtime that it's wrong).

This will trick SF into thinking that MessageID is the PK for each of the BOs, but will still let you insert the value rather than expecting it from the database.

Give that a try and let me know. I'll make sure we have a graceful way to handle it for SFv2 :)

RE: Cannot Create Insert command

Michael Reese — Wed, 30 Jan 2013 09:32:53 GMT

Thanks Ben,

I guess I could but it will take a lot of work.

I have created and HL7 data warehouse that reads HL7 messages and parse the data into segment tables. This is why

MSH MessageID = Generated

PID MessageID = MSH MessageID

OBX MessageID = MSH MessageID

NTE MessageID = MSH MessageID

Im dealing in millions.

RE: Cannot Create Insert command

StrataFrame Team — Wed, 30 Jan 2013 09:20:44 GMT

That's correct. StrataFrame requires that each table have a PrimaryKey. It needs this so that it knows how to save an individual row. Without a PK, StrataFrame would be able to insert rows, but it would not be able to update or delete those rows. It does not have the functionality to do an update or a delete with a complex where clause; the only where clause it can generate for an update or delete is the matching of the PK value.

In fact, this is something we debated for SFv2... do we allow entities to map to tables that do not have a primary key. We ended up on the same side of the fence as SFv1, which is that each table requires a PK. Pre-beta, it's certainly not set in stone, so I could be convinced otherwise.

Would it break your database logic to add a PK to the table? Do you ever need to be able to update/delete rows in this table, or only insert them? Without a PK, you would have to manually specify the where conditions during an update or delete. Let me know.

RE: Cannot Create Insert command

Michael Reese — Wed, 30 Jan 2013 08:44:23 GMT

Thanks, I was looking for that in the BO properties but could not find it.

This is the exception thrown

Is says that DataLayer Exception "Cannot Create Insert command because the BO does not contain any primaryKeyFields.

RE: Cannot Create Insert command

Edhy Rijo — Wed, 30 Jan 2013 08:28:25 GMT

Hi Michael,

You manage these properties on a per BO basis so you have to make sure you configure each BO as its table requires. Look at those properties descriptions and you should be able to set them properly as needed.

RE: Cannot Create Insert command

StrataFrame Team — Wed, 30 Jan 2013 08:26:38 GMT

Hrm. What is the exception that it throws?

RE: Cannot Create Insert command

Michael Reese — Wed, 30 Jan 2013 08:03:24 GMT

Thanks Ben,

That did it.

So, now I have another problem. The business object throws an error when I save to another table that does not have a primary key when I attempt to create a record.

Thanks

Michael

RE: Cannot Create Insert command

StrataFrame Team — Tue, 29 Jan 2013 16:41:59 GMT

On the business object itself, is the PrimaryKeyIsAutoIncremented property set to True? It should be in the CRUD category in the designer. It doesn't matter how the data source's table is actually configured. If that property on the business object it True, it's going to cause that error.