C# Webservice SQL and how to return a result

Hi everyone and thanks in advance for your help.

I am trying to create a web service that uses an input parameter and outputs a string

Example = input parameter = 'Hi'
Result  = 'Hi thanks for stopping  by my web service"

I have created and test the Proc and the proc reads like this
select output1 from table where input1 = @input1
ouput1 is a nvarchar (255)

Now my webservice is working but it is returning a 0 for the results
code below

        [WebMethod]
        public int GetTextResponse(string filter)
        {
            string connString = "Server= xxx ;Initial Catalog=TIRES;Persist Security Info=True;User ID=sa; Password=xxxx;";
            using (SqlConnection sc = new SqlConnection(connString))
            {
                sc.Open();
                using (SqlCommand cmd = new SqlCommand ("usp_GetTextResponse",sc))
                {  
                    cmd.CommandType = CommandType.StoredProcedure;
                   // cmd.Parameters.Add("Output1",SqlDbType.NVarChar,255);
                    //cmd.Parameters["OutPut1"].Direction = ParameterDirection.ReturnValue;
                    cmd.Parameters.Add("@Input1",SqlDbType.NVarChar,50);
                    cmd.Parameters["@Input1"].Direction =ParameterDirection.Input;
                    cmd.Parameters["@Input1"].Value = filter;
                    cmd.ExecuteNonQuery();
                   return (int)cmd.Parameters["Output1"].Value;

What am I doing wrong ?

I know it is in this line of code

           return (int)cmd.Parameters["Output1"].Value;

but am not sure what I need to do to fix

I know I could create a data set and return the output in the dataset ....but sense there is on one output and it is a nvarchar(255) ...I thought they would be some way just to return a string...Is a dataset the best way to go?

Thanks in advance for your help
BBQMemphisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ssflynnCommented:
It's looks to be returning an integer becuase your method has (int) as the return type and this line

return (int)cmd.Parameters["Output1"].Value;

should read

return cmd.Parameters["Output1"].Value.ToString()

Also change

  [WebMethod]
        public int GetTextResponse(string filter)

to

  [WebMethod]
        public string GetTextResponse(string filter)
0
BBQMemphisAuthor Commented:
Thank my code now reads like this

  [WebMethod]
        public string GetTextResponse(string filter)
        {
            string connString = "Server= xxx ;Initial Catalog=TIRES;Persist Security Info=True;User ID=sa; Password=xxx;";
            using (SqlConnection sc = new SqlConnection(connString))
            {
                sc.Open();
                using (SqlCommand cmd = new SqlCommand ("usp_GetTextResponse",sc))
                {  
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("Output1",SqlDbType.NVarChar,255);
                    cmd.Parameters["OutPut1"].Direction = ParameterDirection.ReturnValue;
                    cmd.Parameters.Add("@Input1",SqlDbType.NVarChar,50);
                    cmd.Parameters["@Input1"].Direction =ParameterDirection.Input;
                    cmd.Parameters["@Input1"].Value = filter;
                    cmd.ExecuteNonQuery();
                    return cmd.Parameters["Output1"].Value.ToString();

It is still only outputting a 0 (zero) when I test the web service....I have used SQL profiler and am sure that the proc is running and not error out

Could it be I am really trying to insert a parameter and just print the result set as output .....What am I doing wrong..

Thanks Again
0
ssflynnCommented:
Try the following code.  First is my stored procedure,  then my C# code.

CREATE PROCEDURE uspTest      
      @InputString NVARCHAR(50)
AS
BEGIN
      DECLARE @StringToAdd NVARCHAR(MAX)
      SET @StringToAdd = 'Add this text'
      SELECT @InputString + ' ' + @StringToAdd
END
GO
        public static string GetTextResponse(string filter)
        {
            string connString = "Server=localhost;Initial Catalog=XXX;Persist Security Info=True;User ID=sa; Password=XXX;";
            using (SqlConnection sc = new SqlConnection(connString))
            {
                sc.Open();
                using (SqlCommand cmd = new SqlCommand("uspTest", sc))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@InputString", SqlDbType.NVarChar, 50);
                    cmd.Parameters["@InputString"].Value = filter;
                    return cmd.ExecuteScalar().ToString() ;
                }
            }
        }

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anurag ThakurTechnical ManagerCommented:
i think the culprit is your stored procedure - may be you are not assigining the value to the @Output1 variable

modify your stored procedure like this
Declare Output1 NvarChar(255)
select @output1 = output1 from table where input1 = @input1


or if you let your sp's statement like this
select output1 from table where input1 = @input1

then you can simply use the ExecuteScalar as shown below
cmd.ExecuteScalar().ToString() ;
0
BBQMemphisAuthor Commented:
Thanks for your help.....
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.