searching with date value

[ninjaimp]

now i am doing something that i thought would be real straight forward but for some reason is not.

Im trying to lookup records in a DB based on todays date. ow i know the db contains records for these dates but nothing ever gets returned. My code is below and i wondered if anyone had any ideas?

Dim sql As String
        Dim Command As OleDbCommand
        Dim con As System.Data.OleDb.OleDbConnection
        Dim Searchds As New DataSet
        Dim Searchda As OleDb.OleDbDataAdapter
        Dim TodaysDate As Date

        Dim RecordCount As Integer

        con = New OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\cdr\ReferenceData.mdb")
        con.Open()

        TodaysDate = Format(Date.Today, "dd/MM/yyyy")

        sql = "SELECT * FROM tblStay WHERE tblStay.ArriveDate = #" & TodaysDate & "#"
        Command = New OleDbCommand(sql, con)
        Command.ExecuteNonQuery()

        Searchda = New OleDb.OleDbDataAdapter(sql, con)
        Searchda.Fill(Searchds)

        RecordCount = Searchds.Tables(0).Rows.Count

        If RecordCount > 0 Then
            For i = 0 To RecordCount - 1
                lst1.Items.Add(Searchds.Tables(0).Rows(i).Item(1) & " : " & Searchds.Tables(0).Rows(i).Item(3))
            Next
        End If

        con.Close()

 

[cjard]

ninjaimp: I'm really at a loss as to how you can "get it wrong". Youre told what to do in a very precise manner; Do it. (If I gave you explicit and verified instructions for making a cup of coffee and you returned with a cup of cold water, I would be equally at a loss as to explain how you got it wrong other than choosing not to follow the instructions)
I think youre trying to think about it too much. When Microsoft say "write the query like this" - do as youre told! - dont try and start imagining ways to replace that ? with the contents of a text box, because a) you'll get it wrong because you dont know what youre doing and b) you'll never see that the IDE does it for you, because by not doing as youre told youre breaking things in such a way that the IDE cannot help you

Please.. Do as youre told

my question is that on the sql that they suggest, what does the ? relate to? how does it know what control to take the search value from?

it doesnt; it makes a new one and takes the term from that, as you will see when you do as youre told. When you then see how it does it, you will know how to take the code it has written and adapt it for your own needs. Until that time, you will never know what code you need. Basically, the IDE is trying to write an example for you, youre not letting it, and then youre coming back here to ask what to do next. I'm sending you back to the tutorial to follow it because I know that if you do, you'll be given the example. Shoot first, ask some questions later

Hikidar, thanks for trying to be helpful, but please read the PQ link in my signature before you decide to teach any more newbies SQL.. (And that's aside from the fact that what you purport won't actually work in this case)
I would also caution you not to try and educate people who use a .net version above your own unless you know for sure the advice youre giving is still current and accurate. When it comes to data access in the style ninjaimp is performing, i can promise you that your .net 1.1 methodologies are out of date

 

[ninjaimp]

while i appreciate your help you seem more bothered in patronising and belittling me 'Do as your told'! Im not at school anymore! and i know if i was i would probably been able to resolve this.

The fact is that i was unsure of what i was doing, i apporach the forums for assitance but instead just keep getting the errors i have made pointed out to me with no real assistance, bar some other people!

I know the mechanics, the only other reason i was amending the original sql was to try and find a solution, i ran the code as indicated in the walkthrough and it didnt work! I added the ? as i was connecting to an Access DB and it didnt work.

I also read through the PQ on your signature and it mentions paramatilzed sql and also points out that some reference is made to the value for the paramatilzed queries. My question is where is this done.

I appreciate your an experienced programmer and no doubt get frustrated by trivial problems (in your eyes) but to some of us it isnt always as clear to some of the more learned people on here.

I appreciate your help, but please remember i am only trying to learn and dont add random questions just to frustrate people

regards

 

[cjard]

I dont know what else to tell you. Any advice I can give would only be an effective re-statement of the tutorial. Put the ? mark in just as youre told to do. Dont try to substitute anything for the question mark, because MS do not tell you to perform a substitution. Wehn MS want you to perform a substitution, they will say so e.g. "type d:\autorun where d: is the letter or your cd rom drive"
Watch what the IDE does when you follow the tutorial. If the tutorial doesnt work then the two possibilities are, in descending order of likelihood:
1) There is an error in the SQL syntax you created; when learning new things, and following examples I recommend that you actually do the examples on the databases in the example. Dont try to assemble a flat-pack bed using the instructions for the bookshelves
2) There is an error in the tutorial

I'll help anyone who listens, and I'll involve anyone in their own learning while they continue to demonstrate that they are following instruction and I love it; that's why I offer it for free. On the flip side, I do get somewhat irked when I write or reference a list of instructions that arent followed and all I get is an ensuing question that implies that my advice wasnt heeded.

I've used those tutorials myself; i know they are correct, and that they teach what you need to know. The only conclusion that I draw from you not finding satisfaction in them is that you simply didnt follow what they advocated. As the saying goes, you can take a donkey to water but you cannot make it drink.

just keep getting the errors i have made pointed out to me

