oracle sequence in sqldatasource

Posted on 2011-10-19
Last Modified: 2012-05-12

i'm facing a problem when i try to use the sequence in my insert statement

this is my code

<asp:SqlDataSource ID="sqlLocation" runat="server" 
        ConnectionString="<%$ ConnectionStrings:csValuesTickets %>" 
        DeleteCommand="DELETE FROM &quot;LOCATION&quot; WHERE &quot;LOC_ID&quot; = :LOC_ID" 
        InsertCommand="INSERT INTO &quot;LOCATION&quot; (&quot;LOC_ID&quot;, &quot;LOC_NAME&quot;) VALUES (SEQ_LOCATIONS.nextVal, :LOC_NAME)" 
        ProviderName="<%$ ConnectionStrings:csValuesTickets.ProviderName %>" 
        SelectCommand="SELECT * FROM &quot;LOCATION&quot;" 
        UpdateCommand="UPDATE &quot;LOCATION&quot; SET &quot;LOC_NAME&quot; = :LOC_NAME WHERE &quot;LOC_ID&quot; = :LOC_ID">
            <asp:Parameter Name="LOC_ID" Type="Decimal" />
            <asp:Parameter Name="LOC_NAME" Type="String" />
            <asp:Parameter Name="LOC_NAME" Type="String" />
            <asp:Parameter Name="LOC_ID" Type="Decimal" />

Open in new window

Question by:spirit32
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    you cannot use sequences in VALUES statement, you need to SELECT ... FROM DUAL;

      InsertCommand="INSERT INTO &quot;LOCATION&quot; (&quot;LOC_ID&quot;, &quot;LOC_NAME&quot;) SELECT SEQ_LOCATIONS.nextVal, :LOC_NAME FROM DUAL "  

    Open in new window

    LVL 24

    Expert Comment

    What exactly is the error you are encountering?

    Author Comment

    ORA-01036: illegal variable name/number  

    i still get the same error

    i found a solution which is an event when inserting i get the nextVal from the database and put it as a defult value for the LOC_ID parameter but i feel there should be another solution
    LVL 24

    Assisted Solution

    I think it's the :LOC_NAME that is causing problems since it's a string, right?  Try to also enclose that with &quot;

    Author Comment

    still the same error
    LVL 24

    Accepted Solution

    Okay I'm not an .NET coder but maybe you can look at these:

    Googling about it tells me it's a problem many .NET coders encounter with different SQL statements, so I'm pretty confident it's not the sequence.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious sideā€¦
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
    This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now