|
Edhy Rijo
|
|
|
Group: StrataFrame Users
Posts: 2.4K,
Visits: 23K
|
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 ForEnd IfHere 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.
Edhy Rijo
|
|
|
|
|
Greg McGuffey
|
|
|
Group: Forum Members
Posts: 2K,
Visits: 6.6K
|
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
|
|
|
|
|
Greg McGuffey
|
|
|
Group: Forum Members
Posts: 2K,
Visits: 6.6K
|
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
|
|
|
|
|
Edhy Rijo
|
|
|
Group: StrataFrame Users
Posts: 2.4K,
Visits: 23K
|
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!
Edhy Rijo
|
|
|
|
|
Trent Taylor
|
|
|
Group: StrataFrame Developers
Posts: 6.6K,
Visits: 6.9K
|
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.
|
|
|
|
|
Greg McGuffey
|
|
|
Group: Forum Members
Posts: 2K,
Visits: 6.6K
|
Good to hear Edhy! Yeah, I use the string format command a lot too.  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?".
|
|
|
|
|
Edhy Rijo
|
|
|
Group: StrataFrame Users
Posts: 2.4K,
Visits: 23K
|
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.
Edhy Rijo
|
|
|
|
|
Juan Carlos Pazos
|
|
|
Group: Forum Members
Posts: 144,
Visits: 227
|
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 BooleanDim 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.Intcmd.Parameters.AddWithValue( "@numeroGuia", GuiaNumero).SqlDbType = SqlDbType.VarChar'-- Execute and return the resultsReturn CType(Me.ExecuteScalar(cmd), Integer) = 0End 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) ThenMe.AddBrokenRule(GuiasEnviosBOFieldNames.NumeroGuia, "Duplicated")End IfEnd SubBut 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
 Everything is possible, just keep trying...
|
|
|
|
|
Ivan George Borges
|
|
|
Group: StrataFrame MVPs
Posts: 1.9K,
Visits: 21K
|
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.
|
|
|
|
|
Juan Carlos Pazos
|
|
|
Group: Forum Members
Posts: 144,
Visits: 227
|
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
Everything is possible, just keep trying...
|
|
|
|