We help IT Professionals succeed at work.

how to write value to label based on selectedindexchanged

Medium Priority
231 Views
Last Modified: 2012-05-06
I have a form with two dropdownlists:
one for a Disitribution List
one for a weekending

I am trying to display the value of the stored procedure to a textbox called txtRevenue

I wrote a stored procedured to get the vlaues of the selectedindex:
@DL returns the value of the first selection, @week returns the value of the second dropdown selection:

@DL varchar (5),
@Week varchar (10)
AS
declare @results varchar(100)
select @results = 'Select ' + @DL + ' from Budget
where WeekEnding =' + '''' + @week + ''''

I tried to do this in my VB code (see below) but nothing is working the way it should. Please help!



Protected Sub ddlWE_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles ddlWE.SelectedIndexChanged
        Dim conString As String = ConfigurationManager.ConnectionStrings("KHConnectionString").ToString
        Dim objConn As SqlConnection = New SqlConnection(conString)
        objConn.Open()
        Dim cmd = New SqlCommand("budget_by_DL", objConn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add("@DL", SqlDbType.VarChar).Value = Me.ddlDL.SelectedValue.ToString
        cmd.Parameters.Add("@week", SqlDbType.VarChar).Value = Me.ddlWE.SelectedValue.ToString
        Me.txtRevenue.Text.ToString = cmd.Parameters.Add("@result", SqlDbType.VarChar).Value
        cmd.ExecuteNonQuery()
        objConn.Close()
    End Sub

Open in new window

Comment
Watch Question

The below is a good link that you can refer
http://support.microsoft.com/kb/308049
You should be getting the value of @result *After* you call the ExecuteNonQuery()

You may also need to set the direction f the variable. Sort of like this:

cmd.Parameters.Add("@result", SqlDbType.VarChar).Direction = ParameterDirection.ReturnValue
cmd.ExecuteNonQuery()
txtRevenue.Text = cmd.Parameters("@result").Value

Author

Commented:
It doesn't seem to go to the VB part on selectionindexchanged.
Here is my asp code:

<asp:DropDownList ID="ddlWE" runat="server" DataSourceID="sdsWE" DataTextField="WeekEnding"
                            DataValueField="WeekEnding" CssClass="form" ToolTip="Please select a week ending" AutoPostBack="False">
                        </asp:DropDownList>
                        <asp:SqlDataSource ID="sdsWE" runat="server" ConnectionString="<%$ ConnectionStrings:BudgetConnectionString %>"
                            SelectCommand="View_WE_DD" SelectCommandType="StoredProcedure"></asp:SqlDataSource>
    Private Sub ddlWE_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles ddlWE.SelectedIndexChanged
 
        Dim conString As String = ConfigurationManager.ConnectionStrings("KHConnectionString").ToString
        Dim objConn As SqlConnection = New SqlConnection(conString)
 
 
        Dim cmd = New SqlCommand("budget_by_DL", objConn)
 
        cmd.CommandType = CommandType.StoredProcedure
 
        Dim RetValue As SqlParameter = cmd.Parameters.Add("Results", SqlDbType.VarChar)
        RetValue.Direction = ParameterDirection.ReturnValue
        Dim DL As SqlParameter = cmd.Parameters.Add("@DL", SqlDbType.VarChar, 11)
        DL.Direction = ParameterDirection.Input
        Dim week As SqlParameter = cmd.Parameters.Add("@week", SqlDbType.VarChar, 30)
        week.Direction = ParameterDirection.Input
 
        cmd.ExecuteNonQuery()
 
        Me.txtRevenue.Text = cmd.Parameters("@result").Value
 
        objConn.Close()
 
    End Sub

Open in new window

You need to set the Autopostback to True on the drop down, otherwise it doesn't post back to run the function.

Author

Commented:
I did and it's still not doing anything. How can I test if it even calls the function?
set a debug break point in the function, and then debug it

Author

Commented:
I had to recompile. Good news, I am getting errors :)

ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.

Here is my code:
  Private Sub ddlWE_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles ddlWE.SelectedIndexChanged
 
        Dim conString As String = ConfigurationManager.ConnectionStrings("KHConnectionString").ToString
        Dim objConn As SqlConnection = New SqlConnection(conString)
 
 
        Dim cmd = New SqlCommand("budget_by_DL", objConn)
 
        cmd.CommandType = CommandType.StoredProcedure
 
        Dim RetValue As SqlParameter = cmd.Parameters.Add("Results", SqlDbType.VarChar)
        RetValue.Direction = ParameterDirection.ReturnValue
        Dim DL As SqlParameter = cmd.Parameters.Add("@DL", SqlDbType.VarChar, 11)
        DL.Direction = ParameterDirection.Input
        Dim week As SqlParameter = cmd.Parameters.Add("@week", SqlDbType.VarChar, 30)
        week.Direction = ParameterDirection.Input
        objConn.Open()
        cmd.ExecuteNonQuery()
 
        Me.txtRevenue.Text = cmd.Parameters("@results").Value
 
        objConn.Close()
 
    End Sub

Open in new window

That's weird, you have objConn as the SqlConnection for cmd, so the objConn.Open() should open the connection to the database.  Unless there is an error in the SQL somewhere.

I just noticed you have cmd.Parameters.Add("Results"), and that should be ("@results")

Author

Commented:
Solved first error. Next error:

