Re: VB6 Login Form to SQL Server

From: Mark J. McGinty (mmcginty_at_spamfromyou.com)
Date: 12/02/04 

Date: Thu, 2 Dec 2004 08:48:43 -0800

"Wart" <nospamWart@epix.net> wrote in message
news:%23Qs8vzx1EHA.936@TK2MSFTNGP12.phx.gbl...
> David,
> Three options.
> 1) Use the Lost Focus event
> 2) Don't use theChange or Lost Focus and put a "Login" button on the form
> 3) In the Change Event, call a routine that will validate that an octet
> has been entered. If not, do nothing. If so, try to login.
> HTH,
> CF
> "DavidM" <spam@spam.net> wrote in message
> news:u2mGYwo1EHA.4004@tk2msftngp13.phx.gbl...
>> Well, I got my login form to work.
>>
>> It currently displays a textbox for server, username, password, and a
>> combo for databases.
>>
>> What I have done is within the server, username, and password Change
>> Event, coded a routine to see if there is data in the textbox. If there
>> is, it attempts to call the LoadDatabases routine to attempt to connect
>> to DB using this information simply to execute a "SELECT * FROM
>> sysdatabases" using the master database. Once combo is filled, I destroy
>> the connection and, once the user has selected all four items on the
>> form, the "OK" button will become enabled.
>>
>> Everything works fine except I do have one problem.
>>
>> If I fill in the textboxes using username and password first... and then
>> go to the server box and try and enter an IP address... the change event
>> is called for each keystroke. There is a 10 second delay between
>> keystrokes as ADO tries to connect to the TCP/IP address (even though it
>> is incomplete until the user enters the last octet.) I then added a
>> routine to validate the "server" box for a valid TCPIP... which makes the
>> keystrokes much faster until the last octet is entered.

Attempting to open a connection without knowing with certainty that the
server address and credentials are complete, is a total waste of resources
and connectivity. It may seem adequate responsive once the server is
correct, with only a single user, but if you're trying to tender credentials
on every keystroke in the user or password inputs... it won't take many
users to incur significant but needless load.

Your program may need to be dynamic, but users tend to connect to only a
small number of servers in real life, so do them a favor: when you
successfully connect to a server, store its address in the registry, or
persist it to XML, and then load a combo box with them for the user to pick
from.

As for an event to drive the connection, I like to process KeyPress, and
attempt the connection when the user presses the Enter in any of those 3
inputs, if each contains an entry. If not, Enter acts like Tab, moving
focus to the next input. LostFocus (as has been suggested) is pretty good
too, but it doesn't seem as intuitive to connect when you tab away from an
input. Enter is the traditional "send info for processing" key.

Back to doing the user a favor, chances are very good that the user will
want to connect to the same database that they used last session, so again,
store it in the registry and default to it. Hard to seriously call such a
simple measure "heuristic" but that's how it's usually perceived.

-Mark

>> Anyway -- does anyone know a better way around this?
>>
>> My routine is currently relying on a valid TCP/IP address. If someone
>> tries to type in a server name... well, the validation routine will
>> return false and the "OK" button will never enable.
>>
>> As long as the servername is valid from the get-go, all else seems to be
>> pretty quick.
>>
>> Anyone know of anything else I should try?
>>
>> Perhaps change the txtServer event from "Change" to "Lost Focus"?
>>
>>
>>
>> "Jeff Johnson [MVP: VB]" <i.get@enough.spam> wrote in message
>> news:eqckizl1EHA.4072@TK2MSFTNGP10.phx.gbl...
>>>
>>> "DavidM" <spam@spam.net> wrote in message
>>> news:e6ncSzj1EHA.3336@TK2MSFTNGP11.phx.gbl...
>>>
>>>> Does anyone know a standard way of displaying all databases a specified
>>>> login has access to on SQL Server?
>>>
>>> You could use ADOX, or the OpenSchema method in ADO, or you could open a
>>> recordset with
>>>
>>> SELECT * FROM INFORMATION_SCHEMA.TABLES
>>>
>>> if the SQL Server is 7.0 or later.
>>>
>>
>>
>
>