?
Solved

oracle sequence in sqldatasource

Posted on 2011-10-19
6
Medium Priority
?
343 Views
Last Modified: 2012-05-12
hi

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">
        <DeleteParameters>
            <asp:Parameter Name="LOC_ID" Type="Decimal" />
        </DeleteParameters>
        <InsertParameters>
            <asp:Parameter Name="LOC_NAME" Type="String" />
        </InsertParameters>
        <UpdateParameters>
            <asp:Parameter Name="LOC_NAME" Type="String" />
            <asp:Parameter Name="LOC_ID" Type="Decimal" />
        </UpdateParameters>
    </asp:SqlDataSource>

Open in new window

0
Comment
Question by:spirit32
  • 3
  • 2
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36997845
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

0
 
LVL 24

Expert Comment

by:johanntagle
ID: 36997847
What exactly is the error you are encountering?
0
 

Author Comment

by:spirit32
ID: 36997950
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
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 24

Assisted Solution

by:johanntagle
johanntagle earned 1000 total points
ID: 36997958
I think it's the :LOC_NAME that is causing problems since it's a string, right?  Try to also enclose that with &quot;
0
 

Author Comment

by:spirit32
ID: 36998030
still the same error
0
 
LVL 24

Accepted Solution

by:
johanntagle earned 1000 total points
ID: 36998077
Okay I'm not an .NET coder but maybe you can look at these:

http://forums.asp.net/t/975011.aspx/1
http://weblogs.asp.net/kencox/archive/2008/02/25/fixed-oracle-11g-and-sqldatasource-the-ugly-ora-01036-illegal-variable-name-number-issue.aspx

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.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month13 days, 14 hours left to enroll

809 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