I need to verify the user and PW against a access database.

Database name- lightspw
Table name-    Lightspw
data elements - User   &  PW

Thank you

---

Clarification of Question by amy123456-ga on 01 Apr 2004 14:02 PST

Hi Mathtalk.  By the way the previous update worked really well (Err) Thank you.
I am a little slow sometimes, but I see your point now, and I think!
No.  I know your approach, as always is the correct one. Thank you for
your patience?s and your detail explanation.
To answer some of your questions.  I am using access 2003. The
database is being created for this project and it can be placed on the
network.

Thank you very much as always, mathtalk

---

Clarification of Question by amy123456-ga on 04 Apr 2004 14:36 PDT

I MathTalk.
Do you think i will be able to try the password verification today?

Thank you MathTalk

---

Request for Question Clarification by mathtalk-ga on 04 Apr 2004 22:27 PDT

Hi, Amy:

Sorry, I didn't quite get there today.  Here's my progress...

Created an Access database (in Access 2002, aka the Office XP version,
but actually using the Access 2000 format) named "lightspw.mdb".

Created a table called "Lightspw" more or less according to your
specs.  There's a field called "User" (text, 50 character limit,
required, primary key), a field called "PW" (also text, also 50
character limit, also required), and I threw in a field called
"activity" (long integer, required, defaults to 0) which I thought
might be useful for seeing which users are really online.  The idea is
to increment the activity field every time the password is validated. 
Obviously more could be done with that idea, but it's a start.

Now in .Net there's a namespace System.Data.OleDb that has the classes we need:

OleDbConnection : represents a database connected through an OleDB provider

OleDbCommand : in conjunction with the above, prepares a command to run

OleDbDataReader : handles the return of data rows from a data source

For password verification I think we'll avoid using the third class
for now.  In the OleDbCommand class there is a method ExecuteNonQuery,
which returns the number of rows affected by a SQL command.  So my
idea is to verify a password with an update query that matches the
User and PW fields, incrementing the activity count by 1.  The update
query will either affect 1 row (in which case the user/password is
validated) or no row (in which case the user/password is not
validated).

Simple stuff, but my brain is growing tired.  I'll pick this up in the
morning and try to get you something to test relatively early.

-- mathtalk-ga

---

Clarification of Question by amy123456-ga on 05 Apr 2004 06:02 PDT

OK Thank you very much. get some sleep

---

Request for Question Clarification by mathtalk-ga on 06 Apr 2004 07:37 PDT

Hi, Amy:

I've not forgotten you, I've just need to come into work early each
day as the morning processes have been running slowly and a big push
is underway to get these working better.

I should be able to finish the password verification this evening...
sorry for the delay.

regards, mathtalk-ga

---

Clarification of Question by amy123456-ga on 06 Apr 2004 07:40 PDT

Hi MathTalk. I understand.

Thank you

---

Clarification of Question by amy123456-ga on 08 Apr 2004 03:17 PDT

Hi MathTalk.  Are you OK?

---

Request for Question Clarification by mathtalk-ga on 08 Apr 2004 06:25 PDT

Apart from the usual "burning the candle at both ends" stuff, I cannot complain.

I've decided to scale back slightly the password input design to get
it done.  Popping up a separate dialog was requiring too much extra
code, so I'm putting a (password masked) text entry box on the same
form with the rest of the user interface.

regards, mathtalk

---

Clarification of Question by amy123456-ga on 08 Apr 2004 10:01 PDT

Hi MathTalk.

When do you think you can have something for me to test?  Please

Thank you

---

Request for Question Clarification by mathtalk-ga on 08 Apr 2004 12:53 PDT

Hi, Amy:

I've torn myself away from work relatively early (it's still light
outside!), and I will have something for you today.

regards, mathtalk-ga

---

Clarification of Question by amy123456-ga on 08 Apr 2004 18:02 PDT

Thank you

---

Request for Question Clarification by mathtalk-ga on 08 Apr 2004 19:19 PDT

I admit it's no longer light outside, but I'm close to finishing!

-- mathtalk

---

Request for Question Clarification by mathtalk-ga on 08 Apr 2004 21:43 PDT

Hi, Amy:

I think it's Done!  The revised zip-archive has been uploaded to the
usual place (as switchbox.zip) with the combined .Net
solution/projects.

These last changes are entirely to OrderFulfillment.  I placed the
Access database lightspw.mdb in the same directory as the executable,
for lack of a better way to both send it to you and make it run when
you unzip it all.  That is, the Access database file itself is in the
OrderFulfillment/bin/Debug folder.

I use relative addressing for the "connection string" that opens this
database, so as long as you moved them both together, it should
continue to work.  Recall that I created a table called Lightspw, with
text columns User and PW per your instructions, and threw in a long
integer column called activity.  When the user ID and password are
validated (both case sensitive, both matching against the same row in
the Lightspw table), the activity column is incremented by 1.  So it
keeps a running count of the number of successful order fulfillments
by user.

For convenience I created a desktop icon as before, putting the
C:\lightsr commandline argument into the same textbox as the
executable (but following it and outside the quotation marks).  I
think you set this up before, and no changes should be required to the
shorcut at this time.

Give it a try and let me know if there are some questions.  The
functionality is pretty much as before except for the password
validation.  In the Access database I created two entries, one for you
and one for me, and you'll see the activity count on my entry has been
bumped up by a couple of test runs.  There's actually a Cancel option
on the final dialog box that pops up, so it's actually possible (if
Cancel button is chosen) for the activity count to run ahead of the
number of orders cleared, but presumably we'd do reporting off the
logfile rather than from the password database, so I don't consider it
a major inconsistency.

But let me know what you think!

regards, mathtalk-ga

---

Clarification of Question by amy123456-ga on 09 Apr 2004 14:01 PDT

Thank you very much.

It works very nicely.

1-Question. Where is the log file that it creates that keeps track of
the users, time and date.

2-Question. If i want to just start the ide so that the whole
application is not visable, how would i do that.

Thank you so much MathTalk, have a great weekend and happy Easter.

---

Request for Question Clarification by mathtalk-ga on 09 Apr 2004 17:42 PDT

Hi, Amy:

The only logfile we are creating at the current time is the one that
textextract (or "switchbox" as I informally have called it) writes.

This application, in the current design, serializes the messages from
the "printer" containing doctor's orders and from one or more Order
Fulfillment applications (order's fulfilled).  In this design there is
only one World/lampset and the existing logfile can then reflect the
gatekeeping done for it by the textextract (console) application.

If by IDE you mean Visual Studio .Net 2003, then by default launching
the VS.Net development environment/editor from the Windows Program
menu will not open any projects (although in the default "Start Page"
it presents a list of recent "solutions" that can be opened by
clicking the link).  The Start Page in VS.Net is extremely
customizable.  VS.Net is basically using a built-in Web browser to
present a variety of content (code, WYSIWG form/control editing, help
topic, Wizards, etc.), and the Start Page is one of the more malleable
pieces of the setup.  Perhaps you are accustomed to double-clicking on
the "solution" configuration file, which causes VS.Net to open that
content?

regards, mathtalk-ga

---

Clarification of Question by amy123456-ga on 10 Apr 2004 17:02 PDT

Hi Mathtalk.  In VB 6 you can create an exe to lunch the whole
application, I was wondering if the same could be done in visual
studio.net. The problem will be that i do not want anyone exposed to
the code of the application at any time.

PS- Is there anyway that a log file can be created to reflect when a
user has turned the lights out, user/time/date stamp


Thank you

---

Request for Question Clarification by mathtalk-ga on 11 Apr 2004 12:24 PDT

Hi, Amy:

Please give me a little more detail about what you are looking for in these points.

For this "solution" we already developed three separate executables:

textextract1.exe (a console application, running in a loop)
 - reads input files *.doc and *.prn
 - controls light settings through World 1 and persists them in XML
 - writes output to a logfile from both kinds of input files

OrderFulfillment.exe (a Windows application, run at user demand)
 - accepts user input regarding a room number that's taken care of
 - validates User ID and Password from database
 - creates the *.prn file accordingly, for textextract.exe to read

stopworld.exe (another console application, runs and exits)
 - sole purpose is to create file worldstop.the 

Each executable is created at the time the entire solution is rebuilt
(or built, if there have been changes).  The executables created as
the project is currently configured are "debug" (meaning that they
contain sufficient information for the VS debugger to be able to halt
the code on particular lines and track execution on a line-by-line
basis).

Each executable is found in a subfolder of the textextract directory
named after the "project" or the particular program.  The subfolder is
bin\Debug, e.g.:

textextract\stopworld\bin\Debug

Starting the "switchbox" application (textextract1.exe) and stopping
it (by running the stopworld.exe application) may be considered an
Adminstrative function.  Whether or not a "user" is allowed to do
these functions is of course a policy issue.  We have discussed the
creation of desktop shortcuts; the same configuration of a desktop
shortcut would apply to menu items on the:

Start > Programs 

menu.  The important thing to keep in mind is the consistent use of a
single folder (possibly on the network) as an argument to all three
programs, so that the traffic of files through that common directory
is agreed to.

You might choose to implement the OrderFulfillment.exe program (with a
desktop shortcut) on three machines which are available to the
hospital staff.  The point in time at which a room's orders are
fulfilled then written in the *.prn files and copy from there to the
(combined) logfile, including time and User ID,

One way to limit access to the textextrac1.exe and stopworld.exe
programs would be to locate them on a separate PC.  The main point is
that such a machine will have the World 1 interface attached to it,
and it will need to be able to "connect" to the same network directory
as the OrderFulfillment.exe program(s).

I can't see how it would work to have a single method to launch all
three applications at once.  The one program that a user needs to
launch (and to stop, since it may retain that user's credentials as
long as it runs) is the OrderFulfillment.exe one.  Let me know if you
need further help installing this application on a user's machine, in
configuring it, or in providing instructions to the hospital staff on
how to run and exit it.

I believe the logfile (managed by textextract1.exe) already records
the user information and timestamp when a light is to be turned off. 
This logfile is currently set to be C:\txtxtrct.log in the root of the
machine where the textextract1.exe program is running.

After processing *.prn files it will contain lines like this:

4/12/2004 12:41:30 PM: Room number 08 cleared by mathtalk

regards, mathtalk-ga

---

Clarification of Question by amy123456-ga on 11 Apr 2004 18:26 PDT

Hi MathTalk.

Thank you very much, your explanation helped.

---

Request for Question Clarification by mathtalk-ga on 17 Apr 2004 12:43 PDT

Hi, Amy:

I've left this Question open hoping to help wrap up loose ends as you
put the software into "beta testing".  We've incorporated some
signficant functionality in the user/password verification procedure
as well as tightening the text extraction (parsing) logic.  Have you
gotten feedback from the users yet that you'd like to address before
we set this one to Answered?

regards, mathtalk-ga

---

Clarification of Question by amy123456-ga on 17 Apr 2004 16:59 PDT

Hi MathTalk.  Thank you for standing by. i tested it in the
development environment (SCM),The application that feeds the lightsr
folder with the ORDER SESSION. All phases of the process work in dev.
On monday morning i am moving
the process for testing in production. The PW and User name work well.
I will let you know monday or tuesday how things go. If everything
works with out any errors then it will be implamented the following
week on the unit.

Thank you so much for your efforts and help.

---

Clarification of Question by amy123456-ga on 22 Apr 2004 03:32 PDT

Good morning Mathtalk. Good news, it is working well. 
I have two small request. On the txtxtrct.log on the order side, can
we have a time stamp and date of the order just like the shutting off
the lights.

EVELYN/COLEMANcccc/NICU/11/
4/11/2004 1:17:34 AM: Room number 02 cleared by amy

Second request- on the password interface, can you make it so when a
user is checked against the database for there user id and Pw, there
user id and pw are errassed from the password interface input area.
What i noticed is that the last person that entered their user id and
pw- it stays there, so all the staff members are doing is checking the
room # and clicking ok.

Thank you very much mathTalk

---

Request for Question Clarification by mathtalk-ga on 22 Apr 2004 07:58 PDT

Hi, Amy:

Do you want the timestamp on the Order Session to be locally
generated?  I think it has to be; unless I've forgotten it I don't
think there is a timestamp in the *.doc files.

And, yes, I can blank out the user name and password.  I was thinking
about the user who might be doing more than one room, but closing down
the application afterwards.  Now that you see how the nurses are
actually using the application, I'm convinced.

regards, mathtalk-ga

---

Clarification of Question by amy123456-ga on 22 Apr 2004 14:19 PDT

Hi Mathtalk. 
 
Do you want the timestamp on the Order Session to be locally
generated?   yes, the same process as turning the lights off.

Thank you

Hi, Amy:

I've wrapped these last changes (clearing the user's password and
generally the user ID as well, generating the local timestamp for
receipt of a .DOC file) into the switchbox.zip file, downloadable from
the usual place.

Note that timestamp is being generated locally by the
textextract/switchbox application for the .DOC files, as opposed to
being generated by the Order Fulfillment client application for the
.PRN files.

While I've done a bit of testing in running both the textextract and
Order Fulfillment applications since compiling these changes, I'm sure
that you'll be able to provide a more thorough test since you have an
automated source of the .DOC files to feed the process.

Let me know by Request for Clarification if there are more issues to
respond to with the base system.

regards, mathtalk-ga

---

Request for Answer Clarification by amy123456-ga on 27 Apr 2004 13:09 PDT

Hi MathTalk. On the loop for txtxtrct, is their a wait time on the loop?
If not should there be some wait time?

Thank you

---

Clarification of Answer by mathtalk-ga on 27 Apr 2004 18:18 PDT

Hi, Amy:

Yes, there are brief delays built into the loop in TextExtract. 
Although this may not be obvious from the top level code, you can see
where Sleep() is called at various points within the FolderSearch
subroutine.

If you wish to try a different amount of delay, you might start by
increasing the argument to the third call to Sleep(), at line 89 of
textextract.cs.

regards, mathtalk-ga

Excellente, as always

Hi, Amy:

If a database is needed to verify user and password, perhaps this is a
good argument for maintaining the World/lamp states in the database as
well?

Recent releases of Access and MS SQL Server have begun to blur the
distinction between them.  Many users will think of Access more for
its friendly interface and of MS SQL Server for its "industrial
strength" backend.  However the Access frontend can be used with a
variety of backends, and Microsoft offers a "freely redistributable"
version of SQL Server called MSDE with many developer products.

regards, mathtalk-ga

Hi MathTalk. What are you saying? and how much work is envolved.
The push right now is to verify user and PW so i can put this project 
in production. I have full approval to move forward and test in production 
next week, if i have a way to verify user and pw.

Please help.

Thank you

Hi, Amy:

I guess I'm trying to ask about the priorities.  I think there are two
major areas of functionality that you've got open at this point.  One
has to do with running three copies of the order fulfillment and light
switch applications "in parallel" based on the idea of three parallel
network directories (that act as message queues to keep the three
"stations" synchronized).  The other one is this, which addresses the
user authentication issue by referencing a database.

So one point is to say that if we are introducing a database (esp. as
opposed to three parallel databases) then there's an opportunity to
use the database as way to run multiple copies of the application "in
synch".

My other questions concern "an access database".  Is this a database
that already exists or one that will be created/maintained as an
ongoing part of your project?  Do you envision having one instance of
the database on the network, or putting a separate copy of the
database on each station?  And which version of Access is used?

I'm proceeding with coding a simple interface to an Access/Jet
database from C# that can be used to validate User ID's and passwords.
 This user validation will require, I'd think, a change to the Order
Fulfillment application to accept a password entry.

regards, mathtalk-ga

Hi Mathtalk.  By the way the previous update worked really well (Err) Thank you.
I am a little slow sometimes, but I see your point now, and I think!
No.  I know your approach, as always is the correct one. Thank you for
your patience?s and your detail explanation.
To answer some of your questions.  I am using access 2003. The
database is being created for this project and it can be placed on the
network.

Thank you very much as always, mathtalk

Hi, Amy:

Here's a brief article, somewhat old, on connecting to MS Access from C#.

http://www.csharphelp.com/archives/archive70.html

I guess what I envision is a dialog that appears when a user submits a
fulfillment, accepts the password and verifies it against the
database.  In the long run this would be combined with submitting the
fulfillment to the database; the Order Fulfillment application would
simply get back a success or failure result based on matching the
user's password.

Does this differ from you vision of the verification process?

-- mathtalk-ga

That makes since to me. Lets do it. How long do you see this piece taking?

Thank you MathTalk

Hi MathTalk. 

I received this error this morning. I also attached the console readout at the end.


An unhandled exception of type 'System.ArgumentOutOfRangeException'
occurred in mscorlib.dll

Additional information: Length cannot be less than zero.


Command: 253
Port Cmd: 161 Bit Image: 0
Port Cmd: 160 Bit Image: 16
Port Cmd: 177 Bit Image: 0
Port Cmd: 176 Bit Image: 0
Port Cmd: 193 Bit Image: 0
Port Cmd: 192 Bit Image: 0
Port Cmd: 209 Bit Image: 0
Port Cmd: 208 Bit Image: 0
Port Cmd: 225 Bit Image: 0
Port Cmd: 224 Bit Image: 0
C:\ScmOrders\040402-1.doc
Last name: INPATIENTFPP
First name: TEST16  MS6 16-01
Location: 13y

I tested to see what the problem could be, and i noticed that the
spacing between first name and location can not be less than 3 spaces.
the result follows.


Command: 253
Port Cmd: 161 Bit Image: 0
Port Cmd: 160 Bit Image: 16
Port Cmd: 177 Bit Image: 0
Port Cmd: 176 Bit Image: 0
Port Cmd: 193 Bit Image: 0
Port Cmd: 192 Bit Image: 0
Port Cmd: 209 Bit Image: 0
Port Cmd: 208 Bit Image: 0
Port Cmd: 225 Bit Image: 0
Port Cmd: 224 Bit Image: 0
C:\ScmOrders\040402-1.doc
Last name: INPATIE
First name: TEST16
Location: MS6
Room number: 16

Thank you MathTalk

Hi, Amy:

The parsing logic should probably be revisited.  I recall asking
whether the location values would be drawn from a known set, which
would allow the parsing to match specifically on those values.  I
think what I implemented looks for three or more consecutive spaces in
front of location, which allows for the possibility that a patient's
first name might have an embedded space or two.

Please remind me what values are allowed for the location, and I'll
change the text extract logic accordingly.

I'll be working on your project over the weekend.  If you could check
in from time to time, I'll have updates on my progress (and maybe a
question or two)!

regards, mathtalk-ga

Please see below the actual order. Is their any way that i can send an
attachment to you of the actual order lay out?

                                       CCMC
                                  Order Session


     COLEMAN, EVELYN       MS6 02-01         25y  F   0100907 /
000045020948

        ALVEOLAR RECONSTRUCTION POST-O R
equested:  01/28/2004            MD (C)
        DIET -C
        CT ABDOMEN W/O CONTRAST -C   Requested:  01/28/2004 Routine      MD (C)
                                         
        ACAPELLA RX -C               Requested:  01/28/2004 Routine      MD (C)
                                         
     once



Thank you MathTalk

Hi, Amy:

The file which caused the problem had the first name too close to the
location, like this:

TEST16  MS6 16-01

The file you posted above has plenty of space between first name and
location.  Are you having a problem with that file?

Otherwise what I'd like you to do is provide a list of all the valid
locations, including MS6 (for example), which is the location used in
both of your examples today.

regards, mathtalk-ga

That specific file ms6 test16 had a very Long last name.

The Units names

picu
nicu
ms8
ms7
ms6

Thank you

Hi, Amy:

Just to confirm, the location values are always uppercase only, right?
 Trying to keep the code "as simple as possible, but not simpler."

-- mathtalk-ga

yes. 

Thank you

Hi, Amy:

Okay, I've revised the textextract code so that in parsing the
firstname of the patient, the location value (which must be one of the
five allowed) is used as the delimiter.

Get switchbox.zip from the same place as before and give it a try.  It
worked for me on the coleman sample order session you showed above,
but your tests are the ultimate proof.  Take a look at the code where
the firstname and location values are extracted and be sure you're
comfortable with how it works.  If the code needs more
comments/explication, let's get it squared away now because I want you
to feel comfortable taking over the maintenance of this code.

Meanwhile I'm back to working on the MS Access database interface. 
Here's a link where Microsoft gives a basic example of connecting in
C#:

[HOW TO: Use Data Link Files with the OleDbConnection Object in Visual C# .NET]
http://support.microsoft.com/default.aspx?scid=kb;en-us;308426

regards,
mathtalk-ga

Good morning MathTalk.  It work very nicely. I am also studying Your
code very carefuly to learn all i can so i can do the maintenance. Any
sucgestions on how to fully understand and learn C#.

Thank you once again

Hi, Amy:

It was, if I recall correctly, originally your request to do this
project in C#, although you wondered about the feasibility of doing
so.  Perhaps if I knew a little more about your background in
programming, I could make more expeditious suggestions about learning
strategies.

regards, mathtalk-ga

I have been working with C# this past year, but with simple programing
strategies, This project has taken me to a new level in C# that i
would like to fully understand more indepth.

Thank you