Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2015-01-31
2
Medium Priority
?
297 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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.

916 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