asp.net gridview control - update error - ORA-01008: not all variables bound

[nathb]

Howdy folks (my favourite forum for asp.net),

I have a simple gridview control. It has edit and cancel buttons for each row. User's can click on edit column and edit a row and click on save to save the record.

When the user clicks on save we get the error: ORA-01008: not all variables bound

User is allowed to edit only 3 fields and rest of the fields are only readonly. For those editable three fields, I am using EditItemTemplate. ASP.net doesn't throw any errors for the first two edititemtemplates. The problem comes only with the third field which is "BSIS Type". I tried to figure out almost 5 hours and don't know what to do.

This the code I have:

<asp:GridView ID="GridViewActualsMapping" runat="server"
            HeaderStyle-BackColor="#444444"
            HeaderStyle-ForeColor="White" 
            Font-Size="10pt" Cellpadding="4"                       
            AutoGenerateColumns="False" 
            DataSourceID="SqlDataSource1" DataKeyNames="gl_number"
            onrowcommand="GridViewActualsMapping_RowCommand" Height="285px" 
            onrowdatabound="GridViewActualsMapping_RowDataBound" 
            Font-Names="Verdana" onrowupdating="GridViewActualsMapping_RowUpdating1">
            
            
                <Columns>
                    <asp:CommandField ShowEditButton="true" />                     
                      

                        <asp:TemplateField HeaderText="Multiplier">
                            <ItemTemplate>
                                <%# Eval("multiplier") %>
                            
                            </ItemTemplate>
                            <EditItemTemplate>
                                <asp:DropDownList runat="server" ID="DropdownMulitiplier" DataSource='<%# multiplier %>'
                                    SelectedIndex= '<%#  GetSelectedMultiplier(Eval("multiplier")) %>'
                                    Font-Names="Verdana" Font-Size="XX-Small"  />
                            
                            </EditItemTemplate>
                        </asp:TemplateField>

                    
                        <asp:TemplateField HeaderText="Account Type">
                            <ItemTemplate>
                                    <%# Eval("type") %>
                            </ItemTemplate>
                            <EditItemTemplate>
                                <asp:DropDownList runat="server" ID="DropdownGLAccountType" DataSource='<%# types %>'
                                     SelectedIndex= '<%#  GetSelectedType(Eval("type")) %>'
                                     Font-Names="Verdana" Font-Size="Small"  />
                             
                            </EditItemTemplate>
                        </asp:TemplateField>
            
                      <asp:TemplateField HeaderText="BSIS Type">
                            <ItemTemplate>
                                    <%# Eval("income_expense")%>
                            </ItemTemplate>
                            <EditItemTemplate>
                                <asp:DropDownList runat="server" ID="DropdownBSISType" DataSource='<%# bsistypes %>'
                                     SelectedIndex= '<%#  GetSelectedBSISType(Eval("income_expense")) %>'
                                     Font-Names="Verdana" Font-Size="Small"  />
                             
                            </EditItemTemplate>
                        </asp:TemplateField>
            
           
                       
                    <asp:BoundField DataField="Account" HeaderText="Account" 
                       ReadOnly="true" />
                   
                    
                </Columns>

<HeaderStyle BackColor="#444444" ForeColor="White"></HeaderStyle>
            </asp:GridView>


<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings:ConnectionStringAccounts %>" 
    ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" 
        UpdateCommand="UPDATE accounts SET MULTIPLIER=:MULTIPLIER, type=:type, income_expense=:income_expense WHERE gl_number=:gl_number"
        SelectCommand="select multiplier, type, income_expense, account, gl_number,  as_of_date, coah_average_balance, coah_ending_balance from accounts order by type desc">
</asp:SqlDataSource>

Error is:

ORA-01008: not all variables bound

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.OracleClient.OracleException: ORA-01008: not all variables bound

[gep13]

Hello,

First up, given the exception message that you are receiving, I am going to guess that you are using an Oracle database, is that correct?

If so, although you "can" use the SqlDataSource to communicate with Oracle, I would highly recommend that you don't. You would be much better off using the Oracle Data Provider for .Net.

