Syntax error while trying to UPDATE MS Access

Question

2.33/5 (2 votes)

See more:

It says syntax error but my piece of cording seems to be ok, is there any thing wrong here?

C#

public void updatedata(string EN_NO, string MName, string MAddress, string Society, string Group, string Contact)
        {

            OleDbCommand updatenow = new OleDbCommand();

            con.Open();
            updatenow.Connection = con;
            updatenow.CommandText = "update Member set MName=?, MAddress=?, Society=?, Group=?, Contact=? where EN_NO=?";


            updatenow.Parameters.Add("EN_NO", OleDbType.VarWChar);
            updatenow.Parameters["EN_NO"].Value = EN_NO;

            updatenow.Parameters.Add("MName", OleDbType.VarWChar);
            updatenow.Parameters["MName"].Value = MName;

            updatenow.Parameters.Add("MAddress", OleDbType.VarWChar);
            updatenow.Parameters["MAddress"].Value = MAddress;

            updatenow.Parameters.Add("Society", OleDbType.VarWChar);
            updatenow.Parameters["Society"].Value = Society;

            updatenow.Parameters.Add("Group", OleDbType.VarWChar);
            updatenow.Parameters["Group"].Value = Group;

            updatenow.Parameters.Add("Contact", OleDbType.VarWChar);
            updatenow.Parameters["Contact"].Value = Contact;

            try
            {
                int count = updatenow.ExecuteNonQuery();

                if (count > 0)
                {
                    MessageBox.Show("Successfully record updated", "Methodist Church", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
            catch (OleDbException e)
            {
                MessageBox.Show(e.Message);
            }
            con.Close();

        }


        private void btnupdate_Click(object sender, EventArgs e)
        {
            updatedata(cmbno.Text, txtname.Text, txtaddress.Text, txtsociety.Text, txtgroup.Text, txtcontact.Text);
        }

Posted 17-Jan-14 1:35am

zakirox123

Add a Solution

3 solutions

Solution 2

Because the question mark is a position parameter: so they are replaced in the order that the parameter values are added. Since you add the EN_NO last in the statement and first in the parameters list, you are going to mess things up. Use named parameters instead: it saves counting!

C#

updatenow.CommandText = "update Member set MName=@MName, MAddress=@MAddress, Society=@Society, Group=@Group, Contact=@Contact where EN_NO=@ENNO";

updatenow.Parameters.AddWithValue("@EN_NO", EN_NO);
updatenow.Parameters.AddWithValue("@MName", MName);
updatenow.Parameters.AddWithValue("@MAddress", MAddress);
updatenow.Parameters.AddWithValue("@Society", Society);
updatenow.Parameters.AddWithValue("@Group", Group);
updatenow.Parameters.AddWithValue("@Contact", Contact);

Posted 17-Jan-14 1:46am

OriginalGriff

Comments

zakirox123 17-Jan-14 7:49am

no its not working still i am having the same problem

Solution 4

thanks for every one who helped me any way i sought out the problem by changing all the unique name :D

SQL

datenow.Connection = con;
            updatenow.CommandText = "update Member set mname=?, maddress=?, msociety=?, mgroup=?, mcontact=? where envno=?";


            updatenow.Parameters.Add("mname", OleDbType.VarWChar);
            updatenow.Parameters["mname"].Value = memname;

            updatenow.Parameters.Add("maddress", OleDbType.VarWChar);
            updatenow.Parameters["maddress"].Value = memadderss;

            updatenow.Parameters.Add("msociety", OleDbType.VarWChar);
            updatenow.Parameters["msociety"].Value = memsociety;

            updatenow.Parameters.Add("mgroup", OleDbType.VarWChar);
            updatenow.Parameters["mgroup"].Value = memgroup;

            updatenow.Parameters.Add("mcontact", OleDbType.VarWChar);
            updatenow.Parameters["mcontact"].Value = memcontact;

            updatenow.Parameters.Add("envno", OleDbType.VarWChar);
            updatenow.Parameters["envno"].Value = memEN_NO;

Posted 17-Jan-14 2:16am

zakirox123

Comments

Karthik_Mahalingam 17-Jan-14 8:18am

good

Mike Meinz 17-Jan-14 8:21am

Changing names did not resolve your problem.
Reordering the updatenow.Parameters.Add statements did.

zakirox123 6-Feb-14 21:22pm

i have been declared this statement already ;) even though it showed me error :) any how problem solved thanks for your help

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)

Mike Meinz · Accepted Answer · 2014-01-17T02:02:00

It appears that OLE DB .NET Provider does not support named parameters.

Try this:

C#

updatenow.Connection = con;
updatenow.CommandText = "update Member set MName=?, MAddress=?, Society=?, Group=?, Contact=? where EN_NO=?";


updatenow.Parameters.Add("MName", OleDbType.VarWChar);
updatenow.Parameters["MName"].Value = MName;

updatenow.Parameters.Add("MAddress", OleDbType.VarWChar);
updatenow.Parameters["MAddress"].Value = MAddress;

updatenow.Parameters.Add("Society", OleDbType.VarWChar);
updatenow.Parameters["Society"].Value = Society;

updatenow.Parameters.Add("Group", OleDbType.VarWChar);
updatenow.Parameters["Group"].Value = Group;

updatenow.Parameters.Add("Contact", OleDbType.VarWChar);
updatenow.Parameters["Contact"].Value = Contact;

updatenow.Parameters.Add("EN_NO", OleDbType.VarWChar);
updatenow.Parameters["EN_NO"].Value = EN_NO;

See OLEDBCommand.Parameters property[^]

Quote:
The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used

@OriginalGriff: Thanks for the tip about ? positional parameters. Never used OLDDBCommand before so looked it up in the Help file and learned.