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?

If you are using sql server database then try removing # sign.

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

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

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

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

Can you Response.Write your full query string please?

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

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

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 + "")

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

If you are using sql server database then try removing # sign.