I have the following Stored Proc (I am not sure it is correct). I just am trying to see if there are any duplicates in the table.

  >> cmd.CommandText = String.Format("SELECT COUNT({0}) FROM {1} WHERE {0} > 0 GROUP BY {0} HAVING COUNT(1) > 0", Me.PrimaryKeyField, Me.TableNameAndSchema)

I'd suggest :
cmd.CommandText = String.Format("SELECT COUNT({0}), {2}  FROM {1} GROUP BY {2} HAVING COUNT({0}) > 1", Me.PrimaryKeyField, Me.TableNameAndSchema, Fieldname)
where fieldnames is a single field or comma separated string of fields that defines a dupe


I was thrown by looking for dupes based on PK as the whole idea of a PK is that they be unique to begin with.  

Hi Charles,

I had to do the test this time, since it is not productive posting without testing, but sometimes, there is not much time to get everything right the first time   so here is a generic statement that will check for duplicate for a single field, of course this could be enhanced to go deeper with multiple fields and "WHERE" conditions per needs like in Terry's case in which he needs a WHERE condition.

    Public Function DuplicateRecordFound(ByVal FieldName As String) As Boolean
        If Not Me.CheckColumnExists(Me.CurrentDataTable, FieldName, GetType(String)) Then
            Exit Function
        End If
 
        '-- Create the SqlCommand with an Using to automatically dispose it.
        Using cmd As New SqlCommand
            cmd.CommandType = CommandType.Text
            cmd.CommandText = String.Format("SELECT COUNT(*) FROM {1} GROUP BY {0} HAVING COUNT(*) > 0", FieldName, Me.TableNameAndSchema)
 
            '-- Execute the query and return the value
            Return CType(Me.ExecuteScalar(cmd), Integer) > 1
        End Using
    End Function

Here is a sample on how to call the function in the BO.  Of course if you have this function in your base BO it will be available to all of them and just need to pass the field name to check for duplicate:

        If Me.BizCompany1.DuplicateRecordFound(bizCompany.bizCompanyFieldNames.CompanyName.ToString) Then
            MessageBox.Show("Duplicate found.", "Duplicate check", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else
            MessageBox.Show("No duplicates found.", "Duplicate check", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If
 
        Using bo As New bizCompany
            If bo.DuplicateRecordFound(bizCompany.bizCompanyFieldNames.CompanyName.ToString) Then
                MessageBox.Show("Duplicate found.", "Duplicate check", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Else
                MessageBox.Show("No duplicates found.", "Duplicate check", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End If
        End Using

Terry, notice that in above sample code I am not passing the field name as literal string "Field Name" or "CompanyName", instead I am taking advantage of the business object field name enumeration which will be named after your bo+FieldNames, this is used, so if you change a field name later on, at least Visual Studio will warn you at compilation giving you the chance to correct the field name before building your application.

Charles, thanks again, for looking into the code and your suggestions, greatly appreciated as always.  Now back to work, hope this test pass

Edhy Rijo

This looks good.  Also, if you pass a comma separated list of fields to the fieldname param it will work fine for defining a dupe based on multiple fields

Dim fieldlist as string = "field1, field2, field3"

then pass fieldlist as the param.

Charles, I agree, but I would prefer a param array and then check if Column Exist for each one before executing the query.  Also a 2nd optional parameter could be use to handle "WHERE conditions like in Terry's case.

So far, I did not have the need for such checking, but since I work on multiple projects at the same time, I like to have my code as generic as possible so it can be reused in all projects, even though I do not have a common assembly for all projects just yet

I am still learning this stuff which always amaze me with simple things and the .Net/SF approach to handle them.  Pure and simple, I just love SF at the same level I loved VFP and my previous framework.  It feels good being comfortable with a tool and I am at that level with StrataFrame now.  (hmmm, Trent, that does not mean I am totally settle,  I still want to see the new StrataListView completed with the automation stuff )

Edhy Rijo

I think your idea of an array for multiple fields is very good, especially in keeping the function as generic as possible.

Just as an aside :  I've been working a lot with using table value parameters in stored procedures and I'm really interested to find that unlike other type of params, you don't need to account for *not* passing the param - the TVP in the sproc will just be an empty table.  Interesting in that you can write where clauses in the sproc that let you receive either a single or TVP param for comparison and it will work depending on what you pass in. 

( reading that over it probably doesn't make any sense unless you've been playing with TVPs but for anybody who has, the TVP as empty table if no param is passed came as a surprise to me and somebody may find it useful )

Hi Charles,

I have not worked with a TVP yet.  I have a consultant "Uri Dimant" which is a MS SQL MVP which helps me with those deep needs and even though he is pretty good explaining this type of things with MS-SQL, sometimes I don't have the luxury to pay to much of attention to details.  He has created some complex stored procedures to deal with millions of records and re-factored those couple of times to take advantage of new functionality of MS-SQL 2008 R2.  In case you or anyone needs serious help with any MS-SQL problem, feel free to contact him.

Edhy Rijo