CODEWITHSUNDEEP
c#visual-studio-2010sql-server-2008
Vivek S Bhujbal
Vivek S Bhujbal

Reputation: 35

Unable to get result through stored procedure for dataset in c#

I have a stored procedure as follows:

ALTER PROCEDURE GetPatientSearchResultFromLocation(@Searchvalue varchar(100))
AS
   SELECT    
      RegNo, FirstName, MiddleName, LastName, EmailId, FrCityId, Location, MobileNo
   FROM         
      PatientsReg
   WHERE     
      (Location LIKE '@Searchvalue%');

Here if I run just the SQL query it gives me the appropriate output but when I use it in this procedure it does not return any value but just plain table structure.

My c# code is as follows:

public DataTable GetPatientDetailsFromLocation(string Searchvalue)
{
        command = new SqlCommand("GetPatientSearchResultFromLocation", connection);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.AddWithValue("@Searchvalue", Searchvalue);
        command.UpdatedRowSource = UpdateRowSource.OutputParameters;
        adp = new SqlDataAdapter(command);
        connection.Open();
        adp.Fill(ds);
        connection.Close();
         return ds.Tables[0];
}

there are no exception / errors but no values too.

Upvotes: 0

Views: 454

Answers (2)

Iswanto San
Iswanto San

Reputation: 18569

Try to change this:

(Location LIKE '@Searchvalue%');

to:

(Location LIKE @Searchvalue + '%');

Upvotes: 0

Steve
Steve

Reputation: 216303

The error is in the LIKE clause

  (Location LIKE '@Searchvalue%');

should be 

  (Location LIKE @Searchvalue);

without the single quotes, while the '%' wild card should be added to the parameter value directly in C# code.

 command.Parameters.AddWithValue("@Searchvalue", Searchvalue + "%");

Upvotes: 1

Related Questions