Database Application 11/07/01 Test #2 = Ch. 13 Test #3 = Ch. 11 What do we do if the customer isn't a new customer? How do we deal with that? See handout # 12 Here's one way to deal with it. You need some way to find out if this customer is in the database. Use search form. You'll get a list back. Scroll that list, find what you're looking for, click on that customer, and it will populate the orderentry form. You do a lot of inserts. It's easy to find the customer if you have the customer ID. What do you do if you don't have it? We attached search button, and it displays this form. We need to come up with a set of possible candidates for this customer. We want the smallest set we can get. Think about what set, among these things, makes reasonable search fields. Phone # Some of them would be done with combination (last name + zip code). Think of how many of these fields do you want on there. Suppose I provide more than one. Will the code be and or or or what? Most of this code is on the sheet. When you get the right one, you ought to be able to grab it someway and put it on the order entry form. We can use this form to find people that we need. We've been trying to ignore nulls. One of the problems is that this thing is going to call a stored procedure, and each stored procedure has so many parameters. We have to begin to handle the fact that in a search, 1 may be filled in, and the others may be null. VB doesn't know about nulls – just blanks. I'm not going to bother VB with the null. The ADO stuff, however, (see p. 2 of handout) is going to have to send something to the database. I could handle the nulls in the ADO – or I could handle it in the db itself. A good place for us to handle it is in the ADO. Look at function on first page because DB doesn't know much about nulls. Variant data type. Variants can take on any data type you want. Function called "blank null". If the string is blank, it assigns it a null. If it's not blank, it leaves it alone. The function is returning a variant. Now, if you have nulls, nulls will be assigned to those fields. Now, if you go over to the ADO side (this is the same kind of ADO tha tyou have samples of). Notice that you have a parameter list for the VB side, plus the return code, plus the return message, and it's returning a record set. The only thing different in this procedure is that you'll notice, when I create the parameter list, in that item thing, instead of just assigning it to a text field, I … If I sent over customerID as a blank string, it's now a null. SQL will get a null variable. Now we have to get to the SQL side. The first thing I wrote is not good enough for the final thing. So, on the SQL side, I just looked at each parameter and said if it's not null, do this select statement, and go to done. It all works like that. The problem is that if you have two parameters set up, whichever one is the first one is going to get done, but the 2nd one is not. So, you don't have any way here to deal with multiple parameters that have values. So, the real way to deal with this is to look at which parameters are not null, dynamically build a select statement and execute it. (we did it in a couple of assignments last year). We have a sample of how to dynamically build an update statement in one of our handouts – the stored procedures handout. You may do or's or and's or whatever needs to be done. Execexecute, etc. That gets you from a search form, through an ADO procedure, through and SQL stored procedure to find things. You need to be able to handle customers that are in the db as well as customers that are new. Go through the grid using the text matrix, assign it to the text boxes on the other form. Problems, though. 1 – minor (when you bring up this search form, and you've selected somebody, and you start talking to your neighbor or somebody, it may take a while and something may change before you move it over. 2 – this is a useful form. If we write it this way, we can't use this form for anything else. So, it's probably a better idea to send back the customer number to the other form and let the form get the data. If you build it that way, you can come over to your other form over here (order entry form) – now has a find customer button. Now you have some way of telling on the SQL side if this is an old customer or a new customer. If there's no customerID, you know it's a new customer. But, if it returns a customer ID, you know that this is an old customer. You can ignore that portion of it or you can update it. In my case, I update it. You could go through a lot of trouble to see if any of the fields are dirty, but we're not going to do that. The other way to do that is to not use a search form. Type in the data, send it to the DB and look to see if it's there. But, the problem is that you might end up with more than one customer. This way, you ask the customer if they're new before. There are functions for dealing with nulls on the SQL side. Things like the trim and length functions exist in SQL also. Spaced out … If you unload a form, it's gone, and you can't get at its variables. If you hide it, you can. The same thing's going to happen in my customer maintenance stuff. I called that same procedure. It's basically the same code here as it is in the other form. Anybody in the DB might want to call "Get CustomerID". If you build something that a bunch of forms are going to use, the place to put it is in globals. We ought to find that "get customerID" in globals. The blank-to-null function. Right now, he's got it in his globals, too. That's not where it belongs. When we do Lab 6, we want to get rid of t We're going to have to yank a bunch of this stuff out. A # of things will belong in those components. Connect-to-database will belong in the component – not in the front end. Anything that actually has to have with getting data will come out of there and go in components. In Lab 6, how do we really separate this stuff? Right now we only have logical separation. But, the middle stuff is still in the client, but it's in classes, so it's somewhat separated. Our VB side won't need much at all. Our client won't have any ability to actually get anything out of the database. So, you have to keep all of that in mind as you're building. The last step is to pull that stuff out and package it. It's a step that you have to think about logically. It doesn't take a lot of coding. What goes on is the thinking about the divvying up of the application. What you don't have on this sheet, and what he needs to give us, is something on the client side. See handout #13. Database Application Management 11/07/01 Page 1 of 1