Dynamically populate a label

MaxGay2 · November 14th, 2006, 10:35 AM

I have a problem with some code that I am writing. The code looks like this:

Code:

    Public Function GetLevel5Manager(ByVal strCriteria As String) As DataTable
        Try
            Dim strConn As String = HttpContext.Current.Session("ConnStr")
            Dim conn As New SqlConnection(strConn)
            Dim cmd As SqlCommand
            Dim strCrit As String = Mid(strCriteria, 9, 11)

            conn.Open()
            cmd = conn.CreateCommand()
            With cmd
                .CommandText = "sp_PopulateLevel5Manager"
                .CommandType = CommandType.StoredProcedure
                .Parameters.Add("@DisplayValue", SqlDbType.NVarChar, 50)
                .Parameters.Add("@Lvl5Mgr", SqlDbType.NVarChar, 50)
                .Parameters("@DisplayValue").Value = strCrit
                .Parameters("@DisplayValue").Direction = ParameterDirection.Input
                '.Parameters("@Lvl5Mgr").Value
                .Parameters("@Lvl5Mgr").Direction = ParameterDirection.ReturnValue
            End With
            Dim dtLvl5Mgrs As New DataTable
            Dim da As New SqlDataAdapter
            da = New SqlDataAdapter(cmd)
            da.Fill(dtLvl5Mgrs)

            conn.Close()

            'lblLevel5Manager.Text = Convert.ToString(@Lvl5Mgr)
            Return dtLvl5Mgrs




        Catch ex As Exception
            Dim sTemp As String
            sTemp = ex.Message
        End Try
    End Function

What I am trying to accomplish is to populate lblLevel5Manager with the data stored in dtLvl5Mgrs. How would I accomplish this? I am stuck

MaxGay2 · November 14th, 2006, 02:34 PM

I have got the code to work but it returns a zero not the text that I need. Does anyone have any ideas why? Here is the revised code

Code:

    Public Function GetLevel5Manager(ByVal strCriteria As String) As DataTable
        Try
            Dim strConn As String = HttpContext.Current.Session("ConnStr")
            Dim conn As New SqlConnection(strConn)
            Dim cmd As SqlCommand
            Dim strCrit As String = Mid(strCriteria, 10, 8)

            conn.Open()
            cmd = conn.CreateCommand()
            With cmd
                .CommandText = "sp_PopulateLevel5Manager"
                .CommandType = CommandType.StoredProcedure
                .Parameters.Add("@DisplayValue", SqlDbType.NVarChar, 50)
                .Parameters.Add("@Lvl5Mgr", SqlDbType.NVarChar, 50)
                .Parameters("@DisplayValue").Value = strCrit
                .Parameters("@DisplayValue").Direction = ParameterDirection.Input
                .Parameters("@Lvl5Mgr").Direction = ParameterDirection.ReturnValue
            End With
            'Dim dtLvl5Mgrs As New DataTable
            'Dim da As New SqlDataAdapter
            'da = New SqlDataAdapter(cmd)
            'da.Fill(dtLvl5Mgrs)

            nullcuteScalar()
            'lblLevel5Manager.Text = Convert.ToString(@Lvl5Mgr)
            lblLevel5Manager.Text = Convert.ToString(cmd.Parameters("@Lvl5Mgr").Value)
            'Return dtLvl5Mgrs

            conn.Close()




        Catch ex As Exception
            Dim sTemp As String
            sTemp = ex.Message
        End Try
    End Function

gbianchi · November 14th, 2006, 02:39 PM

hi there.. a quick idea (since i don't have the SP to check out) try add

Code:

SET NOCOUNT ON

and if that's not the problem please copy the SP here.. do you receive data when you try it??

HTH

Gonzalo

MaxGay2 · November 14th, 2006, 03:39 PM

Here is the stored Procedure. I am still getting zero for the result set

Code:

CREATE    PROCEDURE dbo.sp_PopulateLevel5Manager @DisplayValue NVARCHAR(50)  OUTPUT
AS

SET NOCOUNT ON

DECLARE @Lvl5Mgr NVARCHAR(50)

SELECT @Lvl5Mgr=U.FullName FROM DropDownData D, Users U WHERE D.DropDownCategory = 'Level5' AND D.DisplayValue=@DisplayValue and D.Misc1 = U.Associate_ID


GO

gbianchi · November 14th, 2006, 03:45 PM

mmm.. where are you executing the Command?? i don't see any executescalar (that's the best option even to not use an output parameter) or any other execute. Also why are you declaring @DisplayValue as OUTPUT??

HTH

Gonzalo

MaxGay2 · November 14th, 2006, 03:48 PM

The executescalar is in the vb code for some reason it is showing up nullcuteScalar() in the second code posting that I have.

gbianchi · November 14th, 2006, 03:51 PM

well.. executescalar will return the result directly...
use this

Code:

lblLevel5Manager.Text = Convert.ToString(cmd.executescalar)

HTH

Gonzalo

MaxGay2 · November 14th, 2006, 03:58 PM

Now it produces no results. What now

gbianchi · November 14th, 2006, 04:03 PM

can you confirm that the SP drops result if you try it alone?? (outside of the code, just try the SP and see what's returning)... maybe the parameters you are passing are wrong.

HTH

Gonzalo

MaxGay2 · November 14th, 2006, 04:07 PM

I can confirm that the parameters are passing correctly. That is what I thought about this too. Is that the parameters were wrong but they produce the desired results when I run it in query analyzer.

This is really confusing me