C# Windows Forms Return result from OleDb query to MS Access to a custom class

Question

I have the design, framework, and most of the code finished for an inhouse application I have created. I am attempting to add some functionality, which will translate into 3 completely different forms, that access, view, and update data stored on an Access Database, placed on a hidden network drive.

The relevant information for the question I have is as follows... 1) User launches application which then collects and displays their current logged in username, and asks them if they wish to procede. 2) Once they choose to procede, the code searches an Agent table in the MS Access Database to see if there is a record created for the agent's name. 3) If the record exists (currently) the form will close, returning to a data entry form to begin inputing information only displaying their username 4) If the record does NOT exist, it open another form, where the user will enter their details, manager, shift start and end time, and their AgentID 5) Once the Agent details are saved, then the application returns to the data entry form to begin inputing information.

What I wish to accomplish with this code... In step 3), after successfully finding a record that matches their username, return a value from the matching row, in the AgentID column. The database has a lookup field that matches AgentID to their specific role, which I will use later to show an altername form based on the user's role. For now, I only wish to get the AgentID from the corresponding row, then save the value to a static class variable I created to be utilized by other existing forms.

The using statements for reference

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Security.Principal;

The code I want to figure out is within the If function. The code will successfully identify if the user has an entry in the Agent table, and if not, send the user to a form to create the entry.

private void button1_Click(object sender, EventArgs e)
    {
        // set FNameLabel to user's name without domain
        FNameLabel.Text = WindowsIdentity.GetCurrent().Name.Split('\\')[1];
        // create OleDb connection and command, @name for agent name variable
        OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.jet.oledb.4.0;data source=\\myserver\hidden$\database.mdb");
        OleDbCommand cmd = new OleDbCommand("SELECT * FROM Agent WHERE AgentName=@name");
        // create command connection and set parameters
        cmd.Connection = con;
        cmd.Parameters.AddWithValue("@name", FNameLabel.Text);
        con.Open();
        OleDbDataReader read = cmd.ExecuteReader();
        // If there is a record that matches Agent Name, return to previous form
        if (read.Read() == true)
        {
            // MessageBox.Show("agent name in database");

            // If Agent Name matches, return Agent ID, passed to custom class variable
            // Class variable for Agent ID
            // AgentID.Var = [some variable that contains AgentID where AgentName = current user]
            this.Close();
            return;

        }
        // If there is no record that matches Agent Name, open form to enter Agent details
        // MessageBox.Show("agent name NOT in database");
        AgentInfo agents = new AgentInfo();
        agents.ShowDialog();
        this.Close();



    }

The class variable works without a hitch

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DataEntry
{
static class AgentID
{
    private static string agentid = "";

    public static string Var
    {
        get 
        { 
            return agentid; 
        }
        set 
        { 
            agentid = value; 
        }
    }
}

}

Answer 1

I ended up solving this issue by adding 1 line of code and changing my SELECT statement

OleDbCommand cmd = new OleDbCommand("SELECT AgentName, AgentID FROM Agent WHERE AgentName=@name");

I then added this line within the If function

AgentID.Var = read[1].ToString();

By changing the * to the two columns I wanted (AgentName & AgentID) it assigned them to columns 0 and 1. The AgentName in column 0 and the AgentID in column 1. I then simply took my custom variable and assigned the value of what was read from column 1, after it came back true (read[1]) nested in the if (read.Read() == true). I also made certain to assign the value, even though in this case it is a number, to string to pass to my string variable class.

The result, I can now make a call to my AgentID class, and retrieve the value assigned with the .Var anywhere in My application at any time regardless of which form is open.

2 Thumbs Up :)