Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 2.0 > ASP.NET 2.0 Basics
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
ASP.NET 2.0 Basics If you are new to ASP or ASP.NET programming with version 2.0, this is the forum to begin asking questions. Please also see the Visual Web Developer 2005 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 2.0 Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #11 (permalink)  
Old September 12th, 2006, 09:53 AM
Registered User
 
Join Date: Mar 2006
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Well, here we go again. After my last post Microsoft got back to me, and we delved even further. Even output parameters wont always work, and here is the official MS reason: SqlDataSource is bugged.

That's right, SqlDataSource is bugged. Its a known issue that it occurs, and the only work around is to hand code the stored procedure in either the SqlDataSource event or the control events by making a connection, creating the command, creating the parameters and then running the .executeNonQuery() on the command.

*sigh* Sorry for my initial post thinking that would fix everything. *grumbles furiously*
Reply With Quote
  #12 (permalink)  
Old November 3rd, 2006, 11:53 AM
Registered User
 
Join Date: Nov 2006
Location: Titusville, FL, USA.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I'm trying to delete each row in the database using the Deleting event. I have the following code, which is only deleting the first row. Any suggestions?

Thanks,
Carolyn

    protected void IWSSqlDataSource_Deleting(object sender, SqlDataSourceCommandEventArgs e)
    {
        foreach (SqlParameter param in e.Command.Parameters)
        {
            param.Value = "";
        }
    }


Reply With Quote
  #13 (permalink)  
Old November 6th, 2006, 07:12 PM
Registered User
 
Join Date: Nov 2006
Location: , , Hong Kong.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

A SOLUTION HERE! (hopefully it works for everyone...)

ok... firstly i had similar problems
the stored procedure OUTPUT parameter always return null
(i was trying to get the id of a new inserted row)

** code below are not completed, just trying to give u the main idea


so here is my table and stored procedure

/*--------------------------------------------*/

CREATE TABLE myItem(
    itemID INT PRIMARY KEY IDENTITY(1, 1),
    itemName VARCHAR(50)
)

/*--------------------------------------------*/
CREATE PROCEDURE itemCreate

@itemName VARCHAR(50),
@itemIdReturn INT OUTPUT

AS
BEGIN
    --create new record in 'myItem' table
    INSERT INTO myItem (itemName) VALUES (@itemName)

    --return new record 'itemID'
    SELECT @itemIdReturn = @@IDENTITY
END
GO

/*--------------------------------------------*/


and here is my site source code
just a textbox for input, a button for submit,
a label for the return id, and a sqlDataSource control

(this is the NOT WORKING version)

/*--------------------------------------------*/


        <asp:TextBox ID="txtItemName" runat="server"></asp:TextBox>&nbsp;<asp:Button ID="btnCreate"
            runat="server" Text="Create" OnClick="btnCreate_Click" /><br />
        <br />
        <asp:Label ID="lblIdReturn" runat="server"></asp:Label><br />
        <br />
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="..."
            InsertCommand="itemCreate" InsertCommandType="StoredProcedure" ProviderName="System.Data.SqlClient">
            <InsertParameters>
                <asp:ControlParameter ControlID="txtItemName" Name="itemName" PropertyName="Text"
                    Type="String" DefaultValue="0" />
                <asp:ControlParameter ControlID="lblIdReturn" Direction="Output" Name="itemIdReturn"
                    PropertyName="Text" Type="Int32" DefaultValue="0" />
            </InsertParameters>
        </asp:SqlDataSource>

/*--------------------------------------------*/

and here is the code behind
just call the Insert() when submit button clicked

/*--------------------------------------------*/

    protected void btnCreate_Click(object sender, EventArgs e)
    {
        SqlDataSource1.Insert();
    }

/*--------------------------------------------*/


up to here...
supposingly, the label should return the new item id when the button clicked
(at least that what i think), but it always return null

and here is how i got it work !!!
just adding two things
add -- OnInserted ="On_Inserted" -- in the asp:SqlDataSource
and a fucntion call -- public void On_Inserted() -- in the code behind

so here is the WORKING version of the source and code behind
new added code highligthed in red

/*--------------------------------------------*/

        <asp:TextBox ID="txtItemName" runat="server"></asp:TextBox>&nbsp;<asp:Button ID="btnCreate"
            runat="server" Text="Create" OnClick="btnCreate_Click" /><br />
        <br />
        <asp:Label ID="lblIdReturn" runat="server"></asp:Label><br />
        <br />
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="..."
            InsertCommand="itemCreate" InsertCommandType="StoredProcedure" ProviderName="System.Data.SqlClient" OnInserted ="On_Inserted">
            <InsertParameters>
                <asp:ControlParameter ControlID="txtItemName" Name="itemName" PropertyName="Text"
                    Type="String" DefaultValue="0" />
                <asp:ControlParameter ControlID="lblIdReturn" Direction="Output" Name="itemIdReturn"
                    PropertyName="Text" Type="Int32" DefaultValue="0" />
            </InsertParameters>
        </asp:SqlDataSource>

