Error in update query in c# for access database

Question

I am facing error in following Query.According to my knowledge I have written everything perfectly fine. But its giving error that:

"there is an error in update query"

string insert_query = "update aho set read=?,pick=? where Cont_no='" + contract_no + "'";
OleDbCommand ocmd = new OleDbCommand();
ocmd.CommandText = insert_query;
//ocmd.Parameters.AddWithValue("@contrct_no", contract.Text.ToString());
ocmd.Parameters.AddWithValue("@read_val", Convert.ToInt32(read.Text));
ocmd.Parameters.AddWithValue("@pic_val", Convert.ToInt32(pick.Text));
ocmd.Connection = conn;
ocmd.ExecuteNonQuery();

What is your insert_query looks like when you debug it?

Soner Gönül
– Soner Gönül

2013-10-31 12:27:25 +00:00
Commented Oct 31, 2013 at 12:27 — Soner Gönül
– Soner Gönül, Commented Oct 31, 2013 at 12:27

Community · Accepted Answer · 2017-05-23 10:25:47Z

1

You didn't gave us too much information but..

I think your Cont_no type is some numerical type, not one of the character type. Looks like that's why you get error when you try to add it with ''.

For example like;

Cont_no = '123'

Try this one;

string insert_query = "update aho set [read]=?,pick=? where Cont_no=?";
OleDbCommand ocmd = new OleDbCommand();
ocmd.CommandText = insert_query;
ocmd.Parameters.AddWithValue("@read_val", Convert.ToInt32(read.Text));
ocmd.Parameters.AddWithValue("@pic_val", Convert.ToInt32(pick.Text));
ocmd.Parameters.AddWithValue("@contrct_no", contract_no);
ocmd.Connection = conn;
ocmd.ExecuteNonQuery();

EDIT: HansUp is totally right. Read is a reserved keyword. You should use it with square brackets like [Read] in your query.

edited May 23, 2017 at 10:25

CommunityBot

11 silver badge

answered Oct 31, 2013 at 12:38

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user2927881 Over a year ago

Cont_no is string. and your provided solution is still not working

Soner Gönül Over a year ago

@user2927881 What is your insert_query looks like when you debug it?

Soner Gönül Over a year ago

@user2927881 I'm not asking exception. I'm asking when you look at insert_query in debugger, what you see ?

user2927881 Over a year ago

i have put break points but its not taking me to debugger and poping up the exception.

HansUp Over a year ago

Read is a reserved word. Bracket it in the statement ... set [read]=?

|

wind39 · Accepted Answer · 2013-10-31 12:35:50Z

0

In your query string you consider parameters by priority, but when you create them you are giving them a name.

According to http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.parameters.aspx, OleDbCommand does not support named parameters.

Look at this example (source: http://www.java2s.com/Code/CSharp/Database-ADO.net/PassparametertoOleDbCommand.htm):

    using System;
    using System.Data;
    using System.Data.OleDb;

    public class Prepare {    
     public static void Main () { 
       String connect = "Provider=Microsoft.JET.OLEDB.4.0;data source=.\\Employee.mdb";
       OleDbConnection con = new OleDbConnection(connect);
       con.Open();  
       Console.WriteLine("Made the connection to the database");

       OleDbCommand cmd1 = con.CreateCommand();
       cmd1.CommandText = "SELECT ID FROM Employee "
                        + "WHERE id BETWEEN ? AND ?";
       OleDbParameter p1 = new OleDbParameter();
       OleDbParameter p2 = new OleDbParameter();
       cmd1.Parameters.Add(p1);
       cmd1.Parameters.Add(p2);
       p1.Value = "01";
       p2.Value = "03";
       OleDbDataReader reader = cmd1.ExecuteReader();
       while(reader.Read()) 
         Console.WriteLine("{0}", reader.GetInt32(0));
       reader.Close();
       con.Close();
     }
    }

answered Oct 31, 2013 at 12:35

wind39

4514 silver badges15 bronze badges

1 Comment

user2927881 Over a year ago

when i am doing it with priority order.its still not working.

Collectives™ on Stack Overflow

Error in update query in c# for access database

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related