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
