0

I've searched for days on this and I have found many possibilities with the sql connection being made in the C# code, but I can't find anything where the connection is made using the web.config and .aspx page like mine.

The problem: I have my SQLdatasource1 with an Insert using a stored procedure. The SP checks for a duplicate ID (MRN) and spits out a return of 0 if it finds one and spits out a 1 if it doesn't. It will also perform the insert as long as there is no duplicate. All that is just fine.

However, I want to grab the ReturnValue, check for 0 or 1 and give the user a message depending on the result. Here's what I'm working with. It is abbreviated for simplicity. The form is an asp:formview with an InsertItemTemplate and all controls are contained in it.

<asp:sqldatasource ID="sqldatasource1" runat="server"
ConnectionString="<%$ ConnectionStrings:... %>"
ProviderName="..."
...
<InsertParameters>
     <asp:Parameter Name="return_value" DefaultValue="0" Type="Empty" Size="0" ConvertEmptyStringToNull="False" Direction="ReturnValue" />
</InsertParameters>

In the codebehind, I just want to grab the value of the parameter return_value and respond to it. What I have below is what I'd like to do, but it's obviously wrong. I just wanted to post it asI envision it so you'd know my intention. I've tried using the button's click, and that not only gave me the same error that return_value does not exist in current context, but it won't post the message boxes either. I've tried so many different things at this point I'm lost.

UPDATE: This is what I currently have in codebehind. The MessageBox simply shows me "@return_value" when it pops up.

        protected void Sqldatasource1_Inserted(object sender, SqlDataSourceStatusEventArgs e)
{
    SqlParameter return_value = new SqlParameter("@return_value", SqlDbType.Int);
    return_value.Direction = ParameterDirection.ReturnValue;
    return_value.Value = (object)return_value ?? DBNull.Value;
    MessageBox.Show(return_value.Value.ToString());

    if (Convert.ToInt32(return_value.Value) != 1)
    {
        MessageBox.Show(return_value.Value.ToString());
    }
    else
    {
        MessageBox.Show(return_value.Value.ToString());

    }
}

Thanks in advance. Sorry if this is laughably rudimentary, but I'm flying blind.

UPDATE 2 (5/7/2013): After additional searching and reading, I've tried some other things that haven't worked. No errors, just doesn't work. I've tried the following two bits in my Inserted.

int? return_value = e.Command.Parameters["@return_value"].Value as int?;
    MessageBox.Show(return_value.ToString());

and

int return_value = (Convert.ToInt32(e.Command.Parameters["@return_value"]));
    MessageBox.Show(return_value.ToString());
Improve this question

3 Answers 3

Reset to default
1

Have you tried adding a parameter when inserting? Check out this link: http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/7d37ac80-af6d-46ad-8ae0-dc7891596aac

The marked answer says you can add a "Return_Value" parameter that will hold the value.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

thanks for the help. This seems like a step in the right direction. I'm trying to change to c# and do what that OP did for his solution, but I think I'm getting types and conversions all screwed up. I placed a messagebox in there to try to see the return_value before the IF, but the whole thing fails so I get nothing. SqlParameter return_value = default(SqlParameter); return_value.Direction = ParameterDirection.ReturnValue; MessageBox.Show(return_value.Value.ToString()); if (Convert.ToInt32(return_value.Value) != 1)...
I've also tried using SqlParameter return_value = new SqlParameter("@return_value", SqlDbType.Int); instead of default(sqlparameter) but I haven't had any better luck so far.
Are you checking the Return_value in the 'Inserted' event? Inserting is before it does the insert, Inserted will contain the actual return value.
I've tried it with inserted and inserting, and it behaves the same way. If I use create my SqlParameter using ("@return_value"), I just get a message box with @return_value in it. If I use the default(SqlParameter) other people use, nothing at all happens. I'm trying to read everything I can find on the proper types and handling nulls and such for SqlParameter, but so far I haven't hit on the right thing. Thanks for giving me some guidance, though. I appreciate it.
1

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()
Improve this answer

1 Comment

Definitely been a minute since I had this problem, but I certainly appreciate the extra input. There's no telling when I may need to do something like this again, and I will have forgotten everything I did wrong back then. Either way, thanks! Cheers.
0

I have come back to this to leave an answer because I hate loose ends. Unfortunately, I never could make it work the way I set out to--using the sqldatasource and template inside of the aspx file technique. I tried everything I could find and nothing others did worked for me most likely because of my ignorance on the subject I was missing something.

I finally had to go back to the beginning and do everything in the codebehind. So below is the the whole thing minus the 40 other parameters I'm using. for you others who are new or only part time ASP.NET C# dabblers, be aware you have to add the data, data.sqlclient and windows.forms to your codebehind. You also need to add the Windows forms reference (Website menu / Add Reference). Be aware, though, that you almost NEVER want to use the Windows.Forms for your alerts. This was a special case. The below is best for testing, but to publish for others to use, you will need to use Javascript or some other technique for your alerts.

Best of luck, and I hope this helps someone.

using System.Data;  
using System.Data.SqlClient;
using System.Windows.Forms;

... 

    protected void Save_Click(object sender, EventArgs e)
{

    string connectionstring = WebConfigurationManager.ConnectionStrings["MyDB01"].ConnectionString;
    SqlConnection con = new SqlConnection();
    con.ConnectionString = connectionstring;
    SqlCommand cmd = new SqlCommand();

    cmd = new SqlCommand("DS_SSRS_Reports.dbo.DRPinsertPat", con);


    cmd.Parameters.AddWithValue("@NPI", NPI.Text);
    ...
    cmd.Parameters.Add(new SqlParameter("@return_value", SqlDbType.Int));
    cmd.Parameters["@return_value"].Direction = ParameterDirection.ReturnValue;

    cmd.CommandType = CommandType.StoredProcedure;

    con.Open();
    cmd.ExecuteNonQuery();
    con.Close();

    int r = (Convert.ToInt32(cmd.Parameters["@return_value"].Value));

    if (r.Equals(0))
    {
        MessageBox.Show("MRN already exists in DRP database. Patient not entered.");
    }
    else
    {
        MessageBox.Show("Patient successfully added.");
    }
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.