Finding duplicates in a column with SQL query

Need to find a duplicate in a column? You can use a query like this:

select accountID, COUNT(accountID) AS NumOccur
FROM myTable
group by accountID HAVING (COUNT(accountID)>1)

Any duplicates will be returned. That easy

~Wayne

Comma Delimited List From SQL Server

Have you ever needed a list of values from a database based on a join? I have and this is what I have found. What I was trying to do was to return two rows from a database, ID and values. I had a table that housed an ID. This ID could be singular or multiple (i.e. 1 or 1,2,3). I needed to find the values for each of those ID’s. Here is what I came up with:

SELECT ID,
(select stuff((SELECT distinct ',' + value as [text()]
FROM tablename
WHERE ID in (SELECT * FROM ss.ID))
ORDER BY ',' + value
FOR xml path('')),1,1,'')) as 'MultiValue'
FROM tablename1 ss
WHERE ss.Value …

This will return results that start like this:
ID VALUE
1 value1
2 value2
3,4,5 value3,value4,value5

All you have to do is make sure you replace the values I put in there with your tables and column names. Make sure your consistent as the result will not be what you expect if not. With that you can fill in a grid view or a spreadsheet with the comma delimited results. Works like a charm.

Hope this helped someone. Have fun coding and as always, if there are any questions or suggestions, they are always welcome. Thank you.

Using Windows Desktop Search in ASP.NET VB Application

I was searching around the Internet for something or someone to help me with seaching files in a given directory.  No, not that easy, it had to be recursive.  That is where I ran into problems.  As I was looking, I came across something that I wouldn’t even have thought of, why not use windows desktop search?! I was like what?  There is no way.  Then I got to thinking, it’s Microsoft.  Why not. So I dove right in. I am all up for learning something new.  Even if it is something I was not even looking for to begin with, sort of.  Well, I got it working and I thought I would share with you what I did and post some code for you to look at.  I can even use multiple keywords to search on. I do use drop downs vice free for text field though.  I already know what the files are named if you have been following the blog, you know what I am refering to.  With that said, lets get into some code.

 

First here is the code for the datagrid.  I chose to use a datagrid because of the ease of creating the columns.  It was simple to create a hyperlink field. See below.

 

<asp:DataGrid runat=”server” ID=”datagrid1″ Font-Name=”Verdana”

AutoGenerateColumns=”False”

AlternatingItemStyle-BackColor=”#eeeeee”

HeaderStyle BackColor=”Navy”

HeaderStyle-ForeColor=”White”

HeaderStyle-Font-Size=”15pt”

HeaderStyle-Font-Bold=”True”>

<AlternatingItemStyle BackColor=”#EEEEEE”>

</AlternatingItemStyle>

<Columns>

<asp:HyperLinkColumn

DataNavigateUrlField=”Path”

DataTextField=”Filename”

HeaderText=”File Name” />

</Columns>

<HeaderStyle BackColor=”Navy”

Font-Bold=”True”

Font-Size=”15pt”

ForeColor=”White”>

</HeaderStyle>

</asp:DataGrid>

 

On this page I also have drop downs and a search button.  That is what is used to perform the search. 

 

In the code behind in the Page_Load function(I really wish MVC would be finished so I could start using that, but that’s another story) I added the following:

 

If Page.IsPostBack Then

      Dim keyword1 As String = dropdown1.Text

      Dim keyword2 As String = dropdown2.Text

      Dim keyword3 As String = dropdown3.Text

      Dim keyword4 As String = dropdown4.Text

      Dim strCatalog As String

      ‘ Catalog Name

      strCatalog = “catalogNameHere”

      Dim strQuery As String

      strQuery = “select create, filename, path from scope() “

      strQuery = strQuery & ” WHERE FILENAME LIKE ‘%” & keyword1 & “%’”

      strQuery = strQuery & “AND FILENAME LIKE ‘%” & keyword2 & “%’”

      strQuery = strQuery & “AND FILENAME LIKE ‘%” & keyword3 & “%’”

      strQuery = strQuery & “AND FILENAME LIKE ‘%” & keyword4 & “%’”

      strQuery = strQuery & ” AND Path NOT LIKE ‘%vti%’ ORDER BY create DESC”

      Dim connString As String = “Provider=MSIDXS.1;Integrated Security .=”;Data Source=’”

      & strCatalog & “‘”

      Dim cn As New

      System.Data.OleDb.OleDbConnection(connString)

        Dim cmd As New

      System.Data.OleDb.OleDbDataAdapter(strQuery, cn)

            Dim testDataSet As New Data.DataSet()

            cmd.Fill(testDataSet)

            Dim srvrnm As String = Server.MachineName

            Dim dt As DataTable = testDataSet.Tables(0)

            Dim rownum As Integer = 0

            For Each row As DataRow In dt.Rows

            dt.Rows(rownum)(“path”) = dt.Rows(rownum)(“path”). _

      ToString.Replace(“c:\inetpub\dirname\”, “\\” & srvrnm & “\cgweb\”)

                rownum = rownum + 1

            Next

            Dim source As Data.DataView = testDataSet.Tables(0).DefaultView

            datagrid1.DataSource = dt

            datagrid1.DataBind()

      End If

