[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1330
  • Last Modified:

Access query producing error in ASP.NET - Undefined function 'Replace' in expression.

Ok i was just in the MS Access Q&A to get some help building a query for my access database that would find and replace all the line breaks in FLD_Body field of all my records...

This is my Query...
------------------------------------------------------------------------------------------------------
UPDATE TBL_Records SET FLD_Body = Replace(FLD_Body,Chr(13) & Chr(10),"<br />")
WHERE FLD_Body is not null;
------------------------------------------------------------------------------------------------------

This works fine when running it in Access... but now i am trying to execute the query from my ASP.NET page with this:

-------------------------------------------------------------------------------------------------------------------------
<%
Dim myConnection As OleDbConnection
Dim myCommand As OleDbCommand

myConnection = New OleDb.OleDbConnection(System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_Conn_Intranet"))
myConnection.Open()
myCommand = New OleDbCommand( "QRY_HTMLCode", myConnection )
myCommand.CommandType = CommandType.StoredProcedure

myCommand.ExecuteNonQuery()
myConnection.Close
%>
------------------------------------------------------------------------------------------------------------------------

The page gives me this error when trying to execute the query....

------------------------------------------------------------------------------------------------------------------
Exception Details: System.Data.OleDb.OleDbException: Undefined function 'Replace' in expression.

Source Error:

Line 14: myCommand.CommandType = CommandType.StoredProcedure
Line 15:
-->Line 16: myCommand.ExecuteNonQuery()
Line 17: myConnection.Close
Line 18: %>
---------------------------------------------------------------------------------------------------------------------

What the hell is going on here?

Nugs
0
Nugs
Asked:
Nugs
  • 4
  • 2
  • 2
  • +1
3 Solutions
 
mydasxCommented:
are you missing parameters for the stored procedure?... I.e. you are trying to execute a nonexecutable.  (insert/update) but passing nothing to insert or update.

Mydasx
0
 
NugsAuthor Commented:
it is a Update query but there is no parameter... it just runs and repalces...

Nugs
0
 
mydasxCommented:
ok says replace, replace is not a standard error, access is kicking that back, where in SP does it say replace, i.e. show me your sql code.

Mydasx
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
ayha1999Commented:
Hi,

use

Hi,

Just put this function in your page

 Function fixQuotes(ByVal theString)
        fixQuotes = Replace(theString, "'", "''")
    End Function

then

change all your items where you have replace like this

'" & fixQuotes(YourVariable) & "'

If you still get syntax error after testing it then you have to make sure that you spell correctly your database field names in the sql statement.

Use "" for numerals and '" "' for strings.

ayha
0
 
NugsAuthor Commented:
mydasx SQL for the access query is posted in the question...

------------------------------------------------------------------------------------------------------
UPDATE TBL_Records SET FLD_Body = Replace(FLD_Body,Chr(13) & Chr(10),"<br />")
WHERE FLD_Body is not null;
------------------------------------------------------------------------------------------------------

is the query that i built in Access...

Nugs
0
 
Type25Commented:
erm...shouldn't it be:  myCommand.CommandType = CommandType.Text
0
 
Type25Commented:
ah hang on, maybe not...........do you call queries throuugh acess with CommandType.StoredProcedure????
0
 
NugsAuthor Commented:
I believe so, i call another query in the DB fine with that code...
0
 
NugsAuthor Commented:
ayha,

This is a access query. The 'replace' is inside the query... How would the functions help me then?

Nugs
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now