Trying to update data in an Access database from text boxes

Rao Varrey

9y1.5kLearn iOS Programming

private void button1_Click(object sender, EventArgs e)

{

// Saves changes made in the details

OdbcConnection myConnection = new OdbcConnection();

OdbcCommand cmd = myConnection.CreateCommand();

string myConnectionString = @"Driver={Microsoft Access Driver (*.mdb)};" + "Dbq=C:/DROPS-Data/Derrick.mdb";

myConnection.ConnectionString = myConnectionString;

myConnection.Open();

cmd.CommandText = "UPDATE InspInfo SET CompanyName =" + textBox1.Text + "," + "CustomerRef =" + textBox2.Text + ", Contact =" +textBox3.Text + ", RigName =" + textBox4.Text + ", Location =" + textBox5.Text + " WHERE WONo =" + textBox6.Text ;

cmd.ExecuteNonQuery();

myConnection.Close();

}

This is a windows project ,,,trying to update the connected database from the changes in the text boxes in the form....

Getting an error

System.Data.Odbc.OdbcException (0x80131937): ERROR [42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression

Can anyone please help

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Pramod Thakur9y ago
Accepted answer
Hi Varrey,

You have to write '"+TextBox.Text+"' for string and "+Textbox.Text+" for int.
This is basic string concatination thing. Your query should be like this.
cmd.CommandText = "UPDATE InspInfo SET CompanyName ='" + textBox1.Text + "'," + "CustomerRef ='" + textBox2.Text + "', Contact ='" +textBox3.Text + "', RigName ='" + textBox4.Text + "', Location ='" + textBox5.Text + "' WHERE WONo =" + textBox6.Text ;
You can write this way as well.
command.Text = "UPDATE Student SET Address = @add, City = @cit Where FirstName = @fn and LastName = @add";
and then add your parameters accordingly.
command.Parameters.AddWithValue("@ln", lastName);
command.Parameters.AddWithValue("@fn", firstName);
command.Parameters.AddWithValue("@add", address);
command.Parameters.AddWithValue("@cit", city);
Rao Varrey9y ago
Thank you all....I am using ODBC connection......Parameters dont seem to work....I have tried that Suggestion by Pramod Thakur worked fine ....using concatenation
Rajkiran Swain9y ago
Your query is wrong .

Try it .
That is the syntax for Update statement in SQL, you have to use that syntax otherwise you will get the exception.

command.Text = "UPDATE Student SET Address = @add, City = @cit Where FirstName = @fn and LastName = @add";
and then add your parameters accordingly.

command.Parameters.AddWithValue("@ln", lastName);
command.Parameters.AddWithValue("@fn", firstName);
command.Parameters.AddWithValue("@add", address);
command.Parameters.AddWithValue("@cit", city);
Ramesh Palanivel9y ago
Hi Rao,
You have an issue in this line, please change this
cmd.CommandText = "UPDATE InspInfo SET CompanyName =" + textBox1.Text + ",CustomerRef =" + textBox2.Text + ", Contact =" +textBox3.Text + ", RigName =" + textBox4.Text + ", Location =" + textBox5.Text + " WHERE WONo =" + textBox6.Text ;