SQL Server SELECT stored procedure according to combobox.selectedvalue

Question

In order to fill a datagridview according to the selectedvalue of a combobox I've tried creating a stored procedure. However, as I'm not 100% sure what I'm doing, depending on the WHERE statement at the end of my stored procedure, it either returns everything within the table or nothing at all.

This is what's in my class:

Public Function GetAankoopDetails(ByRef DisplayMember As String, ByRef ValueMember As String) As DataTable

    DisplayMember = "AankoopDetailsID"
    ValueMember = "AankoopDetailsID"

    If DS.Tables.Count > 0 Then
        DS.Tables.Remove(DT)
    End If

    DT = DAC.ExecuteDataTable(My.Resources.S_AankoopDetails, _Result, _
                              DAC.Parameter(Const_AankoopID, AankoopID), _
                              DAC.Parameter("@ReturnValue", 0))

    DS.Tables.Add(DT)

    Return DT
End Function

Public Function GetAankoopDetails() As DataTable

    If DS.Tables.Count > 0 Then
        DS.Tables.Remove(DT)
    End If

    DT = DAC.ExecuteDataTable(My.Resources.S_AankoopDetails, _Result, _
                              DAC.Parameter(Const_AankoopID, AankoopID), _
                              DAC.Parameter("@ReturnValue", 0))

    DS.Tables.Add(DT)
    Return DT
End Function

This is the function in the code behind the form I've written in order to fill the datagridview:

  Private Sub GridAankoopDetails_Fill()
    Try
        Me.Cursor = Cursors.WaitCursor
        dgvAankoopDetails.DataSource = Nothing
        _clsAankoopDetails.AankoopDetailsID = cboKeuze.SelectedValue
        dgvAankoopDetails.DataSource = _clsAankoopDetails.GetAankoopDetails



    Catch ex As Exception
        MessageBox.Show("An error occurred while trying to fill the data grid: " & ex.Message, "Oops!", MessageBoxButtons.OK)
    Finally
        Me.Cursor = Cursors.Default
    End Try
End Sub

And finally, this is my stored procedure: (do note that I'm not sure what I'm doing here)

USE [Budget]
GO
/****** Object:  StoredProcedure [dbo].[S_AankoopDetails]    Script Date: 04/12/2010 03:10:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[S_AankoopDetails]
(
 @AankoopID int

)
AS

SELECT     dbo.tblAankoopDetails.AankoopDetailsID, dbo.tblAankoopDetails.AankoopID, dbo.tblAankoopDetails.ArtikelID, dbo.tblAankoopDetails.Aantal, 
                      dbo.tblAankoopDetails.Prijs, dbo.tblAankoopDetails.Korting, dbo.tblAankoopDetails.SoortKorting, dbo.tblAankoopDetails.UitgavenDeelGroepID
FROM         dbo.tblAankoopDetails INNER JOIN
                      dbo.tblAankoop ON dbo.tblAankoopDetails.AankoopID = dbo.tblAankoop.AankoopID INNER JOIN
                      dbo.tblArtikel ON dbo.tblAankoopDetails.ArtikelID = dbo.tblArtikel.ArtikelID INNER JOIN
                      dbo.tblUitgavenDeelGroep ON dbo.tblAankoopDetails.UitgavenDeelGroepID = dbo.tblUitgavenDeelGroep.UitgavenDeelGroepID
WHERE dbo.tblAankoopDetails.Deleted = 0 and dbo.tblAankoopDetails.AankoopID = @AankoopID

ORDER BY AankoopID

Does anyone know what I need to do to resolve this? I guess it's down to the WHERE part of the stored procedure, but I need a way to pass the selectedvalue of the combobox into the @AankoopID parameter.

Any help would be greatly appreciated.

Kind regards Jay

Answer 1

The stored procedure code, WHERE clause included, looks ok. You should specify a table in the ORDER clause:

ORDER by dbo.tblAankoopDetails.AankoopId

but that wouldn't explain why you get all or no rows. You might want to review the actual contents of the tables, make sure it's properly configured, NULL values aren't tripping you up, etc.

Answer 2

Philip, David,

I've resolved the issue by changing one line in the GridAankoopDetails_Fill sub from

_clsAankoopDetails.AankoopDetailsID = cboKeuze.SelectedValue

to

_clsAankoopDetails.AankoopID = cboKeuze.SelectedValue

This because rather than AankoopDetailsID, the AankoopID parameter gets passed along. Thank you for confirming that the stored procedure was OK, narrowing down the search for the problem.

Also Philip, thank you for making me look at what is being passed along in the parameter, this ultimately lead to resolving the issue.

The data grid now updates according to the combobox' selected value.

Best wishes,

Jay