using the replace in update query in msacess database

Question

0.00/5 (No votes)

See more:

hi morning

From the Yesterdays evening i am try to Achieve My Requirement,

it was the small thing but it was Creating the problem

so let me explain the problem,

i want to replace Function in the update query to msacess Database(2003),
this was I had tried so please see that and say where i am Going wrong
it was showing Error as the "Undefined function Replace In the statement"

update addcust set grou=Replace(grou, '" + comboBox1.SelectedItem.ToString() + "', '" + comboBox2.SelectedItem.ToString() + "') where id=" + saaa[i] + "

this is the query i am using
here grou and id is the columnNames
addcust is the table Name

please show the rightway to me , thankq alot;

Posted 29-Sep-11 19:25pm

sivakumarmr10

Updated 29-Sep-11 22:22pm

v2

Add a Solution

Comments

Bala Selvanayagam 30-Sep-11 4:16am

What version of access you are using 2000/2003 or 2007 ?

[no name] 30-Sep-11 4:21am

2003

Bala Selvanayagam 1-Oct-11 7:50am

let me know whether the solution i have posted works for you ?

3 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)

Om Prakash Pant · Answer 1 · 2011-09-29T21:03:00

Solution 1

I think replace() function does not work outside the MS Access environment. check the following link for some of the options:

http://www.tek-tips.com/viewthread.cfm?qid=926348&page=485[^]

Posted 29-Sep-11 21:03pm

Om Prakash Pant

Morgs Morgan · Answer 2 · 2011-09-29T21:13:00

Solution 2

Hi, I have no clue why you are using that Replace but an update statement is fairly basic and could look something like:

C#

String updateQuery = @"UPDATE addcust SET grou='" + comboBox1.SelectedItem.ToString() + "' WHERE id=" + saaa[i] + ";";

For a much complex tutorial on this, please go here:
http://office.microsoft.com/en-us/access-help/update-data-by-using-a-query-HA010076527.aspx[^]

Hope that helps,
Morgs

Posted 29-Sep-11 21:13pm

Morgs Morgan

Comments

[no name] 30-Sep-11 4:19am

i had a situtation to replace the string in the Updatestatement

Morgs Morgan 30-Sep-11 4:35am

But does the above solution resolves your problem? To replace a string you would for instance say:
comboBox1.SelectedItem.ToString().Replace("a", "b");

Let me know if this problem is now resolved.

Bala Selvanayagam · Answer 3 · 2011-10-01T01:49:00

I am afraid the replace statement does not support MS access environment as stated by "Om Prakash Pant" and suggest the followings code - you may have to do bit of cleaning

try
  {

      OleDbConnection oConn = new OleDbConnection();
      oConn.ConnectionString ="Provider =Microsoft.Jet.OLEDB.4.0; Data Source =C:\\TEST PROJECTS\\ACCESS 2003\\db1.mdb";
      oConn.Open();


      OleDbCommand oCmd = new OleDbCommand();
      oCmd.Connection = oConn;
      oCmd.CommandText = "Select grou from addcust where ID=1";

      String strgrou = "";

      using (var oReader = oCmd.ExecuteReader())
      {
          while (oReader.Read())
          {
              strgrou = oReader["grou"].ToString();
          }
      }


      string str1="a";
      string str2="b";

      oCmd.Connection = oConn;
      oCmd.CommandText = "update addcust set grou= '" + strgrou.Replace(str1, str2) + "' where id=1 ";

      oCmd.ExecuteNonQuery();


      MessageBox.Show("success");
  }
  catch (Exception ex)
  {

      MessageBox.Show(ex.Message);
  }