We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Stored procedure works in Visual Studio, but not when I call it from the web page

Medium Priority
549 Views
Last Modified: 2012-05-11
My stored procedure looks like this:

ALTER PROCEDURE jhill.spDeleteExpenses
      (
      @owner_id int,
      @date date,
      @expense_type varchar(20)
      )
AS
       DELETE FROM Expenses
       WHERE owner_id = @owner_id
       AND   date = @date
       AND   expense_type = @expense_type
RETURN



The code in Visual Studio looks like this:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
            ConnectionString="<%$ ConnectionStrings:DOSDBConnectionString1 %>"
           
            SelectCommand="SELECT Expenses.Date, Expenses.Expense_Type, Expenses.Amount, Expenses.Description, Expenses.CreateDateTime, dbo.Owners.Name FROM Expenses INNER JOIN dbo.Owners ON Expenses.Owner_ID = dbo.Owners.Owner_ID"
            DeleteCommand="spDeleteExpenses" DeleteCommandType="StoredProcedure">
    <DeleteParameters>
        <asp:Parameter Name="owner_id" Type="Int32" />
        <asp:Parameter DbType="Date" Name="date" />
        <asp:Parameter Name="expense_type" Type="String" />
    </DeleteParameters>
        </asp:SqlDataSource>
    </div>
    </form>
</body>
</html>


The error that I get from the web page looks like this:

Exception Details: System.Data.SqlClient.SqlException: Procedure or function 'spDeleteExpenses' expects parameter '@date', which was not supplied.

Comment
Watch Question

Try it with DbType = "DateTime" instead of Date


http://forums.asp.net/p/1538329/3747914.aspx

Author

Commented:
The stored procedure still works with Visual Studio, but I still get the same error when I try to delete the row from the web page.
Sr. System Analyst
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
ms says dbtype is

Supported in: 4, 3.5 SP1, 3.0 SP2, 2.0 SP2

Author

Commented:
I tried taking off the Db.  It broke the web page.  It errors now.  I changed it back so that the page comes back up.
I changed the param name to expense_date.

ALTER PROCEDURE jhill.spDeleteExpenses
      (
      @owner_id int,
      @expense_date date,
      @expense_type varchar(20)
      )
AS
             DELETE FROM Expenses
             WHERE owner_id = @owner_id
             AND   date = @expense_date
             AND   expense_type = @expense_type
RETURN

I don't think that I can change the column name right now.

So right now, I am still getting the original error.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
did you try

<asp:Parameter Name="expense_date" Type="Date"/>
HainKurtSr. System Analyst
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
what is the message now after changing the param name?

Author

Commented:
I just tried that and got this error:

Description: An error occurred during the parsing of a resource required to service this request. Please review the following specific parse error details and modify your source file appropriately.

Parser Error Message: Cannot create an object of type 'System.TypeCode' from its string representation 'Date' for the 'Type' property.

Source Error:


Line 101:    <DeleteParameters>
Line 102:        <asp:Parameter Name="owner_id" Type="Int32" />
Line 103:        <asp:Parameter Name="expense_date" Type="Date" />
Line 104:        <asp:Parameter Name="expense_type" Type="String" />
Line 105:    </DeleteParameters>
 

Source File: /ManagementDB/Expenses.aspx    Line: 103

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
can you make it as string and pass parameter as yyyddmm then use
please try this

DeleteCommand="spDeleteExpenses2" DeleteCommandType="StoredProcedure">

<asp:Parameter Name="expense_date" Type="String"/>

ALTER PROCEDURE jhill.spDeleteExpenses2
      (
      @owner_id int,
      @expense_date varchar(10),
      @expense_type varchar(20)
      )
AS
             DELETE FROM Expenses
             WHERE owner_id = @owner_id
             AND   date = convert(datetime, @expense_date, 112) -- yyyymmdd
             AND   expense_type = @expense_type
RETURN
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
yes Date is not valid for Type, but DateTime is... thats why I posted 35445842 :)
Date is valid type for DBType

TypeCodes
http://msdn.microsoft.com/en-us/library/system.typecode.aspx

DBTypeCodes
http://msdn.microsoft.com/en-us/library/system.data.dbtype.aspx

