We have created a aspx web form in C# . We can now enter a lead in this
form and create the lead in MSCRM through CRM Web Service. CRM Web
Service is being called through a Java Script function using
"Msxml2.XMLHTTP". We are reading the form data in this function and
sending them to MSCRM through the above protocol.

Instead of writing lead information in the form, we now want to read
the information (by clicking a button or so) from another database
table and write them in to MSCRM.

Current Java Script function is reading data directly from the form and
writing to MSCRM. But we do not know how we can access a database in
the Java Script and read the records and then pass the information to
MSCRM through this function.

Can someone please guide us how we can best access external database
through a aspx page and then write the information to MSCRM?

Our client has an external Access application that they use for
creating quotes for their customers.
They want that once the quote is created in Access, we should be able
to bring quote info to opportunity in MSCRM by an automated service.
For our demo purpose, we should be able to show them that we can at
least bring the data to MSCRM by clicking a button or so on aspx page..

If anyone has done something like this, we would request your help.

Onkar,

The easiest way is to run the code at the server instead of implementing it
on the client side. Otherwise you don't need ASP.NET.

Anyway, to access a database from JavaScript, use the following as a
template:

var connection = new ActiveXObject("ADODB.Connection");
var connectionString =
"Provider=SQLOLEDB;Server=STUNNWARECRM;Database=st unnware_mscrm;Integrated
Security=sspi";
connection.Open(connectionString);

var query = "SELECT stunnwar_name FROM Filteredstunnwar_b WHERE stunnwar_bid
= '" + crmForm.ObjectId + "'";

var rs = new ActiveXObject("ADODB.Recordset");
rs.Open(query, connection, /*adOpenKeyset*/1, /*adLockPessimistic*/2);

rs.moveFirst();
var values = "";

while (!rs.eof) {
values += rs.Fields(0).Value.toString() + " ";
rs.moveNext();
}

connection.Close();

alert(values);


The code above connects to a CRM database. As you want to read from an
Access database, you need to modify the connection string appropriately.
Keep in mind that all users need to have access to the database, which is
another reason why a server-side implementation may be better.

--
Michael

Web: http://www.stunnware.com/crm2
Feed: http://www.stunnware.com/crm2/atom.aspx

----------------------------------------------------------

"Onkar" <budc57@yahoo.com> schrieb im Newsbeitrag
news:1160314668.912499.167730@e3g2000cwe.googlegro ups.com...

Thank you Michael.
We will write code to run on the server side.


Michael Höhne wrote:

Michael,

I have written server side code. It is working fine. However I am
having problem of dealing with float and Datetime data type. Data from
Quote Date and Quote Value fields from external Quote table (SQL
Database) need to go to Opportunity/Quote entity in CRM but they are
being rejected by CrmSdk throwing error message like "Can not convert
float to CrmSdk.Crmfloat"

How do I deal with Date Time and float data type while writing record
to CRM?
Any hint will be of great help.

Onkar wrote:

Onkar,

Can you provide some code? It sounds like a compiler error when trying to do
something like the following:

entity.dateField = DateTime.Now;
entity.floatField = -1;

Beside string fields, which are strings in the CRM objects as well, the data
is encapsulated in classes like CrmNumber (int), CrmFloat (float),
CrmDateTime (DateTime) and so on, so your code should look like this:

entity.dateField = new CrmDateTime();
entity.dateField.Value = dateValue.ToString("s");

entity.floatField = new CrmFloat();
entity.floatField.Value = -1;

--
Michael

Web: http://www.stunnware.com/crm2
Feed: http://www.stunnware.com/crm2/atom.aspx

----------------------------------------------------------

"Onkar" <budc57@yahoo.com> schrieb im Newsbeitrag
news:1160483655.542597.140040@c28g2000cwb.googlegr oups.com...
Michael,

