Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2003
    Posts
    1

    Unanswered: Incorrect syntax near '#'

    In VB, I can write an SQL query that goes like this:

    “SELECT * FROM Staff WHERE HiredDate BETWEEN #10/23/2002# AND #10/29/2002#”

    and it works perfectly. However, if I run that same query from an ASP page, I get the following error message:

    Microsoft OLE DB Provider for SQL Server (0x80040E14)
    Line 1: Incorrect syntax near '#'.

    If I type

    “SELECT * FROM Staff WHERE Name = ‘John’”

    it works perfectly. The error is obviously related to the date literal. Do you know how I could create an SQL expression that will return records between two specified dates?

  2. #2
    Join Date
    Feb 2003
    Posts
    18
    If you are using sql server database then try removing # sign.

    SELECT * FROM Staff WHERE HiredDate BETWEEN '10/23/2002' AND '10/29/2002'

  3. #3
    Join Date
    Feb 2003
    Location
    Other side of the ocean
    Posts
    6

    escape syntax

    or use the escape syntax which will work against any DB:

    “SELECT * FROM Staff WHERE {d'2002-10-23'} < HiredDate < {d'2002-10-29'}”


  4. #4
    Join Date
    Jul 2010
    Posts
    4
    hello ,,, i am totally new to sqlsever and i am suffering from z same problem
    incorrect syntax near #
    i removed it and enclosed may between date var between single quotes but still not working
    any help please
    it drives me crazy for more than a month now

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you Response.Write your full query string please?
    George
    Home | Blog

  6. #6
    Join Date
    Jul 2010
    Posts
    4
    okay ...thanx for rplay
    re8.Open("select distinct(Dist_Win_Name) from Distributers ,Daily_transaction where Distributers.Dist_Win_Name not in (select Dist_Name from Daily_transaction where Daily_transaction.Agent_No ='" + ComboBox1.SelectedItem + "'and Daily_sts='" + str1 + "' and Tran_Date between " + DateTimePicker1.Value + " and " + DateTimePicker1.Value + " and Distributers.Agent_No=Daily_transaction.Agent_No and Distributers.Agent_Name= Daily_transaction.Agent_Name and Daily_transaction.Agent_Name ='" + ComboBox1.SelectedItem + "'and Daily_sts='" + str1 + "'and Tran_Date between " + DateTimePicker1.Value + " and " + DateTimePicker1.Value + "", DB, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)
    For i = 0 To re8.RecordCount - 1
    dataa = re8("Dist_Win_Name").Value
    RE18.Open("select * from Daily_transaction Where Agent_No = '" + ComboBox1.SelectedItem + "' and Agent_Name = '" + TextBox2.Text + "' and Edition_serial='" + TextBox4.Text + "' and Tran_Date=#" + DateTimePicker1.Value + "#", DB, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)
    RE18.AddNew()
    RE18("Agent_No").Value = ComboBox1.SelectedItem
    RE18("Agent_Name").Value = TextBox2.Text
    RE18("Dist_Win_No").Value = TextBox3.Text
    RE18("Dist_Name").Value = dataa
    RE18("Dist_Add").Value = ""
    RE18("Tran_Date").Value = DateTimePicker1.Value
    RE18("Rec_Copies").Value = 0
    RE18("Rec_Sold").Value = 0
    RE18("Rec_Returend").Value = 0
    RE18("Dist_percent").Value = 0
    RE18("Note").Value = 0
    RE18("Daily_sts").Value = "P"
    RE18("Edition_serial").Value = TextBox4.Text
    RE18("Quantity").Value = 0
    RE18("Estmait").Value = 0
    RE18.Update()
    're2.MoveLast()
    'DB.Close()
    RE18.Close()
    Next
    End If
    End Sub

    and error messege apears in the select statment

  7. #7
    Join Date
    Jul 2010
    Posts
    4
    will it seems you get nothing to understand
    my problem is here
    RE18.Open("select * from Daily_transaction Where Agent_No = '" + ComboBox1.SelectedItem + "' and Agent_Name = '" + TextBox2.Text + "' and Edition_serial='" + TextBox4.Text + "' and Tran_Date=#" + DateTimePicker1.Value + "#")

    where i pass dalte value from my application

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I'm guessing you're used to Access, given the hashes. Surround your datetime values in single quotes as opposed to #'s. You've got one date surrounded by #'s and several more that aren't surrounded by ANYTHING. Look close.


    Also, sanitize your inputs using either stored procedures or parameterized/prepared statements. If you put this page up anywhere on the actual internet, I could find it and completely destroy your database, among other things.

    I'm not kidding. Google around for "sql injection".

    Also, gvee wanted to know what the final string looks like AFTER it has been constructed. Something along these lines:

    Response.Write("select distinct(Dist_Win_Name) from Distributers ,Daily_transaction where Distributers.Dist_Win_Name not in (select Dist_Name from Daily_transaction where Daily_transaction.Agent_No ='" + ComboBox1.SelectedItem + "'and Daily_sts='" + str1 + "' and Tran_Date between " + DateTimePicker1.Value + " and " + DateTimePicker1.Value + " and Distributers.Agent_No=Daily_transaction.Agent_No and Distributers.Agent_Name= Daily_transaction.Agent_Name and Daily_transaction.Agent_Name ='" + ComboBox1.SelectedItem + "'and Daily_sts='" + str1 + "'and Tran_Date between " + DateTimePicker1.Value + " and " + DateTimePicker1.Value + "")
    Last edited by Teddy; 07-27-10 at 11:36.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Jul 2010
    Posts
    4

    Red face

    well as i told you am prand new for using sql
    okay okay ..i just want ti select datetime value from my database as selected by end user(my application)
    'select * from table where date='"+datetimepicker.value +"''
    and datetimepicker.value holds the
    value '01'07'2010 12:23:11'
    and my date column in db is datetime type

    *** actually i tried this
    where date =Convert( datetime ,'" + (DateTimePicker1.Value) + "', 102)
    but it returns incorrect syntax near '01'07'2010'

    what can i do ?? please help mee

  10. #10
    Join Date
    Aug 2010
    Posts
    1
    If you are using sql server database then try removing # sign.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •