i have a text field with name "date" and i want to retrieve all he records from database when user write a specific date on that text field.......I am using MS access data base......Query below is not showing any record
String date=request.getParameter("OrderDate");
String query = "SELECT * FROM DoorStepRequests where OrderDate = #"+date+"# ";
has it occured to you that maybe that is so because there are no entries for that date?
before you query, print that date.
then, run the same query from within ms access, see if you get a result.
but why working with MS Access?
worse, if the date is entered into a format the database doesn't expect things are going to go wrong.
Start learning about PreparedStatement...
This routine will give you the correct string to add to your sql. You do need to convert the input text into a valid date variable butt this approach will work no matter what your regional setting is or in which format your user entered the date.
Shared Function cDateForJet(ByVal d As Date) As String
Return "#" & d.ToString("MM/dd/yyyy") & "#"
End Function
PerplexedB: how is that even supposed to look like Java code?
it's also utterly irrelevant as the very problem with the original code is that it's not safe against various input formats, which he doesn't cover.
Soooo sorry. Posted in the wrong group. Clearly something one should not do ;). Thanks for the -1. Realy appreciated, good use of this feature. Pfff.
I don't think the one who downvoted was only thinking about the use of a wrong language, rather the providing of a custom made solution, instead of pointing the OP to the right direction
I hate to disagree. On the contrary, my post suggests that the textfield get converted to a date type, which suggests some type of validation before it is included in a sql string. In addition, I seem to be the only one to provide the correct format for dates in access/jet, which is crucial, and frankly not very clearly documented.
So the only real probllem is to present code in visual basic in aJava thread. I guess that is blasfamatory. ;). Thanks and take care.
as I already pointed out: possibly not.
you could have said that he needed to convert his date, and help him/her to find out self how to do so, with a bit of help, without handing the code.
I explicitly suggested to convert his text date into a date type. so my post a) addressed the problem correctly and b) suggested the correct approach. So we're not supposed to show code at all anymore? This gets more ridiculous by the minute.
wrong language is irrelevant (translating between languages often isn't that hard), what is relevant is that it doesn't even attempt to solve the problem in whatever language.
What part of "the date must be in MM/dd/yyyy format when submitting it to an MS-Access (jet powered database)" don't you guys understand?
It is very unlikely that String date=request.getParameter("OrderDate"); would result in that format, so it is as likely that the date that was submitted to sql is not the one that was intended. Hence my suggestion.
So again rather than being irrelevant my answer was correct and suggested the right way to solve it, yet contrary to the answers of other "experts" who did not give the right answer and the relevance of which is really none of my business, my (correct and relevant) answer was marked down.
And even after I explained and brought all this to the attention of the "experts" they still persist in their erroneous attitudes.
Something is not right here
Please get back on topic. Downvotes are something you get, often enough from people not involved in the discussion.
Formatting the date for insertion is correct, but the format depends. I don't have much experience with Access, but with my current project we are inserting #yyyy-MM-dd#, so I guess it's a regional setting.
Well that's when you use Access directly or in VBA, and btw this is not regional dependent. If you connect via OLEDB which is how you would connect in VB.NET (which was myt assumption, not sure thre is another way), the format is #MM/dd/yyyy#. I know it's odd, but that's why we are here right?
We use Microsoft.Jet.OLEDB.4.0 (C#).
Interesting, is it not, because I use Microsoft.ACE.OLEDB.12.0 and on two different settings, one in regional dutch and one in regional english uk, and to the best of my knowledge, the required date format is both "MM/dd/yyyyy". Ah well.
try this
SELECT tbl.CrDate
FROM tbl
WHERE datecolumn=#"+date+"# ";
Daemon, I hate to point out the obvious, but either you are missing some "'s, or your code snippet is wrong.
also: did you check the original post? that is exactly what the OP was trying but didn't work.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.