I have written server side code. It is working fine. However I am
having problem of dealing with float and Datetime data type. Data from
Quote Date and Quote Value fields from external Quote table (SQL
Database) need to go to Opportunity/Quote entity in CRM but they are
being rejected by CrmSdk throwing error message like "Can not convert
float to CrmSdk.Crmfloat"

How do I deal with Date Time and float data type while writing record
to CRM?
Any hint will be of great help.

Onkar wrote:

Michael,

I have a function that inserts data in Opportunity entity in Microsoft
CRM.
I am calling this function from Button Click event, reading the
external table, and then calling the function and passing the data
values. Here is the code.
================================================== ==============

public string InsertOpportunity(string prmName, float prmQuoteTotal,
string prmQuoteNum, string prmCompetitor, string prmQuoteDesc)
{
CrmService service = new CrmService();
service.Credentials = System.Net.CredentialCache.DefaultCredentials;
service.Url =
"http://localhost:5555/MSCRMServices/2006/CrmService.asmx";

//Create opportunity object
opportunity oOpportunity = new opportunity ();

//create opportunity properties
oOpportunity.name = prmName;
oOpportunity.new_quotenumber = prmQuoteNum;

oOpportunity.new_quotetotal = new CrmFloat();
oOpportunity.new_quotetotal.Value = prmQuoteTotal;





// CrmFloat f = new CrmFloat();
// f.Value = (float) 10.1;
// f.Value = (float) 10.1;
// oOpportunity.new_quotetotal = f;

oOpportunity.new_competitor = prmCompetitor;
oOpportunity.description = prmQuoteDesc;

Customer customerid = new Customer();
customerid.Value = new Guid
("22833946-a703-da11-b44f-0003ffaaec61");
customerid.type = EntityName.account.ToString(); //this is accountid
for the customer
oOpportunity.customerid = customerid;

// oOpportunity.estimatedclosedate = prmEstCloseDate;

//create opportunity in MSCRM
Guid opportunityGuid = service.Create(oOpportunity);

return "success";

}

private void btnInsertOpp_Click(object sender, System.EventArgs e)
{

string result;

SqlConnection dataConnection = new SqlConnection ();
dataConnection.ConnectionString = "Integrated Security = true;
Initial Catalog = EMERSON; Data Source = DanubeCRM";
dataConnection.Open ();

SqlCommand dataCommand = new SqlCommand ();
dataCommand.Connection = dataConnection;
dataCommand.CommandText = "SELECT QUOTENUM,
QUOTETOTAL,COMPETITOR,QUOTEDESCRIPTION FROM QUOTE";
// dataCommand.CommandText = "SELECT
QUOTENUM,COMPETITOR,QUOTEDESCRIPTION FROM QUOTE";


SqlDataReader dataReader = dataCommand.ExecuteReader();

while (dataReader.Read())

{

string strProjectName = "This is a test Opportunity2";
string strQuoteNum = dataReader.GetString(0);
float fltQuoteTotal = dataReader.GetFloat(1); // WE ARE GETTING
STUCK HERE ON THIS LINE.
string strCompetitor = dataReader.GetString(2);
string strQuoteDesc = dataReader.GetString(3);

result =
InsertOpportunity(strProjectName,fltQuoteTotal,str QuoteNum,strCompetitor,strQuoteDesc);
// result =
InsertOpportunity(strProjectName,strQuoteNum,strCo mpetitor,strQuoteDesc);
}

dataReader.Close ();
dataConnection.Close ();
lblMessage.Text = "Opportunities are now in Microsoft CRM";

}

================================================== ==================
Michael Höhne wrote:

Onkar,

The line you have identified to be the problem isn't related to CRM. If you
get an error, it should be related to your Access database. Just to be sure,
do the following:

Replace

float fltQuoteTotal = dataReader.GetFloat(1); // WE ARE GETTING STUCK HERE
ON THIS LINE.

with

object oQuoteTotal = dataReader["QUOTETOTAL"];
float fltQuoteTotal;