Now step by step through the code.

First we create variables to hold the values of the dropdowns to use later in the query.

Dim keyword1 As String = dropdown1.Text

Dim keyword2 As String = dropdown2.Text

Dim keyword3 As String = dropdown3.Text

Dim keyword4 As String = dropdown4.Text

 

We then create a string variable to hold the catalog name (which will be covered later).

‘ Catalog Name

strCatalog = “catalogNameHere”

 

Now we build our query using dynamic data. Like I said previosly, I used drop down because the file names are known. Here is the query string being built:

Dim strQuery As String

strQuery = “select create, filename, path from scope() “

strQuery = strQuery & ” WHERE FILENAME LIKE ‘%” & keyword1 & “%’”

strQuery = strQuery & “AND FILENAME LIKE ‘%” & keyword2 & “%’”

strQuery = strQuery & “AND FILENAME LIKE ‘%” & keyword3 & “%’”

strQuery = strQuery & “AND FILENAME LIKE ‘%” & keyword4 & “%’”

strQuery = strQuery & ” AND Path NOT LIKE ‘%vti%’ ORDER BY create DESC”      

 

We then have to create our connection string to use the catalog and then fill a dataset with the data returned:

Dim connString As String = “Provider=MSIDXS.1;Integrated Security .=”;Data Source=’” & strCatalog & “‘”\

Dim cn As New

System.Data.OleDb.OleDbConnection(connString)

Dim cmd As New

System.Data.OleDb.OleDbDataAdapter(strQuery, cn)

Dim testDataSet As New Data.DataSet()

cmd.Fill(testDataSet)

 

I needed to replace some of the text before it was displayed so I set the dataset to a datatable and looped through the rows and changed what I needed. I use the varibale rownum so I can look at each row in the loop.  See below:

Dim srvrnm As String = Server.MachineName

Dim dt As DataTable = testDataSet.Tables(0)

Dim rownum As Integer = 0                

For Each row As DataRow In dt.Rows

dt.Rows(rownum)(“path”) = dt.Rows(rownum)(“path”).ToString.Replace _

(“c:\inetpub\dirname\”, “\\” & srvrnm & “\newdirname\”)

rownum = rownum + 1

Next

 

Last but not least I databind the datatable to the datagrid on the display page.

datagrid1.DataSource = dt

datagrid1.DataBind()

 

That’s it. Not too bad. I was happy with myself. I do have to add in the handling of the ‘no records found’ still.

 

As always, if there are any questions or suggestions, they are welcome. Thank you.

P.S. I will work on my coloring scheme. Having issues at the moment.

Working With a Database in C#

I do not use C# much. Here is some code I found while browsing the Internet that helps to connect to a database:

using System.Data;
using System.Data.SqlClient;
string cs = @"Data Source=.\SQLEXPRESS;" +
string cs = @"Initial Catalog=NamesDB;" +
string cs = @"Integrated Security=True;";
using (SqlConnection con = new SqlConnection(cs)) {
con.Open();
string sql = "INSERT INTO Names(Name) VALUES(@Name)";
// insert a record
SqlCommand cmd1 = new SqlCommand(sql, con);
cmd1.Parameters.Add("@Name", SqlDbType.NVarChar, 100);
cmd1.Parameters["@Name"].Value = "Bob";
cmd1.ExecuteNonQuery();
// insert a second record
cmd1.Parameters["@Name"].Value = "David";
cmd1.ExecuteNonQuery();
// read records
sql = "SELECT * FROM Names";
SqlCommand cmd2 = new SqlCommand(sql, con);
using (SqlDataReader r = cmd2.ExecuteReader()) {
int iName = r.GetOrdinal("Name");
while (r.Read()) {
Console.WriteLine(
r.IsDBNull(iName)?"Null":r.GetString(iName)
);
}
}
// read a single value
sql = "SELECT TOP 1 Name FROM Names";
SqlCommand cmd3 = new SqlCommand(sql, con);
Console.WriteLine(cmd3.ExecuteScalar());
}

Good luck and Happy Coding!