hi..
i just want to ask those who know to filtering multiple search using SP. My SP is working and the problem came when i want to call the parameter from my sp.can u check what error with my code

      public partial class WebForm3 : System.Web.UI.Page
                    {



                private string GetConnectionString()
                {
                    //call the Connection string that was set up from the web.config file
                    return System.Configuration.ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;
                }



                private DataTable GetData(SqlCommand cmd)
                {

                    DataTable dt = new DataTable();
                    String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;
                    SqlConnection con = new SqlConnection(strConnString);
                    SqlDataAdapter sda = new SqlDataAdapter();
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = con;
                    try
                    {
                        con.Open();
                        sda.SelectCommand = cmd;
                        sda.Fill(dt);
                        return dt;
                    }

                    catch (Exception ex)
                    {
                        throw ex;
                    }

                    finally
                    {
                        con.Close();
                        sda.Dispose();
                        con.Dispose();

                    }

                }

                protected void Page_Load(object sender, EventArgs e)
                {

                    SqlConnection con = new SqlConnection("data source=localhost;Initial Catalog=ITINVENTORYDB2;User ID=sa;Password=123456");
                    string strQuery = "Select Call_LogID ,Log_status,Log_code,Log_TypeApp ,Log_Branch ,Log_TypeCust ,Log_name,EntryTime" + " From Call_Log_Service";
                    SqlCommand cmd = new SqlCommand(strQuery);
                    DataTable dt = GetData(cmd);
                    GridView1.DataSource = dt;
                    GridView1.DataBind();


                }

                protected void btnsubmit_Click(object sender, EventArgs e)
                {
                     string  DateRangeStartValue = "01/04/2014";      //default(DateTime);

                    DateTime dt = Convert.ToDateTime(DateRangeStartValue);
                    Console.WriteLine("Year: {0}, Month: {1}, Day {2}", dt.Year, dt.Month, dt.Day);
                    IFormatProvider culture = new System.Globalization.CultureInfo("fr-FR", true);
                    DateTime dt2 = DateTime.Parse(DateRangeStartValue, culture, System.Globalization.DateTimeStyles.AssumeLocal);
                    Console.WriteLine("Year: {0}, Month: {1}, Day {2}", dt2.Year, dt2.Month, dt2.Day);

                    string DateRangeEndValue = "01/04/2014";
                    DateTime dt3 = Convert.ToDateTime(DateRangeEndValue);
                    Console.WriteLine("Year: {0}, Month: {1}, Day {2}", dt3.Year, dt3.Month, dt3.Day);
                    IFormatProvider culture1 = new System.Globalization.CultureInfo("fr-FR", true);
                    DateTime dt4 = DateTime.Parse(DateRangeStartValue, culture, System.Globalization.DateTimeStyles.AssumeLocal);
                    Console.WriteLine("Year: {0}, Month: {1}, Day {2}", dt4.Year, dt4.Month, dt4.Day);
     try
                {
                    String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;
                    SqlConnection scn = new SqlConnection(strConnString);
                    string sp = "T_FILTER1";

                    SqlCommand spcmd = new SqlCommand(sp, scn);
                    spcmd.CommandType = CommandType.StoredProcedure;
                    SqlParameter startingDate = new SqlParameter("@StarDate ", SqlDbType.DateTime);
                    startingDate.Value = tbStartDate.Text;

                    SqlParameter endingDate = new SqlParameter("@EndDate ", SqlDbType.DateTime);
                    endingDate.Value = tbEndDate.Text;
                    SqlDataReader dr;
                    spcmd.Parameters.Add(startingDate);
                    spcmd.Parameters.Add(endingDate);
                    scn.Open();
                    dr = spcmd.ExecuteReader();
                    while (dr.Read())
                    {

                        tbStartDate.Text = dr["EntryTime"].ToString();
                        tbEndDate.Text = dr["EntryTime"].ToString();
                    }
                    scn.Close();
                    dr.Close();
                }
                catch (SqlException x)

                    ////MessageBox.Show(x.Message.ToString());
                }

and here its my SP

    ALTER PROCEDURE [dbo].[T_FILTER1]
      (
       @Call_LogID int, 
       @StarDate datetime,
       @EndDate datetime,
        @Log_code char(20)

       )                
        AS
        Select Call_LogID  ,Log_status ,Log_code  ,Log_TypeApp ,Log_Branch ,Log_TypeCust ,Log_name
     From Call_Log_Service Where EntryTime BETWEEN convert(Datetime,@StarDate,102) 
    AND convert(Datetime,@EndDate,102)
    AND (Call_logID LIKE '%@Call_logID')
      AND (Log_code Like  '%@Log_code')
    --Select Call_LogID  ,Log_status ,Log_code  ,Log_TypeApp ,Log_Branch ,Log_TypeCust ,Log_name
    -- From Call_Log_Service Where (EntryTime BETWEEN @StarDate AND @EndDate OR @StarDate IS NULL OR @EndDate IS NULL)


    ORDER  BY Call_LogID

Please help me.tq

Your SP has 4 parameters - @Call_LogID, @StarDate, @EndDate and @Log_code and none of them have default values specified but you are only setting 2 in your C# code - @StarDate and @EndDate. You will need to also pass @Call_LogID and @Log_code to the SP if you want to execute it.

hi
i already try with call my parameter.but the proble still sama.The error is " Datetime convert to string"

Please check my code isn't correct or not and give some example for multiple search.
Tq

This article has been dead for over six months. Start a new discussion instead.