troubleshooting Question

Trying to return a range of values with a stored procedure

Avatar of TheRookie32
TheRookie32 asked on
Visual Basic.NET
19 Comments1 Solution456 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 19 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 19 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros