Assigning values from access database to string variables

Question

The program works just fine until I add the where clause in the select statement resulting in the code throwing an exception "System.Data.OleDb.OleDbException: 'No value given for one or more required parameters.' " which reffers to "dr=cmd.ExecuteReader()". What can I do make the where clause work as I need this for multiple columns for the table.

I should mention I want to extract from a single row each time, as I want only the information of the current logged user.

public partial class Profil : Form
    {
        public string utiliz;
        public Profil()
        {
            InitializeComponent();
        }
        OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db_users.mdb");
        OleDbCommand cmd = new OleDbCommand();
        OleDbDataReader dr;

        private void Profil_Load(object sender, EventArgs e)
        {
            con.Open();
            label1.Text = HomePage.current_user;
            utiliz = HomePage.current_user;
            cmd = new OleDbCommand("SELECT nume FROM tbl_personaldata WHERE utilizator = @utiliz",con);
            dr = cmd.ExecuteReader();
            while(dr.Read())
                txtNume.Text = dr[0].ToString();
        }
}

Caius Jard · Accepted Answer · 2022-04-05 08:11:10Z

You used a parameter here;

cmd = new OleDbCommand("SELECT nume FROM tbl_personaldata WHERE utilizator = @utiliz",con);
                                                                               ^^^^^

But you didn't add it to the command. In C# simply mentioning the name of a variable in a string is not enough to make any form of interaction between the variable and the string

string name = "John";
Console.WriteLine("I'm called name"); //prints "I'm called name", not "I'm called John"

You have to add the parameter to the command:

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

When you have more than one parameter, you must use Add in the order they appear in the SQL

cmd.CommandText = "SELECT * FROM t WHERE a = @a and b = @b";
cmd.Parameters.AddWithValue("@a", "some a");
cmd.Parameters.AddWithValue("@b", "some b");

Even though it understands @ is a parameter, the parameter names are ignored with Access/OLE; it's still sensible to use them to help code readability, but the order of addition is critical . In most other databases you'll use in your life, the names are used, and hence you can add parameters in any order so long as the name matches. With other databases you can also reuse the same name in the SQL more than once. If you want to repeat a value in Access SQL, you must perform another Parameters.Add[WithValue] for the same thing:

//yes
cmd.CommandText = "SELECT * FROM t WHERE firstname = @fname or lastname = @lname";
cmd.Parameters.AddWithValue("@fname", "Lee");
cmd.Parameters.AddWithValue("@lname", "Lee");

//no, not in Access. Other DBs are fine with this
cmd.CommandText = "SELECT * FROM t WHERE firstname = @name or lastname = @name";
cmd.Parameters.AddWithValue("@name", "Lee");

If you switch to using SqlServer in future, cease using AddWithValue, but other databases don't necessarily suffer from its use

Yong Shun · Accepted Answer · 2022-04-05 08:02:54Z

0

You need to provide the parameter and its value as it is a parameterized query.

cmd = new OleDbCommand("SELECT nume FROM tbl_personaldata WHERE utilizator = ?",con);

cmd.Parameters.Add("?", OleDbType.VarChar /* Or specify the column type for OleDbType */).Value = utiliz;

Note:

Remarks

OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text.

In this case, the question mark (?) placeholder must be used.

Reference

OleDbCommand.Parameters Property

answered Apr 5, 2022 at 8:02

Yong Shun

54.6k6 gold badges38 silver badges66 bronze badges

Collectives™ on Stack Overflow

Assigning values from access database to string variables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related