locked
illegal variable name/number RRS feed

  • Question

  • User-592976280 posted

    I want to insert a record into Oracle database. I want to tell when it performs insertion it should get the value of sequence1.NEXTVAL.

    There are many posts related to this issue but there's no solution to it. There's a suggestion to make an additional call to the database and retrieve the MAX value of the sequence field and add one. That might not work because by the time the actual insertion is done that MAX value is not MAX any more because other users might already inserted a bunch of records. Another way to fix the problem is to call a stored procedure and let it get the MAX immediately before the actual insertion. That will work but I thought there might be a better way such as letting FormField takes care of it. I might be wrong but doesn't hurt to explore a different option.

    The code below gives error when I try to Insert a row. "Exception Details: System.Data.OracleClient.OracleException: ORA-01036: illegal variable name/number".

    <body>
        <form id="form1" runat="server">
        <div>
                        <asp:DetailsView ID="DetailsView3" 
                                DataSourceID="SqlDataSource_Details2" 
                                AutoGenerateRows="false"
                                AutoGenerateInsertButton="true" 
                                DataKeyNames="id1" 
                                GridLines="Both" 
                                runat="server">
                            <Fields>
                                <asp:BoundField DataField="id1" HeaderText="id1" />
                                <asp:BoundField DataField="city" HeaderText="city" />
                                <asp:BoundField DataField="id2" HeaderText="id2" />
                            </Fields>
                          </asp:DetailsView>
    
                        <p></p>
                        <p>&nbsp;</p>
                        <asp:GridView ID="GridView2" runat="server" PageSize="100" SelectedIndex="0" 
                                AutoGenerateSelectButton="true" 
                                AutoGenerateColumns="false" 
                                DataKeyNames="id1" 
                                DataSourceID="SqlDataSource1">
                            <Columns>
                                <asp:BoundField DataField="id1" HeaderText="id1" />
                                <asp:BoundField DataField="city" HeaderText="city" />
                                <asp:BoundField DataField="id2" HeaderText="id2" />
                            </Columns>
                        </asp:GridView>
                        
                        <asp:SqlDataSource ID="SqlDataSource1" Runat="server" 
                                SelectCommand="SELECT id1, id2, city FROM tbl1"
                                ConflictDetection="OverwriteChanges" SelectCommandType="text"
                                ConnectionString="..." 
                                ProviderName="..." >
                        </asp:SqlDataSource>
                        
                        <asp:SqlDataSource ID="SqlDataSource_Details2" runat="server" 
                                ConnectionString="..." 
                                ProviderName="..." 
                                SelectCommand="SELECT id1, id2, city 
                                               FROM tbl1
                                               WHERE id1 = :id1"
                                ConflictDetection="OverwriteChanges" SelectCommandType="text"
                                InsertCommand="INSERT INTO schema1.tbl1 (id1, id2, city) VALUES (schema1.sequence1.NEXTVAL, :id2, :city)" >
                          <SelectParameters>
                                <asp:ControlParameter ControlID="GridView2" Name="id1" PropertyName="SelectedValue" Type="Int32" />
                          </SelectParameters>
                          <InsertParameters>
                                <asp:Parameter Name="id2" Type="Int32" />
                                <asp:Parameter Name="city" Type="String" />
                          </InsertParameters>
                        </asp:SqlDataSource>
                        
        </div>
        </form>
    </body>
    Monday, July 18, 2011 8:47 AM

