Link to home
Start Free TrialLog in
Avatar of TheRookie32
TheRookie32

asked on

Trying to return a range of values with a stored procedure

I was wondering how to write a stored procedure that will return all the info between parameters that a user specifies.  Specifically i want the user to be able to say id like all the wines from Row 123, Col A through Row 125 Col BB.  Then this will display in a datagrid.  I de-normalized my table and have the WineColumn and WineRow in tblWine so there doesnt need to be any joins.

(The caveat is that there can be two wines in one row column area.  ie:  There can be a wine at Row 125 col B, and Row 125 col BB...  the wine at BB is behind the other in the database.)  The rows will never be bigger than 3 digits and there is usually always two wines per column area...  (b and bb, c and cc etc.)

The code i have so far is this:  (i have it written as a sub procedure but it will be called from a button)

Public Sub BrowseLocation()
        Dim conString As String = Pre_con & "Data Source=" & Server & "Initial Catalog=" & Database
        Dim myConnection As SqlConnection = New SqlConnection(conString)
        Dim myDTL As New DataTable

        'one way
        Dim cmdL = New SqlCommand("spBrowseResultsLocation", myConnection)
        'myDT = Nothing
        cmdL.Connection = myConnection

        Try
            cmdL.Connection.Open()

            cmdL.CommandText = "spBrowseLocation"
            cmdL.CommandType = CommandType.StoredProcedure

            If cboRowFrom.SelectedIndex > 0 Then cmdL.Parameters.Add("@RowFrom", Me.cboRowFrom.SelectedValue)
            If cboRowTo.SelectedIndex > 0 Then cmdL.Parameters.Add("@RowTo", Me.cboRowTo.SelectedValue)
            If cboColFrom.SelectedIndex > 0 Then cmdL.Parameters.Add("@ColFrom", Me.cboColFrom.SelectedValue)
            If cboColTo.SelectedIndex > 0 Then cmdL.Parameters.Add("@ColTo", Me.cboColTo.SelectedValue)

            Dim myAdapter As SqlDataAdapter = New SqlDataAdapter(cmdL)
            myAdapter.Fill(myDTL)
        Catch ex As System.Exception
            MsgBox(ex.Message)
        Finally
            dgDisplay.DataSource = Nothing
            dgDisplay.DataSource = myDTL

            myDTL = Nothing
            cmdL.Dispose()
            cmdL.Connection.Close()
        End Try
    End Sub

Other code you may need:

'This is how i bind the combo boxes to their data...
Public Sub BindDropDownCbo(ByVal SQLstring As String, ByVal cbolist As ComboBox, _
            ByVal DisplayMember As String, ByVal ValueMember As String)

        Dim conString As String = Pre_con & "Data Source=" & Server & "Initial Catalog=" & Database
        Dim myConnection As SqlConnection = New SqlConnection(conString)
        Dim myCommand As SqlCommand = New SqlCommand(SQLstring, myConnection)
        Dim myAdapter As SqlDataAdapter = New SqlDataAdapter(myCommand)
        Dim myD As DataSet = New DataSet
        Dim myTbl As DataTable
        Dim myRow As DataRow
        myAdapter.Fill(myD)

        myTbl = myD.Tables(0)
        myRow = myTbl.NewRow()
        myRow(DisplayMember) = "(Select One)"
        myRow(ValueMember) = "-1"
        myTbl.Rows.InsertAt(myRow, 0)

        cbolist.DataSource = myTbl
        cbolist.DisplayMember = DisplayMember
        cbolist.ValueMember = ValueMember

    End Sub

'This is the code for the form load event:

Private Sub frmWizardBrowse_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim wineryCmd As String = "Select WineryID, WineryName FROM tblWinery ORDER BY WineryOrderBy"
        Dim areaCmd As String = "Select AreaID, AreaName FROM tblArea ORDER BY AreaOrderBy"
        Dim matfromCmd As String = "Select WineID, WineFrom FROM tblWine"
        Dim matpeakCmd As String = "Select WineID, WinePeak FROM tblWine"
        Dim mattoCmd As String = "Select WineID, WineTo FROM tblWine"
        Dim sizeCmd As String = "Select SizeID, SizeNumber FROM tblSize ORDER BY SizeOrderBy"
        Dim vintageCmd As String = "Select WineID, VintageYear FROM tblWine"
        Dim grapeCmd As String = "Select GrapeID, GrapeType FROM tblGrape ORDER BY GrapeOrderBy"
        'Dim locationCmd As String = "Select LocationID, LocationRow, LocationColumn FROM tblLocation ORDER BY LocationOrderBy"
        Dim locationCmd As String = "Select WineID, WineRow, WineColumn FROM tblWine"

        Dim DisplayMember As String
        Dim ValueMember As String

        Try
            BindDropDownCbo(wineryCmd, cboWinery, "WineryName", "WineryID")
            BindDropDownCbo(areaCmd, cboArea, "AreaName", "AreaID")
            BindDropDownCbo(sizeCmd, cboSize, "SizeNumber", "SizeID")

            BindDropDownCbo(matfromCmd, cboFrom, "WineFrom", "WineID")
            BindDropDownCbo(matpeakCmd, cboPeak, "WinePeak", "WineID")
            BindDropDownCbo(mattoCmd, cboTo, "WineTo", "WineID")

            BindDropDownCbo(vintageCmd, cboVintage, "VintageYear", "WineID")
            BindDropDownCbo(grapeCmd, cboGrape, "GrapeType", "GrapeID")

            BindDropDownCbo(locationCmd, cboRowFrom, "WineRow", "WineID")
            BindDropDownCbo(locationCmd, cboRowTo, "WineRow", "WineID")
            BindDropDownCbo(locationCmd, cboColFrom, "WineColumn", "WineID")
            BindDropDownCbo(locationCmd, cboColTo, "WineColumn", "WineID")

        Catch ex As Exception
            MessageBox.Show(ex.StackTrace.ToString, ex.Message.ToString)
        End Try

    End Sub
Avatar of super786
super786
Flag of United States of America image

We really need to see the stored procedure, since that is what you are apparently having issues.  Can you run it in query analyzer (or your query engine of choice) passing it two rows and two columns and have it return the data you want?

If not, then you need something added to your where class such as...

where WineRow between @Row1 and @Row2 and WineColumn

