C# OleDbDataReader: Parameter Query is failing to retrieve data

Question

I'm developing an application that reads the users data from a database (MS Access) and sets the information into variables. The sql command takes a parameter to check the username against the database, however the OleDbDataReader is showing up empty (userData.HasRows).

public void UpdatePage(string Username)
    { 
        OleDbDataReader userData;
        string sqlCmd = "SELECT * FROM [Profile Data] WHERE Username = ?";

        TabPage profile = new TabPage();

        using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source = LogIn Profiles.accdb;"))
        {
            OleDbCommand cmd = new OleDbCommand(sqlCmd, conn);

            cmd.Parameters.AddWithValue("@Username", Username);

            try
            {
                conn.Open();
                userData = cmd.ExecuteReader();

                if(userData.HasRows)
                {
                    //Used for debugging
                }

                while (userData.Read()) //is not entering into the loop
                {
                    userID = userData[0].ToString();
                    username = userData[1].ToString();
                    password = userData[2].ToString();
                    country = userData[3].ToString();
                    occupation = userData[4].ToString();
                    gender = userData[5].ToString();
                }
                userData.Close();
            }
            catch
            {
                MessageBox.Show("Error 3: Error to connect to database", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

When I remove the parameter and set the sqlCmd to have a set name ("...WHERE Username = 'Mitchell'";) in the database, then the while loop is entered and the variables are assigned. Awesome. So something is happening with the parameter.

Also I have confirmed that the user data is in the database before this method is called, so it should be finding it.

Have you confirmed in the debugger that `Username` has the right value? And no trailing whitespace? — D Stanley, Feb 03 '15 at 14:26
Yes the string is exact, no trailing whitespaces, and setting itself correctly into the cmd (as far as I can tell) — InStasis, Feb 03 '15 at 14:31

score 0 · Answer 1 · answered Feb 03 '15 at 14:25

0

From what I remember, access doesn't support named parameters, and using "@parm" where "parm" is the same name as the column you intend to update might confuse it by implying...

set ColumnA = ColumnA

vs the parameter you are actually providing. The "?" you have is correct to act as the ordinal place-holder of the parameter, so I would just try changing from

"@Username" to just "parmUserName"

don't use the "@" sign

answered Feb 03 '15 at 14:25

DRapp

47,638
12
72
142

1

You can name parameters (even with `@`) but it will just apply them in the order that it finds `?` in the query. It won't try to match them up by name. – D Stanley Feb 03 '15 at 14:27
Tried both with the same result – InStasis Feb 03 '15 at 14:32
@InStasis, is the user name padded with spaces on either side and failing it? Case-sensitive match or not? Does userData.HasRows come back as true? or is it falling into the CATCH for some other error? – DRapp Feb 03 '15 at 15:13

score 0 · Answer 2 · answered Feb 04 '15 at 10:22

So after hours of playing around with things I found the solution, however I am not totally sure why it works. I had to change the Data Source of the OleDbConnection to the direct file path, rather than the relative path.

Before this method is another that inserts data to the database, and only after the program was closed could it be accessed with this UpdatePage method. Not entirely sure why, however I did have to use the full address in the other method also.

C# OleDbDataReader: Parameter Query is failing to retrieve data

2 Answers2