update not working on gridview

When I hard code the values into the updatecommand then the gridview gets updated, but if I just have the parameters the gridview does not get updated.  What could the issue be?

This updates the grid:
UpdateCommand="Update atable set [afield]='test', [bfield]='test' where [id]=@id"
<UpdateParameters>
             <asp:Parameter Name="afield" Type="string" />
             <asp:Parameter Name="bfield" Type="string" />
<asp:Parameter Name="id" Type="Int32"
             </UpdateParameters>

This doesn't:
UpdateCommand="Update atable set [afield]=@afield, [bfield]=@bfield where [id]=@id"

<UpdateParameters>
             <asp:Parameter Name="afield" Type="string" />
             <asp:Parameter Name="bfield" Type="string" />
<asp:Parameter Name="id" Type="Int32"
             </UpdateParameters>
LVL 4
newbiealAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

divinewind80Commented:
Are you declaring the values for your parameters in the code behind?  If not, it would appear they remain blank... updating nothing in your table.  

Another option, of course, is to use a control parameter or another type...
0
newbiealAuthor Commented:
No, I'm doing nothing in the code behing reg the parameters/values.  Do you have an example?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

newbiealAuthor Commented:
thanks but that link's info doesn't help me in my case, as I have everything set up the way it mentions.   What exactly did you mean by defining the parameter in the code behind?  Example?
0
NazoUKCommented:
In your gridview do you have bound fields for afield, bfield and id? If not the gridview won't know where to get the values from.
0
newbiealAuthor Commented:
Yes.  In fact when I hard code values the gridview gets updated. This works:


UpdateCommand="Update atable set [afield]='test', [bfield]='test' where [id]=@id"

But not when I put the parameter values there.  
0
NazoUKCommented:
Can you post the code you are using for your gridview?
0
newbiealAuthor Commented:
Here is my Gridview code.  In addition to gridview I have 2 dropdownlists and the filtering logic is on the page_load event one the code behind (VB).
   <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
            AutoGenerateColumns="False" BackColor="White" BorderColor="#999999" BorderStyle="None"
            BorderWidth="1px" CellPadding="3" DataKeyNames="ID" DataSourceID="DATA"
            GridLines="Vertical"
        Font-Names="Verdana"
        Font-Size="8pt"
        HeaderStyle-BackColor="#aaaadd" >
        <PagerSettings Position="TopAndBottom"
          FirstPageText="First Page"
          LastPageText="Last Page" 
          Mode="NumericFirstLast" >
     </PagerSettings>
 
            <FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
            <Columns>
                <asp:HyperLinkField DataNavigateUrlFields="ANOTHER_ID" DataNavigateUrlFormatString="test2.aspx?ANOTHER_ID={0}"
                    Text="View MRPS" />
                <asp:CommandField ShowEditButton="True" />
                <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False"
                    ReadOnly="True" SortExpression="ID" />
                <asp:BoundField DataField="AFIELD" HeaderText="AFIELD" SortExpression="AFIELD" />
                <asp:BoundField DataField="BFIELD" HeaderText="BFIELD" SortExpression="BFIELD" />
                <asp:BoundField DataField="CFIELD" HeaderText="CFIELD" SortExpression="CFIELD" ReadOnly="True" />
                <asp:BoundField DataField="DATE" HeaderText="DATE" SortExpression="DATE" ReadOnly="True" />                
            </Columns>
            <RowStyle BackColor="#EEEEEE" ForeColor="Black" />
            <EmptyDataTemplate>
                No Records Found
            </EmptyDataTemplate>
            <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
            <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
            <AlternatingRowStyle BackColor="Gainsboro" />
        </asp:GridView>

Open in new window

0
NazoUKCommented:
I recreated your example using an accessdatasource and it worked fine for me. What kind of datasource object are you using?
Also you don't need to explicitly declare the update parameters, it will automatically figure out what they are, not sure if that's causing the problem but worth a shot.
0
NazoUKCommented:
Also do you have anything in your code-behind (such as in page_load) that could be interfering with the update operation?
0
newbiealAuthor Commented:
I've removed the update parameters and it didn't make a difference - it still didn't work.

I'm using sqldata source.

Yes, page load has some logic reg. dropdownlist selection (ie. if a then not b, etc.) and after each if statements I have a gridview1.databind().
0
NazoUKCommented:
There's the problem. Calling databind in your pageload is causing the changes to be lost.
The gridview will automatically bind to a sqldatasource if it's datasourceid is set (which it is), there shouldn't be any need to manually call databind unless you're manually changing things.
If the data in the gridview somehow depends on the values in the dropdown lists (changing the datasource or setting select parameters, this kind of thing) then move the logic to a selectedindexchanged event for them and call the databind in there.
I suspect calling databind in the pageload is causing the update values to be lost thus nothing happens.

I've confirmed this in my own test, calling databind in page_load causes the update to fail.
0
newbiealAuthor Commented:
I have this logic in the page_load:

 

If Not Page.IsPostBack Then

            GridView1.DataBind()

        End If

        If DropDownList1.SelectedIndex.Equals(0) And DropDownList2.SelectedIndex.Equals(0) Then

            DATA.SelectCommand = ("SELECT * FROM [ATABLE]")

            GridView1.DataBind()

        ElseIf Not DropDownList1.SelectedIndex.Equals(0) And DropDownList2.SelectedIndex.Equals(0) Then

            DATA.SelectCommand = ("SELECT * FROM [ATABLE] where AFIELD = '" & DropDownList1.SelectedValue.ToString & " ' ")

            GridView1.DataBind()

        ElseIf DropDownList1.SelectedIndex.Equals(0) And Not DropDownList2.SelectedIndex.Equals(0) Then

            DATA.SelectCommand = ("SELECT * FROM [ATABLE] where BFIELD = '" & DropDownList2.SelectedValue.ToString & " ' ")

            GridView1.DataBind()

        ElseIf Not DropDownList1.SelectedIndex.Equals(0) And Not DropDownList2.SelectedIndex.Equals(0) Then

            DATA.SelectCommand = ("SELECT * FROM [ATABLE] where AFIELD = '" & DropDownList1.SelectedValue.ToString & " ' and BFIELD = '" & DropDownList2.SelectedValue.ToString & " ' ")

            GridView1.DataBind()

        End If

Once I removed this code, the update worked.  Where should I put this code so that it doesn't interfere with the update?
0
newbiealAuthor Commented:
if i take the databind out in page_load then the filtering doesn't work.  If I put the code in the selectindexchanged event then the gridview is not displayed when the page loads.  Also, when I got to edit a filtered record, the page just refreshes and the grid is gone...
0
NazoUKCommented:
I think what I'd be tempted to do is to rewrite your select command in the datasource to use the values of the dropdown list directly. Assuming index 0 of your drop down lists is an empty string (replace @Param1/2 ='' with whatever the default value is if not)

SELECT * FROM [Atable] WHERE (Afield=@param1 or @Param1='') AND (Bfield=@Param2 OR @Param2='')

This will match everything if your parameter is blank, or the selected value if something is chosen.

Then in your sqldatasource:

<selectParameters>
     <asp:controlparameter name="param1" controlid="DropDownList1" PropertyName="SelectedValue" type="String" />
     <asp:controlparameter name="param2" controlid="DropDownList2" PropertyName="SelectedValue" type="String" />
</selectParameters>

If you set the autopostback="true" properties on your dropdownlist objects the data will automatically rebind on postback, otherwise add a button on there or whatever. Tehy key thing is asp.net will filter the gridview without you having to do anything manually and it shouldn't interfere with your updates.

You can then get rid of the decision logic in the page_load altogether.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
newbiealAuthor Commented:
NazoUK,

You are awesome!! I've made the changes you suggested and it works like a charm.  Thank you so much!!
0
NazoUKCommented:
You're very welcome, glad it worked :)
0
newbiealAuthor Commented:
Perfect solution provided!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.