Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 472
  • Last Modified:

sqldatasource and parameterized query using oracle access client

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
dfisher12
Asked:
dfisher12
1 Solution
 
dj_alikCommented:
Did you try to remove COLON symbol? (:)
0
 
dfisher12Author Commented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now