Click here to Skip to main content
15,936,349 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
This codes where working yesterday but today, its giving error "No value given for one or more required parameters." I do not know what I have done.

I am passing data from window formA to Microsoft Access database using a dynamic method "DBeditor" in "mClass" class.
C#
//DBeditor method in mClass.
public void DBeditor(string query, params Control[] A)
{
    static string path = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath + "\\dBase.accdb";

    OleDbCommand cmd = new OleDbCommand();
    OleDbDataAdapter adt = new OleDbDataAdapter();
    OleDbConnection con = new OleDbConnection(path);
    DataTable dt = new DataTable();
    DataSet ds = new DataSet();

    con.Open();
    cmd.Connection = con;
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = query;
    for (int i = 0; i < A.Length; i++)
    {
        cmd.Parameters.Add(new OleDbParameter(("@cell" + "[" + i.ToString() + "]"), OleDbType.VarChar)).Value = A[i].Text; //the problem is here.
        cmd.ExecuteNonQuery();
        con.Close();
    }
}

//Using DBeditor in Windows FormA
            mClass mcl = new mClass();
            mcl.DBeditor("Insert into AdministratorsTable ([SURNAME], [OTHER NAMES])values(@cell1, @cell2)",
                txtSName, txtONames);

Please help, I am really confused right now.
Posted
Updated 30-May-14 0:31am
v2

I guess the execution should take place after the for loop.
C#
for (int i = 0; i < A.Length; i++)
{
    cmd.Parameters.Add(new OleDbParameter(("@cell" + "[" + i.ToString() + "]"), OleDbType.VarChar)).Value = A[i].Text; //the problem is here.
    cmd.ExecuteNonQuery();
    con.Close();
}

cmd.ExecuteNonQuery();
con.Close();

The previous code would work if the A.Length is 1. But when it exceeds 1, it would create problems.
 
Share this answer
 
Comments
Salisu Shaibu 30-May-14 7:44am    
You were very correct!
Thanks. :)
The problem here is that your parameter names are @cell1 and @cell2, but in your for loop, you are adding values to parameter names like @cell[1] and @cell[2]. You should remove the square brackets. Also, you should put the ExecuteNonQuery and Close method outside the loop, otherwise you execute the query before all parameters have values.
C#
for (int i = 0; i < A.Length; i++)
{
    cmd.Parameters.Add(new OleDbParameter("@cell" + i.ToString(), OleDbType.VarChar)).Value = A[i].Text; // remove square brackets []
}
cmd.ExecuteNonQuery(); // put these two lines outside the loop
con.Close();
 
Share this answer
 
v2
Comments
Salisu Shaibu 30-May-14 7:45am    
The solution was:
// put these two lines outside the loop.

Thank you!!!
Thomas Daniels 30-May-14 7:59am    
You're welcome!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900