MS SQL SELECT stored procedure according to combobox.selectedvalue
- by Jay
Hello,
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 it, depending on the WHERE statement at the end of my stored procedure, 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,
@ReturnValue int output
)
AS
declare @Value int
set @Value =@@rowcount
if @Value = 0
begin
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
if @@rowcount >0
begin
set @ReturnValue=999
end
else
begin
set
@ReturnValue=997
end
end
if @Value >0
begin
--Dit wil zeggen dat ik een gebruiker wil ingeven die reeds bestaat. (998)
set @ReturnValue=998
end
Does anyone know what I'm need to do to resolve this?
Kind regards,
Jay
