?
Solved

VB.net: SQL query range of records

Posted on 2007-07-23
9
Medium Priority
?
298 Views
Last Modified: 2011-10-03
Good Afternoon

Experts:

I am intersted in retrieving a range of records for my VB.net recordset  with the following data on the screen:

1)StartItem and StartLoc
2)EndItem ans EndLoc

Item         Loc
-----        -----
r             Cleve
x            Cleve
y            Cleve
z            Cleve
r             Akron
x            Akron
y            Akron
z            Akron

In "English" terms, I would like to do the following:
Select a range of records starting at Item = y and Loc = Cleve down to Item = y and Loc = Akron

Can you help me achieve this? Is it possible?

Thanks,
jimbo99999
0
Comment
Question by:Jimbo99999
9 Comments
 
LVL 48

Expert Comment

by:jpaulino
ID: 19549910
x , y, z are numbers ?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 19549986
Just modify your table to add an identity column

ALTER tABLE urTable
ADD i int identity

DECLARE @Start int , @End int

SELECT @Start = i
FROM urTable WHERE Item ='Y' and Loc = 'Cleve'

SELECT @End = i
FROM urTable WHERE Item ='Y' and Loc = 'Akron'

SELECT * from urTable
WHERE i between @Start and @End
0
 

Author Comment

by:Jimbo99999
ID: 19549987
jpaulino:

The Items will not be numbers.  Examples are: 099-1001, 101-789, XMatl.

Thanks,
jimbo99999
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 48

Expert Comment

by:jpaulino
ID: 19550009
As aneeshattingal post it you must have an identity column to easy achieve that
0
 

Author Comment

by:Jimbo99999
ID: 19550111
Thanks for the replies.

Unfortunately, I cannot change the table as it is a SAP table.   I looked at the table, and the key is Item/Loc.  Does this help at all?

Thanks,
jimbo99999
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 19550214
Another way is to make a loop thru all table (SELECT * FROM YourTable ORDER BY Item) and start collecting when Item ='Y' and Loc = 'Cleve' and stop when Item ='Y' and Loc = 'Akron', using the IF statement.

It is a slower method but &
0
 

Author Comment

by:Jimbo99999
ID: 19550333
jpaulino:

Pardon my troubles with this topic, as you suggested can this give one recordset at the end after iterating through the table?  I am not aware how to do so.

Thanks,
jimbo99999
0
 
LVL 4

Expert Comment

by:bamboo7431
ID: 19550584
Do you know how the data is sorted in the table?
You're saying the key is item/loc, but your example is not sorted in this order (it's actually loc DESC, item ASC)
0
 
LVL 48

Accepted Solution

by:
jpaulino earned 2000 total points
ID: 19550764
Normally I do it this way

Inside a Module

        Public Function GetConnection() As SqlConnection
        Dim SqlServerName As String = "MYSERVER"
        Dim SqlServerDatabase As String = "myDB"
        Dim SqlServerLogin As String = "efw"
        Dim SqlServerPassword As String = "fwefw"

        Dim conn As SqlConnection

        Dim strConn As String = "Data Source=" & SqlServerName & ";Initial Catalog=" & SqlServerDatabase & ";User ID=" & SqlServerLogin & ";Password=" & SqlServerPassword & ";Integrated Security=SSPI;"

        conn = New SqlConnection(strConn)
        Try

            conn.Open()

        Catch SqlEx As SqlException
            MsgBox("Error SQL:" & vbCrLf & vbCrLf & SqlEx.Message, MsgBoxStyle.Critical)

        Catch ex As Exception
            MsgBox("Error message", MsgBoxStyle.Critical)
     
        End Try
        GetConnection = conn
    End Function


    <System.Diagnostics.DebuggerStepThrough()> _
    Public Shared Function ExecuteReader(ByVal query As String) As SqlDataReader
        Dim rdr As SqlDataReader = Nothing
        Dim conn As SqlConnection = GetConnection()

        Try

            Dim cmd As SqlCommand = New SqlCommand(query, conn)

            rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

        Catch SqlEx As SqlException
            MsgBox("Error SQL:" & vbCrLf & vbCrLf & SqlEx.Message, MsgBoxStyle.Critical)

        Catch ex As Exception
            MsgBox("Error message", MsgBoxStyle.Critical)

        End Try
        ExecuteReader = rdr
    End Function

Then in the Form:
   Imports System.Data
   Imports System.Data.SqlClient

And in your SUB
            Dim StartCollect as Boolean
            Dim SQL1 As String = "SELECT * FROM YourTable ORDER BY Item;"
            Dim dr1 As SqlDataReader = ExecuteReader(SQL1)
            While dr1.Read
                If dr1.Item("Item") = "Y" AND dr1.Item("Loc")  = "Cleve" Then StartCollect = True
                If dr1.Item("Item") = "Y" AND dr1.Item("Loc")  = "Akron" Then Exit While
                If StartCollect  Then
                     ' Stats to collect you info
                End If
             End While
             dr1 = Nothing
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone 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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

864 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