Parameters
http://msdn.microsoft.com/en-us/library/c3hdxdtf.aspx
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
you can use

AND   date = convert(date, @expense_date, 112) -- yyyymmdd

or just this

AND   date = convert(date, @expense_date)

in your spDeleteExpenses2

Author

Commented:
Okay.  I think I have made all of the changes.
Here is the error I get:

Exception Details: System.Data.SqlClient.SqlException: Procedure or function 'spDeleteExpenses2' expects parameter '@expense_type', which was not supplied.

ALTER PROCEDURE jhill.spDeleteExpenses2
      (
      @owner_id int,
      @expense_date varchar(10),
      @expense_type varchar(20)
      )
AS
             DELETE FROM Expenses
             WHERE owner_id = @owner_id
             AND   date = convert(date, @expense_date) --yyyymmdd
             AND   expense_type = @expense_type
RETURN

          DeleteCommand="spDeleteExpenses2" DeleteCommandType="StoredProcedure">
    <DeleteParameters>
        <asp:Parameter Name="owner_id" Type="Int32" />
        <asp:Parameter Name="expense_date" Type="String" />
        <asp:Parameter Name="expense_type" Type="String" />
    </DeleteParameters>
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
ok, now... how do you set the values????? post the code to set the values...

Author

Commented:
I don't know if this is what you are looking for or not.  I am a little over my head with this part.  I am working with a couple other students on this project.  I don't know the ASP.net stuff.  I am a little more familiar with the database.  Sorry.

</head>
<body>
    <form id="form1" runat="server">
    <div id="aspMenu">
        <asp:Menu ID="Menu1" runat="server" Orientation="Horizontal">
            <Items>
                <asp:MenuItem NavigateUrl="~/ManagementDB/Default.aspx" Text="Home"
                    Value="Home"></asp:MenuItem>
                <asp:MenuItem Text="Manage Customers" Value="Manage Customers"
                    NavigateUrl="~/ManagementDB/ManageCustomers.aspx">
                    <asp:MenuItem NavigateUrl="~/ManagementDB/Customers.aspx" Text="View Customers"
                        Value="View Customers"></asp:MenuItem>
                </asp:MenuItem>
                <asp:MenuItem Text="Manage Dinners" Value="Manage Dinners"
                    NavigateUrl="~/ManagementDB/ManageDinners.aspx">
                    <asp:MenuItem NavigateUrl="~/ManagementDB/Dinners.aspx" Text="Dinners"
                        Value="Dinners"></asp:MenuItem>
                </asp:MenuItem>
                <asp:MenuItem NavigateUrl="~/ManagementDB/ManageExpenses.aspx"
                    Text="Manage Expenses" Value="Manage Expenses">
                    <asp:MenuItem NavigateUrl="~/ManagementDB/Expenses.aspx" Text="Expenses"
                        Value="Expenses"></asp:MenuItem>
                </asp:MenuItem>
                <asp:MenuItem Text="Manage Mileage" Value="Manage Mileage"
                    NavigateUrl="~/ManagementDB/ManageMileage.aspx">
                    <asp:MenuItem NavigateUrl="~/ManagementDB/Mileage.aspx" Text="Mileage"
                        Value="Mileage"></asp:MenuItem>
                </asp:MenuItem>
                <asp:MenuItem Text="Search" Value="Search">
                    <asp:MenuItem NavigateUrl="~/ManagementDB/SearchCustomers.aspx"
                        Text="Search Customers" Value="Search Customers">
                    </asp:MenuItem>
                    <asp:MenuItem NavigateUrl="~/ManagementDB/SearchMileageByDate.aspx"
                        Text="Search Mileage" Value="New Item"></asp:MenuItem>
                    <asp:MenuItem NavigateUrl="~/ManagementDB/SearchDinners.aspx"
                        Text="Search Dinners" Value="New Item"></asp:MenuItem>
                    <asp:MenuItem NavigateUrl="~/ManagementDB/SearchExpenses.aspx"
                        Text="Search Expenses" Value="Search Expenses"></asp:MenuItem>
                </asp:MenuItem>
            </Items>
        </asp:Menu>
    <br />
   <br />
   <br />
   <br />
    </div>
    <br />
    <div>
        <asp:GridView ID="GridView1" runat="server" AllowPaging="True"
            AllowSorting="True" AutoGenerateColumns="False" DataSourceID="SqlDataSource1"
            PageSize="100">
            <Columns>
                <asp:CommandField ShowDeleteButton="True" />
                <asp:BoundField DataField="Date" HeaderText="Date" SortExpression="Date" />
                <asp:BoundField DataField="Expense_Type" HeaderText="Expense_Type"
                    SortExpression="Expense_Type" />
                <asp:BoundField DataField="Amount" HeaderText="Amount"
                    SortExpression="Amount" />
                <asp:BoundField DataField="Description" HeaderText="Description"
                    SortExpression="Description" />
                <asp:BoundField DataField="CreateDateTime" HeaderText="CreateDateTime"
                    SortExpression="CreateDateTime" />
                <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
            </Columns>
        </asp:GridView>
        <a href="ManageExpenses.aspx">Add more expenses here</a>
        <div>
        <asp:Button
                ID="btnExportGrid" runat="server"
                Text="Export to Excel" OnClick="BtnExportGrid_Click" />
        </div>
        <div>
    <asp:LoginName ID="LoginName1" runat="server" FormatString="Logged in as {0}" />
    <asp:LoginView ID="LoginView2" runat="server">
        <LoggedInTemplate>
            <asp:LoginStatus ID="LoginStatus2" runat="server" />
        </LoggedInTemplate>
        <RoleGroups>
            <asp:RoleGroup Roles="Managers">
                <ContentTemplate>
                    <asp:HyperLink ID="HyperLink1" runat="server"
                        NavigateUrl="~/Login.aspx">Manage Site</asp:HyperLink> or
                    <asp:LoginStatus ID="LoginStatus1" runat="server" />  
                </ContentTemplate>
            </asp:RoleGroup>
        </RoleGroups>
    </asp:LoginView>
    </div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
            ConnectionString="<%$ ConnectionStrings:DOSDBConnectionString1 %>"
           
            SelectCommand="SELECT Expenses.Date, Expenses.Expense_Type, Expenses.Amount, Expenses.Description, Expenses.CreateDateTime, dbo.Owners.Name FROM Expenses INNER JOIN dbo.Owners ON Expenses.Owner_ID = dbo.Owners.Owner_ID"
            DeleteCommand="spDeleteExpenses2" DeleteCommandType="StoredProcedure">
    <DeleteParameters>
        <asp:Parameter Name="owner_id" Type="Int32" />
        <asp:Parameter Name="expense_date" Type="String" />
        <asp:Parameter Name="expense_type" Type="String" />
    </DeleteParameters>
        </asp:SqlDataSource>
    </div>
    </form>
