Integrate CRM with WSS 3.0 Picture Library

I worked for a project which needs integrate CRM with WSS 3.0 picture library.
The requirement was: Each account has it's own picture library in WSS, so the pictures could be use in CRM report etc.
Here's the code:

public partial class _Default : System.Web.UI.Page
{
    string accountGUID = "";
    protected void Page_Load(object sender, EventArgs e)
    {
        string entityId = Request.QueryString["oId"];
        Guid accountId = (entityId == null) ? (new Guid("00000000-0000-0000-0000-000000000000")) : new Guid(entityId);
        accountGUID = accountId.ToString();
        string url = "http://WSS:6666/PL/" + accountGUID; // PL is a picture library in WSS 3.0

        if (CheckUrl(url))
        {
            Response.Redirect(url);
        }
        else
        {
            Label1.Text = "This account doesn't has a Pictures Library, please create a one for it.";
        }
    }

    public static bool CheckUrl(string url)
    {
        //check if the url(pictures library) is existing
        HttpWebResponse httpResponse = null;

        try
        {
            HttpWebRequest httpRequest = (HttpWebRequest)WebRequest.Create(url);
            httpRequest.Credentials = System.Net.CredentialCache.DefaultCredentials;
            httpResponse = (HttpWebResponse)httpRequest.GetResponse();
            return (httpResponse.StatusCode == System.Net.HttpStatusCode.OK);
        }
        catch (Exception ex)
        {
            return false;
        }
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        //Response.Redirect("http://WSS:6666/PL/Forms/Upload.aspx?Type=1");
        SPSite Site = new SPSite("http://WSS:6666"); //site url
        SPWeb Web = Site.OpenWeb();
        Site.AllowUnsafeUpdates = true;
        Web.AllowUnsafeUpdates = true;
        SPFolder rootFolder = Web.GetFolder("PL"); // PL is a picture library in WSS 3.0
        rootFolder.SubFolders.Add(accountGUID);
        Page_Load(null, null);
    }
}

Accessing CRM database from JScript

Here's the code how to accesss CRM database via JScript!

//initial connection
var connection = new ActiveXObject("ADODB.Connection");
var connectionString = "Provider=SQLOLEDB;Server=_db;Database=_mscrm;Integrated Security=sspi";
//open connection
connection.Open(connectionString);
//query string
var query = "SELECT name FROM FilteredAccount";
//create a ADO object
var rs = new ActiveXObject("ADODB.Recordset");
//open connection
rs.Open(query, connection, 1, 2);
rs.moveFirst();
var values = "";
//read data
while (!rs.eof)
{
    values += rs.Fields(0).Value.toString() + " ";
    rs.moveNext();
}
//close connection
connection.Close();
//alert value
alert(values);

Age calculate by Birthday

if(crmForm.all.birthdate.DataValue != null)
{
    var now = new Date();
    var birthday = crmForm.all.birthdate.DataValue;
    var monthdif = now.getMonth() - birthday.getMonth();
    if(monthdif > -1)
        crmForm.all.new_age.DataValue = now.getYear() - birthday.getYear();
    else
        crmForm.all.new_age.DataValue = now.getYear() - birthday.getYear() - 1;
}

Uninstall WSS 3.0 MICROSOFT##SSEE Database

Sometimes we need to uninstall WSS 3.0 database manually, here is the way:
1. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall
2. Look for each {GUID} which Display Name = "Microsoft SQL 2005 Embedded Edition"
3. Run: MsiExec.exe /X {GUID} CALLERID=OCSETUP.EXE

WORD TIPS 1 – Find and Replace

History: XXXX

History 02): XXXX

History 03): XXXX


Use Find and Replace (Use wildcards):

Find what: ^13History ([0-9][0-9])

Replace with: , \1

History: XXXX , 02): XXXX , 03): XXXX

EXCEL Tips 2

'Macro to split cells at the comma

Sub SplitName()
Dim cell As Range
Dim separator As Integer
'loop through each cell in the selection
For Each cell In Selection
'search for a comma
separator = InStr(cell.Value, ",")
If separator > 0 Then
'put the lastname in the first column to the right
cell.Offset(0, 1).Value = Left(cell.Value, separator - 1)
'put the firstnames in the second column to the right
cell.Offset(0, 2).Value = Mid(cell.Value, separator + 1)
End If
Next cell
End Sub

'Macro to join 2 cells

Sub Join()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic
On Error Resume Next
Dim irows As Long, mrows As Long, ir As Long, ic As Long
irows = Selection.Rows.Count
Set lastcell = Cells.SpecialCells(xlLastCell)
mrow = lastcell.Row
If mrow < irows =" mrow" icols =" Selection.Columns.Count" ir =" 1" newcell =" Trim(Selection.Item(ir," ic =" 2" trimmed =" Trim(Selection.Item(ir,"> 0 Then newcell = newcell & " " & trimmed
Selection.Item(ir, ic) = ""
Next ic
Selection.Item(ir, 1).Value = newcell
Next ir
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

EXCEL Tips 1

User	Hobby	Read	Internet	Music	Computer
User1	Reading,Internet,Music	Yes	Yes	Yes	No
User2	Internet,Computer,Reading	Yes	Yes	No	Yes
User3		No	No	No	No
User4	Music	No	No	Yes	No

=IF(ISERR(FIND(C$1,$B2)),"No","Yes")

IE7 Window Close Alert solution:

Solution 1: http://support.microsoft.com/kb/911328/
Solution 2: edit default.aspx, modify:

if (window.name != "MSCRM_MAIN")
{
  //var oMe = window.self;
  //oMe.opener = window.self;
  //oMe.close();
  window.open('','_parent','');
  window.close();
}

Funny: How about if I sent email to wrong person!

One of my friends ask me: she sent an email to a wrong person 10 minutes ago, there were many secrets in this email, so she does't want that person see it!

Unfortunatelly, there are no UN-DO function after you have sent this email. Of cause you may contact with his email account provider(such like webmaster@yahoo.com), and explain your situation, and then maybe they would remove it before that person saw it. Luck you!

If you don't want to do that, there are another trick way: How about send him other 100 emails with same title of your first one, but the email body is different(Just say blesss him)? What he will do after open his inbox? --Saw the last email you sent to him and get your bless, and then delete other emails you sent to him(becasue it looks 'all same') .

So, how you do this?

1. Manually send 100 emails? NO!
2. Buy software? NO!
3. Ask your friend to help you? NO!

The simplest way is using a script, but you must make sure your computer which will run this script is already be SMTP supported. Open the Notepad, and copy the following codes to Notepad.

set Msg = CreateObject("CDO.Message")

with Msg

.from = "His Name"

.to = "Your Name"

.subject = "My Secrets!"

.textbody = "God Bless You!"

end with

DIM i

FOR i = 1 TO 100

Msg.Send

NEXT

Msgbox "Email has been sent."

Save the above codes as send.vbs, and run it on your server, then he will reveive your 100 copies of the same messages. Please make sure you are using the Same Name, the Same Email Address of Yours, and the Same Email Title.

DO NOT USE IT AS SPAM!!!

Consulting: Microsoft CRM 3.0

At the begining of this month, I went to Bournemouth to do a consulting project for Microsoft CRM 3.0.

That company has been installed CRM 1.2 on a Windows 2003 server which was a member of domain, now they want to re-deploy CRM 3.0 on the DC which is SBS 2003 and remove the Windows 2003 server from domain. Obviously, that wasn't a good idea. However, for the license and other issues, they want to install CRM 3.0 on the SBS 2003 DC.

The whole process includes: system backup, data export from CRM 1.2, uninstall CRM 1.2 for both server and clients, upgrade SBS 2003 DC, troubleshooting for DC, DHCP, DNS, SQL Server and System Events. I have to deal with the third party's drivers problem after complete the system upgrade. And also, install CRM 3.0 SBS version on the DC and migrate all the data to CRM 3.0 database. Finally, deploy CRM 3.0 for outlook clients.

From this project, I can see that Microsoft CRM 3.0, externally looks good, but internally, there are many places need to be improved. On the other side, maybe that's a good news for CRM ISVs. ;-)


CRM30 Consulting Services Homepage (United Kindom)