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("spBrowseResult sLocation" , myConnection)
'myDT = Nothing
cmdL.Connection = myConnection
Try
cmdL.Connection.Open()
cmdL.CommandText = "spBrowseLocation"
cmdL.CommandType = CommandType.StoredProcedur e
If cboRowFrom.SelectedIndex > 0 Then cmdL.Parameters.Add("@RowF rom", Me.cboRowFrom.SelectedValu e)
If cboRowTo.SelectedIndex > 0 Then cmdL.Parameters.Add("@RowT o", Me.cboRowTo.SelectedValue)
If cboColFrom.SelectedIndex > 0 Then cmdL.Parameters.Add("@ColF rom", Me.cboColFrom.SelectedValu e)
If cboColTo.SelectedIndex > 0 Then cmdL.Parameters.Add("@ColT o", 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(locationCm d, cboRowFrom, "WineRow", "WineID")
BindDropDownCbo(locationCm d, cboRowTo, "WineRow", "WineID")
BindDropDownCbo(locationCm d, cboColFrom, "WineColumn", "WineID")
BindDropDownCbo(locationCm d, cboColTo, "WineColumn", "WineID")
Catch ex As Exception
MessageBox.Show(ex.StackTr ace.ToStri ng, ex.Message.ToString)
End Try
End Sub
(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("spBrowseResult
'myDT = Nothing
cmdL.Connection = myConnection
Try
cmdL.Connection.Open()
cmdL.CommandText = "spBrowseLocation"
cmdL.CommandType = CommandType.StoredProcedur
If cboRowFrom.SelectedIndex > 0 Then cmdL.Parameters.Add("@RowF
If cboRowTo.SelectedIndex > 0 Then cmdL.Parameters.Add("@RowT
If cboColFrom.SelectedIndex > 0 Then cmdL.Parameters.Add("@ColF
If cboColTo.SelectedIndex > 0 Then cmdL.Parameters.Add("@ColT
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,
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
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,
BindDropDownCbo(areaCmd, cboArea, "AreaName", "AreaID")
BindDropDownCbo(sizeCmd, cboSize, "SizeNumber", "SizeID")
BindDropDownCbo(matfromCmd
BindDropDownCbo(matpeakCmd
BindDropDownCbo(mattoCmd, cboTo, "WineTo", "WineID")
BindDropDownCbo(vintageCmd
BindDropDownCbo(grapeCmd, cboGrape, "GrapeType", "GrapeID")
BindDropDownCbo(locationCm
BindDropDownCbo(locationCm
BindDropDownCbo(locationCm
BindDropDownCbo(locationCm
Catch ex As Exception
MessageBox.Show(ex.StackTr
End Try
End Sub
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
TS
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.
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.
ASKER
This is my stored procedure so far:
CREATE PROCEDURE dbo.spBrowseResultsLocatio n @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'.
CREATE PROCEDURE dbo.spBrowseResultsLocatio
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'.
ASKER
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.spBrowseResultsLocatio n
(
@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
ALTER PROCEDURE dbo.spBrowseResultsLocatio
(
@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'
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)
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.
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.
ASKER
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.)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.spBrowseResultsLocatio n
(
@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
@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.spBrowseResultsLocatio
(
@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
ASKER
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.spBrowseResultsLocatio n
(
@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...
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.spBrowseResultsLocatio
(
@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...
ASKER
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...)
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.WineCo lumn) 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?
and putting this in my where clause solved my problem...
convert(int,tblWine.WineCo
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
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
ASKER
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.
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.SelectedVa
if Len(cboColTo.SelectedValue
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.