/*--------------------------------------------*/

using System.Data.Common; // <---- REMEMBER THIS
    protected void btnCreate_Click(object sender, EventArgs e)
    {
        SqlDataSource1.Insert();
    }

    public void On_Inserted(Object sender, SqlDataSourceStatusEventArgs e)
    {
        DbCommand command = e.Command;

        lblIdReturn.Text = command.Parameters["@itemIdReturn"].Value.ToString();
    }

/*--------------------------------------------*/


HOPEFULLY now you should be able to see the return id of the new inserted row in the label control
(at least i did... :))

hope this code is clear enuf to understand
cos i seldom post...

a reference to the Inserted thing...
http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.inserted(V S.80).aspx

Reply With Quote
  #14 (permalink)  
Old November 19th, 2006, 11:55 AM
Registered User
 
Join Date: Nov 2006
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

:) :D :) :D
You really made my day, i cracked my head into this issue for the last 3 hours but your solution was super cool
GOD BLESS YOU

super solution

thank soooooooooooooooooooooo much

all thanks to wrox !!!

Reply With Quote
  #15 (permalink)  
Old March 12th, 2008, 02:43 AM
Registered User
 
Join Date: Jun 2003
Location: Bangalore, Karnataka, India.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I want to pass an sql query(create a stored procedure) through a textbox in jsp page, in struts using hibernate. It should execute that query and display results.I m using MySQL db,Please suggest me or send code.Anyone please do the needfull...

Pradeep Murti
Reply With Quote
  #16 (permalink)  
Old March 12th, 2008, 02:31 PM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,413
Thanks: 0
Thanked 16 Times in 16 Posts
Default

Pradeep -

How does this relate to ASP.NET?

-Peter
peterlanoie.blog
Reply With Quote
  #17 (permalink)  
Old March 14th, 2017, 01:45 PM
Registered User
Points: 3, Level: 1
Points: 3, Level: 1 Points: 3, Level: 1 Points: 3, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2017
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Fix

Quote:
Originally Posted by Tarcash View Post
OK, this problem is something that took me a long time to figure out, and that's with Microsoft's support line. Luckily, this became classified as "a known issue" so I wasn't charged for anything. There is no complete description as to why it occurs, but it seems to occur when there is an issue passing items to your stored procedure, or an error occurs in your stored procedure. There is an easy fix, but it may not be what you wanted to use.

Use an OUTPUT parameter instead of a return value. I know, I hate doing it too, but it removes the issue. Like I said, the return parameters for sqlDataSources are bugged, so use an output parameter.
Sorry for bringing up a new post, only I found this post when I was trying to figure out this issue and hope to help someone who might also come across it.

Sorry for the long delay, thought it would be useful to document this since I had the same issue and couldn't find any answers on the internet.

To retrieve a return value from SQL server, you need to access it from the Inserted event.

protected void DSProjects_Inserted(object sender, SqlDataSourceStatusEventArgs e)
{
string strID = (e.Command.Parameters["@RETURN_VALUE"].Value).ToString();


Response.Redirect("ProjectDetails.aspx?id=" + strID);
}

A couple of things to mention that was overlooked:

1. Return value is not the selected value in SQL. In your Stored procedure, you need to use "RETURN @ID", rather than Select @ID
2. ASP parameter should look like this (Ensure the Size is not default 0):

<asp:Parameter Direction="ReturnValue" Name="RETURN_VALUE" Type="Int32" Size="100" />

Alternatively, you can use the Output SQL Parameter:

<asp:Parameter Direction="Output" Name="ID" Type="Int32" Size="100" />

and set up SQL using outputs. In the Scope:

@ID VARCHAR(20) OUTPUT

in the code section:

SET @ID = SCOPE_IDENTITY()
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
[Resolved] Get return value from stored procedure snufse ASP.NET 2.0 Basics 1 August 8th, 2008 12:05 PM
Stored Procedure return value dmiles C# 3 December 6th, 2007 05:20 AM
Can i return a recordset in a Stored procedure chiefouko VB Databases Basics 6 December 12th, 2006 03:34 AM
Stored Procedure Insert Null Value harpua SQL Server ASP 2 March 5th, 2005 10:19 AM
how i use the Stored Procedure return value and ho yoord ADO.NET 5 June 28th, 2004 09:39 AM



All times are GMT -4. The time now is 03:30 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.