Question:
Given that NTCR is only good in a network environment, how can I redirect a user back to a login page (or trap something) if I pass their username and password into a OLE DB connection string and use SQL authentication? When a user is not in the SQL user list the error is: Microsoft OLE DB Provider for SQL Server (0x80040E4D). Login failed for user ‘weblogin’. Apart from installing additional software on the server is there a elegant way to handle this and take the user to a nicer place?
Answer:
One simple technique is to set “On Error Resume next” prior to making the database hit. Then check the value of the Err object when you return. If it is not zero, you know an error took place and can proceed to a nicer rejection page.
On Error Resume NextobjConn.Open "dsn", "userid", "password"If Err <> 0 Then display neat error messageend if
Encapsulate the above in its own function so that the On Error statement does not affect your other code.
Obviously, this is a simple solution for your immediate problem. Perhaps you can rethink your design so that user names and passwords of users to your site are stored in a table against which you validate using a common SQL Server login name and password rather than have to generate and maintain SQL Server users.
