StrataFrame Forum

Using the BO.Seek() method to find duplicate record in current view....

http://forum.strataframe.net/Topic16872.aspx

By Edhy Rijo - 6/5/2008

Hi All,

I have a process that will create some customer payment records, in this process I can not allow a duplicate record to be created, so in my process I need to look for any possible duplicate record and then skip the creation of the duplicate one.

I am trying to do this with the BO.Seek, but I am getting an error which I can not properly identify.  Here my code:

strSeekWhere("FK_InsuredCustomer=51 and FK_Vehicle=0 and FK_Policy=34 and PaymentNumber=0 and PaymentDueDate=5/31/2008 12:00:00 AM and PaymentAmount=500.0000")

If loCurrentPaymentScheduleBO.Seek(strSeekWhere) Then

    Continue For

End If

Here is the exeption error:

SyntaxErrorException
  Syntax error: Missing operand after '12' operator.

Source     : System.Data

Stack Trace:
   at System.Data.ExpressionParser.Parse()
   at System.Data.DataExpression..ctor(DataTable table, String expression, Type type)
   at System.Data.Select..ctor(DataTable table, String filterExpression, String sort, DataViewRowState recordStates)
   at System.Data.DataTable.Select(String filterExpression, String sort, DataViewRowState recordStates)
   at MicroFour.StrataFrame.Business.BusinessLayer.Seek(String WhereClause)
   at IBS_BOL.PolicyBO.CreatePaymentSchedule(Int32 pInsuredCustomerPK, PaymentScheduleBO loCurrentPaymentScheduleBO, DateTime PaymentStartDate, Int32 pVehiclePK) in E:\Visual Studio 2008 Projects\StrataFrame\Insurance Broker System (SF)\BOL\IBS_BOL\Main Forms BOs\PolicyBO.vb:line 217
   at IBS_UI.frmPolicy.tsbCreatePaymentSchedule_Click(Object sender, EventArgs e) in E:\Visual Studio 2008 Projects\StrataFrame\Insurance Broker System (SF)\UI\Main Forms\frmPolicy.vb:line 389
   at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)
   at System.Windows.Forms.ToolStripButton.OnClick(EventArgs e)
   at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)
   at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)
   at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)
   at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)
   at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
   at System.Windows.Forms.ToolStrip.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativewindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativewindow.WndProc(Message& m)
   at System.Windows.Forms.Nativewindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

What am I doing wrong here? or is there any other way to try to identify a possible duplicate record in the BO.CurrentView.

By Greg McGuffey - 6/5/2008

you aren't delimiting the date. Try putting ## around the date...

strSeekWhere("FK_InsuredCustomer=51 and FK_Vehicle=0 and FK_Policy=34 and PaymentNumber=0 and PaymentDueDate=#5/31/2008 12:00:00 AM[b]# and PaymentAmount=500.0000")



If loCurrentPaymentScheduleBO.Seek(strSeekWhere) Then



Continue For



End If
By Greg McGuffey - 6/5/2008

Oops, I tried to bold the #, which didn't show up very well and left a [b] in the code:





strSeekWhere("FK_InsuredCustomer=51 and FK_Vehicle=0 and FK_Policy=34 and PaymentNumber=0 and PaymentDueDate=#5/31/2008 12:00:00 AM# and PaymentAmount=500.0000")



If loCurrentPaymentScheduleBO.Seek(strSeekWhere) Then



Continue For



End If
By Edhy Rijo - 6/5/2008

Greg McGuffey (06/05/2008)
you aren't delimiting the date. Try putting ## around the date...

Hi Greg,

That was it!  I am trying to format the values use in the filter and did not noticed that the date was coming out without the ##.  I am falling in love with the String.Format command, here the final version:

strSeekWhere = String.Format("FK_InsuredCustomer={0} and FK_Vehicle={1} and FK_Policy={2} and PaymentNumber={3} and PaymentDueDate=#{4}# and PaymentAmount={5}", pInsuredCustomerPK, pVehiclePK, Me.PK_Policy, PaymentNumber, PaymentDueDateItem, PaymentAmountDueItem)

Thanks a lot!

By Trent L. Taylor - 6/6/2008

All good stuff...the only time that this will not work for you Edhy, is if you don't have all of the records in the BO itself.  In that case, you will want to create a scalar method and query the server...this is generally what we will do.  For example:

Private Function IsUniqueCode(ByVal currentRecordPk As Integer, ByVal customerCode as String) As Boolean
Dim cmd As New SqlCommand("SELECT COUNT(*) FROM Customers WHERE cs_pk != @currentRecordPk AND cs_CustomerCode = @customerCode")

'-- Create the parms.  The reason you include the current record PK is so that the current record
'    will be ignored and only look for other records with the same code value.
cmd.Parameters.AddWithValue("@currentRecordPk",currentRecordPk).SqlDbType = SqlDbType.Int
cmd.Parameters.AddWithValue("@customerCode",customerCode).SqlDbType = SqlDbType.VarChar

'-- Execute and return the results
Return CType(Me.ExecuteScalar(cmd), Integer) = 0

End Function

If you have the need, you can then combine an internal search of the BO with the testing of the value on the server.

By Greg McGuffey - 6/6/2008

Good to hear Edhy! Yeah, I use the string format command a lot too. BigGrin



I also second what Trent is saying, that you might just consider using the scalar method to check the db directly. My first thought when reading this was "what if another user added an payment after the BO was filled?".
By Edhy Rijo - 6/6/2008

Trent, Greg,

Thanks again for the good advice.

