Handle login failure with CDatabase and SQL Server, SQLState 28000 and SQL server error 18456

Question

I have a SQL Server and ODBC to connect to this server in C++. I am using the CDatabase class and the openEx method for the connection. Here a bit of my code:

inline void openConnection() 
{
    try
    {
        if (!database.IsOpen())
        {
            CString connectionString = "DSN=" + name + ";UID=" + user + ";PWD=" + pwd;              
            database.OpenEx(connectionString, CDatabase::noOdbcDialog);
        }
    }
    catch (CDBException* p)
    {
        //For later use.
    }
    
    cout << "Everythings alright!";
};

Everything works fine when I type in the correct parameters.Now I want to handle the problem where someone typed in the wrong parameters. The problem is that when I give the code wrong parameters (e.g. name = testDSN, UID = noUser, PWD = wrongPassword) I get no error message when the program is compiling. It just gives me "Everythings alright". When I change the 'CDatabase::noOdbcDialog' into 'CDatabase::forceOdbcDialog' I can click on my ODBC connection.

After clicking on it I get the login window with my wrong login values.

Now when I click on 'ok' I am getting the message giving me the:

SQLState '28000' and SQL server error 18456. "Login failed for user "noUser".

Is it possible to check if the values are correct so the compiler tells me that something is wrong?

I tried wrong parameters and looked for some ways to maybe getting some SQLException but my program just don't care about the parameters.

Answer 1

Everytime using wrong parameters a CDBException appears. So just use the catch block:

    catch (CDBException* p)
    {
        cout << "Error!" << endl;
    }

Alternatively I can output m_nRetCode, m_strError and m_strStateNativeOrigin from the CDBException class for getting more information about the exception.

        catch (CDBException* p)
        {
            cout << "m_nRetCode: " << p->m_nRetCode << endl;
            cout << "m_strError: " << p->m_strError << endl;
            cout << "m_strStateNativeOrigin: " << p->m_strStateNativeOrigin << endl;
        }

Answer 2

"Try" is used for moments you want to check the connection between the program and your database or files and ... is correctly connected and handle the err maybe appeared during this session or connection.

But for this section you must check the user and pass just after getting them through the user and check it by correct user and pass you fetch from your SQL or somewhere stored in your code like this:

user_name = " name"; user_pass = " pass";

or

name = my query (connection, "Select Name form table name").

or any code you prefer to use. and also, do it for password.

Then check whether the user's name and pass are equal to the one's is cached from the database, or you have set within the program or not. If yes, your permit the user to continue and if not, you drive the user to correct place of code:

     try
{
    if (!database.IsOpen())
    {
        CString connectionString = "DSN=" + name + ";UID=" + user + ";PWD=" + pwd;              
        database.OpenEx(connectionString, CDatabase::noOdbcDialog);
    }
}
catch (CDBException* p)
{
    //For later use.
}
if (user_name == sql_user && pass_user == sql_pass)

    cout << "Everythings alright!";
else
    cout<< " Wrong user or pass";

}