Procedure or function 'budget_by_DL' expects parameter '@DL', which was not supplied.
cmd.Parameters.Add("@DL", SqlDbType.VarChar, 11)

According to the stored procedure you supplied in your initial post, the length should only be 5, not 11

Author

Commented:
Thank you. I changed the length for the week as well. THe @DL value should come from my first dropdown selected value. How do I pass the value to @DL?
Dim DL As SqlParameter = cmd.Parameters.Add("@DL", SqlDbType.VarChar, 11)
DL.Direction = ParameterDirection.Input
DL.Value = ddlDL.SelectedValue
What type of database are you using?  That stored procedure doesn't look correct for MSSQL.

Typically it would look something like this:

CREATE PROCEDURE dbo.budget_by_DL (
    @DR VARCHAR(5),
    @Week varchar(10),
    @results varchar(100) OUTPUT)
AS
SELECT @results =  @DL FROM Budget WHERE WeekEnding = @Week

Author

Commented:
Great, getting closer. It's writing a value to the textbox but it's 0. I am guessing it has something to with my conversion in the sp that is called on the WE dropdown:  

      SELECT
            convert(varchar,WeekEnding,107) as 'WeekEnding'
      FROM Budget
      ORDER BY WeekEnding ASC

I tried changing my stored procedure accordingly to;

select @results = 'Select ' + @DL + ' from Budget
where convert(varchar,WeekEnding,107) WeekEnding  =' + '''' + @week + ''''

When I do that I get the following error:

An expression of non-boolean type specified in a context where a condition is expected, near 'WeekEnding'.


select @results = 'Select ' + @DL + ' from Budget
where Convert(varchar,WeekEnding,107)  = ' + '''' + @week + ''''

Author

Commented:
When I do that I get the following error:

An expression of non-boolean type specified in a context where a condition is expected, near 'WeekEnding'.
What are you trying to query from the DB?  
What values are @DL and @Week?
Is WeekEnding a datetime field?

With that convert function, it would be doing something like this:
"Select [DL] FROM Budget WHERE Feb 1, 2009 = [Week]"

Author

Commented:
DL is varchar and WeekEnding is DateTime



Ok, but what values would you be passing to @DL and @Week?

You may not need to use Convert() on WeekEnding in your SP.

Author

Commented:
Dim RetValue As SqlParameter = cmd.Parameters.Add("@results", SqlDbType.VarChar)
RetValue.Direction = ParameterDirection.ReturnValue

---> Gives a return value of 6 in debug mode?

  Me.txtRevenue.Text = cmd.Parameters("@results").Value

----> displays a 0

 It looks like there is something wrong with the results parameter. @DL and @Week are returning the correct values

SP looks like this:

ALTER PROC [dbo].[budget_by_DL]
@DL varchar (5),
@Week varchar (30)
AS
declare @results varchar(100)

select @results = 'Select ' + @DL + ' from Budget
where WeekEnding  = ' + '''' + @week + ''''

exec (@results)
GO

You don't need the exec(@results), exec is used for executing another stored procedure or command.

select @results = "2"

Would make @results return 2, since you are selecting into @results

Author

Commented:
DL also returns the name of the column that I am looking for.

I can't do select columnname from table since I won't know what the name is until i have the returnned value from @DL. THe dropdownlist represents all the columnames in the table. (budget1, Buddget2, Budget 3 etc).

I hope this mkaes sense. Our dba didn't want to build a bunch of tables for each DL so he told me to use this stored procedure.
Is @week a date value as a string?  Like "Feb 2, 2009", or "02/02/2009", or?

Also, have you tried testing the SP in the query window in SQL manager?

Author

Commented:
Yes week is a data value as a string.
How do I test the query in SQL? I have replaced the parameters with real values and its returning the correct data.

Author

Commented:
Running [dbo].[budget_by_DL] ( @DL = DL2, @Week = 2/1/2009 12:00:00 AM ).

No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[budget_by_DL].
Did you replace the parameters in the selectedindexchanged function?  If so then that means the SP is working correctly, then you just have to make sure the dropdownlists are returning the correct values.

Author

Commented:
I replaced the parameters in the selectedindexchanged function. There must be something wrong with the retrun value. How do I get the return value from the SP?

Author

Commented:
WHen I replace the parameters in my sp with values from the dropdowns I am getting the correct value:

'Select ' + @DL + ' from Budget
where WeekEnding  = ' + '''' + @week + ''''

replaced with

Select DL1  from Budget
where WeekEnding  =  '2/8/2009 12:00:00 AM'

returns 157533.0909 and the column name is DL1

for some reason it's not passing that value to the @results.
Oh I see the problem, the direction is wrong on the parameter.

Dim RetValue As SqlParameter = cmd.Parameters.Add("@results", SqlDbType.VarChar, 100)
RetValue.Direction = ParameterDirection.OutPut

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
New error:

Conversion failed when converting the varchar value 'Select DL1 from Budget where convert(varchar,WeekEnding,107) WeekEnding  = 'Jun 14, 2009'' to data type int.
Is the s supposed to be convert(varchar, weekending, 107) AS weekending?

 

Author

Commented:
When I change the direction of the Parameter I get this:

String[0]: the Size property has an invalid size of 0.

Author

Commented:
That error went away. Now I get this:

Procedure or function budget_by_DL has too many arguments specified.
Did you get it working?

If not can you paste what you have for your SP and your code to execute the SqlCommand?
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.