Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

VB.net SQL Find first and last text items in a column

Posted on 2015-01-31
2
Medium Priority
?
308 Views
Last Modified: 2015-01-31
Hi

I use the following VB.net code to find the maximum value in numeric and date columns
How do I find the maximum or minimum of an text column where for instance "Albert" would
be the minimum and "Zach" would be the maximum

 Function oMaximum_in_Column(ByVal oTableName As String, ByVal oColumnName As String) As String
        Try

            Dim oSQL As String

            oSQL = "Select Max([" & oColumnName & "]) As MyMax From [" & oTableName & "]"

            Dim cn As New SqlConnection(Globals.ThisAddIn.oRIGHT.lblConnectionString.Text)
            Dim cmd As New SqlCommand(oSQL, cn)
            cmd.CommandTimeout = My.Settings.Timeout
            cn.Open()

            oMaximum_in_Column = cmd.ExecuteScalar.ToString

        Catch ex As Exception
            oMaximum_in_Column = "N/A"
        End Try
    End Function
0
Comment
Question by:Murray Brown
2 Comments
 
LVL 34

Accepted Solution

by:
Mike Eghtebas earned 2000 total points
ID: 40581376
oSQL = "Select Max(t.[" & oColumnName & "]) As MyMax, Min(t.[" & oColumnName & "]) As MyMin From [" & oTableName & "] As t"

Dim rdr As SqlDataReader = oSQL.ExecuteReader()

If (rdr.HasRows) Then
   oMaximum_in_Column = rdr("MyMax").ToString
   oMinimum_in_Column = rdr("MyMin").ToString
Else
   oMaximum_in_Column = "no records exist"
   oMinimum_in_Column = "no records exist"
End

revised...
0
 

Author Closing Comment

by:Murray Brown
ID: 40581437
Thanks very much
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question