All replies

  • User-1917713218 posted

    Hello,

    Without using Sequence you are able to insert record in database.

    Because i think error is not because of Sequence.

    Monday, July 18, 2011 9:31 AM
  • User269602965 posted

    Cross-check:

    For the Table you are inserting into called tbl1.

    The datatype of the id1 column must be NUMBER(12,0) (ie. an Oracle equivalent of an integer)

    Show your tbl1 data definitions.

    Other ideas now that you show more of your code:

    I would remove the SelectCommandType and ConflictDetection.

    I would change id2 insert parameter from datatype Int32 to Decimal.  Oracle will still store it as a NUMBER(x,0) if you defined it that way.

     

     

    Monday, July 18, 2011 5:19 PM
  • User-592976280 posted

    The datatype of the id1 column in Oracle is NUMBER.

    I would remove the SelectCommandType and ConflictDetection.

    They're removed and still no luck.

    I would change id2 insert parameter from datatype Int32 to Decimal. 

    It's changed and again no luck.

    Tuesday, July 19, 2011 8:48 AM
  • User269602965 posted

     <asp:ControlParameter ControlID="GridView2" Name="id1" PropertyName="SelectedValue" Type="Int32" />

    Try Type="Decimal" in select parameters.

    Are you using MASTER PAGES???

    If so, the controlID will likely need additional description.

    +++++++

    Oracle NUMBER is decimal

    Oracle NUMBER(x,0) or NUMBER(x) are integers

    Oracle NUMBER(x,y) is decimal

    Tuesday, July 19, 2011 9:37 PM
  • User-592976280 posted

    Try Type="Decimal" in select parameters.

    try that and still not working.

    I don't think Int32 is causing the problem because it works great when I don't use the sequence.

    Are you using MASTER PAGES???

    Yes, there's a master page and a nested master page and content pages.

    If so, the controlID will likely need additional description.

    How? Remember that all controlID's work great when I don't use the sequence. It's just the sequence that is causing the problem.

    Thursday, July 21, 2011 8:35 AM
  • User269602965 posted

    If SqlDataSource_Details2 is associated with the DetailsView3 control,

    then

    would not the select pararmeter control ID be DetailsView3 controlID and not the GridView2 controlID

     

    I am stil thinking the NEXTVAL has nothing to do with the error.

     

    ++++++++++++++

    Side comment, I often find I have to use "Decimal" datatype when dealing with ASP.NET parameters for oracle numbers.

     

    Thursday, July 21, 2011 6:33 PM
  • User-592976280 posted

    Even though SqlDataSource_Details2 is associated with the DetailsView3 control, the SelectParameters ControlID should be GridView2 controlID because the detailsview is displayed based on a selection in the gridview2.

    I don't know how to fix the problem after trying out all of your suggestions and still not working.

    Monday, July 25, 2011 7:43 AM
  • User269602965 posted

    Try this for the select parameter instead of the ControlParameter

     

     <SelectParameters>
        <asp:Parameter Name="id1" Type="Decimal" />
     </SelectParameters>

    Monday, July 25, 2011 10:17 AM
  • User-592976280 posted

    I try that but it doesn't display the detailsview when I select a row in the gridview.

    It needs ControlParameter in order to do so.

    Monday, July 25, 2011 11:02 AM
  • User269602965 posted

    Well since no other experts are jumping in here to help,

    I will load your code into a web page project, build Oracle table and SEQ objects, and see what happens to me.

    I use Telerik Rad Controls for my hierarchical grids and I do not have all these problems.

    Are you compiled in 32-bit, ASP.NET 4.0?? Just to be on same sheet of music.

    Do you have code behind that supports the Gridview??

     

    Monday, July 25, 2011 5:17 PM
  • User-592976280 posted

    I am using Visual Studio 2010 -- ASP.NET 4.0 -- on Windows XP 32bit machine.

    There is no code behind that supports the Gridview.

    Thank you for doing that.

    Tuesday, July 26, 2011 1:33 PM
  • User-592976280 posted

    I will load your code into a web page project, build Oracle table and SEQ objects, and see what happens to me.

    Were you able to do that? Thanks.

    Tuesday, August 2, 2011 9:58 AM
  • User269602965 posted

    Sorry been busy with other projects

    Tuesday, August 2, 2011 2:08 PM
  • User269602965 posted

    For hierarchical grids, I use Telerik RadGrid.  Can nest many details, subdetails, etc. and passes the primary key without use of control parameters.

    Control parameters often have complex (not intuitive nor easy to find reference for) naming cosniderations, espcially when used with apps that have master and child pages as a framework for a grid or the grid is in a user control added to a page.  Failure to name control parameters correctly results in errors.

     

     

    Tuesday, August 2, 2011 2:14 PM