Problem with sql parameter with c#

deadfingers

Hi I have a sql parameter that takes as input the value of a dropdownlist, the problem I am having is that it works fine for one insrance but if I try it for a second time (in the same session) then I get the following error.

"The variable name '@comp' has already been declared. Variable names must be unique within a query batch or stored procedure."

Below is my code,

        string com = DropDownList1.SelectedValue.ToString();
        
        Parameter company = new Parameter("comp", TypeCode.String, com);
        SqlDataSource2.SelectParameters.Add(company);
                
        SqlDataSource2.SelectCommand = "SELECT * FROM [Product] WHERE   Customer = @comp "; 
        GridView1.DataBind(); 

Anybody got any suggestions?

JuncoPartner

Hey man, have you tried just changing the param value...
So in the code create the param and then on the selected index change or whatever event your code gets triggered by just change its value there instead of trying to create it again?

WizZard

Your code could check if the parameter exists before creating it, and if it exists then just assign the new value to it

bonkey

deadfingers wrote:

but if I try it for a second time (in the same session) then I get the following error.
...
Anybody got any suggestions?

Sounds like your variable is non-local and not being re-initialised between setups.

There's a number of ways of fixing this, but basically it looks like your variable persists between calls, while the "setup" code assumes it doesn't.

JuncoPartner

bonkey wrote:

Sounds like your variable is non-local and not being re-initialised between setups.

There's a number of ways of fixing this, but basically it looks like your variable persists between calls, while the "setup" code assumes it doesn't.

I love this guy!

What a useful post.
Your code is broken. It can be fixed.

Anyway back to the point, have you tried the suggested solutions posted above?

bonkey

JuncoPartner wrote:

I love this guy!

Thank you, thank you I'm here all week.

What a useful post.
Your code is broken. It can be fixed.

You forgot to notice (or mention) that I also mentioned why it is most probably broken.

If you understand why something is broken, you can figure out how to fix it and learn from your mistake.
If you simply copy someone else's correction, you learn how to copy and paste another piece of code.

Evil Phil

bonkey wrote:

Thank you, thank you I'm here all week.

rofl.

Anyway, as bonkey said you're variable is persisting between calls. Perhaps in a control or something.

mewso

It's a sqldatasource presumably declared on the aspx page in the html. This means the first time is fine but since most controls store their state in the viewstate then when you add the parameter it's going in on top of the already existing parameter that has been pulled from the viewstate.
The first question I would ask is why you are doing it in code when the sql datasource can be set to do this in the aspx page's html very easily. If you must do it in code then setting enableviewstate for the sqldatasource to false should solve the problem but if you have other controls on the page posting back it may lead to problems so you could instead remove all parameters from the sqldatasource before adding the new one - "SqlDataSource2.SelectParameters.Clear" which relys on the fact that it has already pulled the parameter from the viewstate. I'm not sure which would happen first.

Mutant_Fruit

The first question I would ask is why you are doing it in code when the sql datasource can be set to do this in the aspx page's html very easily.

Keeping code away from your HTML is a good thing. It's why you write your C# in the .CS file and your html in the .aspx file.

If you must do it in code then setting enableviewstate for the sqldatasource to false should solve the problem but if you have other controls on the page posting back it may lead to problems so you could instead remove all parameters from the sqldatasource before adding the new one - "SqlDataSource2.SelectParameters.Clear" which relys on the fact that it has already pulled the parameter from the viewstate. I'm not sure which would happen first.

When a page "posts back" to the server, the very first thing that happens is the page gets processed. i.e. viewstate gets loaded in, control positions get updated etc. When it starts running your C# code, you can be sure the page has all the viewstate data loaded (unless you've overridden something or hooked into some specific events).

Also, you can safely disable viewstate for specific controls without messing up other controls. i.e. disabling viewstate for DropDownList1 won't affect DropDownList2. So disabling viewstate on controls that don't need it can be a good thing as less useless data is retained in the viewstate.

mewso

Mutant_Fruit wrote:

Keeping code away from your HTML is a good thing. It's why you write your C# in the .CS file and your html in the .aspx file.

Eh folks anyone used the dot net 2 controls???? Where did I say to put the code in the html? What I'm referring to results in no code whatsoever. It's all in the sqldatasource control declaration within the aspx page.

mewso

Mutant_Fruit wrote:

Also, you can safely disable viewstate for specific controls without messing up other controls. i.e. disabling viewstate for DropDownList1 won't affect DropDownList2. So disabling viewstate on controls that don't need it can be a good thing as less useless data is retained in the viewstate.

If your sqldatasource is being bound to a gridview for example then disabling the viewstate for the sqldatasource may cause editing/sorting etc. on the gridview to stop working.

mewso

Example with sqldatasource control:-

<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnString %>"
            SelectCommand="SELECT * FROM [Product] WHERE   Customer = @comp">
            <SelectParameters>
                <asp:ControlParameter ControlID="DropDownList1" Name="comp" PropertyName="SelectedValue" />
            </SelectParameters>
        </asp:SqlDataSource>

Mutant_Fruit

If your sqldatasource is being bound to a gridview for example then disabling the viewstate for the sqldatasource may cause editing/sorting etc. on the gridview to stop working.

Well, that should be obvious. How can you databind to data that isn't there. I suppose i misunderstood what you meant by "if you have other controls on the page posting back it may lead to problems". I thought you meant that if you disable the viewstate (on the control), it would adversely affect all other controls that have viewstate/postback.

mewso

Yes what I meant was that the OP appeared to be running that code on the postback of the dropdownlist. If other buttons or dropdownlists are posting back he may have problems with viewstate off.