By Terry Bottorff - 11/27/2010
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.
CREATE PROCEDURE [dbo].[sp_CountSRStockforDups] -- Add the parameters for the stored procedure here @myRowCount int = 0 OUTPUTASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
-- Insert statements for procedure here select stock, count(stock) as pen from dbo.SRstocks where stock > 0 group by stock having (count(stock)) > 1 select @myRowCount = @@ROWCOUNT return @myRowCountEND
I know I have 2 records that repeat but with the following code it returns zero(0).? Dim nret As Integer = 1000
Dim cmd As New SqlCommand("sp_CountSRStockforDups")
cmd.CommandType = Data.CommandType.StoredProcedure
' OutPut Parameter
cmd.Parameters.Add("@myRowCount", Data.SqlDbType.Int).Direction = Data.ParameterDirection.Output
nret = CType(cmd.Parameters("@myRowCount").Value, Integer) Please understand I am not sure any of this is correct except the attached picture seems to indicate that my stored proc is working but any help would be appreciated. TIA |
By Ivan George Borges - 11/27/2010
Hi Terry.
Haven't you forgotten to execute the query?
Guess your code is in a BO Data Retrieval Method, so right after the line where you Add your parameter, put the following code:
Me.ExecuteNonQuery(cmd)
And then just return your output parameter value the way you are doing and you should be fine.
|
By Terry Bottorff - 11/27/2010
Actually I am running the code in the click of a button and so when I putme.ExecuteNonQuery(cmd)
I get an error that says ExecuteNonQuery is not a member of Rodeo.CheckforDupsfrm I know that it probably just in not the correct syntax because it is in the click of a button but I tried a couple of things and they did not work either.?
|
By Terry Bottorff - 11/27/2010
I think I found it.MicroFour.StrataFrame.Data.DataBasics.DataSources("").ExecuteNonQuery(cmd)
Thanks for the help.
|
By Ivan George Borges - 11/27/2010
You're welcome. 
|
By Edhy Rijo - 11/28/2010
Hi Terry,
Your approach is correct but I prefer to have all data validation code at the business level so it can be use from other places if you need to. So what I do is create a sub or function in the BO that will return the desired value, in your case you want to know if a duplicate record exist, so I would do this:
''' <summary>
''' This a generic function to find duplicate records in tables where the Primary Key is an integer
''' </summary>
''' <returns>True if duplicate records are found.</returns>
''' <remarks>This code will only work for Integer PK, if using other type, simply change the CType in the return statement.</remarks>
Public Function DuplicateRecordFound() As Boolean
'-- 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({0}) FROM {1} WHERE {0} > 0 GROUP BY {0} HAVING COUNT(1) > 0", Me.PrimaryKeyField, Me.TableNameAndSchema)
'-- Execute the query and return the value
Return CType(Me.ExecuteScalar(cmd), Integer) > 0
End Using
End Function
Then if you need to use it from any code where you don't have an instance of the BO, you can do this:
Using bo As New bizTransactionItems
If bo.DuplicateRecordFound = True Then
' Do whatever you want here.
End If
End Using
So if you are using a base BO, simply copy above function and it will work with any BO with an Integer Primary Key. Also noticed the use of "Using bo" in the sample calling code to properly dispose the bo instance and by the way, in this case, no need to create a Stored Procedure because the code is pretty simply and would not make any difference in speed or connection, and remember the idea is to have it generic so it can be used in any BO.
Have fun!!!
|
By Trent L. Taylor - 11/28/2010
Terry, I agree with Edhy. It is never good Mojo to put your query logic in a button. You are better of encapsulating your query logic in the BO or a Singleton class you use to execute queries in this manner. By embedding your code in the button click and the logic of your application, you break encapsulation which is far more prone to downstream bugs.
|
By Terry Bottorff - 11/28/2010
Oh if my learning curve was not straight up enough. Now it has a curve backwards. Wow Thanks>But on the statement: Using bo As New bizTransactionItemsI get the following error: Type bizTransactionItems is not defined. I did replace bo with my BO name? I can not seem to find bizTransactionsItems in the Strataframe Help. |
By Edhy Rijo - 11/28/2010
Hi Terry,
Sorry for the confusion, bizTransationItems is one of my BO. I simply paste one of my code as a sample. 
You must use your own BO class.
We are in a constant learning and since my switch from VFP a couple of years ago to StrataFrame and VB.NET you will not believe how many times I have refactored by code based on posting in these forums. 
|
By Terry Bottorff - 11/29/2010
Thank you so much. That makes sense. Zowie this stuff is cool.
|
By Charles R Hankey - 11/29/2010
Hey Edhy -
It's Monday and I have SET STUPID ON but could you clarify the select statement? It looks like you are looking for records with duplicate primary keys?
I have a function like this checking dupes but I pass in the where that determines what a "match" is.
This looks like you are checking for duplicate primary keys? Isn't that prevented on the backend to start with ?
(or do I completely misunderstand what is going on ?)
Could you show an example of what the string.format would resolve to?
|
By Edhy Rijo - 11/29/2010
Hi Charles,
SET STUPID OFF (on my side  )
Yes you are right, this one is looking for duplicates in the primary key field, but the function can be enhanced by passing the field name to be used in the SELECT statement, here is an untested sample code, please let us know if this may work for you?
''' <summary>
''' This a generic function to find duplicate records in tables where the Primary Key is an integer
''' </summary>
''' <param name="FieldName">The field name used in the SQL to check for duplicate.</param>
''' <returns>True if duplicate records are found.</returns>
''' <remarks>This code should only work any field type.</remarks>
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({0}) FROM {1} WHERE {0} > 0 GROUP BY {0} HAVING COUNT(1) > 0", FieldName, Me.TableNameAndSchema)
'-- Execute the query and return the value
Return CType(Me.ExecuteScalar(cmd), Integer) > 0
End Using
End Function
''' <summary>
''' Ensures that the column specified exists within the table.
''' </summary>
''' <param name="table"></param>
''' <param name="columnName"></param>
''' <param name="columnType"></param>
''' <remarks></remarks>
Public Function CheckColumnExists(ByVal table As DataTable, ByVal columnName As String, ByVal columnType As System.Type) As Boolean
'-- Establish Locals
Dim col As DataColumn
Dim columnFound As Boolean = False
'-- Cycle through all of the columns so we can test on a case-insensitive basis
For Each col In table.Columns
'-- Check to see if the column name matches
columnFound = col.ColumnName.Equals(columnName, StringComparison.OrdinalIgnoreCase)
'-- If the column was found, exit
If columnFound Then Exit For
Next
'-- If the column is not found then throw and error letting the developer knows that the field name passed is wrong.
CheckColumnExists = columnFound
If Not columnFound Then
Dim errMsg As String = String.Format("Make sure the field name [{0}] exist in this table {1}.", columnName, Me.TableNameAndSchema)
Throw New Exception(errMsg)
End If
End Function
|
By Charles R Hankey - 11/29/2010
>> 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.
|
By Edhy Rijo - 11/29/2010
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 
|
By Charles R Hankey - 11/29/2010
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.
|
By Edhy Rijo - 11/29/2010
Charles R Hankey (11/29/2010)
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
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  )
|
By Terry Bottorff - 11/29/2010
I did find the Primary Key problem and I was able to change that but now with All of the Great Ideas I will be looking to change almost all of my fill routines.
|
By Charles R Hankey - 11/29/2010
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 )
|
By Edhy Rijo - 11/29/2010
Terry Bottorff (11/29/2010)
...but now with All of the Great Ideas I will be looking to change almost all of my fill routines.
Hi Terry,
I've been there, done that! Welcome to the club
Glad you find it useful.
|
By Edhy Rijo - 11/29/2010
Charles R Hankey (11/29/2010)
(... 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.
|
By Terry Bottorff - 11/29/2010
Is there some where I can find a basic subclass of the BO that has Everything that is needed to make it work so I can start adding some of these functions and not have to do it on Every BO?Thanks in Advance.
|
By Edhy Rijo - 11/29/2010
Hi Terry,
Yes, there are some sample code in the forums, probably Ivan (the Google expert) may provide some links, but basically this what you have to do:
- Create a BO class and do not map it to any of your tables. You could name it whatever you want, I name mine "ApplicationBaseBusinessClass.vb"
- Add all the custom, generic code you expect to use in all your BOs like the ones I posted here. Also if you use some custom field properties in all your BO with the same name, here is a good place to have them all.
- In your current and new BO, change the "Inherits" statement from:
Inherits MicroFour.StrataFrame.Business.BusinessLayer
to
Inherits CardTrackingSystem.Business.ApplicationBaseBusinessClass
Of course in the sample above the namespace "CardTrackingSystem" should be replace with your own NameSpace. After that, recompile your project and start having fun with your base BO class.
|
By Terry Bottorff - 11/29/2010
So let me get this straight. - I create a BO (say BaseBO) and I don't map it to any table.
- I then add MY Functions, Sub Routines and whatever to my BaseBO.
- Once I have 1 and 2 done Then when I create a new BO I change the Inherits MicroFour.Strataframe.Business.BusinessLayer
- to Inherits Rodeo.Business.BaseBO Where Rodeo is I believe my Namespace.
TIA.
|
By Edhy Rijo - 11/29/2010
Yeap you are now in the right track, so go for it. Also for your current BO all you need to do is change the Inherits to your new base BO, the same thing applies to forms and other controls.
|
By Ger Cannoll - 11/30/2010
Hi Edhy.
This might sound like a silly question but I 'Asumed' that a primary key field in SQL 'had' to be unique and you could not have duplicates ?. This must'nt be the case if you have written a routine to validate it ??
|
By Edhy Rijo - 11/30/2010
Gerard O Carroll (11/30/2010)
This might sound like a silly question but I 'Asumed' that a primary key field in SQL 'had' to be unique and you could not have duplicates ?. This must'nt be the case if you have written a routine to validate it ??
Hi Gerard,
Nothing silly about it. Even thought you may come across an Integer PK which is not auto incremented by the database, but you are right. That was a code I wrote fast without testing for Terry, but was corrected in the following messages.
|
|