Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

UPDATE Command Parameters in C# For Access 2003 Not update

Tags:

c#

ms-access

access 2003 vs 2010 c#

I cannot see where I have gone wrong. There is no error but no data is being updated. I have the insert, delete and edit working but I don't know why I can't get this to work. Please can someone kindly help me here, thanks in advance...

connection string

 myCon = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data 
 Source=C:..\TempDB.mdb");

Update method...

    private void btnUpdate_Click(object sender, EventArgs e)
    {
        OleDbCommand cmd = new OleDbCommand();
        cmd.CommandType = CommandType.Text;

        cmd.CommandText = "UPDATE [Family] SET [FirstName] = ?, [LastName] = ?, [FamilyDOB] = ?, [Medical] = ? WHERE [ID] = ?";
        //tried this as well
        //cmd.CommandText = "UPDATE [Family] SET [FirstName] = FirstName, [LastName] = @LastName, [DOB] = @StudentDOB, [Medical] = @Medical WHERE [ID] = @ID";
        cmd.Parameters.AddWithValue("@ID", txtFamID.Text);
        cmd.Parameters.AddWithValue("@FirstName", txtFirstName.Text);
        cmd.Parameters.AddWithValue("@LastName", txtLastName.Text);
        cmd.Parameters.AddWithValue("@FamDOB", txtFamDOB.Text);
        cmd.Parameters.AddWithValue("@Medical", txtMedical.Text);

        cmd.Connection = myCon;
        myCon.Open();
        cmd.ExecuteNonQuery();
        myCon.Close();
    }
like image 925
bucketblast Avatar asked Dec 26 '22 07:12

bucketblast


2 Answers

Supply the parameter values in the same order as they appear in the SQL statement.

cmd.Parameters.AddWithValue("@FirstName", txtFirstName.Text);
cmd.Parameters.AddWithValue("@LastName", txtLastName.Text);
cmd.Parameters.AddWithValue("@FamDOB", txtFamDOB.Text);
cmd.Parameters.AddWithValue("@Medical", txtMedical.Text);
cmd.Parameters.AddWithValue("@ID", txtFamID.Text);

OleDB plus MS Access doesn't care about the parameter names, only their order.

like image 107
HansUp Avatar answered Jan 11 '23 02:01

HansUp


The OLE DB.NET Framework Data Provider uses positional parameters that are marked with a question mark (?) instead of named parameters.

Change this:

    cmd.Parameters.AddWithValue("@ID", txtFamID.Text);
    cmd.Parameters.AddWithValue("@FirstName", txtFirstName.Text);
    cmd.Parameters.AddWithValue("@LastName", txtLastName.Text);
    cmd.Parameters.AddWithValue("@FamDOB", txtFamDOB.Text);
    cmd.Parameters.AddWithValue("@Medical", txtMedical.Text);

to:

    cmd.Parameters.AddWithValue("?", txtFamID.Text);
    cmd.Parameters.AddWithValue("?", txtFirstName.Text);
    cmd.Parameters.AddWithValue("?", txtLastName.Text);
    cmd.Parameters.AddWithValue("?", txtFamDOB.Text);
    cmd.Parameters.AddWithValue("?", txtMedical.Text);

More: OleDbParameter Class

like image 40
Iswanto San Avatar answered Jan 11 '23 01:01

Iswanto San