SQL Replace function

 How do you write this statement with Subsonic:

Update 	authors
Set	city = replace(city, 'Salt', 'Olympic')

Not all qquerys can be made using the builtin in object builders.  Sometimes you just have to execute some SQL.

Dim querysql As String = "Update authorsSet city = replace(city, ''Salt'', ''Olympic'')"
        Dim queryc As New SubSonic.QueryCommand(querysql, DataService.Provider.Name)

DataService.ExecuteQuery(queryc)

Thank you.

// run a delete query instead of itterating over payments collection
SubSonic.Query qDelete = new SubSonic.Query( GenClass.Schema);
qDelete.QueryType = SubSonic.QueryType.Delete;
// add keys to delete accross
qDelete.WHERE( GenClass.Columns.aField, "aField value");
qDelete.Execute();

substitute your classes where needed. This is what I use to update and delete using subsonic.

I believe qDelete.AddUpdateSetting( field, value) will update your columns on the found set.

Found this in the forums awhile back. Your query type would be Update of course.  GenClass is a Subsonic Generated Class, and istead of typing out the field  names, I used my Subsonic object, so if I screw up my table schema names, I will get a compiler error and I keep the DB layer almost hidden from the presentation...mixed with the subsonic generated layer anyways.

 NM, I thought you were just updating a value and not calling a function.

 Philmee95, it doesn't matter, thank you for trying to help me out.

3x!My MSN:yuyejian@hotmail.com  Website:www.ucfar.cn

ExecuteReader, ExecuteDataset, or fill your ClassCollection for the news table that you generated from subsonic (NewsCollection.load( qc);

 new YourProviderName.AuthorsControlers(),update('city','Salt','Olympic');

Just easy to do it...

 Sorry, posted this on wrong thread