StrataFrame Forum

How do I call this stored procedure

http://forum.strataframe.net/Topic32332.aspx

By Thomas Holste - 12/5/2013

Hi there,

I have a stored procedure in my database which works if I use the Management Studio:



USE [GALDATA]

GO

/****** Object: StoredProcedure [dbo].[getfaktposvkzahl] Script Date: 12/05/2013 16:05:32 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[getfaktposvkzahl]

@cISBN char(13),

@cBZNr char(13),

@cDat char(8),

@nVKZahl int Out

AS

BEGIN

SET NOCOUNT OFF;

select @nVKZahl = COUNT(menge) from FAKTPOS inner join FAKTUR 

on dbo.FAKTPOS.rgnummer = dbo.faktur.RGNUMMER 

where ISTEXT = 0 and rgdatum >= @cDat and (NUMMER = @cISBN or NUMMER = @cBZNr)

END



I call the proc with some value and get a return value which is correct.

When I call this proc in my BO, I always do get a value of 0 although the value must be more.



Public Function getvkzahl(ByVal cISBN As String, ByVal cBZNr As String, ByVal cDat As String) As Integer

Dim loCommand As New SqlCommand("getfaktposvkzahl")

loCommand.CommandType = Data.CommandType.StoredProcedure

loCommand.Parameters.Add("@cISBN", Data.SqlDbType.Char).Direction = Data.ParameterDirection.Input

loCommand.Parameters("@cISBN").Value = cISBN

loCommand.Parameters.Add("@cBZNr", Data.SqlDbType.Char).Direction = Data.ParameterDirection.Input

loCommand.Parameters("@cBZNr").Value = cBZNr

loCommand.Parameters.Add("@cDat", Data.SqlDbType.Char).Direction = Data.ParameterDirection.Input

loCommand.Parameters("@cDat").Value = cDat

loCommand.Parameters.Add("@nVKZahl", Data.SqlDbType.Int).Direction = Data.ParameterDirection.Output

Return CType(ExecuteScalar(loCommand), Int32)

End Fu



Maybe someone could show me, what I am doing wrong.

Thanks in Advance

Thomas
By Edhy Rijo - 12/5/2013

Hi Thomas,

I would change your stored procedure and function as follow:


ALTER PROCEDURE [dbo].[getfaktposvkzahl]

@cISBN char(13),
@cBZNr char(13),
@cDat char(8)

AS

select
    -- use the COALESCE() function to always return a record at least with zero value.
    COALESCE(COUNT(menge),0)
from
    FAKTPOS
    INNER JOIN FAKTUR ON FAKTPOS.rgnummer = faktur.RGNUMMER
where
    ISTEXT = 0 and rgdatum >= @cDat and (NUMMER = @cISBN or NUMMER = @cBZNr)



    Public Function getvkzahl(ByVal cISBN As String, ByVal cBZNr As String, ByVal cDat As String) As Integer
        Using cmd As New SqlCommand()
            cmd.CommandType = Data.CommandType.StoredProcedure
            cmd.CommandText = "dbo.getfaktposvkzahl"
            cmd.Parameters.AddWithValue("@cISBN", cISBN).SqlDbType = SqlDbType.Char
            cmd.Parameters.AddWithValue("@cBZNr", cBZNr).SqlDbType = SqlDbType.Char
            cmd.Parameters.AddWithValue("@cDat", cDat).SqlDbType = SqlDbType.Char

            Return CType(ExecuteScalar(cmd), Int32)
        End Using
    End Function


See if that works for you...
By Thomas Holste - 12/8/2013

Hi Edhy,

thank you very much, it works perfectly.

Best regards

Thomas
By Edhy Rijo - 12/9/2013

Hi Thomas,

I am glad it worked out for you. 

One thing I noticed you are using CHAR(13) type for fields with values of more than 3 characters, normally you should be using VarChar(13), specially if you may not be filling all 13 characters, you may end up with blank spaces on the right of your fields.

Also try to stick with the command Using...End Using since it is optimized internally by .Net and will dispose the used object automatically and faster than when you use "Dim", this apply only to objects that use the iDisposable interface, good thing about Visual Studio is that it will tell you if an object cannot be instantiated with Using...End Using.