//set a breakpoint here and look at the type of oQuoteTotal - is it a float?
if (oQuoteTotal is float) {
fltQuoteTotal = (float) oQuoteTotal;
}

else {
//not a float?
}

However the error you mentioned before ("Can not convert float to
CrmSdk.Crmfloat") cannot occur on that line. Do you get any other error?. Is
QUOTETOTAL a nullable field in the database?
--
Michael

Web: http://www.stunnware.com/crm2
Feed: http://www.stunnware.com/crm2/atom.aspx

----------------------------------------------------------

"Onkar" <budc57@yahoo.com> schrieb im Newsbeitrag
news:1160530028.793969.103840@i42g2000cwa.googlegr oups.com...
Michael,

I have a function that inserts data in Opportunity entity in Microsoft
CRM.
I am calling this function from Button Click event, reading the
external table, and then calling the function and passing the data
values. Here is the code.
================================================== ==============

public string InsertOpportunity(string prmName, float prmQuoteTotal,
string prmQuoteNum, string prmCompetitor, string prmQuoteDesc)
{
CrmService service = new CrmService();
service.Credentials = System.Net.CredentialCache.DefaultCredentials;
service.Url =
"http://localhost:5555/MSCRMServices/2006/CrmService.asmx";

//Create opportunity object
opportunity oOpportunity = new opportunity ();

//create opportunity properties
oOpportunity.name = prmName;
oOpportunity.new_quotenumber = prmQuoteNum;

oOpportunity.new_quotetotal = new CrmFloat();
oOpportunity.new_quotetotal.Value = prmQuoteTotal;





// CrmFloat f = new CrmFloat();
// f.Value = (float) 10.1;
// f.Value = (float) 10.1;
// oOpportunity.new_quotetotal = f;

oOpportunity.new_competitor = prmCompetitor;
oOpportunity.description = prmQuoteDesc;

Customer customerid = new Customer();
customerid.Value = new Guid
("22833946-a703-da11-b44f-0003ffaaec61");
customerid.type = EntityName.account.ToString(); //this is accountid
for the customer
oOpportunity.customerid = customerid;

// oOpportunity.estimatedclosedate = prmEstCloseDate;

//create opportunity in MSCRM
Guid opportunityGuid = service.Create(oOpportunity);

return "success";

}

private void btnInsertOpp_Click(object sender, System.EventArgs e)
{

string result;

SqlConnection dataConnection = new SqlConnection ();
dataConnection.ConnectionString = "Integrated Security = true;
Initial Catalog = EMERSON; Data Source = DanubeCRM";
dataConnection.Open ();

SqlCommand dataCommand = new SqlCommand ();
dataCommand.Connection = dataConnection;
dataCommand.CommandText = "SELECT QUOTENUM,
QUOTETOTAL,COMPETITOR,QUOTEDESCRIPTION FROM QUOTE";
// dataCommand.CommandText = "SELECT
QUOTENUM,COMPETITOR,QUOTEDESCRIPTION FROM QUOTE";


SqlDataReader dataReader = dataCommand.ExecuteReader();

while (dataReader.Read())

{

string strProjectName = "This is a test Opportunity2";
string strQuoteNum = dataReader.GetString(0);
float fltQuoteTotal = dataReader.GetFloat(1); // WE ARE GETTING
STUCK HERE ON THIS LINE.
string strCompetitor = dataReader.GetString(2);
string strQuoteDesc = dataReader.GetString(3);

result =
InsertOpportunity(strProjectName,fltQuoteTotal,str QuoteNum,strCompetitor,strQuoteDesc);
// result =
InsertOpportunity(strProjectName,strQuoteNum,strCo mpetitor,strQuoteDesc);
}

dataReader.Close ();
dataConnection.Close ();
lblMessage.Text = "Opportunities are now in Microsoft CRM";

}

================================================== ==================
Michael Höhne wrote:

Thank you Michael for your help.
But I am still missing something. Can you please give me an example
code just for the followings:

In C#

Code line for reading QUOTE TOTAL from SQL DATABASE.: DataType is
Float.
Code line for reading QUOTE ORDER DATE from SQL DATABASE: Data Type :
Date/Time

I used:

float fltQuoteTotal = DataReader.GetFloat();

and this returns float


Passing the above values to the following function to write in to CRM
database.

Function InsertOpportunity(QuoteTotal, OrderDate)

code line for writing QuoteTotal in crm
code line for writing OrderDate in crm

I am missing something on the above code lines while writing to CRM

Writing String values not a problem. I have problem with float and
date/time values.

Thank you once again




Michael Höhne wrote:

Your basic routine should be:

void InsertOpportunity(float quoteTotal, DateTime orderDate) {

CrmFloat crmQuoteTotal = new CrmFloat();
crmQuoteTotal.Value = quoteTotal;

CrmDateTime crmOrderDate = new CrmDateTime();
crmOrderDate.Value = orderDate.ToString("s");

//create appropriate CRM objects and assign the fields crmQuoteTotal and
crmOrderDate
}


--
Michael

Web: http://www.stunnware.com/crm2
Feed: http://www.stunnware.com/crm2/atom.aspx

----------------------------------------------------------

"Onkar" <budc57@yahoo.com> schrieb im Newsbeitrag
news:1160571428.723534.128770@i42g2000cwa.googlegr oups.com...
Thank you Michael for your help.
But I am still missing something. Can you please give me an example
code just for the followings:

In C#

Code line for reading QUOTE TOTAL from SQL DATABASE.: DataType is
Float.
Code line for reading QUOTE ORDER DATE from SQL DATABASE: Data Type :
Date/Time

I used:

float fltQuoteTotal = DataReader.GetFloat();

and this returns float


Passing the above values to the following function to write in to CRM
database.

Function InsertOpportunity(QuoteTotal, OrderDate)

code line for writing QuoteTotal in crm
code line for writing OrderDate in crm

I am missing something on the above code lines while writing to CRM

Writing String values not a problem. I have problem with float and
date/time values.

Thank you once again




Michael Höhne wrote:

Thanks Michael.
I will try that.

Onkar


Michael Höhne wrote:

Michael,

Thanks again. Resolved data type for Float and Date Time with your
help.
Can I request some more help from you?

After I create the opportunity in MSCRM, I would like to retrieve the
OpportunityID for this Quote (Identified by Quote Number in
OpportunityBase table) and write it back to Quote Table in external
application. I do not really know how to retrieve the Opportunity ID
from CRM. There are few examples in SDK however they are not properly
explained.

Our process is as below

Read Quote information from Quote Table in External Database (this is
done)
Create Opportunity in MSCRM with these information (this is done)
Retrieve the OpportunityID for this Opportunity from Opportunity Base
table in MSCRM and write it back to Quote Table of the external
database table (we need to do this)

How do I retrieve the OpportunityID based on the Quote Number?
Some code example will help me a lot.

Onkar

Onkar wrote:

Onkar,

The service.Create method returns the id of the newly created record, so all
you need to do is the following:

Guid opportunityId = service.Create(newOpportunity);

Simply update your database with the returned opportunityId. If you're still
looking for samples on how to do queries in CRM, look at
http://www.stunnware.com/crm2/topic....d=FindingData1,
http://www.stunnware.com/crm2/topic....d=FindingData2 and
http://www.stunnware.com/crm2/topic....d=FindingData3.

--
Michael

Web: http://www.stunnware.com/crm2
Feed: http://www.stunnware.com/crm2/atom.aspx

----------------------------------------------------------

"Onkar" <budc57@yahoo.com> schrieb im Newsbeitrag
news:1160984080.385117.46180@e3g2000cwe.googlegrou ps.com...
Michael,

Thanks again. Resolved data type for Float and Date Time with your
help.
Can I request some more help from you?

After I create the opportunity in MSCRM, I would like to retrieve the
OpportunityID for this Quote (Identified by Quote Number in
OpportunityBase table) and write it back to Quote Table in external
application. I do not really know how to retrieve the Opportunity ID
from CRM. There are few examples in SDK however they are not properly
explained.

Our process is as below

Read Quote information from Quote Table in External Database (this is
done)
Create Opportunity in MSCRM with these information (this is done)
Retrieve the OpportunityID for this Opportunity from Opportunity Base
table in MSCRM and write it back to Quote Table of the external
database table (we need to do this)

How do I retrieve the OpportunityID based on the Quote Number?
Some code example will help me a lot.

Onkar

Onkar wrote:

Hi Michael,

I seek your advise and guidance again.
Thanks for all that guidances earlier.

I now want to close an opportunity programmatically.
For example, when we change the status of Quotation in external
application to "Won" or "Lost", I would like to close the opportunity
in CRM automatically.

I have now developed the system as below:

When a quote is created in External application, our integration can
create related opportunity in CRM automatically.

when a quote is updated in External application, it is updated in CRM
as well.

Only thing I need to do now, once the quote is won or lost in External
Application, I want to close the opportunity automatically.

I have gone through the underneath tables. It seems like I need to
create activitypointer object record and also need to create a record
in OpportunityClose entity.

I am sure that your guidance will make me create the last part of our
system ie "to close the opportunity".
Thank you Michael. You have been great help to me.

Regards,
Onkar

Michael Höhne wrote:

Onkar,

You need to create a WinOpportunityRequest or LoseOpportunityRequest. Both
have an OpportunityClose property,which must be specified. I'm sure there
are samples in the microsoft.public.crm.developer newsgroup, simply search
for "OpportunityClose".

--
Michael

Web: http://www.stunnware.com/crm2
Feed: http://www.stunnware.com/crm2/atom.aspx

----------------------------------------------------------

"Onkar" <budc57@yahoo.com> schrieb im Newsbeitrag
news:1161239708.469811.220250@e3g2000cwe.googlegro ups.com...
Hi Michael,

I seek your advise and guidance again.
Thanks for all that guidances earlier.

I now want to close an opportunity programmatically.
For example, when we change the status of Quotation in external
application to "Won" or "Lost", I would like to close the opportunity
in CRM automatically.

I have now developed the system as below:

When a quote is created in External application, our integration can
create related opportunity in CRM automatically.

when a quote is updated in External application, it is updated in CRM
as well.

Only thing I need to do now, once the quote is won or lost in External
Application, I want to close the opportunity automatically.

I have gone through the underneath tables. It seems like I need to
create activitypointer object record and also need to create a record
in OpportunityClose entity.

I am sure that your guidance will make me create the last part of our
system ie "to close the opportunity".
Thank you Michael. You have been great help to me.

Regards,
Onkar

Michael Höhne wrote:

Michael,

Is it possible to do this type of connections from an onChange in a CRM
Form?

I need to check a field in the CRM database (custom) that contains an
internal ID number. If the number exist, then I have to tell the user
to try a different number.

Do you follow this?

Allen J. Pemberton
allen@pembcons.com


Michael Höhne wrote:

Yes, you can do it in an OnChange event as well.

--
Michael

Web: http://www.stunnware.com/crm2
Feed: http://www.stunnware.com/crm2/atom.aspx

----------------------------------------------------------

"Al" <allen@pembcons.com> schrieb im Newsbeitrag
news:1165271785.008934.200400@j44g2000cwa.googlegr oups.com...
Michael,

Is it possible to do this type of connections from an onChange in a CRM
Form?

I need to check a field in the CRM database (custom) that contains an
internal ID number. If the number exist, then I have to tell the user
to try a different number.

Do you follow this?

Allen J. Pemberton
allen@pembcons.com


Michael Höhne wrote: