Hi everyone,

I am trying to write a sql script to run against my database.

The issue i am having is that I need to insert a text/html module on to each Politicans profile.  Simple easy enough.

Then i need to insert an image, simple no problem

I need to make the image into a hyperlink with the URL pointing to the Politicians Rating Page. 

I have the string for the image and the URL but, the URL that points to the politicans rating page will have to change every iteration. Below is an example of the string for the image and hyperlink to the rating page.

<a href="http://r8yourpolitician.co.uk/Rate/Ratealexattwood/tabid/915/Default.aspx"><img height="105" width="229" src="/Portals/0/ratemeUK.png" alt=""/></a>

Can someone tell me how I can pull a URL for a page in the DNN database.

This url below is an example of the URL i need.

http://r8yourpolitician.co.uk/Rate/Ratealexattwood/tabid/915/Default.aspx

here is an example of the entry in the Tab table

915    49    0    Rate Alex  Attwood    0    182    1        0    Rate Alex  Attwood    Rate Alex  Attwood    Rate Alex  Attwood    0        NULL   NULL  //Rate//RateAlexAttwood    NULL    NULL    NULL    NULL    0

I would assume that the TabID ( 915) and the Tab Path are the main fields i want.  These will change for every politican.

915 +  //Rate//RateAlexAttwood

but how do i go from having "915" + "//Rate//Rate/AlexAttwood" to having

http://r8yourpolitician.co.uk/Rate/Ratealexattwood/tabid/915/Default.aspx

I am thinking that i need to do a select on the tabs table and extract these two coulmns trim the two "//" and then insert them into two variables,then when i am doing my insert of the string with the image break it up

for example

do selectand get the tabid and tabpath

var1 = tabID

var2 = TabPath

INSERT INTO [dbo].[dnnfusion_mp_Module_MyText]
           ( [UserProfileModuleId] ,[Body] )
    

VALUES
           (UserProfileModuleId, "http://r8yourpolitician.co.uk/" + TabPath + "tabid/" + tabID "/Default.aspx" ,)

The problem is that the tab path has double forward slashes in it , how can i get rid of them.  Is their a way I can clean up the TabPath so that it does not have the double forward slashes. 

The tab path comes out like this :> "//Rate//Rate/AlexAttwood" i want it to be like this  "/Rate/Rate/AlexAttwood"

any ideas community?

 please be aware, that cursor driven procedures are always tend to be less performant than select driven statements. If you are able to convert the statement to an update (with subqueries or whatever) it might be a solution. If you need to do it with a cursor, consider to execute it from business login in your module instead.