Updating data in database

Question

I have a problem trying to update this data but giving error messages all the time...

private void btnEnterReturn_Click(object sender, EventArgs e)
{
        OleDbConnection connect = new OleDbConnection();
        connect.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=MovieLibrary.accdb";
        connect.Open();
        addnew = "UPDATE tblLoan set MovieID='" + this.txtMovieID + "', DateIssued='" + this.txtDateIssued.Text + "', DateReturned='" + this.txtDateReturned.Text + "' WHERE MemberID='" + this.txtMemberID.Text + "';";

        com = new OleDbCommand(addnew, connect);

        com.ExecuteNonQuery();
        connect.Close();
        MessageBox.Show("Successfully returned..");

        //clear screen
        txtMemberID.Text = " ";
        txtMovieID.Text = " ";
        txtDateIssued.Text = " ";
        txtDateReturned.Text = " ";
}

What is the error, and where does it occur? Where are `com` and `addnew` declared? — pinkfloydx33, Apr 09 '14 at 16:32

score 1 · Answer 1 · answered Apr 09 '14 at 16:34

You error is the string concatenation. You get always bad results with that (in particular when you try to pass dates and numbers as string or a whole textbox control instead of its Text property).

Instead use a well formed parameterized query

string conString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=MovieLibrary.accdb";
using(OleDbConnection connect = new OleDbConnection(conString))
{
     connect.Open();
     string addnew = @"UPDATE tblLoan set MovieID=?, DateIssued=?, DateReturned=?,
                       WHERE MemberID=?";
     using(OleDbCommand com = new OleDbCommand(addnew, connect))
     {
         com.Parameters.AddWithValue("@p1", Convert.ToInt32(this.txtMovieID.Text)
         com.Parameters.AddWithValue("@p2", Convert.ToDateTime(this.txtDateIssued.Text)
         com.Parameters.AddWithValue("@p3", Convert.ToDateTime(this.txtDateReturned.Text )
         com.Parameters.AddWithValue("@p4", Convert.ToInt32(this.txtMemberID.Text )
         com.ExecuteNonQuery();
         MessageBox.Show("Successfully returned..");
     }
}

In your original code, you tried to pass the txtMovieID as the value per the MovieID field, but, from its name I suppose that this is a TextBox, so you need to add the property Text.

A part from that, I also suppose that your txtDateIssued and txtDateReturned textbox contains a datetime value and the corresponding database field expects a datetime. The same thing for the two ID fields.

Updating data in database

1 Answers1