C# Update error with OleDb

Question

0.00/5 (No votes)

See more:

Hi all,

I am new to programming, and I have started my first C# project (which is very basic), but I just need a little help with an error I am getting.

I have a C# front end and a MS Access 2003 back end (I will use SQL when I get a little more advanced.)

When I try updating a record, I get a message saying: Syntax error {missing operator) in query expression 'ContactId ='

My code is as follows:

private void btnSave_Click(object sender, EventArgs e)
        {
            OleDbConnection conn = new OleDbConnection();
            conn.ConnectionString = "PROVIDER=Microsoft.Jet.OleDb.4.0;Data Source=C:\\Users\\Antoine\\Documents\\Visual Studio 2010\\Projects\\myProject\\imyProject\\bin\\Debug\\myProject.mdb";
           
            conn.Open();

            string strUpdate = "UPDATE Contact SET ContactLastName = '" + txtLastName.Text + "' WHERE ContactId = " + txtContactId.Text + "";

            OleDbCommand cmd = new OleDbCommand(strUpdate, conn);

            cmd.ExecuteNonQuery();

            MessageBox.Show("Record updated", "Contacts", MessageBoxButtons.OK);
        }

It will be something very simple, I know, and I'm sure there's more efficient coding methods, but I'm having fun learning.

Thanks in advance for your help.

Antony

[edit]Code block added - OriginalGriff[/edit]
[edit2]Indentation corrected - OriginalGriff[/edit2]

Posted 24-Apr-11 1:43am

Antony Coulson

Updated 24-Apr-11 1:50am

OriginalGriff

v3

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

ambarishtv · Answer 1 · 2011-04-24T01:53:00

Solution 1

try this

string strUpdate = "UPDATE Contact SET ContactLastName = '" + txtLastName.Text + "' WHERE ContactId = " + txtContactId.Text;

- verify ContactLastName,ContactId is valid format
- Read this Tutorials - http://msdn.microsoft.com/en-us/library/aa288436(v=vs.71.aspx

Posted 24-Apr-11 1:53am

ambarishtv

Updated 24-Apr-11 2:03am

v3

Comments

Venkatesh Mookkan 24-Apr-11 7:56am

The quote next to txtContactId.Text is not harmful and is not the actual problem.

His query and your solution does the same thing. :)

Venkatesh Mookkan · Answer 2 · 2011-04-24T01:54:00

Solution 2

Seems like ContactId is not Numeric (Number) in the table. Check the table if it is String data type.

Posted 24-Apr-11 1:54am

Venkatesh Mookkan

v2

OriginalGriff · Answer 3 · 2011-04-24T01:56:00

The UPDATE command looks ok, if there is a valid Contact ID in your text box, and no special characters - a single space is all it would take to cause a problem. I would suggest that you use Parametrized queries anyway: they are a lot safer, and make your code easier to read:

string strUpdate = "UPDATE Contact SET ContactLastName = @CLN WHERE ContactId = @CID";
OleDbCommand cmd = new OleDbCommand(strUpdate, conn);
cmd.Parameters.AddWithValue("@CLN", txtLastName.Text);
cmd.Parameters.AddWithValue("@CID", txtContactId.Text);

This may well get rid of your error!

Antony Coulson · Answer 4 · 2011-04-24T02:13:00

Hi,

Thanks for all your suggestions.

I've just discovered that when the ContactId textbox is visible the code works.

I had it hidden, as it would seem pointless having an Id field displayed.

In response to your suggestions, I tried:

string strUpdate = "UPDATE Contact SET ContactLastName = '" + txtLastName.Text + "' WHERE ContactId = " + txtContactId.Text; with the ContactId textbox hidden again, but that never worked. Thanks anyway.

ContactId is an AutoNumber (Primary key) field in the table (apologies, I should have mentioned this in the initial post.)

I will look up paramatized queries for future work.

Why would the code work with the textbox visible, but not when hidden? (Is this a stupid question?)

Antony

C# Update error with OleDb

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0