Passing Microsoft Access value in Visual Studio

Question

Okay guys, I'm doing a bank application for my school project where I combine Microsoft Access with Visual Studio, I have the information in Access and I retrieve them with Visual Studio.

        OleDbConnection connection;
        yhteys = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source= tilit.accdb");
        try
        {
            connection.Open();

        }
        catch (OleDbException tietokantavika)
        {
            Console.WriteLine("No connection!\n" + tietokantavika.Message);
            return;
        }
        OleDbCommand haekomento = new OleDbCommand();
        haekomento.Connection = yhteys;
        haekomento.CommandText = "Select clientnumber, accountNumber,pinCode, money FROM Account";
        OleDbDataReader  lukija = haekomento.ExecuteReader();
        while (lukija.Read())
        {

            string accountNumber2 = (string)lukija["accountNumber"];
            int pinCode2 = (int)lukija["pinCode"];
            decimal money2 = (decimal)lukija["money"];
            int clientNumber2 = (int)lukija["clientNumber"];




            if ((txtAccountNumber.Text == accountNumber2) && int.Parse(pinCode.Text) == pinCode2)
            {
                Menu GoToMenu = new Menu();
                GoToMenu.Show();
            }
            else
                lblväärä.Text = "Wrong pin or account!";

So I log in with the accountNumber 1111-1111 and pincode 1111 which is registered to Steve Bush

The problem here is that when I log in and get to the Menu form it logs in as the wrong user. It should log in as Steve Bush (clientNumber 1) but instead it logs in as Greg Ott (clientNumber 3). I think the problem is because I keep using the same connection, but I dont know how to fix it. My form 2

 OleDbConnection connection;
            yhteys = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source= tilit.accdb");
            try
            {
               connection.Open();

            }
        catch (OleDbException tietokantavika)
        {
            Console.WriteLine("No connection!\n" + tietokantavika.Message);
            return;
        }
        OleDbCommand haekomento = new OleDbCommand();
        haekomento.Connection = yhteys;
        haekomento.CommandText = "Select  clientNumber, firstName, lastName FROM Client";




        OleDbDataReader lukija = haekomento.ExecuteReader();

        while (lukija.Read())
        {
            int clientNumber2 = (int)lukija["clientNumber"];
            string lastName2 = (string)lukija["lastName"];
            string  firstName2 = (string)lukija["firstName"];


            lblNimi.Text = "Welcome" + firstName2  + lastName2; // Here it should display Steve Bush but instead it says Greg Ott

I know i've made alot of mistakes in that code, that's why I came here for help. I've been stuck with this for hours and dont know how to fix it.

The `SELECT` statement that you are sending to Access requests ___every single client record___, then you are going through that list and testing (with an `if`) until you find (or DO NOT find) a match for both the `AccountNumber` and the `PIN`. While this is not a direct answer to your question, I believe that what you are trying to do will be simpler if you change your `SELECT` statement to retrieve `WHERE accountNumber = txtAccountNumber.Text`. The you should have either 1 matching client record or ZERO. Then no need to loop--AND I bet you'll be looking at the proper client. — David Tansey, Apr 14 '13 at 17:34
PLEASE NOTE: I didn't try to get this syntax exactly correct -- AND this ___raises other issues about using screen input in queries___ But it is for school, right? — David Tansey, Apr 14 '13 at 17:35
Yeah it's for school so and we're just beginners so no worries, but how do I retrieve txtAccountNumber.Text from Form 1 to Form2? — Sami Assasi, Apr 14 '13 at 17:43
Yes WinForms, I have txtAccountNumber.Text in Form1 and need to pass it on to Form2 — Sami Assasi, Apr 14 '13 at 18:00
Take a look at the following: http://stackoverflow.com/questions/1293926/c-sharp-winforms-global-variables and see if it gives you an idea. Try to think of it this way: in Form2 you are **not** actually interested in `txtAccountNumber.Text` what you **are interested in** is the value of the ___valid accountNumber which was entered to gain access to the system___. `txtAccountNumber` is the UI control that allowed the user to enter a value. We don't want to depend on that control forever so we put its value `txtAccountNumber.Text` into a variable and then refer to that. Make sense? — David Tansey, Apr 14 '13 at 18:12
I understand what you're saying, but I dont understand what's the difference between _globalvar and globalvar, but here's what I did (and which didn't work) public static class yhteiset { public static string txtAccountNumber { get { return txtAccountNumber; } } haekomento.CommandText = "SELECT asno, sukunimi, etunimi FROM Asiakas WHERE asno =" login.yhteiset.txtTilinumero — Sami Assasi, Apr 14 '13 at 18:21
It helps to break it down into smaller pieces. Try this: `public static class GlobalVars { public static string AccountNumber { get; set; }}` and then in Form1 `GlobalVars.AccountNumber = txtAccountNumber.Text;` Good luck. — David Tansey, Apr 14 '13 at 18:36
Sorry to keep bothering you @David, I just keep failing and dunno what to do http://s22.postimg.org/7dfnwgpu9/form1.png here is my form 1 and my form 2: haekomento.CommandText = "Select tapahtumano, tilino, tapAika, tapMäärä, tapTyyppi FROM Tilitapahtuma WHERE tilino = login.yhteiset.Tilinumero" ; it's in finnish so dont worry, tilino means accountNumber — Sami Assasi, Apr 14 '13 at 19:02
The mistake seems to be in my Select Clause or something, but I've tried to do like 50 different variations of it but none seem to work.. — Sami Assasi, Apr 14 '13 at 20:15

Passing Microsoft Access value in Visual Studio

0 Answers0