[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 210
  • Last Modified:

Experts, could you help?

Hi
  I have trying to do a ODBC Oracle Connection using the logon screen created by VB forms and command button and textboxes.
I have a command button named connect.it contain the following codes:

connect="ODBC;....DSn......"
set db=opendatabase("",false,false,connect)

I'm supposed to use my logon screen to capture user id and password,
but the moment I key in an invalid oracle user id and password, oracle logon screen will be invoked.What I want is that my logon screen will be invoked (to let user try again) instead of the oracle logon.So is there anyway we can suppress that oracle logon.Please help.
0
ssptkt
Asked:
ssptkt
  • 4
  • 4
  • 2
  • +3
1 Solution
 
ndnetCommented:
You would have to have the password checked before the password to be given to the oracle checker. (I.E. Have it download the password database, then check the input against it.)
0
 
ssptktAuthor Commented:
Hi ndnet,
    it rather impossible for what you have suggested. Still I deeply appreciate your input. Thank You.

Best wishes,
Ken
0
 
ndnetCommented:
I realize it would be hard, but it is the only solution without contacting Oracle. As soon as you submit the first password, the Oracle program takes over. One last   idea:(This goes in the code for the submit button)


On Error Goto wrong 'goes at very top

wrong: ' goes at very bottom
load frmLogin 'frmLogin is the login form



The only problem is that the Oracle Window may open.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
yowkeeCommented:
ssptkt,

  For not invoke Oracle logon windows and show your own window, you must apply a DriverNoPrompt action and do a error trapping.

  If you are using RDO, eg.
-----
  Dim rd As RDOConnection
  Set rd = New RDOConnection

  On Error Goto ErrHandler
GetUser:
  ' Your code to getting UserID from User
  ' eg. sUID = InputBox("Input User ID to logon:")
  rd.Connect = "UID=xx;PWD=;DSN=xxx;"
  rd.CursorDriver = rdUseOdbc
  rd.EstablishConnection rdDriverNoPrompt
  :
  :
  Exit Sub

ErrHandler:
  If Err.Number = xxx Then   ' Check whether error is cause by
     Resume GetUser          ' invalid user and pwd
  End If                    
----

If you are using DAO, you must open database thru ODBCDirect Workspace:

  Dim wrk As WorkSpace
  Dim db as Database
  set wrk = CreateWorkspace("ODBCWorkspace", "admin", _
            "", dbUseODBC)
  Workspaces.Append wrk

  On Error Goto ErrHandler    ' Same as example above
GetUser:
  ' Getting User ID
  set db = wrk.OpenDatabase("", dbDriverNoPrompt, False, _
           "ODBC;....")  
  :
  :  ' Just same as example above....

------

 For specify DriveNoPrompt, ODBC driver won't prompt you the remote database logon screen. So you get to know that is error while opendatabase thru error handler.

Regards.  
0
 
ssptktAuthor Commented:
Hi Yowkee,
   So sorry I did not mention the VB version which I'm using, it's VB4.0. I'm quite positive that it does not support the feature of DriverNoPrompt and I'm not using RDO.
   Please let me know if you have other ways of solving the problems. I would be most grateful. Thank You and have a nice weekend.
0
 
martiCommented:
ssptkt, have you tried adding UID=xx;PWD=xx to the connect string?
0
 
ssptktAuthor Commented:
Yes I did. I have tried everything I could. Real demoralising.
0
 
yowkeeCommented:
ssptkt,

  One way to implement it is thru ODBC API. Assume you are using VB4 32 bits:

-----
Declare Function SQLAllocConnect Lib "odbc32.dll" (ByVal hEnv As Long, pHdbc As Long) As Long
Declare Function SQLAllocEnv Lib "odbc32.dll" (pHenv As Long) As Long
Declare Function SQLConnect Lib "odbc32.dll" (ByVal hDbc As Long, ByVal szDSN As String, ByVal cbDSN As Long, ByVal szUID As String, ByVal cbUID As Long, ByVal szAuthStr$, ByVal cbAuthStr As Long) As Long
Declare Function SQLDisconnect Lib "odbc32.dll" (ByVal hDbc As Long) As Long
Declare Function SQLFreeConnect Lib "odbc32.dll" (ByVal hDbc As Long) As Long
Declare Function SQLFreeEnv Lib "odbc32.dll" (ByVal hEnv As Long) As Long

Const SQL_NTS = -3
Const SQL_SUCCESS = 0
Const SQL_SUCCESS_WITH_INFO = 1

Sub GetUser()

    Dim hEnv As Long
    Dim rc As Long
    Dim hDbc As Long
    Dim sUid As String
    Dim sPwd As String
   
    rc = SQLAllocEnv(hEnv)
    rc = SQLAllocConnect(hEnv, hDbc)
    Do
       ' Get User ID to sUID, Password to PWD
       ' Put your routine/form here, I use get userid as example
       sUid = InputBox("UserID")
       sPwd = "temp"
       rc = SQLConnect(hDbc, "RiskMonSrv", SQL_NTS, _
                       sUid, Len(sUid), sPwd, Len(sPwd))
    Loop While (rc <> SQL_SUCCESS) Or (rc <> SQL_SUCCESS_WITH_INFO)
   
    SQLDisconnect hDbc
    SQLFreeConnect hDbc
    SQLFreeEnv hEnv
   
End Sub
-----

Note: I am in a hurry, so didn't any error handler in the procedure. The other things, please verify the API declaration if
facing any problem while testing. Sorry, I didn't spend much time to verify it. Remember that it is always a good pratice to free the resource after you don't use it(SQLDisconnect, SQLFreeConnect, SQLFreeEnv...)

Regards.

0
 
yowkeeCommented:
The 2nd parameter of SQLConnect is DSN.
0
 
twardCommented:
Would you be interested in using Oracle Objects for OLE?  That is what I use here to connect to Oracle and it is very easy to connect etc...

If you want to go that route I will post the Module that I created here that I use in about 50 different programs.
0
 
anthonycCommented:
vbDriverNoPrompt works in VB4 with DAO.  

set db = opendatabase("databasename", dbDriverNoPrompt, false, "ODBC;DNS=..."

0
 
yowkeeCommented:
anthonyc,

  I also mentioned using DAO with dbDriverNoPrompt in my comment. I didn't have VB4.0 and what I get from VB5 help is that dbDriverNoPrompt only support in ODBCDirect workspace. Is it work for VB4 too?
0
 
ssptktAuthor Commented:
Hi anthonoyc,
   I would like to confirm with you whether is it vbDriverNoprompt or dbDriverNoprompt and by the way is vb or dbDriverNoprompt suppose to be a driver, method or property? How and where can I determine/check if dbDriverNoprompt is residing on my VB4.0. And do I have to specially preset anything else on install anything else.Please advise

Thanks and have a nice weekend,
Ken (98)

 
0
 
anthonycCommented:
the way you use it is

set db = opendatabase("DSNNAME", dbDriverNoPrompt, False, "ODBC;...")
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 4
  • 4
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now