CODEWITHSUNDEEP
c#ms-access
safi
safi

Reputation: 3766

error in stored procedure Access

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.

Upvotes: 0

Views: 1095

Answers (4)

David-W-Fenton
David-W-Fenton

Reputation: 23067

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().

Upvotes: 1

Paolo Falabella
Paolo Falabella

Reputation: 25854

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
   // ...
}

Upvotes: 1

Fionnuala
Fionnuala

Reputation: 91376

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.

Upvotes: 2

Vincent Vancalbergh
Vincent Vancalbergh

Reputation: 3337

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?

Upvotes: 1

Related Questions