I've looked at all the posts on this topic, and I'm sure it's something that I'm doing or not doing, but for the life of me I can't see it.  I'm using Visual Studio 2005 with VB.NET to write ASP.NET pages for an Oracle 10 database.

The SELECT command is a join and I had to hand-code it because the QueryBuilder kept giving errors when I tried to create a join. It works fine. The problem is with the DELETE command.

Here's my GridView code:

1    <asp:GridView ID="GridViewL2" runat="server"
2          Style="position: static" CellPadding="4" ForeColor="#333333" GridLines="None" AllowSorting="True" AutoGenerateColumns="False" DataSourceID="SqlDataSource1">
3            <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
4            <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
5            <EditRowStyle BackColor="#999999" Font-Bold="True" />
6            <SelectedRowStyle BackColor="LightYellow" Font-Bold="True" ForeColor="#333333" />
7            <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
8            <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
9            <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
10           <Columns>
11             <asp:BoundField DataField="PARENT" HeaderText="PARENT" SortExpression="PARENT" />
12             <asp:BoundField DataField="CHILD" HeaderText="CHILD" SortExpression="CHILD" />
13             <asp:BoundField DataField="POSITION" HeaderText="POSITION" SortExpression="POSITION" />
14             <asp:BoundField DataField="LASTLEVEL" HeaderText="LASTLEVEL" SortExpression="LASTLEVEL" />
15             <asp:BoundField DataField="DOCUMENT" HeaderText="DOCUMENT" SortExpression="DOCUMENT" />
16             <asp:BoundField DataField="PARENTPOS" HeaderText="PARENT POS" SortExpression="PARENTPOS"  Visible="False"/>
17             <asp:BoundField DataField="COMPANY" HeaderText="COMPANY" SortExpression="COMPANY" />
18             <asp:BoundField DataField="PARENTID" HeaderText="PARENT ID" SortExpression="PARENTID" />
19             <asp:BoundField DataField="NODEID" HeaderText="NODEID" SortExpression="NODEID" />
20             <asp:CommandField ButtonType="Button" ShowEditButton="True" />
21             <asp:CommandField ButtonType="Button" ShowDeleteButton="True" />
22           </Columns>
23         </asp:GridView>

Here's the SqlDataSource:

1    <asp:SqlDataSource ID="SqlDataSource1" runat="server"  ConnectionString="<%$ ConnectionStrings:ADV_MS_ConnStr %>"
2              ProviderName="System.Data.OracleClient" OldValuesParameterFormatString="Original_{0}"  ConflictDetection="OverwriteChanges"
3              SelectCommand="SELECT L1.ADV_NODETEXT AS PARENT, L2.ADV_NODETEXT AS CHILD, L2.ADV_POSITION AS POSITION, L2.ADV_LASTLEVEL AS LASTLEVEL, L2.ADV_DOCUMENT AS DOCUMENT, L1.ADV_POSITION AS PARENTPOS, L2.COMPANY_CODE AS COMPANY, L1.ADV_LEVELID AS PARENTID, L2.ADV_LEVELID AS NODEID FROM ADV_LEVEL2 L2, ADV_LEVEL1 L1 WHERE (L2.ADV_PARENTID = L1.ADV_LEVELID) AND (L1.COMPANY_CODE = L2.COMPANY_CODE) ORDER BY L1.ADV_POSITION, L2.ADV_POSITION" 
4             DeleteCommand="DELETE FROM ADV_LEVEL2 WHERE (COMPANY_CODE = :COMPANY) AND (ADV_PARENTID = :PARENTID) AND (ADV_LEVELID = :NODEID)" >
5        <DeleteParameters>
6          <asp:Parameter Name="COMPANY" Size="10" Type="String" Direction="InputOutput" />
7          <asp:Parameter Name="PARENTID" Size="3" Type="Decimal" Direction="InputOutput" />
8          <asp:Parameter Name="NODEID" Size="3" Type="Decimal" Direction="InputOutput" />
9        </DeleteParameters>
10    </asp:SqlDataSource>

I added DataKeyNames to the GridView. Then it started to give me ORA-01035: illegal variable name/number. 

I very much appreciate the time you take to look at this code.

-sajala

Can you post the DataKeyNames value that you set in the GridView?

From what I have seen, you get this error when you send in more parameter values than you have bind variables in the statement. 

Thanks, Prashant.   DataKeyNames="COMPANY,PARENTID,NODEID"

-sajala

Also, when I add in the DataKeyNames tag, I get another Oracle error: ORA-01036 illegal variable name/number.

-sajala

Here's the stack trace, but I couldn't glean anything useful from it.  

Stack Trace: 

[OracleException (0x80131938): ORA-01036: illegal variable name/number]
   System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc) +167
   System.Data.OracleClient.OracleParameterBinding.Bind(OciStatementHandle statementHandle, NativeBuffer parameterBuffer, OracleConnection connection, Boolean& mustRelease, SafeHandle& handleToBind) +1166
   System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals) +1604
   System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciRowidDescriptor& rowidDescriptor) +503
   System.Data.OracleClient.OracleCommand.ExecuteNonQuery() +125
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +493
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteDelete(IDictionary keys, IDictionary oldValues) +922
   System.Web.UI.DataSourceView.Delete(IDictionary keys, IDictionary oldValues, DataSourceViewOperationCallback callback) +176
   System.Web.UI.WebControls.GridView.HandleDelete(GridViewRow row, Int32 rowIndex) +914
   System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +1067
   System.Web.UI.WebControls.GridView.RaisePostBackEvent(String eventArgument) +215
   System.Web.UI.WebControls.GridView.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +31
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +32
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +244
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3835

 

Specify the DataKeyNames, remove the DeleteParameters and try.

If it still doesn't work, let us know.

Prashant,

Thanks for trying to help me. Removing the DeleteParameters did not work. What I ended up doing was removing the OldValuesParameterFormatString. Now it works. Only one or two people are going to be using this application and it's not going to get a lot of modifications once the tables are filled, so I don't need to do a lot of conflict checking.

Thanks.

sajala

But this (deleting parameter and setting the datakeyname) worked for me....this is a really a strange error.....you know whats actually causing the error? I am not able to get any logical reason why it worked.

suba_subramanian6:

But this (deleting parameter and setting the datakeyname) worked for me....this is a really a strange error.....you know whats actually causing the error? I am not able to get any logical reason why it worked.

Suba,

I do not know why I continued to have this problem. I never did find a solution for the sqldatasource control. I ended up doing all the logic in the code behind and set all databound fields in the gridview to templates so that I could have access to the contents of the controls.

Sorry. I know that's not a very satisfactory answer.

 -Sara

hi,

i did every thing that you do, but i continue with this error (ORA-01036).

any one have another idea???

tks

You tried to populate, update and delete from the gridview in your code behind page and that did not work? Please be more specific about what you tried and did not work.

Thanks.

sajala