error in stored procedure Access

Question

I have created a query which is: SELECT Replace(column_name,a,b) AS expr1 FROM table1; the name of this query is:filepath.

I have wrote the following code in C#. when i compile the code it Syntax error in PROCEDURE clause.

            OleDbCommand cmd1 = new OleDbCommand();
            cmd1.Connection= ren_connection1;
            cmd1.CommandType = CommandType.Text;
            cmd1.CommandText = "Execute filepath";

            OleDbParameter oldfilevalue = new OleDbParameter();
            oldfilevalue.ParameterName = "a";
            oldfilevalue.OleDbType = OleDbType.VarChar;
            oldfilevalue.Direction = ParameterDirection.Input;
            oldfilevalue.Value = oldname2;

            OleDbParameter newfilevalue = new OleDbParameter();
            newfilevalue.ParameterName = "b";
            newfilevalue.OleDbType = OleDbType.VarChar;
            newfilevalue.Direction = ParameterDirection.Input;
            newfilevalue.Value = oldname1;

            cmd1.Parameters.Add(oldfilevalue);
            cmd1.Parameters.Add(newfilevalue);

            i = cmd1.ExecuteNonQuery();

//oldefile value can be like this: D:/myfile/pictures/cars/ //newfile value can be like this: D:/myfile/pictures/jeeps/

i want to replace in a row a string with another string without modifying the whole row..and i thought replace will work but it didnt :(

access version is:2007.

any idea or help will be greatly appreciated.

Thanks alot.

@paolo: when i give the name of the procedure only then i get this error: .`Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'` — safi, Mar 02 '11 at 16:12

score 2 · Answer 1 · edited May 23 '17 at 11:55

2

I am afraid Replace is only available if you are running within Access, as Vincent mentions, it is a VBA function, not a Jet/ACE function. Also discussed: Exception when trying to execute "REPLACE" against MS Access. I have replied to what i think is your first question, update table access, with a possible solution.

edited May 23 '17 at 11:55

Community

1
1

answered Mar 02 '11 at 21:56

Fionnuala

90,370
7
114
152

Paolo Falabella · Accepted Answer · 2011-03-03T13:18:48.663

1

Try changing your commandtext with

cmd1.CommandText = "EXECUTE yourProcedureName";

Edit now that your procedure is invoked correctly you need to work around the missing "Replace" function (btw, have you tried Vincent Vancalbergh's suggestion to see if "Replace can be made to work? That would be much easier....)

What I was saying in the comments is that you could select the content of the table, perform the replace in c# code and (if needed) update your table with the new values.

your select becomes:

SELECT table1_id, column_name FROM table1;

and your code changes like this:

//you should change ExecuteNonQuery to ExecuteReader, since you want 
// to read the results of your SELECT 
OleDbDataReader rdr= cmd1.ExecuteReader();

//Iterate through the table
while(rdr.Read())
{
   string currentValue=rdr["column_name"].ToString();
   string newValue = currentValue.Replace(a, b);

   //now do what you need with the row
   // ...
}

edited Mar 03 '11 at 13:18

answered Mar 02 '11 at 16:10

Paolo Falabella

24,914
3
72
86

yes and now i get another error: `Undefined function 'Replace' in expression.` this is defined in the access, i have put it already in the question if you can check it. – safi Mar 02 '11 at 16:15
3

Seems on this page (http://www.bigresource.com/Tracker/Track-ms_access-cjK5N78g/) that REPLACE cannot be called this way. – Vincent Vancalbergh Mar 02 '11 at 16:34
@safi I'm afraid the Oledb provider doesn't understand the "replace" function. Would it be feasible for you to SELECT all rows and do the replace in C# code? – Paolo Falabella Mar 02 '11 at 16:35
@paolo, yes i also searched and finally know that it does not support this functionality. how should i do it in c#? you mean writing the replace function here? or ? – safi Mar 02 '11 at 16:41
@Vincent Vancalbergh I check it and yes you are right, i am using Access and it is not working. i want to replace in a row a string with another string without modifying the whole row..and i thought replace will work but it didnt :( – safi Mar 02 '11 at 16:48
@safi judging by the code in your question, you're only doing a SELECT. After reading the rows, are you only expecting to display them in your application or do you need to write the results of the "replace" back to Access? – Paolo Falabella Mar 02 '11 at 16:57
@Paolo, I am doing a replace and i think it will be replaced or do i need to update is as well. but the replace is not working, so i am now thinking of another soloution :) or you think some thing can be done in the current solution – safi Mar 02 '11 at 17:24
@safi by doing a SELECT the string will not be written in the Access DB, you would need an UPDATE for that. I'm not sure why you would want to replace the string in the DB every time you run the query, though. If you post some more info on what you want to do with the data I or others may be able to assist more. – Paolo Falabella Mar 03 '11 at 09:27
@paolo well i am running this query every time because i am updating corresponding column, and i also need to update this as well. the corresponding column update easily as i do not need to update the substring, but in the column i just need to update the substring as the request contains not full info. – safi Mar 03 '11 at 10:12
1

@safi I updated the answer (writing code in comments is too hard...) – Paolo Falabella Mar 03 '11 at 11:21

score 1 · Answer 3 · answered Mar 02 '11 at 16:41

I found the following here:

Prior to a company wide upgrade to XP there was no problem with access databases, so I am not sure if this would be a solution to your issue. But I had a problem similar to yours after an upgrade to XP from 2000 with some access databases. Undefined Function "Replace" errors started to pop up. At the end of the day it turned out to be the version of VBA installed. 6.0 versus 6.3. The problem machines had 6.0 installed. Start access Help -> About MS Access -> SYSTEM INFO -> APPLICATIONS -> Microsoft Access 2000 -> SUMMARY. The VBA version of 6.00 produced the error, VBA version 6.03 no problem. Take Care, Nick

Now the question is, what VBA version are you using?

Thanks Vincent, i follow the Paolo Solution and it did helped, I mean around this.. — safi, Mar 03 '11 at 13:05

score 1 · Answer 4 · edited May 23 '17 at 12:11

1

The solution is in @onedaywhen's answer her:

Exception when trying to execute "REPLACE" against MS Access

It uses Mid(), Len() and InStr() in place of Replace().

edited May 23 '17 at 12:11

Community

1
1

answered Mar 04 '11 at 04:08

David-W-Fenton

22,871
4
45
58

If you like my pointer to the answer, be sure to upvote the answer I'm pointing you to! – David-W-Fenton Mar 05 '11 at 21:12

error in stored procedure Access

4 Answers4

Linked