• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • Last Modified:

how to write value to label based on selectedindexchanged

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

0
JessyRobinson1234
Asked:
JessyRobinson1234
  • 17
  • 16
1 Solution
 
CB_ThirumalaiCommented:
The below is a good link that you can refer
http://support.microsoft.com/kb/308049
0
 
aibusinesssolutionsCommented:
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

0
 
JessyRobinson1234Author 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

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
aibusinesssolutionsCommented:
You need to set the Autopostback to True on the drop down, otherwise it doesn't post back to run the function.
0
 
JessyRobinson1234Author Commented:
I did and it's still not doing anything. How can I test if it even calls the function?
0
 
aibusinesssolutionsCommented:
set a debug break point in the function, and then debug it
0
 
JessyRobinson1234Author 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

0
 
aibusinesssolutionsCommented:
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")
0
 
JessyRobinson1234Author Commented:
Solved first error. Next error:

Procedure or function 'budget_by_DL' expects parameter '@DL', which was not supplied.
0
 
aibusinesssolutionsCommented:
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
0
 
JessyRobinson1234Author 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?
0
 
aibusinesssolutionsCommented:
Dim DL As SqlParameter = cmd.Parameters.Add("@DL", SqlDbType.VarChar, 11)
DL.Direction = ParameterDirection.Input
DL.Value = ddlDL.SelectedValue
0
 
aibusinesssolutionsCommented:
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
0
 
JessyRobinson1234Author 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'.


0
 
aibusinesssolutionsCommented:
select @results = 'Select ' + @DL + ' from Budget
where Convert(varchar,WeekEnding,107)  = ' + '''' + @week + ''''
0
 
JessyRobinson1234Author 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'.
0
 
aibusinesssolutionsCommented:
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]"
0
 
JessyRobinson1234Author Commented:
DL is varchar and WeekEnding is DateTime



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

You may not need to use Convert() on WeekEnding in your SP.
0
 
JessyRobinson1234Author 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

0
 
aibusinesssolutionsCommented:
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
0
 
JessyRobinson1234Author 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.
0
 
aibusinesssolutionsCommented:
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?
0
 
JessyRobinson1234Author 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.
0
 
JessyRobinson1234Author 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].
0
 
aibusinesssolutionsCommented:
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.
0
 
JessyRobinson1234Author 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?
0
 
JessyRobinson1234Author 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.
0
 
aibusinesssolutionsCommented:
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
0
 
JessyRobinson1234Author 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.
0
 
aibusinesssolutionsCommented:
Is the s supposed to be convert(varchar, weekending, 107) AS weekending?

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

String[0]: the Size property has an invalid size of 0.
0
 
JessyRobinson1234Author Commented:
That error went away. Now I get this:

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

If not can you paste what you have for your SP and your code to execute the SqlCommand?
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 17
  • 16
Tackle projects and never again get stuck behind a technical roadblock.
Join Now