Pre-filled form update problem

[laura301019]

Hi I have a page for users to edit their details, I've used the following code to prefill the form.

 protected void loadData()
        {
            OleDbConnection conn = new OleDbConnection();


            conn.ConnectionString = ConfigurationManager.ConnectionStrings["ProjectDatabaseConnectionString"].ConnectionString;
            conn.Open();

            DataTable dt = new DataTable();
            DataSet ds = new DataSet();
            ds.Tables.Add(dt);

            OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM Clients WHERE Username = '"+lblSessUser.Text.ToString()+"'", conn);
            da.Fill(dt);

            foreach (DataRow r in dt.Rows)
            {

                    txtUser.Text = r[1].ToString();
                    txtAdd.Text = r[2].ToString();
                    txtTown.Text = r[3].ToString();
                    txtCounty.Text = r[4].ToString();
                    txtPhone.Text = r[5].ToString();
                    txtMob.Text = r[6].ToString();
                    txtEmail.Text = r[9].ToString();

            }

            conn.Close();


        }

But when I alter any of the fields it's not updating. The code for the update is.

 protected void btnSave_Click(object sender, EventArgs e)
{
    OleDbConnection conn;
            OleDbCommand cmd;

            using (conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["ProjectDatabaseConnectionString"].ConnectionString))
            {
                using (cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "UPDATE Clients (ClientName, Address, Town, County, PhoneNumber, MobileNumber, Username [Password], EmailAddress WHERE Username = '"+lblSessUser.Text.ToString()+"') VALUES (@name, @add,@town,@county,@phone,@mobile,@username, @password, @email)";

                    cmd.Parameters.AddWithValue("@name", txtUser.Text);
                    cmd.Parameters.AddWithValue("@add", txtAdd.Text);
                    cmd.Parameters.AddWithValue("@town", txtTown.Text );
                    cmd.Parameters.AddWithValue("@county", txtCounty.Text );
                    cmd.Parameters.AddWithValue("@phone", txtPhone.Text);
                    cmd.Parameters.AddWithValue("@mobile", txtMob.Text);
                    cmd.Parameters.AddWithValue("@username", lblSessUser.Text);
                    cmd.Parameters.AddWithValue("@password", txtPass.Text);
                    cmd.Parameters.AddWithValue("@email", txtEmail.Text);
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
            }
    }

When I run the application I am getting 'Syntax error in UPDATE statement', I can't see any errors so if anyone else can that would be great.

Also when I run it through debug I can see that any of the fields that are changed are still holding the pre filled data. Any way of getting around this?

Thanks in advance

[JorgeM]

With regard to your first question about the syntax errror, you are missing a comma between Username and [Password] (line 10).

Also, why aren't you using a parameter for here: '"+lblSessUser.Text.ToString()+"') VALUES like you have done with the others...

[laura301019]

Thank you I had been looking at the syntax for ages.

Sorry, I'm pretty new to using oledb etc in Visual Studio so having to guess how to write the code. What I was wanting to do was update the row for the user that is already logged into the system (where the username matches the username that is logged in). I have the username in session and placed in lblSessUser. What other way can I write the WHERE clause in the UPDATE statement?

[JorgeM]

Actually, i didnt even notice you were trying to perform an UPDATE. I should have paid more attention before I responded. my appologies..

I had assumed insert because of the way the query was written. For an UPDATE, it would look like this...

cmd.CommandText = "UPDATE Clients SET ClientName = @name, Address = @add, Town = @town, County = @county, PhoneNumber = @phone, MobileNumber = @mobile, Password = @password,  EmailAddress = @email WHERE Username = @username";

cmd.Parameters.AddWithValue("@name", txtUser.Text);
cmd.Parameters.AddWithValue("@add", txtAdd.Text);
cmd.Parameters.AddWithValue("@town", txtTown.Text );
cmd.Parameters.AddWithValue("@county", txtCounty.Text );
cmd.Parameters.AddWithValue("@phone", txtPhone.Text);
cmd.Parameters.AddWithValue("@mobile", txtMob.Text);
cmd.Parameters.AddWithValue("@username", lblSessUser.Text);
cmd.Parameters.AddWithValue("@password", txtPass.Text);
cmd.Parameters.AddWithValue("@email", txtEmail.Text);

conn.Open();
cmd.ExecuteNonQuery();