</body>
</html>

Author

Commented:
Thank you for your help and your time!  I will show your instructions to the other person on this project with me and see if he understands more about what you are asking.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
try this:

 SelectCommand="SELECT Expenses.Date as expense_date, Expenses.Expense_Type, Expenses.Amount, Expenses.Description, Expenses.CreateDateTime, dbo.Owners.Name FROM Expenses INNER JOIN dbo.Owners ON Expenses.Owner_ID = dbo.Owners.Owner_ID"
            DeleteCommand="spDeleteExpenses2" DeleteCommandType="StoredProcedure">
    <DeleteParameters>
        <asp:Parameter Name="owner_id" Type="Int32" />
        <asp:Parameter Name="expense_date" DBType="Date" />
        <asp:Parameter Name="expense_type" Type="String" />
    </DeleteParameters>
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
I am trying to figure out this too :)

parameters are bound from selected columns with same name... so I added an alias expense_date to date column... and used that one as parameter... it will be passed to sp, with same name...

so here it is...
ALTER PROCEDURE jhill.spDeleteExpenses
      (
      @owner_id int,
      @expense_date date,
      @expense_type varchar(20)
      )
AS
             DELETE FROM Expenses
             WHERE owner_id = @owner_id
             AND   date = @expense_date
             AND   expense_type = @expense_type
RETURN