Would you make this same complaint if I observed you had made yourself a cup of cold water instead of a coffee? You wanted a coffee! I gave you the instructions, and you made a cold water because you didnt follow them!
Come on; quit it with the "it's not my fault, youre horrible and youre not helping me" line - if you mean I'm not doing it for you, then darn straight I'm not because you wont learn a thing that way!
So you actually want a hot tea, and i'm providing you with the instructions for hot coffee; is it still my fault that you ended up with a drink that isnt even hot?

no real assistance, bar some other people!

Much as you dont fully have an appreciation of it now, in a few years, maybe, you will come to know just how wrong of a garden path those others have led you down. I dont have time to hold every newbies' hand who comes through here, while they learn all about data access, and I genuinely feel that pointing them to a set of incredibly comprehensive, accurate and clear tutorials written by the manufacturer of their chosen programming language, FAR exceeds [in terms of quality and depth] any advice that someone on a forum (including myself if I were to teach you what those tuts do) would be willing to give up for free

i ran the code as indicated in the walkthrough and it didnt work!

Well, you might not be using the Northwind database in your app.. so we get back to the point about assembling the shelves using the bed instructions or whatever; it's neither my nor microsoft's fault if that is the case, but I can promise you 100% that the advice given in Microsoft's help does work with Microsoft's programming language.

I added the ? as i was connecting to an Access DB and it didnt work.

ps; do please make your problem reports more detailed than "it didnt work"

My question is where is this done.

I answered that for you in my previous post. Do you make a point of only reading the first 2 lines of a document (which explains the trouble youre having with the tutorials) or were you so enthused to write me a response about how patronising and belittling I am that you overlooked that part?

The IDE will do the linkup for you, follow the instructions, see the results, understand THAT it works and if youre still curious as to how, come ask and i'll tell you where to look to educate yourself about all the code the IDE has written for you. The reason i'm not telling you right now is that there exists HUNDREDS of lines of the stuff and it wont help you. In this life, every day you will use more complicated things that you will never know the details of than you can truly appreciate.. Starting with how the alarm clock works that woke you up this morning. SOme things, you dont need to know right now, and some things (when you see THAT they work) you never need to know how they work

I appreciate your an experienced programmer and no doubt get frustrated by trivial problems

I never get frustrated by a trivial question here, I just point in the direction of a well trodden path that many people have been down while learning. The ones I get frustrated by are the ones who come back and say "Well, i didnt listen to a thing you said, it doesnt work and I want some help with it"

If you cant see that that's what youre doing, then I dont really know what else to say. There ARE a set of video tutorials covering similar material to these links, but I dont have them, link them or use them. I believe jmcilhinney may have a link to them

 

[ninjaimp]

humble pie time!

For whatever reason, the first couple of time i tried it it did not work. But this time, and answering my question, it created a text box in the menu which the user could input the search term. This was never appearing hence why i was asking what it was referencing the ? against - Admittidly, sometimes i can be guilty of skipping certain elements in search of finishing the task sooner - but it seldum works that way.

I appreciate your help but sometimes not the way you convey it - it could be a little more subtle!

anyway, i apologise for wasting your time but it appears that some headway is being made by me, I will go through the tutorials again while easting a nice big slice of humble pie

regards

 

[cjard]

I appreciate your help but sometimes not the way you convey it - it could be a little more subtle!

Noted

Now, for a fillin on what happens behind the scenes:

The IDE watches you make a query with a parameter character
It looks in the query and Identifies what the other side of the parameter is, in terms of type:

WHERE name = ?

Name is a string, so it makes a string parameter and adds it to the collection of parameters associated with a particular command.
If you want to see this, click the show-all-files button on the Solution Explorer and look at MyDataSet.Designer.vb (replace bold with your dataset name) or just use notepad in the solution directory on disk
Read the method called InitCommands - note the huge amount of effort the IDE puts in and lots of data access code written for you, 100% correct.

Now when you come out and just look at the code int eh form, it's so simple eh? You can do away witht he auto made text box and use your own on the same pattern. If for any reason it didnt generate the auto made textbox, well it probably generated the method ont he tableadapter for you to call yourself so just code up something like myTableAdapter.FillByWhateverYouCalledYourFillMethod(myDataSetObject, myParameter1, myparameter2...).

Side note if you want to see it all in action, you can go into
TOOLS>>OPTIONS>>DEBUGGING>>GENERAL and untick "Just My Code"
Now the debugger will step into all the code the IDE wrote for you, but beware; there's a lot of it and its easy to get lost in it.. Only do it if youre really curious

This wasnt the video I was referring to but it might have some of the content of the tutorials, if you wanna see it in action http://msdn.microsoft.com/en-us/data/cc138242.aspx

 

[ninjaimp]

cjard, many thanks

it was where the IDE was doing all the 'Behind the scenes' that i just couldnt get my head round as my first attempts just weren't creating the search box for me.

i will go through all the tutorials but at the same time still doing battle with different elements of a project

many thanks for your help

 

[cjard]

I do recall once, when following those tuts, I got in such a mess with one (to be fair, i hadnt followed the instructions) that I jsut deletd my app and started again.. Mystically all worked fine second time and since

Leave the behind the scenes stuff there for the most part