problem with multiple criteria - not all variables bound

[dpimental]

I have a gridview that I'm populating.

I'm connecting to oracle and when I use one select parameter, Name, it works fine.

When I add another one, it gives me the error, ORA-01008: not all variables bound .

Anybody see this error before in this context?


David Pimental
(US, Oh)

 

[jbenson001]

Are you using a datasource control, inline SQL, or SP?
More info and a code snippet would be helpful

 

[dpimental]

I am using a datasource control. The Code for the datasource control is below.

<asp:SqlDataSource  DataSourceMode="DataSet"  ID="institutionResults" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" 
SelectCommand="SELECT &quot;INSTITUTION_ID&quot;, &quot;NAME&quot;, &quot;ADDRESS&quot;, &quot;CITY&quot;, &quot;STATE&quot;, &quot;ZIP&quot; FROM &quot;INSTITUTION_SEARCH_VIEW&quot; WHERE ((NAME LIKE '%' || UPPER(:NAME) || '%') OR (NAME LIKE '%' || initcap(:NAME) || '%')) AND (ZIP = :ZCODE)">
            <SelectParameters>
                <asp:FormParameter FormField="institutionName" Name="NAME" Type="String" />
                <asp:FormParameter FormField="zip" Name="ZCODE"  Type="String" />
            </SelectParameters>
        </asp:SqlDataSource>

This code produces the error.
Any idea what I'm doing wrong?
Or, is there some Oracle-Specific issue here?

David Pimental
(US, Oh)

 

[jbenson001]

Any idea what I'm doing wrong?

Yes. You are using the datasource controls. Those things are one of the worst things MS came up with. They are great for a very simple page, but will throw errors and become unusable once you do something a bit complicated.
Make your life easier and write a stored procedure and call it from your code. Then when you get the time, write your own DAL.
The datasource controls are not debugable. If you were calling a stored procedure for example, you would be able to trace through your code and find exactly where the error is happening and what it is.

I cannot be of more help with the error because I do not know Oracle.
Good luck.

Jim

 

[dpimental]

Thanks for the help - I appreciate the input.

David Pimental
(US, Oh)

 

[jbenson001]

Sure. Look into using Microsoft's Data Access Application Block

http://msdn.microsoft.com/en-us/library/ff649538.aspx

http://msdn.microsoft.com/en-us/library/ff648047.aspx

 

[MarkSweetland]

May want to get rid of the double quotes in the select command too.

SelectCommand="SELECT INSTITUTION_ID, NAME, ADDRESS, CITY, STATE, ZIP 
FROM INSTITUTION_SEARCH_VIEW WHERE ((NAME LIKE '%' || UPPER(:NAME) || '%') 
OR (NAME LIKE '%' || initcap(:NAME) || '%')) AND (ZIP = :ZCODE)">

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach