Dynamically populate a label

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

eh.. i get out of ideas here.. i always get results with executescalar... what if you take out the convert???

HTH

Gonzalo

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

No results. It must be something in the stored proc. I am running out of ideas also.

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

ok.. last idea..

let's try writting another SP.. can you??

Code:

CREATE PROCEDURE dbo.testprocedure
(@DisplayValue NVARCHAR(50))
AS
SET NOCOUNT ON
SELECT U.FullName FROM DropDownData D, Users U WHERE D.DropDownCategory = 'Level5' AND D.DisplayValue=@DisplayValue and D.Misc1 = U.Associate_ID
GO

can you try with that???
also in the code you will have 1 extra parameter (comment it)... and see if you get a result with executescalar.

HTH

Gonzalo

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

I copied and pasted your code (I hope you don't mind) into a new procedure and I still get no results in the label I am trying to populate.

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

no problem.. can you trace the code?? do you get to the line that fill the label or are you getting out with an error?????
(try this in the catch)

Code:

lblLevel5Manager.Text = ex.Message

HTH

Gonzalo

MaxGay2 · November 15th, 2006, 08:56 AM

I am getting to the line with the textbox in it and when I tried the "lblLevel5Manager.text = ex.Message" it produced nothing either. Should I put the old proc back into play or keep the one that you gave me?

gbianchi · November 15th, 2006, 10:08 AM

do you get an error?? or you paste the code i gave you in the wrong place?? can you paste the function here like you have it now??

we will get to the bottom of it...

HTH

Gonzalo

MaxGay2 · November 15th, 2006, 10:16 AM

Here is the function:

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)
            lblLevel5Manager.Text = nullcuteScalar
            'Return dtLvl5Mgrs

            conn.Close()




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

The stored procedure is the same one that you gave me

MaxGay2 · November 15th, 2006, 10:33 AM

I don't know if this is of any help but if I change the executescalar to ExecuteNonQuery I get a result of -1. Still not the result I want but it is a result.

gbianchi · November 15th, 2006, 10:42 AM

ok.. let's try something.. maybe i'm not explaining ok...

i want you to try this function with the SP i gave to you...

Code:

Public sub GetLevel5Manager(ByVal strCriteria As String) 
        Try
            Dim strConn As String = HttpContext.Current.Session("ConnStr")
            Dim conn As New SqlConnection(strConn)
            Dim cmd As SqlCommand
'when you trace the code be sure that this string has the rigth data and please try it in the SP alone to see if you get results
            Dim strCrit As String = Mid(strCriteria, 10, 8)
            conn.Open()
            cmd = conn.CreateCommand()
            With cmd
                .CommandText = "testprocedure" 'the one i gave you
                .CommandType = CommandType.StoredProcedure
                .Parameters.Add("@DisplayValue", SqlDbType.NVarChar, 50)
                .Parameters("@DisplayValue").Value = strCrit
            End With
            lblLevel5Manager.Text = executeScalar
            conn.Close()
        Catch ex As Exception
            lblLevel5Manager.Text= ex.Message
        End Try
    End sub

try this function.. if it still returns nothing then the parameter you are passing to the SP is bad and that's why you are not receiving any result. this should give to you the data or an exception...

HTH

Gonzalo