'Date syntax error in query expression'
-
I getting this error and cant find reason why ? System.Data.OleDb.OleDbException: 'Date syntax error in query expression' datumdos BETWEEN # 9.8.2019. # AND # 18.8.2019. # '.' sql1 = "SELECT * FROM tblpromet WHERE datumdos BETWEEN # 1.1.2019.ToShortDateString() # AND # 10.5.2019.ToShortDateString() #" da1 = New OleDb.OleDbDataAdapter(sql1, con) da1.Fill(ds, "PrometBase") ' This Table name
-
You are converting the dates to strings, so the comparison will not work as it requires Date types.
If I change string to : sql1 = "SELECT * FROM tblpromet WHERE datumdos BETWEEN # 1.1.2019. # AND # 10.5.2019. #" I have same message, and this are not a string, any suggestion ?
-
If I change string to : sql1 = "SELECT * FROM tblpromet WHERE datumdos BETWEEN # 1.1.2019. # AND # 10.5.2019. #" I have same message, and this are not a string, any suggestion ?
You should use [ISO format](https://en.wikipedia.org/wiki/ISO\_8601) of date:
sql1 = "SELECT * FROM tblpromet WHERE datumdos BETWEEN #2019-01-01# AND #2019-05-10#"
-
You should use [ISO format](https://en.wikipedia.org/wiki/ISO\_8601) of date:
sql1 = "SELECT * FROM tblpromet WHERE datumdos BETWEEN #2019-01-01# AND #2019-05-10#"
Thanks that is working now I need to change this date with datetimepicker in which is date visible in Eu format dd.MM.yyyy
-
I getting this error and cant find reason why ? System.Data.OleDb.OleDbException: 'Date syntax error in query expression' datumdos BETWEEN # 9.8.2019. # AND # 18.8.2019. # '.' sql1 = "SELECT * FROM tblpromet WHERE datumdos BETWEEN # 1.1.2019.ToShortDateString() # AND # 10.5.2019.ToShortDateString() #" da1 = New OleDb.OleDbDataAdapter(sql1, con) da1.Fill(ds, "PrometBase") ' This Table name
Don't do it like that! In this particular instance, you might be OK, since the values are dates. But concatenating values into your query is a bad habit which will lead to SQL Injection vulnerabilities in your code. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query. Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^] How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^] Query Parameterization Cheat Sheet | OWASP[^]
Const query As String = "SELECT * FROM tblpromet WHERE datumdos BETWEEN ? AND ?"
da1 = New OleDb.OleDbDataAdapter(query, con)
da1.SelectCommand.Parameters.AddWithValue("min", minDatePicker.Value)
da1.SelectCommand.Parameters.AddWithValue("max", maxDatePicker.Value)
da1.Fill(ds, "PrometBase")
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Thanks that is working now I need to change this date with datetimepicker in which is date visible in Eu format dd.MM.yyyy
No, you don't! All you have to do is to pass proper date from DateTimePicker, see:
sql1 = String.Format("SELECT * FROM tblpromet WHERE datumdos BETWEEN #{0}# AND #{1}#", DateTimePicker1.Value, DateTimePicker2.Value)
Date is a date. Its format is for string representation only!
-
No, you don't! All you have to do is to pass proper date from DateTimePicker, see:
sql1 = String.Format("SELECT * FROM tblpromet WHERE datumdos BETWEEN #{0}# AND #{1}#", DateTimePicker1.Value, DateTimePicker2.Value)
Date is a date. Its format is for string representation only!
That's a SQL query you've just injected user-controlled values into. It might be OK with dates, but it's still a bad habit and should be discouraged.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Don't do it like that! In this particular instance, you might be OK, since the values are dates. But concatenating values into your query is a bad habit which will lead to SQL Injection vulnerabilities in your code. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query. Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^] How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^] Query Parameterization Cheat Sheet | OWASP[^]
Const query As String = "SELECT * FROM tblpromet WHERE datumdos BETWEEN ? AND ?"
da1 = New OleDb.OleDbDataAdapter(query, con)
da1.SelectCommand.Parameters.AddWithValue("min", minDatePicker.Value)
da1.SelectCommand.Parameters.AddWithValue("max", maxDatePicker.Value)
da1.Fill(ds, "PrometBase")
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
That's it thanks a lot
-
Don't do it like that! In this particular instance, you might be OK, since the values are dates. But concatenating values into your query is a bad habit which will lead to SQL Injection vulnerabilities in your code. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query. Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^] How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^] Query Parameterization Cheat Sheet | OWASP[^]
Const query As String = "SELECT * FROM tblpromet WHERE datumdos BETWEEN ? AND ?"
da1 = New OleDb.OleDbDataAdapter(query, con)
da1.SelectCommand.Parameters.AddWithValue("min", minDatePicker.Value)
da1.SelectCommand.Parameters.AddWithValue("max", maxDatePicker.Value)
da1.Fill(ds, "PrometBase")
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
This is perfect only how to add more filters like : FROM tblpromet WHERE kod like '" & txttrkod.Text & "%'and obrada like '" & txtobrada & "%' order by kod"
-
This is perfect only how to add more filters like : FROM tblpromet WHERE kod like '" & txttrkod.Text & "%'and obrada like '" & txtobrada & "%' order by kod"
In exactly the same way:
Const query As String = "SELECT * FROM tblpromet WHERE kod like ? AND obrada like ? ORDER BY kod"
da1 = New OleDb.OleDbDataAdapter(query, con)
da1.SelectCommand.Parameters.AddWithValue("kod", txttrkod.Text & "%")
da1.SelectCommand.Parameters.AddWithValue("obrada", txtobrada.Text & "%")
da1.Fill(ds, "PrometBase")You just have to make sure to add the parameters in the same order that the
?placeholders appear in the query, since you can't use named parameters.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
That's a SQL query you've just injected user-controlled values into. It might be OK with dates, but it's still a bad habit and should be discouraged.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
haha your right like Homer :laugh: :laugh: :laugh:
-
I getting this error and cant find reason why ? System.Data.OleDb.OleDbException: 'Date syntax error in query expression' datumdos BETWEEN # 9.8.2019. # AND # 18.8.2019. # '.' sql1 = "SELECT * FROM tblpromet WHERE datumdos BETWEEN # 1.1.2019.ToShortDateString() # AND # 10.5.2019.ToShortDateString() #" da1 = New OleDb.OleDbDataAdapter(sql1, con) da1.Fill(ds, "PrometBase") ' This Table name
The error is with in the query string. Between keyword does not work well with string datatype.