Now, unless you are adding the parameters in code behind, I don't see any update parameters in the definition of the SqlDataSource.

Gary

[nathb]

Gary,

For some reason, in the code window, you can't scroll. So I am posting the entire code without the code window. Please bear with me.

Yes I am using Oracle database.

<asp:GridView ID="GridViewActualsMapping" runat="server"
HeaderStyle-BackColor="#444444"
HeaderStyle-ForeColor="White"
Font-Size="10pt" Cellpadding="4"
AutoGenerateColumns="False"
DataSourceID="SqlDataSource1" DataKeyNames="gl_number"
onrowcommand="GridViewActualsMapping_RowCommand" Height="285px"
onrowdatabound="GridViewActualsMapping_RowDataBound"
Font-Names="Verdana" onrowupdating="GridViewActualsMapping_RowUpdating1">


<Columns>
<asp:CommandField ShowEditButton="true" />


<asp:TemplateField HeaderText="Multiplier">
<ItemTemplate>
<%# Eval("multiplier") %>

</ItemTemplate>
<EditItemTemplate>
<aspropDownList runat="server" ID="DropdownMulitiplier" DataSource='<%# multiplier %>'
SelectedIndex= '<%# GetSelectedMultiplier(Eval("multiplier")) %>'
Font-Names="Verdana" Font-Size="XX-Small" />

</EditItemTemplate>
</asp:TemplateField>


<asp:TemplateField HeaderText="Account Type">
<ItemTemplate>
<%# Eval("type") %>
</ItemTemplate>
<EditItemTemplate>
<aspropDownList runat="server" ID="DropdownGLAccountType" DataSource='<%# types %>'
SelectedIndex= '<%# GetSelectedType(Eval("type")) %>'
Font-Names="Verdana" Font-Size="Small" />

</EditItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="BSIS Type">
<ItemTemplate>
<%# Eval("income_expense")%>
</ItemTemplate>
<EditItemTemplate>
<aspropDownList runat="server" ID="DropdownBSISType" DataSource='<%# bsistypes %>'
SelectedIndex= '<%# GetSelectedBSISType(Eval("income_expense")) %>'
Font-Names="Verdana" Font-Size="Small" />

</EditItemTemplate>
</asp:TemplateField>



<asp:BoundField DataField="Account" HeaderText="Account"
ReadOnly="true" />


</Columns>

<HeaderStyle BackColor="#444444" ForeColor="White"></HeaderStyle>
</asp:GridView>


<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionStringAccounts %>"
ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"
UpdateCommand="UPDATE accounts SET MULTIPLIER=:MULTIPLIER, type=:type, income_expense=:income_expense WHERE gl_number=:gl_number"
SelectCommand="select multiplier, type, income_expense, account, gl_number, as_of_date, coah_average_balance, coah_ending_balance from accounts order by type desc">
</asp:SqlDataSource>

Please see the update command. If I convert Income_expense field to a boundfield, it works fine. I mean with no combo box. Just a plain vanilla text box where user can type. I wanted to provide combo box for the user to pick.

[gep13]

Hello,

So again, to me, the correct way to fix this would be to NOT use the SqlDataSource at all.

It sounds like this is not an option though, in which case you will need to create an UpdateParameter for the SqlDataSource. Since you are no longer using a BoundField, the SqlDataSource no longer knows where to get the information from, and you need to tell it where to get it from.

Gary

[nathb]

Gary,

The updatecommand below works perfectly fine as long as "income_expense" is not a combo box but just a text box. I didn't have to use any parameter to update MULTIPLIER and type fields. They are also combo boxes. Why do I need an UpdateParameter for "income_expense".

UpdateCommand="UPDATE accounts SET MULTIPLIER=:MULTIPLIER, type=:type, income_expense=:income_expense WHERE gl_number=:gl_number"

thanks

[gep13]

Hmm, I see what you mean.

Using the Updating Event of the SqlDataSource, inspect the properties of the arguments being fed into it, and try to verify the values that are being passed into the Update command.

Gary