Ken

Ken

  • NA
  • 35
  • 0

WebService SQL results help

Nov 8 2008 2:03 PM
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

Answers (1)