Help with a Select statement

PDS8475

Member
Joined
Jun 25, 2019
Messages
13
Programming Experience
Beginner
Hi I have been messing around with this for two day trying to get it to work.
At the moment I am getting the following rather long Exception message

573


I ended up using a string builder program to generate the connection string and the select statement. Not that they are exactly right as I want them to say the database is in the same folder as this program, But getting the code just to work is the main issue.
My code is as follows
C#:
private void Pass_textBox_TextChanged(object sender, EventArgs e)
        {
            string UN = TextBox2.Text;
            string pass = Pass_textBox.Text;
            string connString = @"Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:\Users\Paul\source\repos\FixIT\FixIT\bin\Debug\FixITAdmin.mdb";
            List<String> passw = new List<String>();
            OleDbConnection oleConn = new OleDbConnection(connString);
            try
            {
                oleConn.Open();
                
                string my_querry = "SELECT Admins.PW AS [Admins PW] FROM Admins AS Admins WHERE( Admins.Name LIKE 'UN') ORDER BY  Admins.Name";
                OleDbCommand cmd = new OleDbCommand(my_querry , oleConn);
                OleDbDataReader reader = cmd.ExecuteReader();

                reader.Read();
                
                passw.Add(reader["PW"].ToString());
                
                string result = cmd.ExecuteScalar().ToString();
                MessageBox.Show(result);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                oleConn.Close();
            }


            if (Pass_textBox.Text == "")
            {
                Pass_textBox.PasswordChar = '\0';
                Pass_textBox.Text = "Password";
            }
            
        }

If I put reader.read() a while statement as in

C#:
 while (reader.Read())
                {

                    passw.Add(reader["PW"].ToString());
                }
I get the following Exception message

574


I realise the part of the select statement that gets input from Textbox2 (WHERE( Admins.Name LIKE 'UN')) may be wrong so I tried changing UN for a fixed value and get a PW out of range exception.
Since I don't fully understand these exception messages I feel I am going round in circles.
Any help would be much appreciated
 

Sheepings

Senior Programmer
Joined
Sep 5, 2018
Messages
159
Location
UK
Programming Experience
10+
Check your other topic where I showed you parameter values. Also, why are you not using Sql workbench to help you build and evaluate sql syntax.

For a learner, it will help you alot. Anyway, can you explain what your command statement is meant to do.
 

Sheepings

Senior Programmer
Joined
Sep 5, 2018
Messages
159
Location
UK
Programming Experience
10+
The error is due to mismanagement of your already open connection. You should check if a connection is already open before trying to open another. Vice-versa for closing. I gave you a helpful link the other day, go back and read it.
 

PDS8475

Member
Joined
Jun 25, 2019
Messages
13
Programming Experience
Beginner
Hi Thanks
I think this is what you mean? The command statement is supposed to run the select statement and retrieve PW from the database provided in the connection string where the Name field is equal to textbox2.text.

I will have a look at the link you posted.

Thanks again
 

Sheepings

Senior Programmer
Joined
Sep 5, 2018
Messages
159
Location
UK
Programming Experience
10+
This is your command :: OleDbCommand cmd = new OleDbCommand(my_querry , oleConn);
This is your command statement :: "SELECT Admins.PW AS [Admins PW] FROM Admins AS Admins WHERE( Admins.Name LIKE 'UN') ORDER BY Admins.Name";
Where in that statement do you see any relation to textbox2.Text? Read from here down regarding parameters.

See post 3 above regarding what your exception error is about. Had you read what I shared with you the other day about opening and closing connections, you can find a helpful snipped of code on a link in your other topic. Follow the above link. It will only open a connection if there are none already open. Providing you call it once before and after every command execution. It ensures you never have a connection left open!
 

Sheepings

Senior Programmer
Joined
Sep 5, 2018
Messages
159
Location
UK
Programming Experience
10+
Why are you using the text changed event? That is incorrect usage.

Every time you type a new letter that event is going to fire. That is not what you want either.

And this; oleConn.Open(); - Replace that with a check to see the current connection state. How do you know the connection needs to be open unless you check. Which you're not doing.
 

Skydiver

Well-known member
Joined
Apr 6, 2019
Messages
205
Location
Virginia Beach, VA
Programming Experience
10+
The exception regarding the ordinal is because you are looking for reader["PW"], but your SELECT statement said to get the password field AS [Admins PW]. "PW" != "Admins PW".
 

Sheepings

Senior Programmer
Joined
Sep 5, 2018
Messages
159
Location
UK
Programming Experience
10+
You sure? I am sure there will be a separate error after the connection error, but the conn error needs addressing first.
 

Skydiver

Well-known member
Joined
Apr 6, 2019
Messages
205
Location
Virginia Beach, VA
Programming Experience
10+
Yes, there is a separate exception with regards to the connection management, or in this case, mismanagement.
 
Top Bottom