Cannot Create Insert command

StrataFrame Forum

Back

Cannot Create Insert command

Author	Message
Michael Reese	Michael Reese posted 12 Years Ago HOT Topic Details Share Topic
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
	Reply Like 2
Replies
Michael Reese	Michael Reese posted 12 Years Ago Post Details Share Post
Group: StrataFrame Users Posts: 235, Visits: 1.6K	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.
	Reply Like 2
StrataFrame Team	S StrataFrame Team posted 12 Years Ago Post Details Share Post
S Group: StrataFrame Developers Posts: 3K, Visits: 2.5K	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
	Reply Like 2
Michael Reese	Michael Reese posted 12 Years Ago Post Details Share Post
Group: StrataFrame Users Posts: 235, Visits: 1.6K	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
	Reply Like 2
Edhy Rijo	E Edhy Rijo posted 12 Years Ago Post Details Share Post
E 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
	Reply Like 1
StrataFrame Team	S StrataFrame Team posted 12 Years Ago Post Details Share Post
S 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.
	Reply Like 1

Merge into selected topic...

Merge into merge target...

Merge into a specific topic ID...