Provided that your rows and columns can be sorted properly (ie, Z doesn't come somewhere between A and C) then this should give you all of the wines that meet the criteria of being in the specified row range and the specified column range.  Now, as for your doubled column issue, do you want to treat B and BB as a single column?  If so, then you can drop the initial letter off of your first column designator before passing it to the stored procedure (ie, instead of passing cboColFrom.SelectedValue, pass Left(cboColFrom.SelectedValue,1) ) and double up the to columns (ie, instead of passing cboColTo.SelectedValue, do something like
if Len(cboColTo.SelectedValue) = 1 then
    sToPass = cboColTo.SelectedValue + cboColTo.SelectedValue
else
    sToPass = cboColTo.SelectedValue
end if

If, however, you want to treat BB separately from B, then you are essentially dealing with a three-dimensional data set.  You can treat your Z axis as the number of letters in your column designator as a z coordinate.  So the where clause would become something like

where WineRow between @Row1 and @Row2 and Left(WineColumn,1) between Left(@Col1, 1) and Left(@Col2, 1) and Len(WineColumn) between len(@Col1) and Len(@Col2)

This says to pull all wines that are in the row range, and in the column range designated only by the first letter in your column designator, and then all wines that are in the depth range determined by the length of your column designators.  So if both of the column designators were 2 digits, you would only pull the wines in the back of the slots, whereas if one was 1 digit and one was 2 digits, you would pull the wines in the front and back of the slot.
Avatar of levertlee
levertlee

I think you'll have a better control by doing this at datasource level, rather than writing a stored procedure to specify the return rows and columns.

TS
Avatar of TheRookie32

ASKER

I havent been able to get a stored procedure written to do this...  been trying though.  Thats what i was hoping to get the help with .

If i have wines stored in

(Row) (Col)
1  A
1  AA
1  B
1  C
2  B

And i run a search for all wines between Row 1 col A And Row 2 col B id like to be able to display all the wines above in the datagrid.
This is my stored procedure so far:

CREATE PROCEDURE dbo.spBrowseResultsLocation @ColFrom varchar=NULL, @ColTo varchar=NULL, @RowFrom varchar=NULL, @RowTo varchar=NULL

 as
 
SELECT
   
   *
   
FROM

     tblWine

WHERE
     tblWine.WineColumn between @ColFrom and @ColTo
     tblWine.WineRow between @RowFrom and @RowTo
   
It doesnt like my syntax for this stored procedure.  It says there is incorrect sytax near keyword 'as' and incorrect syntax near 'tblWine'.
I got my sp working, but i dont know yet if its returning all the correct information... or will once there are lots of wines in there.  I dont know how to ask this but does sql or my stored procedure realize b comes between a and c?  ;$

ALTER PROCEDURE dbo.spBrowseResultsLocation
                                                (
                                                @ColFrom int=NULL,
                                                @ColTo int=NULL,
                                                @RowFrom varchar,
                                                @RowTo varchar
)

AS
 
SELECT
   
   *
   
FROM

     tblWine

WHERE
     tblWine.WineColumn between @ColFrom and @ColTo and
     tblWine.WineRow between @RowFrom and @RowTo
Sql knows.  If your database is set up to be case sensitive (not the default, i don't think) then it may not realize that b comes between A and C, but other than that you shouldn't have a problem.  If you need to test it, create a table as such...

Create table tblTest (
   testfield char(1)
)
declare @iTest int set @iTest = 0
while @iTest < 26 begin
   Insert into tblTest(
      testField
   ) values (
      char(ascii('A') + @iTest)
   )
   Insert into tblTest(
      testField
   ) values (
      char(ascii('a') + @iTest)
   )
   set @iTest = @iTest + 1
end


and then run this query...

select * from tblTest where testfield between 'a' and 'c'
One thing to note, however, is that it probably doesn't know what comes between Null and Null.  So if your columns come in as null, you probably need to set them to 'A' and 'Z' or something.  You can either set the default to these values, or do...

if @ColFrom is null begin
   set @ColFrom = 'A'
end
if @ColTo is null begin
   Set @ColTo = 'ZZ'
end

(a-z will get you everything except ZZ, I believe)
One more thing (I need to think of these before I hit submit) common practice is to put unnecessary parameters on the end.  So, @RowFrom, @RowTo, @ColFrom = 'A', @ColTo = 'ZZ'.  This way, you can execute the procedure as...
p_Proc 'Row1', 'Row2'
or
p_Proc 'Row1', 'Row2', 'Col1'
or
p_Proc 'Row1', 'Row2', 'Col1', 'Col2'

Otherwise, you have to do
p_Proc ,,'Row1', 'Row2'
which won't work in sql  (although skipping those parameters when adding parameters to the command object will probably still work).

It looks from your VB code that all the parameters are actually optional, so you can ignore my comment about putting the optional ones on the end, but you would then need to apply the "is null" checks to your rows as well.
Where do i put this code in my stored procedure?  (The columns are actually the numerical ones and rows are the alphabetical ones.  I dont think he has more than 5 rows so i wont use up the alphabete... thankfully!)

if @ColFrom is null begin
   set @ColFrom = 'A'
end
if @ColTo is null begin
   Set @ColTo = 'ZZ'
end

Also in query analyzer, if i run my stored procedure (as is without the if block) giving it:

exec spBrowseResultsLocation @ColFrom='1', @ColTo='2', @RowFrom='A', @RowTo='B'

And my data is like this:
Col Row
1  A
1  AA
3  B

I dont get the bottle AA...   I think i may need to add some joins as well so it returns the name of the winery etc instead of the wineryid (which is in tblWine, WineryName is in tblWinery... but i should be able to manage that.)
SOLUTION
Avatar of wtconway
wtconway

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I had to change all the columns (which are numbers) to rows (which are letters) to varchar because of issues with the databinding in BindDropDownCbo.  It didnt want to let me bind an int to a string...

@wtconway: i couldnt get the AA wine to show up when running the query in query analyzer... as you had stated but also i find the where clause of your sp wasnt working as well as teh between statement was.  I think that is because of the datatype changes...

ie if i run in QA:
exec spBrowseResultsLocation @ColFrom='1', @ColTo='1', @RowFrom='A', @RowTo='AA'
It only gives me the wine at 1A and doesnt give me the one at 1AA.  where my test wines are at:

Col Row
1  A
1  AA
3  B

Also if i run a query in QA for:
exec spBrowseResultsLocation @ColFrom='1', @ColTo='10', @RowFrom='A', @RowTo='B'

I only get 1A and 1AA back.  I dont get 3B back until I run a query like this in QA:
exec spBrowseResultsLocation @ColFrom='1', @ColTo='30', @RowFrom='A', @RowTo='B'

Is the 3 for the row getting multiplied to be 30?  3-9 work but as soon as i get to 10, 10-29 dont work till i enter 30 in the @ColTo row and its only in row 3...  Strange...

This is what i have now that works except for when the bounds of the query land right on a double letter:
ALTER PROCEDURE dbo.spBrowseResultsLocation
                                        (
                                        @ColFrom varchar(3)=NULL,
                                        @ColTo varchar(3)=NULL,
                                        @RowFrom varchar(2)=1,
                                        @RowTo varchar
)

AS

SET ANSI_NULLS ON

--You can't set the default value to be the result of a SELECT statement...so we'll do it here as a new variable
DECLARE @myColTo AS varchar
DECLARE @myRowTo AS varchar

IF @ColTo IS NULL
     BEGIN
          SELECT @myColTo = MAX(WineColumn) FROM tblWine
     END
ELSE
     BEGIN
          SELECT @myColTo = @ColTo
     END
     
     IF @RowTo IS NULL
     BEGIN
          SELECT @myRowTo = MAX(WineRow) FROM tblWine
     END
ELSE
     BEGIN
          SELECT @myRowTo = @RowTo
     END
 
SELECT
   
   *
   
FROM

     tblWine

WHERE
     tblWine.WineColumn between @ColFrom and @ColTo and
     tblWine.WineRow between @RowFrom and @RowTo
Well here is the stored procedure(see below) that works as far as returning A and AA when i run: (in Query Analyzer)
exec spBrowseResultsLocation @ColFrom='1', @ColTo='1', @RowFrom='A', @RowTo='AA'
... dont know why this wasnt working before...

But I still have the problem with the third wine not showing up in a query that allows for it to fit.  (ie:
exec spBrowseResultsLocation @ColFrom='1', @ColTo='10', @RowFrom='A', @RowTo='B'

Couple questions
@super786:

am i setting my variables similar to how you were.  i couldnt get

if @ColFrom is null begin
   set @ColFrom = 'A'
end
if @ColTo is null begin
   Set @ColTo = 'ZZ'
end

... to work

@wtc:

y do you do this:

FROM
     tblWine as w
... in your stored procedure?  easier to type w instead of tblWine?

Also when you declare a variable in sql , is it opposite how you set one in .net which is why you have:

@myColTo = @ColTo

In .net that would make me think you are setting @mycolto to be @colto...  just curious...

ALTER PROCEDURE dbo.spBrowseResultsLocation
                                        (
                                        @ColFrom varchar(3)=1,
                                        @ColTo varchar(3)=NULL,
                                        @RowFrom varchar(2)=1,
                                        @RowTo varchar(2)=null
)

AS

SET ANSI_NULLS ON

DECLARE @myColTo AS varchar
DECLARE @myRowTo AS varchar

IF @ColTo IS NULL
     BEGIN
          SELECT @myColTo = MAX(WineColumn) FROM tblWine
     END
ELSE
     BEGIN
          SELECT @myColTo = @ColTo
     END
     
IF @RowTo IS NULL
     BEGIN
          SELECT @myColTo = MAX(WineRow) FROM tblWine
     END
ELSE
     BEGIN
          SELECT @myRowTo = @RowTo
     END
     
SELECT
   
   tblWine.WineColumn,
     tblWine.WineRow,
     tblWinery.WineryName,
     tblWine.VintageYear,
     tblArea.AreaName,
     tblGrape.GrapeType,
     tblSize.SizeNumber,
     tblWine.WineFrom,
     tblWine.WinePeak,
     tblWine.WineTo,
     tblWine.WinePurchasePrice,
     tblWine.WinePurchaseDate,
     tblWine.WineRating
   
FROM

     tblWine
     INNER JOIN tblWinery on tblWinery.WineryID = tblWine.WineryID
     INNER JOIN tblArea on tblArea.AreaID = tblWine.AreaID
     INNER JOIN tblSize on tblSize.SizeID = tblWine.SizeID
     INNER JOIN tblGrape ON tblGrape.GrapeID = tblWine.GrapeID

WHERE
     tblWine.WineColumn between @ColFrom and @ColTo and
     tblWine.WineRow between @RowFrom and @RowTo

Hope this post wasnt too confusing!  Just that last bit of getting that elusive third wine is all i need...  
I was informed to why it isnt showing up when its 10-29, because alphabetically, 10 comes before 1 with varchar, therefore it wont show up...  So if there is someway i can add a 0 before each number that is single digit,(maybe have to do this when the user enters the column number) I think i will solve this.  (may open a new question for this particular aspect of it...)  
How can i check to make sure sql is set up to be case sensitive?  And could you guys answer the questions i asked in the last post and ill close this out as my issue with 10 coming before 1 alphabetically in a varchar is a different issue than the origional post.  Thanks!
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The letters came back and they were not grouped etc..  so thats good.

and putting this in my where clause solved my problem...

convert(int,tblWine.WineColumn) between convert(int,@ColFrom) and convert(int,@ColTo) and
     tblWine.WineRow between @RowFrom and @RowTo

@wtconway- can you answer my question inline above?  (repeated here so you can find it)

just curious, y do you do this:

FROM
     tblWine as w
... in your stored procedure?  easier to type w instead of tblWine whenever you reference it?
yes...i tend to like using long tables names so I can know at a glance what type of information is in that table...the following line just aliases the table

FROM tblWine as w

so now MUST refer to tblWine as "w" or the sql statement will not work. But's it's always helpful...particularly if you're doing a good bit of JOINS
u ever use views instead of linking straight to the table?
Yes...that is the "preferred" way to give users access to your data. You can restrict them from being able to touch the table by making them use views. Stored Procedures are also a very good way of accomplishing this.