Why is my store procedure not working in web browser?

Below is the store producedure that I use:

IFexists(SELECT [Outlook].[ID], [Outlook].[title], [Outlook].[name], [Outlook].[issueID]FROM [OLissue], [Outlook]WHERE([Outlook].[issueID]= @dotnet.itags.org.OLissueIDAND [Outlook].[issueID]= [OLissue].[issueID]))

SELECT [ID], [title], [name], [issueID]FROM [Outlook]WHERE([issueID]= @dotnet.itags.org.OLissueID)

ELSE

SELECTTOP 1NULL ID,'No parent page yet' titleFROM [Outlook]

The store procedure above works fine when run in Microsoft SQL Server Manager Studio but when run in the browser it's not working. Here is the Outlook table:

Column NameDate TypeAllow NullsIDintUncheckedmenubitCheckedmnuOrderintCheckedmnuLevelintCheckedparentintCheckedissueIDintUncheckedmasterPagevarchar(100)UncheckedvisiblebitCheckednamevarchar(50)Checkedtitlevarchar(250)Uncheckeddescriptionvarchar(1000)CheckedsummarytextCheckedcontentstextCheckedimagevarchar(50)CheckedimgCaptionvarchar(1000)CheckedapprovebitCheckedcreateDatedatetimeUnchecked

And the OLissue table:

Column NameDate TypeAllow NullsissueIDintUncheckednamevarchar(50)Uncheckedtitlevarchar(100)Checkeddescriptionvarchar(500)CheckedcrntIssuebitCheckedfrntPageintCheckedarchivedbitCheckednavOrderintCheckeddateCreatedatetimeChecked

Now, let me explain further how my web application works. I have two SqlDataSource controls in my .aspx page. The sqlOLpage control is to retrieve all the data in the Outlook table. The sqlParent control is set to use the above store procedure. In my .aspx page, I have a FormView control. In the Edit/Insert mode, I have a drop down list (ddlParent). The ddlParent DataSourceID is set to equal the sqlParent control. However, this ddlParent is set to Bind with the sqlOLpage control. I kept getting this error when enter the Edit mode:

'ddlParent' has a SelectedValue which is invalid because it does not exist in the list of items.
Parameter name: value

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.ArgumentOutOfRangeException: 'ddlParent' has a SelectedValue which is invalid because it does not exist in the list of items.
Parameter name: value

This error occurs when the Outlook.parent and the OLissue.frntpage field is NULL. However, what I don't understand is why is it that SQL Server Management Studio returns the record that is in the Outlook table fine.

Again, thank so much for any help.

the drop downlist takes aarguments one of the is the value and the other one is the string or item that you can see on the ddl. now when your sqlproc. returns null, the value will be null on the ddl and this action is not supported. you can do this, try to give a value for the null items like 0 or something on databond time.

check this link for more information about the same challange that you have.

http://forums.asp.net/p/648074/648671.aspx

hope this will help you

Thanks! I'll take a look at the link. However, what I do not understand is that the SP returns a valid value (not null) when run in SQL Server Management Studio. And yes, the DDL's display value is set totitle and the data field value is set toID and both of these records and the rest of the selecedt records returned fine when ran in sql mgmt. studio. So why is it not returned when using with a DDL control?

Okay, I test the sqlParent control inside of VS.net and it does return a valid record but still receive the error when running the whole page in the browser.

Okay, the problem seems to be coming from the dropdownlist control and not my store procedure. But if the SP does return a value, why is the dropdownlist having problem selecting?

Okay, I think got it...but still need help. I narrow the problem to the data binding. When the ID of SelectedValue in the DDL does not equal the returned value of the SP, it generates this error. If set the DDL control to unbound and I enter the edit mode fine without any errors and the DDL does show the returned value from the SP. So my question is, how do I avoid this error when in this situation and list the returned value from the SP but still binding to a database field?

Okay, by careful look at the scenareo again, I'm still confused about the DropDownList selectedValue property. Here is I did a test of the problem.

<asp:DropDownListID="DropDownList1"runat="server"DataSourceID="sqlParent"DataTextField="title"DataValueField="ID"SelectedValue='<%# Bind("ID") %>'></asp:DropDownList>

The above line generates the error stated above. If I take theSelectedValue='<%# Bind("ID") %>' out then it works fine. Now, I have a textBox like this:

<asp:TextBoxID="TextBox1"runat="server"Text='<%# Bind("ID") %>'></asp:TextBox><br/>

And the ID from the TextBox1 and DropDownList1 both equal so, why is there an error when I use the SelectedValue property on the DropDownList1?

Hi,

Based on my understanding, you want to set the SelectedValue to the DropDownList which exists in the FormView. When you set the value which be retrieved from database to the SelectedValue property, you get the error message above. If I have misunderstood you, please feel free to let me know.

We need to understand the DropDownList's SelectedValue property first. When we set the value to SelectedValue property, we have to make sure that the DropDownList contains this value. If it don't contain this value, we will get the error message above.

For more information, seeDropdownlist in a Gridview and the default (null) value problem.

I hope this helps.

Hey, thanks! That article seems to solves my problem for now. However, if I do Value="", will I get a null value insert into the database field? I data field is setup to accept null so it will be nice if it is null and not blank.

Okay, I just encounter the problem to my previous question. It seem that since the Value="", when I try to insert new record, there is a popup window kept telling me that I have the data in the wrong format. How do I resolve this error? How do I enter a null value is I didn't choose anything?

Any other idea, anyone?