Cannot Create Insert command


Author
Message
Michael Reese
Michael Reese
Advanced StrataFrame User (533 reputation)Advanced StrataFrame User (533 reputation)Advanced StrataFrame User (533 reputation)Advanced StrataFrame User (533 reputation)Advanced StrataFrame User (533 reputation)Advanced StrataFrame User (533 reputation)Advanced StrataFrame User (533 reputation)Advanced StrataFrame User (533 reputation)Advanced StrataFrame User (533 reputation)
Group: StrataFrame Users
Posts: 235, Visits: 1.6K
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

Replies
Edhy Rijo
E
StrataFrame VIP (6.4K reputation)StrataFrame VIP (6.4K reputation)StrataFrame VIP (6.4K reputation)StrataFrame VIP (6.4K reputation)StrataFrame VIP (6.4K reputation)StrataFrame VIP (6.4K reputation)StrataFrame VIP (6.4K reputation)StrataFrame VIP (6.4K reputation)StrataFrame VIP (6.4K reputation)
Group: StrataFrame Users
Posts: 2.4K, Visits: 23K
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:

'' 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


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.

Edhy Rijo

StrataFrame Team
S
StrataFrame Developer (6.5K reputation)StrataFrame Developer (6.5K reputation)StrataFrame Developer (6.5K reputation)StrataFrame Developer (6.5K reputation)StrataFrame Developer (6.5K reputation)StrataFrame Developer (6.5K reputation)StrataFrame Developer (6.5K reputation)StrataFrame Developer (6.5K reputation)StrataFrame Developer (6.5K reputation)
Group: StrataFrame Developers
Posts: 3K, Visits: 2.5K
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.
GO

Merge Selected

Merge into selected topic...



Merge into merge target...



Merge into a specific topic ID...




Threaded View
Threaded View
Michael Reese - 12 Years Ago
StrataFrame Team - 12 Years Ago
Michael Reese - 12 Years Ago
                 Hrm. What is the exception that it throws?
StrataFrame Team - 12 Years Ago
Edhy Rijo - 12 Years Ago
                         Thanks, I was looking for that in the BO properties but could not find...
Michael Reese - 12 Years Ago
                             That's correct. StrataFrame requires that each table have a...
StrataFrame Team - 12 Years Ago
                                 Thanks Ben, I guess I could but it will take a lot of work. I have...
Michael Reese - 12 Years Ago
                                     HL7 you probably never update the records. If you delete an entire...
StrataFrame Team - 12 Years Ago
                                         Thanks , I'll give is a shot. I want to do as much as I can through...
Michael Reese - 12 Years Ago
                                             Hi Michael, If you are parsing millions of record in the BO and then...
Edhy Rijo - 12 Years Ago
                                                 Good call, Edhy. A bulk insert into SQL is always the fastest way to...
StrataFrame Team - 12 Years Ago
Michael Reese - 12 Years Ago
             Mi Michael, Ben, You are welcome.
Edhy Rijo - 12 Years Ago

Similar Topics

Reading This Topic

Login

Explore
Messages
Mentions
Search