I am trying to search for stored files "for example from date: 15/12/2003 to: 24/6/2006" and when i press search no results appeare the following is the database code:
1 public DataTable searchData(string fileNo,string Title,string dFrom,string dTo,string brief)
2 {
3 string str ="";
4
5 str ="select * from Tb_File where Active = 1 ";
6
7 if (fileNo !="")
8 str +=" and FileNo='" + fileNo +"'";
9 if (Title !="")
10 str +=" and Title like '%" + Title +"%' ";
11 if (brief !="")
12 str +=" and Brief like '%" + brief +"%' ";
13 if (dFrom !="")
14 str +=" and DFrom >= convert(datetime,'" + Convert.ToDateTime(dFrom).ToShortDateString() +"',103) ";
15 if (dTo !="")
16 str +=" and DTo < convert(datetime,'" + Convert.ToDateTime(dTo).ToShortDateString() +"',103) ";
17
18 ole.Open();
19 SqlDataAdapter DA =new SqlDataAdapter(str, ole);
20 DataTable DT =new DataTable();
21 DA.Fill(DT);
22 ole.Close();
23 return DT;
24
25 }
i am using sql 2000, with Visual Studio 2005.
When you use dates in a database, always use the format yyyymmdd as this will eliminate any potential date format issues.
|||Thanks for your advice, But i have to use this format ==> so do u have a solution ??
|||
ca8msm:
When you use dates in a database, always use the format yyyymmdd as this will eliminate any potential date format issues.
Hi BigSmile,
you can use Profiler to find out what query is executed actually when you run your code. This might give you an idea of where it has gone wrong.
-Kishore
|||
But i have to use this format
Why?! You want to fix the sql to return the correct records, and it looks like it could potentially be a date formatting problem, so you should use the correct format...
There is probably some ambiguity in the date formats on both ends (e.g. yyddmm and yymmdd).
To avoid this you can write your dates out explicitly as "yyyy-MMM-dd" as in this example: "2007-Oct-20".
This is parsed by SQLServer correctly and avoids all ambiguities.
Writing this out is more code than you are currently writing, but is worth it to ensure 100% accuracy.
I also note you are using string concatenation - you should look at using System.Text.StringBuilder object instead.
Combining many string together produces lots of copies in memory as the string objects are immutable - StringBuilder has only one memory allocation as all appends done are on the same object.
try something like:
System.Text.Stringbuilder sb = new System.Text.StringBuilder(500);
sb.Append("Select * from tb_File where Active =1 ");
if( fileNo != "")
sb.Append(" and FileNo='" + fileNo + "'");
..etc
SqlDataAdapter DA = new SqlDataAdapter(sb.ToString(), ole);
...etc.
You could then write the dates out using this stringbuilder:
DateTime fromdate =Convert.ToDateTime(dFrom);
sb.Append( fromdate.Year.ToString() );
sb.Append("-");
sb.Append( fromdate.Month.ToString("MMM") );
sb.Append("-");
sb.Append(" fromDate.Day.ToString() );
You should also read this article onSQL Injection as your site is potentially very insecure.
Thanks, i'll try then mark it as an answer if it apply.
|||
There is probably some ambiguity in the date formats on both ends (e.g. yyddmm and yymmdd).
To avoid this you can write your dates out explicitly as "yyyy-MMM-dd" as in this example: "2007-Oct-20".
This is parsed by SQLServer correctly and avoids all ambiguities.Writing this out is more code than you are currently writing, but is worth it to ensure 100% accuracy.
I also note you are using string concatenation - you should look at using System.Text.StringBuilder object instead.
Combining many string together produces lots of copies in memory as the string objects are immutable - StringBuilder has only one memory allocation as all appends done are on the same object.try something like:
System.Text.Stringbuilder sb = new System.Text.StringBuilder(500);
sb.Append("Select * from tb_File where Active =1 ");if( fileNo != "")
sb.Append(" and FileNo='" + fileNo + "'");..etc
SqlDataAdapter DA = new SqlDataAdapter(sb.ToString(), ole);
...etc.You could then write the dates out using this stringbuilder:
DateTime fromdate =Convert.ToDateTime(dFrom);
sb.Append( fromdate.Year.ToString() );
sb.Append("-");
sb.Append( fromdate.Month.ToString("MMM") );
sb.Append("-");
sb.Append(" fromDate.Day.ToString() );
i rewrite it as follows:
public DataTable searchData(string fileNo,string Title,string dFrom,string dTo,string brief) { StringBuilder sb =new StringBuilder(500); sb.Append("select * from Tb_File where Active = 1 ");if (fileNo !="") sb.Append(" and FileNo='" + fileNo +"'");if (Title !="") sb.Append(" and Title like '%" + Title +"%'");if (brief !="") sb.Append(" and Brief like '%" + brief +"%'");if (dFrom !="") { DateTime fromdate = Convert.ToDateTime(dFrom); sb.Append(" and DFrom >= '"); sb.Append( fromdate.Year.ToString() ); sb.Append("-"); sb.Append( fromdate.Month.ToString("MMM") ); sb.Append("-"); sb.Append( fromdate.Day.ToString() ); sb.Append("'"); }if (dTo !="") { DateTime todate = Convert.ToDateTime(dTo); sb.Append(" and DTo < '"); sb.Append(todate.Year.ToString()); sb.Append("-"); sb.Append(todate.Month.ToString("MMM")); sb.Append("-"); sb.Append(todate.Day.ToString()); sb.Append("'"); } ole.Open(); SqlDataAdapter DA =new SqlDataAdapter(sb.ToString(), ole); DataTable DT =new DataTable(); DA.Fill(DT); ole.Close();return DT; }but there still an error and this is the error message:
Syntax error converting datetime from character string.
System.Data.SqlClient.SqlException was unhandled by user code
Message="Syntax error converting datetime from character string."
Source=".Net SqlClient Data Provider"
ErrorCode=-2146232060
Class=16
LineNumber=1
Number=241
Procedure=""
Server="localhost"
State=1
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.HasMoreRows()
at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
at System.Data.SqlClient.SqlDataReader.Read()
at System.Data.ProviderBase.DataReaderContainer.Read()
at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at DataAccess.searchData(String fileNo, String Title, String dFrom, String dTo, String brief) in c:\Documents and Settings\Yousef\My Documents\Visual Studio 2005\WebSites\FAProgram\App_Code\DataAccess.cs:line 124
at FileSearch.bindData() in c:\Documents and Settings\Yousef\My Documents\Visual Studio 2005\WebSites\FAProgram\FileSearch.ascx.cs:line 35
at FileSearch.btnSearch_Click(Object sender, EventArgs e) in c:\Documents and Settings\Yousef\My Documents\Visual Studio 2005\WebSites\FAProgram\FileSearch.ascx.cs:line 61
at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
Plz it is the final part of my project and there is no time, i need your quick replies|||You need to see what sb.ToString() equals before you execute it. I suggest you start debugging your application and take notice of the previous suggestions that people have given to you (such as using profiler and looking at what SQL Injection means).
|||
Hi BigSmile.
I think I gave you the incorrect code for the writing of the month as I hadn't tested it here.
To catch this, you should put a breakpoint on your line:
SqlDataAdapter DA =new SqlDataAdapter(sb.ToString(), ole);
...and see what the value of sb.ToString() is.
I imagine it is something like "2007-MMM-10"
I think the correct lines should be:
sb.Append( fromdate.ToString("MMM") );
sb.Append( todate.ToString("MMM") );|||
Sorry, it doesn't work.. i apply and this is the result:
this is the existing files:
and this is the results:
|||
Hi BigSmile.
I think I gave you the incorrect code for the writing of the month as I hadn't tested it here.
To catch this, you should put a breakpoint on your line:
SqlDataAdapter DA =new SqlDataAdapter(sb.ToString(), ole);
...and see what the value of sb.ToString() is.
I imagine it is something like "2007-MMM-10"I think the correct lines should be:
sb.Append( fromdate.ToString("MMM") );
sb.Append( todate.ToString("MMM") );
You need to see what sb.ToString() equals before you execute it. I suggest you start debugging your application and take notice of the previous suggestions that people have given to you (such as using profiler and looking at what SQL Injection means).
To catch this, you should put a breakpoint on your line:
SqlDataAdapter DA = new SqlDataAdapter(sb.ToString(), ole);...and see what the value of sb.ToString() is.
Are you actually bothering to listen to anyones replies? Read the two quotes above and act on the advice given.
Thanks alot i think i found the problem
the string builder contains an incorrect string value:
{select * from Tb_File where Active = 1 and Brief like '%03/11/2007%' and DTo < '2007-Mar-9'}
|||
You need to see what sb.ToString() equals before you execute it. I suggest you start debugging your application and take notice of the previous suggestions that people have given to you (such as using profiler and looking at what SQL Injection means).
To catch this, you should put a breakpoint on your line:
SqlDataAdapter DA = new SqlDataAdapter(sb.ToString(), ole);...and see what the value of sb.ToString() is.
Are you actually bothering to listen to anyones replies? Read the two quotes above and act on the advice given.
Hi BigSmile,
Looking at your code, the only way this is possible is that you have the parameters coming into the method mixed up.
You are putting the "from date" value in the "brief" string parameter of the searchData method.
This is evident by the fact that the date is not being formatted with the "MMM" format.
(if so, can you also mark this as the answer to the solution! thanks).
No comments:
Post a Comment