locked
Where is the error RRS feed

  • Question

  • User1052662409 posted

     Hi all my searching query is as follows

    select * from tblIssued where RcDate between '" + ddlMonth.SelectedItem.Text + "/" + ddlDay.SelectedItem.Text + "/" + ddlYear.SelectedItem.Text + "' and '" + ddlMonthNext.SelectedItem.Text + "/" + ddlDayNext.SelectedItem.Text + "/" + ddlYearNext.SelectedItem.Text + " and catId='2' '";

    please help me out

    Tuesday, March 31, 2009 6:14 AM

Answers

  • User-690506545 posted

    You're missing the whole of the FROM clause...

     select * from table where

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 31, 2009 7:20 AM
  • User191633014 posted

    1. if  CategoryId is INT or BIGINT remove quotation marks around 2.

    2. Use next to create sql statement:

            DateTime d1 = new DateTime(ddlYear.SelectedItem.Text, ddlDay.SelectedItem.Text, ddlMonth.SelectedItem.Text);
            DateTime d2 = new DateTime(ddlDayNext.SelectedItem.Text, ddlDayNext.SelectedItem.Text, ddlDayNext.SelectedItem.Text);

            string strSql =string .Format ( "select * from tblIssued where RcDate between '{0:yyyy-MM-dd}' and '{1:yyyy-MM-dd}' and CategoryId=2", d1,d2);

     

    or use Parameters:

            DateTime d1 = new DateTime(ddlYear.SelectedItem.Text, ddlDay.SelectedItem.Text, ddlMonth.SelectedItem.Text);
            DateTime d2 = new DateTime(ddlDayNext.SelectedItem.Text, ddlDayNext.SelectedItem.Text, ddlDayNext.SelectedItem.Text);

            string strSql ="select * from tblIssued where RcDate between @d1 and @d2 and CategoryId=2";

            MySqlCommand.Parameters.AddWithValue("d1", d1);
            MySqlCommand.Parameters.AddWithValue("d2", d2);

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 31, 2009 7:34 AM
  • User-690506545 posted

    ok now it is

    "select * from tblIssued where RcDate between '" + ddlMonth.SelectedItem.Text + "/" + ddlDay.SelectedItem.Text + "/" + ddlYear.SelectedItem.Text + "' and '" + ddlMonthNext.SelectedItem.Text + "/" + ddlDayNext.SelectedItem.Text + "/" + ddlYearNext.SelectedItem.Text + "' and CategoryId='2'";

    but now error is(as it was same)

     Data type mismatch in criteria expression.

     

    try this:

    "select * from tblIssued where RcDate between '" + ddlMonth.SelectedItem.Text + "/" + ddlDay.SelectedItem.Text + "/" + ddlYear.SelectedItem.Text + "' and '" + ddlMonthNext.SelectedItem.Text + "/" + ddlDayNext.SelectedItem.Text + "/" + ddlYearNext.SelectedItem.Text + "' and CategoryId=2";

    Removed the quotes around the  CategoryId='2'

    Why not just dump the sqlselect string out to the console and then fire up query analyser or something similar, it'll be far easier to see what's going wrong.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 31, 2009 7:36 AM

All replies

  • User191633014 posted

     You have one single quotation mark on the end of the statement, that is unnecessary.

    And if  catId is INT or BIGINT remove remove quotation marks around 2

     select * from tblIssued where RcDate between '" + ddlMonth.SelectedItem.Text + "/" + ddlDay.SelectedItem.Text + "/" + ddlYear.SelectedItem.Text + "' and '" + ddlMonthNext.SelectedItem.Text + "/" + ddlDayNext.SelectedItem.Text + "/" + ddlYearNext.SelectedItem.Text + " and catId=2";

    Tuesday, March 31, 2009 6:28 AM
  • User-957313 posted

    try this....

    string s1 =ddlMonth.SelectedItem.Text + "/" + ddlDay.SelectedItem.Text + "/" + ddlYear.SelectedItem.Text ;

    string s2=ddlMonthNext.SelectedItem.Text + "/" + ddlDayNext.SelectedItem.Text + "/" + ddlYearNext.SelectedItem.Text ;

    string query ="select * from tblIssued where RcDate between 's1' and 's2' and catId=2 ";

    Tuesday, March 31, 2009 6:35 AM
  • User160155926 posted

     sorry not the answer but aint you leaving yourself open to SQL injection by inserting values straight into your DB string without
    any kind of verification.

     just a note incase you dont know about sql injection:

     as a guide though http://www.codeproject.com/KB/database/SqlInjectionAttacks.aspx

    is a good start

     

    Tuesday, March 31, 2009 6:39 AM
  • User1052662409 posted

     Same error [:(]

    Tuesday, March 31, 2009 7:08 AM
  • User191633014 posted

    Post your code and the error message. 

    Tuesday, March 31, 2009 7:13 AM
  • User1052662409 posted

     string strSql = "select * from where RcDate between '" + ddlMonth.SelectedItem.Text + "/" + ddlDay.SelectedItem.Text + "/" + ddlYear.SelectedItem.Text + "' and '" + ddlMonthNext.SelectedItem.Text + "/" + ddlDayNext.SelectedItem.Text + "/" + ddlYearNext.SelectedItem.Text + "' and CategoryId='2'";

    error is Syntax error in FROM clause.

    Tuesday, March 31, 2009 7:16 AM
  • User-690506545 posted

    You're missing the whole of the FROM clause...

     select * from table where

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 31, 2009 7:20 AM
  • User191633014 posted

     You forgot to add your table name

     select * from ........  where RcDate between

    Tuesday, March 31, 2009 7:21 AM
  • User1052662409 posted

    ok now it is

    "select * from tblIssued where RcDate between '" + ddlMonth.SelectedItem.Text + "/" + ddlDay.SelectedItem.Text + "/" + ddlYear.SelectedItem.Text + "' and '" + ddlMonthNext.SelectedItem.Text + "/" + ddlDayNext.SelectedItem.Text + "/" + ddlYearNext.SelectedItem.Text + "' and CategoryId='2'";

    but now error is(as it was same)

     Data type mismatch in criteria expression.

    Tuesday, March 31, 2009 7:24 AM
  • User191633014 posted

    1. if  CategoryId is INT or BIGINT remove quotation marks around 2.

    2. Use next to create sql statement:

            DateTime d1 = new DateTime(ddlYear.SelectedItem.Text, ddlDay.SelectedItem.Text, ddlMonth.SelectedItem.Text);
            DateTime d2 = new DateTime(ddlDayNext.SelectedItem.Text, ddlDayNext.SelectedItem.Text, ddlDayNext.SelectedItem.Text);

            string strSql =string .Format ( "select * from tblIssued where RcDate between '{0:yyyy-MM-dd}' and '{1:yyyy-MM-dd}' and CategoryId=2", d1,d2);

     

    or use Parameters:

            DateTime d1 = new DateTime(ddlYear.SelectedItem.Text, ddlDay.SelectedItem.Text, ddlMonth.SelectedItem.Text);
            DateTime d2 = new DateTime(ddlDayNext.SelectedItem.Text, ddlDayNext.SelectedItem.Text, ddlDayNext.SelectedItem.Text);

            string strSql ="select * from tblIssued where RcDate between @d1 and @d2 and CategoryId=2";

            MySqlCommand.Parameters.AddWithValue("d1", d1);
            MySqlCommand.Parameters.AddWithValue("d2", d2);

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 31, 2009 7:34 AM
  • User-690506545 posted

    ok now it is

    "select * from tblIssued where RcDate between '" + ddlMonth.SelectedItem.Text + "/" + ddlDay.SelectedItem.Text + "/" + ddlYear.SelectedItem.Text + "' and '" + ddlMonthNext.SelectedItem.Text + "/" + ddlDayNext.SelectedItem.Text + "/" + ddlYearNext.SelectedItem.Text + "' and CategoryId='2'";

    but now error is(as it was same)

     Data type mismatch in criteria expression.

     

    try this:

    "select * from tblIssued where RcDate between '" + ddlMonth.SelectedItem.Text + "/" + ddlDay.SelectedItem.Text + "/" + ddlYear.SelectedItem.Text + "' and '" + ddlMonthNext.SelectedItem.Text + "/" + ddlDayNext.SelectedItem.Text + "/" + ddlYearNext.SelectedItem.Text + "' and CategoryId=2";

    Removed the quotes around the  CategoryId='2'

    Why not just dump the sqlselect string out to the console and then fire up query analyser or something similar, it'll be far easier to see what's going wrong.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 31, 2009 7:36 AM
  • User160155926 posted

     your problem is the date, if your column is a DATE filed then it will be in the format yy-mm-dd

    if you have a varchar field with 12/12/09 then you can not do a between search as this is not a date, it is a varchar

    http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

     

    will help out

    Tuesday, March 31, 2009 8:19 AM