...
SelectCommand="SELECT Expenses.Date as expense_date, Expenses.Expense_Type, Expenses.Amount, Expenses.Description, Expenses.CreateDateTime, dbo.Owners.Name FROM Expenses INNER JOIN dbo.Owners ON Expenses.Owner_ID = dbo.Owners.Owner_ID" 
DeleteCommand="spDeleteExpenses" DeleteCommandType="StoredProcedure">
    <DeleteParameters>
        <asp:Parameter Name="owner_id" Type="Int32" />
        <asp:Parameter Name="expense_date" DBType="Date" />
        <asp:Parameter Name="expense_type" Type="String" />
    </DeleteParameters>

Open in new window

Author

Commented:
The page won't pull up when I put in the alias.

Author

Commented:
Okay, I changed this line:
 <Columns>
                <asp:CommandField ShowDeleteButton="True" />
                <asp:BoundField DataField="Expense_Date" HeaderText="Date" SortExpression="Expense_Date" />

and then put the alias back in.  The page comes up, but I still get this error when I try to delete:

Exception Details: System.Data.SqlClient.SqlException: Procedure or function 'spDeleteExpenses2' expects parameter '@expense_date', which was not supplied.

Author

Commented:
Does this Stack Trace help?

Stack Trace:


[SqlException (0x80131904): Procedure or function 'spDeleteExpenses2' expects parameter '@expense_date', which was not supplied.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +2030802
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +5009584
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +234
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2275
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +215
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +987
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
   System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +178
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +394
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteDelete(IDictionary keys, IDictionary oldValues) +576
   System.Web.UI.DataSourceView.Delete(IDictionary keys, IDictionary oldValues, DataSourceViewOperationCallback callback) +92
   System.Web.UI.WebControls.GridView.HandleDelete(GridViewRow row, Int32 rowIndex) +946
   System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +952
   System.Web.UI.WebControls.GridView.RaisePostBackEvent(String eventArgument) +210
   System.Web.UI.WebControls.GridView.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +176
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5563

 

Commented:
Make sure you got all the items now using the same (new) name Expense_Date. BoundField, SqlParameter, StoredProc parameter, and column name in db. Then I'm wondering when you look on the rendered page at the gridview make sure the data is not empty for date ( you're not allowing nulls are you) as this would cause this failure...

I'm posting this cause I stuck my nose in and I dont want to mess up the process. I can see that HainKurt is on top of things and with me chiming in there's just too many cooks...so I'm stepping back.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
I am not sure but try this (it may seem stupid but easy to test...)

    <DeleteParameters>
        <asp:Parameter Name="@owner_id" Type="Int32" />
        <asp:Parameter Name="@expense_date" DBType="Date" />
        <asp:Parameter Name="@expense_type" Type="String" />
    </DeleteParameters>

Author

Commented:
No luck.  ddayx10 suggested one thing that I haven't tried.  That is to rename the column in the database.  I haven't done that yet.  I am afraid that it will affect other things by doing it though.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
ok, one more try... remove the second param from everywhere and try to delete... just trying to find if the issue is on one parameter...


ALTER PROCEDURE jhill.spDeleteExpenses
      (
      @owner_id int,
      @expense_type varchar(20)
      )
AS
             DELETE FROM Expenses
             WHERE owner_id = @owner_id
             AND   expense_type = @expense_type
RETURN

...
SelectCommand="SELECT Expenses.Date as expense_date, Expenses.Expense_Type, Expenses.Amount, Expenses.Description, Expenses.CreateDateTime, dbo.Owners.Name FROM Expenses INNER JOIN dbo.Owners ON Expenses.Owner_ID = dbo.Owners.Owner_ID" 
DeleteCommand="spDeleteExpenses" DeleteCommandType="StoredProcedure">
    <DeleteParameters>
        <asp:Parameter Name="owner_id" Type="Int32" />
        <asp:Parameter Name="expense_type" Type="String" />
    </DeleteParameters>

Open in new window

Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
HainKurtSr. System Analyst
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Yes, I believe that was part of the problem.  After I changed the column name there were several more things that had to be changed before it would work.  I thank you for your help!

Author

Commented:
The help provided by the Experts on this site is awesome.  For someone like myself who has very little experience and limited understanding, the experts do a fine job of interpreting what it is that I am trying to explain and do.  Then they helped me to arrive at a satisfactory conclusion!  You people are amazing!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.