Solved

sqldatasource and parameterized query using oracle access client

Posted on 2013-01-17
4
448 Views
Last Modified: 2013-12-13
I have a sqldatasource on my page that will run if I change the parameter to a fixed value but will not run as a parameterized query. It will run in the oracle connect screen, but I cannot get it to run from the page. I am trying to convert from VS 2005 to VS2010 and from the Microsoft  provider (which is going away) to the oracle provider.

I have already tried the following:
 - there is only 1 parameter so the bind by name/position issue should not factor in
 - I have added a type and dbtype of 'string'
 - I have added a size

when I try to execute, I receive the  ORA-01008 not all variables bound error.

Code:

<asp:SqlDataSource ID="SqlDS_SEM11" runat="server"
        ConnectionString="DATA SOURCE=PROD_DB.world;PASSWORD=xx;PERSIST SECURITY INFO=True;USER ID=xx"
        ProviderName="Oracle.DataAccess.Client"
       
        SelectCommand="SELECT DISTINCT SATURN.SMRACAA.SMRACAA_AREA, SATURN.SMRACAA.SMRACAA_TERM_CODE_EFF, SATURN.SMRACAA.SMRACAA_SET, SATURN.SMRACAA.SMRACAA_SUBSET, decode(saturn.SMRACAA.SMRACAA_SUBSET, '111', NULL, '222', 'Or', '333', 'Or', NULL) AS and_or, SATURN.SCBCRSE.SCBCRSE_SUBJ_CODE, SATURN.SCBCRSE.SCBCRSE_CRSE_NUMB, concat(SATURN.SCBCRSE.SCBCRSE_SUBJ_CODE, concat('-', SATURN.SCBCRSE.SCBCRSE_CRSE_NUMB)) AS course, SATURN.SCBCRSE.SCBCRSE_TITLE, SATURN.SMBPGEN.SMBPGEN_PROGRAM, SATURN.SMBPGEN.SMBPGEN_TERM_CODE_EFF, SATURN.SCBCRSE.SCBCRSE_CREDIT_HR_HIGH FROM SATURN.SCBCRSE INNER JOIN SATURN.SMRACAA ON SATURN.SCBCRSE.SCBCRSE_SUBJ_CODE = SATURN.SMRACAA.SMRACAA_SUBJ_CODE AND SATURN.SCBCRSE.SCBCRSE_CRSE_NUMB = SATURN.SMRACAA.SMRACAA_CRSE_NUMB_LOW INNER JOIN SATURN.SMBPGEN ON SATURN.SMRACAA.SMRACAA_TERM_CODE_EFF = SATURN.SMBPGEN.SMBPGEN_TERM_CODE_EFF AND substr(SATURN.SMRACAA.SMRACAA_AREA, 1, 6) = saturn.SMBPGEN.SMBPGEN_PROGRAM WHERE (saturn.SMBpgen.SMBPGEN_PROGRAM, saturn.SMBPGEN.SMBPGEN_TERM_CODE_EFF) IN (SELECT saturn.SMBPGEN.SMBPGEN_PROGRAM, MAX(saturn.SMBPGEN.SMBPGEN_TERM_CODE_EFF) FROM saturn.SMBPGEN WHERE saturn.SMBPGEN.SMBPGEN_ACTIVE_IND = 'Y' GROUP BY saturn.SMBPGEN.SMBPGEN_PROGRAM) AND (saturn.SMRACAA.SMRACAA_AREA, saturn.SMRACAA.SMRACAA_TERM_CODE_EFF) IN (SELECT saturn.SMBAGEN.SMBAGEN_AREA, MAX(saturn.SMBAGEN.SMBAGEN_TERM_CODE_EFF) FROM saturn.SMBAGEN WHERE saturn.SMBAGEN.SMBAGEN_ACTIVE_IND = 'Y' GROUP BY saturn.SMBAGEN.SMBAGEN_AREA) AND (saturn.SCBCRSE.scbcrse_subj_code, saturn.SCBCRSE.SCBCRSE_CRSE_NUMB, saturn.SCBCRSE.SCBCRSE_EFF_TERM) IN (SELECT saturn.SCBCRSE.SCBCRSE_SUBJ_CODE, saturn.SCBCRSE.SCBCRSE_CRSE_NUMB, MAX(saturn.SCBCRSE.SCBCRSE_EFF_TERM) FROM saturn.SCBCRSE GROUP BY saturn.SCBCRSE.SCBCRSE_SUBJ_CODE, saturn.SCBCRSE.SCBCRSE_CRSE_NUMB) AND (saturn.SMBPGEN.SMBPGEN_PROGRAM = :prog_ram) AND (substr(saturn.SMRACAA.SMRACAA_area, 7, 2) = '11') ORDER BY saturn.SMRACAA.SMRACAA_SET, saturn.SCBCRSE.SCBCRSE_SUBJ_CODE, saturn.SCBCRSE.SCBCRSE_CRSE_NUMB">
       
        <SelectParameters>
            <asp:QueryStringParameter DbType="String" DefaultValue="" Name="PROG_RAM"
                QueryStringField="program" Size="6" Type="String" />
        </SelectParameters>
    </asp:SqlDataSource>

Any ideas?

Thanks in advance
0
Comment
Question by:dfisher12
4 Comments
 
LVL 18

Expert Comment

by:dj_alik
ID: 38792574
Did you try to remove COLON symbol? (:)
0
 

Accepted Solution

by:
dfisher12 earned 0 total points
ID: 38794424
Thanks - I had tried that already.

I have been trying to test this in the query builder window, and receiving this error message. I decided to create a data grid pointing to this source and it is working. Apparently, this is a flaw in the query builder functionality.

A day I will never get back!
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

785 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