Get String Value Returned From SQL Server

Question

I am using C# to run a SQL Server Stored Procedure, I want to store the returned variable from my sql stored procedure in a string variable, but every time I try to run it, my C# syntax errors telling me it is unable to convert varchar to int. I am very new when it comes to communicating with C# and SQL so please forgive me if I omit needed information or am overlooking the most obvious points. Below is my code, please informe me if any additional code is needed to further assist me in this issue. Thank you in advance for all who assist

//C# Syntax
private void btnBTNBTN_Click()
{
    string returnedvariable = DoThisStuff(abcd, faraca);
}

public string DoThisStuff(string abcd, int faraca)
{
    string value = string.Empty; 
    sqlsyntaxxxx = new StringBuilder();
    sqlsyntaxxxx.Append("exec dbo.AlphaDawg ");
    sqlsyntaxxxx.Append("'" + faraca + "'");
    _dataSet = RUNSQL(abcd, sqlsyntaxxxx.ToString());
    return value;
}

public DataSet RUNSQL(string abcd, string sqlsyntaxxxx)
{          
    _connectionString = System.Configuration.ConfigurationManager.AppSettings[connectionString].ToString();     
    _sqlDatabaseConnection = new SqlConnection(_connectionString);
    _sqlCommand = new SqlCommand(sqlsyntaxxxx, _sqlDatabaseConnection);
    _sqlDatabaseConnection.Open();
    _dataSet = new DataSet();
    _sqlDataAdapter = new SqlDataAdapter(_sqlCommand);            
    _sqlDataAdapter.Fill(_dataSet, "Data");
    return _dataSet;
}

SQL Stored Procedure

ALTER PROCEDURE [dbo].[AlphaDawg]
(
    @faraca int
)
AS
BEGIN
    DECLARE @returnvalue varchar(500)

    UPDATE [dbo].[redllama]
    SET [AZYXIE] = '682134'+[zoneNumber]
    WHERE faraca = @faraca

    Set @returnvalue = (Select [AZYXIE] from [dbo].[redllama] WHERE faraca = @faraca)

    return @returnvalue
END

FYI, the SQL return statement can only return an integer from a procedure and that value isn't part of any dataset. — RBarryYoung
– RBarryYoung, Commented Sep 11, 2015 at 18:11
@RBarryYoung - how is it possible to return a string value then? Use 2 separate SQL Calls one to update and one to query to return the needed value? — Simon Paris
– Simon Paris, Commented Sep 11, 2015 at 18:16
return is not what you want, even for integers 99% of the time (it's intended as a status value for other SQL procedures). You usually return data through a dataset by executing a SELECT <value[s]> ... in your procedure. You can also return single values through an output parameter, but I would recommend the dataset until you are more familiar with this. — RBarryYoung
– RBarryYoung, Commented Sep 11, 2015 at 18:19

RBarryYoung · Accepted Answer · 2015-09-11 18:29:48Z

First, the SQL return statement can only return an integer from a procedure and that value isn't part of any dataset.

In any event, return is not what you want, even for integers 99% of the time (it's intended as a status value for other SQL procedures). You usually return data through a dataset by executing a SELECT ... in your procedure. You can also return single values through an output parameter, but I would recommend the dataset until you are more familiar with this.

Change your SQL procedure as follows and it should work:

ALTER PROCEDURE [dbo].[AlphaDawg]
(
    @faraca int
)
AS
BEGIN
    DECLARE @returnvalue varchar(500)

    UPDATE [dbo].[redllama]
    SET [AZYXIE] = '682134'+[zoneNumber]
    WHERE faraca = @faraca

    Select [AZYXIE] from [dbo].[redllama] WHERE faraca = @faraca)
END

Hmm, looks like your c# code needs to be changed a little bit also. I haven't done this in a while, but I think that this is what you want:

public string DoThisStuff(string abcd, int faraca)
{
    string value = string.Empty; 
    sqlsyntaxxxx = new StringBuilder();
    sqlsyntaxxxx.Append("exec dbo.AlphaDawg ");
    sqlsyntaxxxx.Append("'" + faraca + "'");
    _dataSet = RUNSQL(abcd, sqlsyntaxxxx.ToString());

    value = _dataSet.Tables[0].Rows[0]["AZYXIE"].ToString();
    return value;
}

Brian Pressler · Accepted Answer · 2015-09-11 19:47:30Z

2

Your paramater variable faraca is defined as an int. So you need to change this line:

sqlsyntaxxxx.Append("'" + faraca + "'");

to

sqlsyntaxxxx.Append(faraca.ToString());

Also, you will have to set the value of value in your method DoThisStuff. As it is now... you are going to always just return an empty string. So you need to pull a field out of your result set before the return statement... something like:

value = ds.Tables[0].Rows[0]["Myfield"]

Then change your stored procedure to:

ALTER PROCEDURE [dbo].[AlphaDawg]
(
    @faraca int
)
AS
BEGIN
    DECLARE @returnvalue varchar(500)

    UPDATE [dbo].[redllama]
    SET [AZYXIE] = '682134'+[zoneNumber]
    WHERE faraca = @faraca

    Select [AZYXIE] 
    from [dbo].[redllama] 
    WHERE faraca = @faraca

END

edited Sep 11, 2015 at 19:47

answered Sep 11, 2015 at 18:08

Brian Pressler

6,7232 gold badges21 silver badges42 bronze badges

2 Comments

Simon Paris Over a year ago

if I try this value = _dataSet.Tables[0].Rows["AZYXIE"]; It presents a compiler error of System.Data.DataRowCollection.this[int] has some invalid arguments.

Brian Pressler Over a year ago

you have to return a dataset in your procedure. I updated my answer to show how the the procedure should look.

Collectives™ on Stack Overflow

Get String Value Returned From SQL Server

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related