I will create the scalar method just to be on the safe side.  In this case, the payments will be created only by one user, just to make sure it is correct, and this is done once a year.  I just wanted to add a bit of code to make sure in case that person need to delete a payment record (which of course has not been paid) could do so and that the routine to do it should make sure not duplicate payments records be created. 

In this case the PaymentScheduleBO should always have filled with all the payments to validate, but the scalar method idea will add another safety and fool proof this process even more.

By Juan Carlos Pazos - 10/24/2008

Hi

I take a look to the samples provided for check for duplicates, but I can not find how to work on this.

In the same situation as Edjy, Where does should the Function IsUniqueCode should go.

I put this in the BO like this:

Public Function IsUniqueCode(ByVal IdRegistro As Integer, ByVal GuiaNumero As String) As Boolean

Dim cmd As New SqlCommand("SELECT COUNT(*) FROM GuiasEnvios WHERE IdGuia != @idGuia AND NumeroGuia = @numeroGuia")

'-- Create the parms. The reason you include the current record PK is so that the current record

' will be ignored and only look for other records with the same code value.

cmd.Parameters.AddWithValue("@idGuia", IdRegistro).SqlDbType = SqlDbType.Int

cmd.Parameters.AddWithValue("@numeroGuia", GuiaNumero).SqlDbType = SqlDbType.VarChar

'-- Execute and return the results

Return CType(Me.ExecuteScalar(cmd), Integer) = 0

End Function

 and call this way in the BO:

Private Sub GuiasEnviosBO_CheckRulesOnCurrentRow(ByVal e As MicroFour.StrataFrame.Business.CheckRulesEventArgs)

If IsUniqueCode(Me.IdGuia, Me.NumeroGuia) Then

Me.AddBrokenRule(GuiasEnviosBOFieldNames.NumeroGuia, "Duplicated")

End If

End Sub

But when I edit the record It says that the record value is duplicated, so it's not ignoring the current record. Also if I add a new one.

Hope you can point me in the rigth path.

Regards
By Ivan George Borges - 10/25/2008

Hi Juan.

Have a look at your table and see if you don't have already more than 1 record with the same NumeroGuia content.

By Juan Carlos Pazos - 10/25/2008

Hi Ivan

I have one record, after add the first if I add a new one (with different number) fires the duplicate record message, also if I try to edit the same record.

Regards

By Ivan George Borges - 10/25/2008

Sorry Juan, I should have read it more carefully.

Have a look at your IF statement:

If IsUniqueCode(Me.IdGuia, Me.NumeroGuia) Then

You are testing if it IS unique. You probably want to test if it is NOT unique. Wink

By Juan Carlos Pazos - 10/25/2008

Hi Ivan

Wink You are right. Thanks

Before read this, I take a look to StrataFlix and found the same thing, I just adapt to my own BO and all works. Thanks for you help.

Private Sub GuiasEnviosBO_CheckRulesOnCurrentRow(ByVal e As MicroFour.StrataFrame.Business.CheckRulesEventArgs)

'-- Verifica que si se ingreso el n£mero de gu¡a sea £nico

If Me.NumeroGuia.Length > 0 AndAlso (Not EsGuiaUnica()) Then Me.AddBrokenRule(GuiasEnviosBOFieldNames.NumeroGuia, "El n£mero de gu¡a ya ha sido utilizado.")

End Sub

Private Function EsGuiaUnica() As Boolean

'-- Establish Locals

Dim cmd As New SqlCommand("SELECT COUNT(*) FROM GuiasEnvios WHERE IdGuia != @idGuia AND NumeroGuia = @numeroGuia")

'-- Create the parms

cmd.Parameters.AddWithValue("@idGuia", IdGuia).SqlDbType = SqlDbType.Int

cmd.Parameters.AddWithValue("@numeroGuia", NumeroGuia).SqlDbType = SqlDbType.VarChar

'-- Return the results

Return CType(Me.ExecuteScalar(cmd), Integer) = 0

End Function

Kindest regards

By Ivan George Borges - 10/25/2008

Glad you got it going, Juan!

Just for you to think about, have a look at the Localization & Message Editor. It might be a good idea to start creating your messages with it, and then use it in your application using the message keys, instead of entering an string directly. This way not only you get a centralized editor for all your messages, but are only 1 step away from having a multi-language application.

Have a great weekend. Cool

By Trent L. Taylor - 10/26/2008

One other thought to cap off this post, all of the server side query information from my posts and others is on target, but if you have the need to query within the BO (uncommitted reecord) as well, call a Select on the CurrentDataTable to get a count of records:

Dim hasDupe As Boolean

hasDupe = MyBo.CurrentDataTable.Select("MyPk <> 1 AND MyLastName = 'Taylor'").Length > 0

You can use this in conjunction with the server side query.  Do one and then the other to make sure that there are no dupes on either side of the committed records.

Also, Ivan's suggestions about the localization records are good...it is a much better way to keep up with your messages, etc.  Especially if you have the need to localize an app!

By Juan Carlos Pazos - 10/26/2008

Hi Trent

Thanks for you comment. The other solution is in the BO, this validation you mention, Where should it go? I think that in the Validating event of the control, but I'm not sure.

About localization, I'm working in two apps right now. One has localization for every part of it because is English/Spanish, but the other no, that's why I'm using only direct text for messages.

Kindest regards

By Trent L. Taylor - 10/27/2008

They would both go in the BO when validating through the CheckRulesOnCurrentRow event.  This was just to show that you may need to query uncommitted records as well in addition to those checked on the server to get 100% coverage.  This isn't always necessary, I just thought I would bring it to your attention.