CODEWITHSUNDEEP
c#mysqlasp.net
Lucian Tarna
Lucian Tarna

Reputation: 1827

querying a database after a Date in asp throws an incorrect syntax exception

I have been struggling to get the date from an asp database table and I managed with the following query:

SELECT p.nume, n.data_inceput,n.data_sfarsit,n.pret,n.id FROM hotel p ,rezervare n where p.ID = n.id_hotel and  (CONVERT(nvarchar(10), n.data_inceput, 101) LIKE '%date%'

This query is clearly working (god knows how much I searched until I stumbled across that convert) .

However when I try to execute this in the code I get:

Incorrect syntax near '%09/11/2014%'.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '%09/11/2014%'.

Source Error:


Line 18:             conn.Open();
Line 19:             SqlCommand cmd = new SqlCommand(query, conn);
Line 20:             SqlDataReader reader = cmd.ExecuteReader();
Line 21:             FillTable(reader);
Line 22:             conn.Close();

With line 20 being the problem.

I have the following code :

 protected void Page_Load(object sender, EventArgs e)
        {
            string query = GetQuery();
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
            conn.Open();
            SqlCommand cmd = new SqlCommand(query, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            FillTable(reader);
            conn.Close();
        }



private string GetQuery()
    {
        string query = string.Empty;
        if (!string.IsNullOrEmpty(TextBox1.Text))
        {
           // DateTime start;
           // DateTime.TryParse(TextBox1.Text, out start);
            query = string.Format("SELECT p.nume, n.data_inceput,n.data_sfarsit,n.pret,n.id FROM hotel p ,rezervare n where p.ID = n.id_hotel and  (CONVERT(nvarchar(10), n.data_inceput, 101) LIKE '%{0}%'", TextBox1.Text);
        }
        else
        {
            query = string.Format("SELECT p.nume, n.data_inceput,n.data_sfarsit,n.pret,n.id FROM hotel p ,rezervare n where p.ID = n.id_hotel");
        }
        return query;
    }

What could be the problem ? I don't really know where to look

Upvotes: 1

Views: 58

Answers (2)

dario
dario

Reputation: 5269

You better you ISO date: yyyyMMdd.

So, in code behind:

private string GetQuery()
{
    string query = string.Empty;
    if (!string.IsNullOrEmpty(TextBox1.Text))
    {
        DateTime dt = DateTime.MinValue;
        DateTime.TryParse(TextBox1.Text, out dt);

        query = string.Format("SELECT p.nume, n.data_inceput,n.data_sfarsit,n.pret,n.id FROM hotel p ,rezervare n where p.ID = n.id_hotel and n.data_inceput > '{0}' AND n.data_inceput < '{1}'", dt.AddDays(-1).ToString("yyyyMMdd"), dt.AddDays(1).ToString("yyyyMMdd"));
    }
    else
    {
        query = string.Format("SELECT p.nume, n.data_inceput,n.data_sfarsit,n.pret,n.id FROM hotel p ,rezervare n where p.ID = n.id_hotel");
    }
    return query;
}

Upvotes: 1

Christos
Christos

Reputation: 53958

You have missed a closing parenthesis at the end of your sql statement.

This 

(CONVERT(nvarchar(10), n.data_inceput, 101) LIKE '%{0}%'

should change to this:

(CONVERT(nvarchar(10), n.data_inceput, 101) LIKE '%{0}%)'

Upvotes: 2

Related Questions