Need to access external database and bring info to MSCRM

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.

0
budc57 (3)
10/8/2006 1:37:48 PM
crm 35858 articles. 1 followers. Follow

15 Replies
502 Views

Similar Articles

[PageSpeed] 47

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=stunnware_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.googlegroups.com...
> 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.
> 


0
Michael
10/8/2006 1:59:52 PM
Thank you Michael.
We will write code to run on the server side.


Michael H=F6hne wrote:
> 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 =3D new ActiveXObject("ADODB.Connection");
> var connectionString =3D
> "Provider=3DSQLOLEDB;Server=3DSTUNNWARECRM;Database=3Dstunnware_mscrm;Int=
egrated
> Security=3Dsspi";
> connection.Open(connectionString);
>
> var query =3D "SELECT stunnwar_name FROM Filteredstunnwar_b WHERE stunnwa=
r_bid
> =3D '" + crmForm.ObjectId + "'";
>
> var rs =3D new ActiveXObject("ADODB.Recordset");
> rs.Open(query, connection, /*adOpenKeyset*/1, /*adLockPessimistic*/2);
>
> rs.moveFirst();
> var values =3D "";
>
> while (!rs.eof) {
>     values +=3D 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.googlegroups.com...
> > 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.
> >

0
Onkar
10/9/2006 12:33:21 PM
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:

> Thank you Michael.
> We will write code to run on the server side.
>
>
> Michael H=F6hne wrote:
> > Onkar,
> >
> > The easiest way is to run the code at the server instead of implementin=
g 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 =3D new ActiveXObject("ADODB.Connection");
> > var connectionString =3D
> > "Provider=3DSQLOLEDB;Server=3DSTUNNWARECRM;Database=3Dstunnware_mscrm;I=
ntegrated
> > Security=3Dsspi";
> > connection.Open(connectionString);
> >
> > var query =3D "SELECT stunnwar_name FROM Filteredstunnwar_b WHERE stunn=
war_bid
> > =3D '" + crmForm.ObjectId + "'";
> >
> > var rs =3D new ActiveXObject("ADODB.Recordset");
> > rs.Open(query, connection, /*adOpenKeyset*/1, /*adLockPessimistic*/2);
> >
> > rs.moveFirst();
> > var values =3D "";
> >
> > while (!rs.eof) {
> >     values +=3D 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.googlegroups.com...
> > > We have created a aspx web form in C# . We can now enter a lead in th=
is
> > > 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 a=
nd
> > > 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=
..=2E
> > >
> > > If anyone has done something like this, we would request your help.
> > >

0
Onkar
10/10/2006 12:34:15 PM
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.googlegroups.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:

> Thank you Michael.
> We will write code to run on the server side.
>
>
> Michael H�hne wrote:
> > 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=stunnware_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.googlegroups.com...
> > > 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.
> > >


0
Michael
10/10/2006 10:11:42 PM
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.
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

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

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

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

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





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

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

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

	//		oOpportunity.estimatedclosedate =3D prmEstCloseDate;

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

			return "success";

		}

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

			string result;

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

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


			SqlDataReader dataReader =3D dataCommand.ExecuteReader();

			while (dataReader.Read())

			{

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

				result =3D
InsertOpportunity(strProjectName,fltQuoteTotal,strQuoteNum,strCompetitor,st=
rQuoteDesc);
			//	result =3D
InsertOpportunity(strProjectName,strQuoteNum,strCompetitor,strQuoteDesc);
			}

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

		}

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Michael H=F6hne wrote:

> Onkar,
>
> Can you provide some code? It sounds like a compiler error when trying to=
 do
> something like the following:
>
> entity.dateField =3D DateTime.Now;
> entity.floatField =3D -1;
>
> Beside string fields, which are strings in the CRM objects as well, the d=
ata
> is encapsulated in classes like CrmNumber (int), CrmFloat (float),
> CrmDateTime (DateTime) and so on, so your code should look like this:
>
> entity.dateField =3D new CrmDateTime();
> entity.dateField.Value =3D dateValue.ToString("s");
>
> entity.floatField =3D new CrmFloat();
> entity.floatField.Value =3D -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.googlegroups.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:
>
> > Thank you Michael.
> > We will write code to run on the server side.
> >
> >
> > Michael H=F6hne wrote:
> > > Onkar,
> > >
> > > The easiest way is to run the code at the server instead of implement=
ing
> > > 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 =3D new ActiveXObject("ADODB.Connection");
> > > var connectionString =3D
> > > "Provider=3DSQLOLEDB;Server=3DSTUNNWARECRM;Database=3Dstunnware_mscrm=
;Integrated
> > > Security=3Dsspi";
> > > connection.Open(connectionString);
> > >
> > > var query =3D "SELECT stunnwar_name FROM Filteredstunnwar_b WHERE
> > > stunnwar_bid
> > > =3D '" + crmForm.ObjectId + "'";
> > >
> > > var rs =3D new ActiveXObject("ADODB.Recordset");
> > > rs.Open(query, connection, /*adOpenKeyset*/1, /*adLockPessimistic*/2);
> > >
> > > rs.moveFirst();
> > > var values =3D "";
> > >
> > > while (!rs.eof) {
> > >     values +=3D 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 appropriate=
ly.
> > > 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.googlegroups.com...
> > > > 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 ab=
le
> > > > 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.
> > > >

0
Onkar
10/11/2006 1:27:08 AM
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.googlegroups.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,strQuoteNum,strCompetitor,strQuoteDesc);
// result =
InsertOpportunity(strProjectName,strQuoteNum,strCompetitor,strQuoteDesc);
}

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

}

====================================================================
Michael H�hne 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.googlegroups.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:
>
> > Thank you Michael.
> > We will write code to run on the server side.
> >
> >
> > Michael H�hne wrote:
> > > 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=stunnware_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.googlegroups.com...
> > > > 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.
> > > >


0
Michael
10/11/2006 7:39:41 AM
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 =3D 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=F6hne wrote:

> Onkar,
>
> The line you have identified to be the problem isn't related to CRM. If y=
ou
> get an error, it should be related to your Access database. Just to be su=
re,
> do the following:
>
> Replace
>
> float fltQuoteTotal =3D dataReader.GetFloat(1); // WE ARE GETTING STUCK H=
ERE
> ON THIS LINE.
>
> with
>
> object oQuoteTotal =3D dataReader["QUOTETOTAL"];
> float fltQuoteTotal;
>
> //set a breakpoint here and look at the type of oQuoteTotal - is it a flo=
at?
> if (oQuoteTotal is float) {
>     fltQuoteTotal =3D (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.googlegroups.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.
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>
> public string InsertOpportunity(string prmName, float prmQuoteTotal,
> string prmQuoteNum, string prmCompetitor, string prmQuoteDesc)
> {
> CrmService service =3D new CrmService();
> service.Credentials =3D System.Net.CredentialCache.DefaultCredentials;
> service.Url =3D
> "http://localhost:5555/MSCRMServices/2006/CrmService.asmx";
>
> //Create opportunity object
> opportunity oOpportunity =3D new opportunity ();
>
> //create opportunity properties
> oOpportunity.name =3D prmName;
> oOpportunity.new_quotenumber =3D prmQuoteNum;
>
> oOpportunity.new_quotetotal =3D new CrmFloat();
> oOpportunity.new_quotetotal.Value =3D prmQuoteTotal;
>
>
>
>
>
> // CrmFloat f =3D new CrmFloat();
> // f.Value =3D (float) 10.1;
> // f.Value =3D (float) 10.1;
> // oOpportunity.new_quotetotal =3D f;
>
> oOpportunity.new_competitor =3D prmCompetitor;
> oOpportunity.description =3D prmQuoteDesc;
>
> Customer customerid =3D new Customer();
> customerid.Value =3D new Guid
> ("22833946-a703-da11-b44f-0003ffaaec61");
> customerid.type =3D EntityName.account.ToString(); //this is accountid
> for the customer
> oOpportunity.customerid =3D customerid;
>
> // oOpportunity.estimatedclosedate =3D prmEstCloseDate;
>
> //create opportunity in MSCRM
> Guid opportunityGuid =3D service.Create(oOpportunity);
>
> return "success";
>
> }
>
> private void btnInsertOpp_Click(object sender, System.EventArgs e)
> {
>
> string result;
>
> SqlConnection dataConnection =3D new SqlConnection ();
> dataConnection.ConnectionString =3D "Integrated Security =3D true;
> Initial Catalog =3D EMERSON; Data Source =3D DanubeCRM";
> dataConnection.Open ();
>
> SqlCommand dataCommand =3D new SqlCommand ();
> dataCommand.Connection =3D dataConnection;
> dataCommand.CommandText =3D "SELECT QUOTENUM,
> QUOTETOTAL,COMPETITOR,QUOTEDESCRIPTION FROM QUOTE";
> // dataCommand.CommandText =3D "SELECT
> QUOTENUM,COMPETITOR,QUOTEDESCRIPTION FROM QUOTE";
>
>
> SqlDataReader dataReader =3D dataCommand.ExecuteReader();
>
> while (dataReader.Read())
>
> {
>
> string strProjectName =3D "This is a test Opportunity2";
> string strQuoteNum =3D dataReader.GetString(0);
> float fltQuoteTotal =3D dataReader.GetFloat(1); // WE ARE GETTING
> STUCK HERE ON THIS LINE.
> string strCompetitor =3D dataReader.GetString(2);
> string strQuoteDesc =3D dataReader.GetString(3);
>
> result =3D
> InsertOpportunity(strProjectName,fltQuoteTotal,strQuoteNum,strCompetitor,=
strQuoteDesc);
> // result =3D
> InsertOpportunity(strProjectName,strQuoteNum,strCompetitor,strQuoteDesc);
> }
>
> dataReader.Close ();
> dataConnection.Close ();
> lblMessage.Text =3D "Opportunities are now in Microsoft CRM";
>
> }
>
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> Michael H=F6hne wrote:
>
> > Onkar,
> >
> > Can you provide some code? It sounds like a compiler error when trying =
to
> > do
> > something like the following:
> >
> > entity.dateField =3D DateTime.Now;
> > entity.floatField =3D -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 =3D new CrmDateTime();
> > entity.dateField.Value =3D dateValue.ToString("s");
> >
> > entity.floatField =3D new CrmFloat();
> > entity.floatField.Value =3D -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.googlegroups.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:
> >
> > > Thank you Michael.
> > > We will write code to run on the server side.
> > >
> > >
> > > Michael H=F6hne wrote:
> > > > 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 =3D new ActiveXObject("ADODB.Connection");
> > > > var connectionString =3D
> > > > "Provider=3DSQLOLEDB;Server=3DSTUNNWARECRM;Database=3Dstunnware_msc=
rm;Integrated
> > > > Security=3Dsspi";
> > > > connection.Open(connectionString);
> > > >
> > > > var query =3D "SELECT stunnwar_name FROM Filteredstunnwar_b WHERE
> > > > stunnwar_bid
> > > > =3D '" + crmForm.ObjectId + "'";
> > > >
> > > > var rs =3D new ActiveXObject("ADODB.Recordset");
> > > > rs.Open(query, connection, /*adOpenKeyset*/1, /*adLockPessimistic*/=
2);
> > > >
> > > > rs.moveFirst();
> > > > var values =3D "";
> > > >
> > > > while (!rs.eof) {
> > > >     values +=3D 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, wh=
ich
> > > > 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.googlegroups.com...
> > > > > 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 a=
nd
> > > > > sending them to MSCRM through the above protocol.
> > > > >
> > > > > Instead of writing lead information in the form, we now want to r=
ead
> > > > > 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 fo=
rm
> > > > > 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 datab=
ase
> > > > > 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 servi=
ce.
> > > > > 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 hel=
p=2E
> > > > >

0
Onkar
10/11/2006 12:57:08 PM
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.googlegroups.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:

> 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.googlegroups.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,strQuoteNum,strCompetitor,strQuoteDesc);
> // result =
> InsertOpportunity(strProjectName,strQuoteNum,strCompetitor,strQuoteDesc);
> }
>
> dataReader.Close ();
> dataConnection.Close ();
> lblMessage.Text = "Opportunities are now in Microsoft CRM";
>
> }
>
> ====================================================================
> Michael H�hne 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.googlegroups.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:
> >
> > > Thank you Michael.
> > > We will write code to run on the server side.
> > >
> > >
> > > Michael H�hne wrote:
> > > > 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=stunnware_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.googlegroups.com...
> > > > > 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.
> > > > >


0
Michael
10/11/2006 2:01:01 PM
Thanks Michael.
I will try that.

Onkar


Michael H=F6hne wrote:

> Your basic routine should be:
>
> void InsertOpportunity(float quoteTotal, DateTime orderDate) {
>
>     CrmFloat crmQuoteTotal =3D new CrmFloat();
>     crmQuoteTotal.Value =3D quoteTotal;
>
>     CrmDateTime crmOrderDate =3D new CrmDateTime();
>     crmOrderDate.Value =3D 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.googlegroups.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 =3D 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=F6hne 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 =3D dataReader.GetFloat(1); // WE ARE GETTING STUCK=
 HERE
> > ON THIS LINE.
> >
> > with
> >
> > object oQuoteTotal =3D dataReader["QUOTETOTAL"];
> > float fltQuoteTotal;
> >
> > //set a breakpoint here and look at the type of oQuoteTotal - is it a
> > float?
> > if (oQuoteTotal is float) {
> >     fltQuoteTotal =3D (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=
?=2E
> > 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.googlegroups.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.
> > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> >
> > public string InsertOpportunity(string prmName, float prmQuoteTotal,
> > string prmQuoteNum, string prmCompetitor, string prmQuoteDesc)
> > {
> > CrmService service =3D new CrmService();
> > service.Credentials =3D System.Net.CredentialCache.DefaultCredentials;
> > service.Url =3D
> > "http://localhost:5555/MSCRMServices/2006/CrmService.asmx";
> >
> > //Create opportunity object
> > opportunity oOpportunity =3D new opportunity ();
> >
> > //create opportunity properties
> > oOpportunity.name =3D prmName;
> > oOpportunity.new_quotenumber =3D prmQuoteNum;
> >
> > oOpportunity.new_quotetotal =3D new CrmFloat();
> > oOpportunity.new_quotetotal.Value =3D prmQuoteTotal;
> >
> >
> >
> >
> >
> > // CrmFloat f =3D new CrmFloat();
> > // f.Value =3D (float) 10.1;
> > // f.Value =3D (float) 10.1;
> > // oOpportunity.new_quotetotal =3D f;
> >
> > oOpportunity.new_competitor =3D prmCompetitor;
> > oOpportunity.description =3D prmQuoteDesc;
> >
> > Customer customerid =3D new Customer();
> > customerid.Value =3D new Guid
> > ("22833946-a703-da11-b44f-0003ffaaec61");
> > customerid.type =3D EntityName.account.ToString(); //this is accountid
> > for the customer
> > oOpportunity.customerid =3D customerid;
> >
> > // oOpportunity.estimatedclosedate =3D prmEstCloseDate;
> >
> > //create opportunity in MSCRM
> > Guid opportunityGuid =3D service.Create(oOpportunity);
> >
> > return "success";
> >
> > }
> >
> > private void btnInsertOpp_Click(object sender, System.EventArgs e)
> > {
> >
> > string result;
> >
> > SqlConnection dataConnection =3D new SqlConnection ();
> > dataConnection.ConnectionString =3D "Integrated Security =3D true;
> > Initial Catalog =3D EMERSON; Data Source =3D DanubeCRM";
> > dataConnection.Open ();
> >
> > SqlCommand dataCommand =3D new SqlCommand ();
> > dataCommand.Connection =3D dataConnection;
> > dataCommand.CommandText =3D "SELECT QUOTENUM,
> > QUOTETOTAL,COMPETITOR,QUOTEDESCRIPTION FROM QUOTE";
> > // dataCommand.CommandText =3D "SELECT
> > QUOTENUM,COMPETITOR,QUOTEDESCRIPTION FROM QUOTE";
> >
> >
> > SqlDataReader dataReader =3D dataCommand.ExecuteReader();
> >
> > while (dataReader.Read())
> >
> > {
> >
> > string strProjectName =3D "This is a test Opportunity2";
> > string strQuoteNum =3D dataReader.GetString(0);
> > float fltQuoteTotal =3D dataReader.GetFloat(1); // WE ARE GETTING
> > STUCK HERE ON THIS LINE.
> > string strCompetitor =3D dataReader.GetString(2);
> > string strQuoteDesc =3D dataReader.GetString(3);
> >
> > result =3D
> > InsertOpportunity(strProjectName,fltQuoteTotal,strQuoteNum,strCompetito=
r,strQuoteDesc);
> > // result =3D
> > InsertOpportunity(strProjectName,strQuoteNum,strCompetitor,strQuoteDesc=
);
> > }
> >
> > dataReader.Close ();
> > dataConnection.Close ();
> > lblMessage.Text =3D "Opportunities are now in Microsoft CRM";
> >
> > }
> >
> > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> > Michael H=F6hne wrote:
> >
> > > Onkar,
> > >
> > > Can you provide some code? It sounds like a compiler error when trying
> > > to
> > > do
> > > something like the following:
> > >
> > > entity.dateField =3D DateTime.Now;
> > > entity.floatField =3D -1;
> > >
> > > Beside string fields, which are strings in the CRM objects as well, t=
he
> > > data
> > > is encapsulated in classes like CrmNumber (int), CrmFloat (float),
> > > CrmDateTime (DateTime) and so on, so your code should look like this:
> > >
> > > entity.dateField =3D new CrmDateTime();
> > > entity.dateField.Value =3D dateValue.ToString("s");
> > >
> > > entity.floatField =3D new CrmFloat();
> > > entity.floatField.Value =3D -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.googlegroups.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:
> > >
> > > > Thank you Michael.
> > > > We will write code to run on the server side.
> > > >
> > > >
> > > > Michael H=F6hne wrote:
> > > > > 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 a=
s a
> > > > > template:
> > > > >
> > > > > var connection =3D new ActiveXObject("ADODB.Connection");
> > > > > var connectionString =3D
> > > > > "Provider=3DSQLOLEDB;Server=3DSTUNNWARECRM;Database=3Dstunnware_m=
scrm;Integrated
> > > > > Security=3Dsspi";
> > > > > connection.Open(connectionString);
> > > > >
> > > > > var query =3D "SELECT stunnwar_name FROM Filteredstunnwar_b WHERE
> > > > > stunnwar_bid
> > > > > =3D '" + crmForm.ObjectId + "'";
> > > > >
> > > > > var rs =3D new ActiveXObject("ADODB.Recordset");
> > > > > rs.Open(query, connection, /*adOpenKeyset*/1,
> > > > > /*adLockPessimistic*/2);
> > > > >
> > > > > rs.moveFirst();
> > > > > var values =3D "";
> > > > >
> > > > > while (!rs.eof) {
> > > > >     values +=3D rs.Fields(0).Value.toString() + " ";
> > > > >     rs.moveNext();
> > > > > }
> > > > >
> > > > > connection.Close();
> > > > >
> > > > > alert(values);
> > > > >
> > > > >
> > > > > The code above connects to a CRM database. As you want to read fr=
om
> > > > > 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.googlegroups.com...
> > > > > > 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 datab=
ase
> > > > > > 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 databa=
se
> > > > > > in
> > > > > > the Java Script and read the records and then pass the informat=
ion
> > > > > > 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.
> > > > > >

0
Onkar
10/15/2006 12:38:11 AM
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:

> Thanks Michael.
> I will try that.
>
> Onkar
>
>
> Michael H=F6hne wrote:
>
> > Your basic routine should be:
> >
> > void InsertOpportunity(float quoteTotal, DateTime orderDate) {
> >
> >     CrmFloat crmQuoteTotal =3D new CrmFloat();
> >     crmQuoteTotal.Value =3D quoteTotal;
> >
> >     CrmDateTime crmOrderDate =3D new CrmDateTime();
> >     crmOrderDate.Value =3D orderDate.ToString("s");
> >
> >     //create appropriate CRM objects and assign the fields crmQuoteTota=
l 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.googlegroups.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 =3D 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=F6hne 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 =3D dataReader.GetFloat(1); // WE ARE GETTING STU=
CK HERE
> > > ON THIS LINE.
> > >
> > > with
> > >
> > > object oQuoteTotal =3D dataReader["QUOTETOTAL"];
> > > float fltQuoteTotal;
> > >
> > > //set a breakpoint here and look at the type of oQuoteTotal - is it a
> > > float?
> > > if (oQuoteTotal is float) {
> > >     fltQuoteTotal =3D (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 err=
or?.
> > > 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.googlegroups.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.
> > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> > >
> > > public string InsertOpportunity(string prmName, float prmQuoteTotal,
> > > string prmQuoteNum, string prmCompetitor, string prmQuoteDesc)
> > > {
> > > CrmService service =3D new CrmService();
> > > service.Credentials =3D System.Net.CredentialCache.DefaultCredentials;
> > > service.Url =3D
> > > "http://localhost:5555/MSCRMServices/2006/CrmService.asmx";
> > >
> > > //Create opportunity object
> > > opportunity oOpportunity =3D new opportunity ();
> > >
> > > //create opportunity properties
> > > oOpportunity.name =3D prmName;
> > > oOpportunity.new_quotenumber =3D prmQuoteNum;
> > >
> > > oOpportunity.new_quotetotal =3D new CrmFloat();
> > > oOpportunity.new_quotetotal.Value =3D prmQuoteTotal;
> > >
> > >
> > >
> > >
> > >
> > > // CrmFloat f =3D new CrmFloat();
> > > // f.Value =3D (float) 10.1;
> > > // f.Value =3D (float) 10.1;
> > > // oOpportunity.new_quotetotal =3D f;
> > >
> > > oOpportunity.new_competitor =3D prmCompetitor;
> > > oOpportunity.description =3D prmQuoteDesc;
> > >
> > > Customer customerid =3D new Customer();
> > > customerid.Value =3D new Guid
> > > ("22833946-a703-da11-b44f-0003ffaaec61");
> > > customerid.type =3D EntityName.account.ToString(); //this is accountid
> > > for the customer
> > > oOpportunity.customerid =3D customerid;
> > >
> > > // oOpportunity.estimatedclosedate =3D prmEstCloseDate;
> > >
> > > //create opportunity in MSCRM
> > > Guid opportunityGuid =3D service.Create(oOpportunity);
> > >
> > > return "success";
> > >
> > > }
> > >
> > > private void btnInsertOpp_Click(object sender, System.EventArgs e)
> > > {
> > >
> > > string result;
> > >
> > > SqlConnection dataConnection =3D new SqlConnection ();
> > > dataConnection.ConnectionString =3D "Integrated Security =3D true;
> > > Initial Catalog =3D EMERSON; Data Source =3D DanubeCRM";
> > > dataConnection.Open ();
> > >
> > > SqlCommand dataCommand =3D new SqlCommand ();
> > > dataCommand.Connection =3D dataConnection;
> > > dataCommand.CommandText =3D "SELECT QUOTENUM,
> > > QUOTETOTAL,COMPETITOR,QUOTEDESCRIPTION FROM QUOTE";
> > > // dataCommand.CommandText =3D "SELECT
> > > QUOTENUM,COMPETITOR,QUOTEDESCRIPTION FROM QUOTE";
> > >
> > >
> > > SqlDataReader dataReader =3D dataCommand.ExecuteReader();
> > >
> > > while (dataReader.Read())
> > >
> > > {
> > >
> > > string strProjectName =3D "This is a test Opportunity2";
> > > string strQuoteNum =3D dataReader.GetString(0);
> > > float fltQuoteTotal =3D dataReader.GetFloat(1); // WE ARE GETTING
> > > STUCK HERE ON THIS LINE.
> > > string strCompetitor =3D dataReader.GetString(2);
> > > string strQuoteDesc =3D dataReader.GetString(3);
> > >
> > > result =3D
> > > InsertOpportunity(strProjectName,fltQuoteTotal,strQuoteNum,strCompeti=
tor,strQuoteDesc);
> > > // result =3D
> > > InsertOpportunity(strProjectName,strQuoteNum,strCompetitor,strQuoteDe=
sc);
> > > }
> > >
> > > dataReader.Close ();
> > > dataConnection.Close ();
> > > lblMessage.Text =3D "Opportunities are now in Microsoft CRM";
> > >
> > > }
> > >
> > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> > > Michael H=F6hne wrote:
> > >
> > > > Onkar,
> > > >
> > > > Can you provide some code? It sounds like a compiler error when try=
ing
> > > > to
> > > > do
> > > > something like the following:
> > > >
> > > > entity.dateField =3D DateTime.Now;
> > > > entity.floatField =3D -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 thi=
s:
> > > >
> > > > entity.dateField =3D new CrmDateTime();
> > > > entity.dateField.Value =3D dateValue.ToString("s");
> > > >
> > > > entity.floatField =3D new CrmFloat();
> > > > entity.floatField.Value =3D -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.googlegroups.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 f=
rom
> > > > 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 conve=
rt
> > > > float to CrmSdk.Crmfloat"
> > > >
> > > > How do I deal with Date Time and float data type while writing reco=
rd
> > > > to CRM?
> > > > Any hint will be of great help.
> > > >
> > > > Onkar wrote:
> > > >
> > > > > Thank you Michael.
> > > > > We will write code to run on the server side.
> > > > >
> > > > >
> > > > > Michael H=F6hne wrote:
> > > > > > 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 =3D new ActiveXObject("ADODB.Connection");
> > > > > > var connectionString =3D
> > > > > > "Provider=3DSQLOLEDB;Server=3DSTUNNWARECRM;Database=3Dstunnware=
_mscrm;Integrated
> > > > > > Security=3Dsspi";
> > > > > > connection.Open(connectionString);
> > > > > >
> > > > > > var query =3D "SELECT stunnwar_name FROM Filteredstunnwar_b WHE=
RE
> > > > > > stunnwar_bid
> > > > > > =3D '" + crmForm.ObjectId + "'";
> > > > > >
> > > > > > var rs =3D new ActiveXObject("ADODB.Recordset");
> > > > > > rs.Open(query, connection, /*adOpenKeyset*/1,
> > > > > > /*adLockPessimistic*/2);
> > > > > >
> > > > > > rs.moveFirst();
> > > > > > var values =3D "";
> > > > > >
> > > > > > while (!rs.eof) {
> > > > > >     values +=3D 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.googlegroups.com...
> > > > > > > We have created a aspx web form in C# . We can now enter a le=
ad in
> > > > > > > this
> > > > > > > form and create the lead in MSCRM through CRM Web Service. CR=
M Web
> > > > > > > Service is being called through a Java Script function using
> > > > > > > "Msxml2.XMLHTTP". We are reading the form data in this functi=
on
> > > > > > > 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 dat=
abase
> > > > > > > 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 data=
base
> > > > > > > in
> > > > > > > the Java Script and read the records and then pass the inform=
ation
> > > > > > > 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 f=
or
> > > > > > > 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 a=
spx
> > > > > > > page..
> > > > > > >
> > > > > > > If anyone has done something like this, we would request your
> > > > > > > help.
> > > > > > >

0
Onkar
10/16/2006 7:34:40 AM
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.aspx?id=FindingData1, 
http://www.stunnware.com/crm2/topic.aspx?id=FindingData2 and 
http://www.stunnware.com/crm2/topic.aspx?id=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.googlegroups.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:

> Thanks Michael.
> I will try that.
>
> Onkar
>
>
> 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.googlegroups.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:
> >
> > > 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.googlegroups.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,strQuoteNum,strCompetitor,strQuoteDesc);
> > > // result =
> > > InsertOpportunity(strProjectName,strQuoteNum,strCompetitor,strQuoteDesc);
> > > }
> > >
> > > dataReader.Close ();
> > > dataConnection.Close ();
> > > lblMessage.Text = "Opportunities are now in Microsoft CRM";
> > >
> > > }
> > >
> > > ====================================================================
> > > Michael H�hne 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.googlegroups.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:
> > > >
> > > > > Thank you Michael.
> > > > > We will write code to run on the server side.
> > > > >
> > > > >
> > > > > Michael H�hne wrote:
> > > > > > 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=stunnware_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.googlegroups.com...
> > > > > > > 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.
> > > > > > >


0
Michael
10/16/2006 8:00:50 AM
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=F6hne 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 =3D service.Create(newOpportunity);
>
> Simply update your database with the returned opportunityId. If you're st=
ill
> looking for samples on how to do queries in CRM, look at
> http://www.stunnware.com/crm2/topic.aspx?id=3DFindingData1,
> http://www.stunnware.com/crm2/topic.aspx?id=3DFindingData2 and
> http://www.stunnware.com/crm2/topic.aspx?id=3DFindingData3.
>
> --
> 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.googlegroups.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:
>
> > Thanks Michael.
> > I will try that.
> >
> > Onkar
> >
> >
> > Michael H=F6hne wrote:
> >
> > > Your basic routine should be:
> > >
> > > void InsertOpportunity(float quoteTotal, DateTime orderDate) {
> > >
> > >     CrmFloat crmQuoteTotal =3D new CrmFloat();
> > >     crmQuoteTotal.Value =3D quoteTotal;
> > >
> > >     CrmDateTime crmOrderDate =3D new CrmDateTime();
> > >     crmOrderDate.Value =3D orderDate.ToString("s");
> > >
> > >     //create appropriate CRM objects and assign the fields crmQuoteTo=
tal
> > > 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.googlegroups.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 =3D 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=F6hne 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 =3D dataReader.GetFloat(1); // WE ARE GETTING S=
TUCK
> > > > HERE
> > > > ON THIS LINE.
> > > >
> > > > with
> > > >
> > > > object oQuoteTotal =3D dataReader["QUOTETOTAL"];
> > > > float fltQuoteTotal;
> > > >
> > > > //set a breakpoint here and look at the type of oQuoteTotal - is it=
 a
> > > > float?
> > > > if (oQuoteTotal is float) {
> > > >     fltQuoteTotal =3D (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.googlegroups.com...
> > > > Michael,
> > > >
> > > > I have a function that inserts data in Opportunity entity in Micros=
oft
> > > > 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.
> > > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> > > >
> > > > public string InsertOpportunity(string prmName, float prmQuoteTotal,
> > > > string prmQuoteNum, string prmCompetitor, string prmQuoteDesc)
> > > > {
> > > > CrmService service =3D new CrmService();
> > > > service.Credentials =3D System.Net.CredentialCache.DefaultCredentia=
ls;
> > > > service.Url =3D
> > > > "http://localhost:5555/MSCRMServices/2006/CrmService.asmx";
> > > >
> > > > //Create opportunity object
> > > > opportunity oOpportunity =3D new opportunity ();
> > > >
> > > > //create opportunity properties
> > > > oOpportunity.name =3D prmName;
> > > > oOpportunity.new_quotenumber =3D prmQuoteNum;
> > > >
> > > > oOpportunity.new_quotetotal =3D new CrmFloat();
> > > > oOpportunity.new_quotetotal.Value =3D prmQuoteTotal;
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > // CrmFloat f =3D new CrmFloat();
> > > > // f.Value =3D (float) 10.1;
> > > > // f.Value =3D (float) 10.1;
> > > > // oOpportunity.new_quotetotal =3D f;
> > > >
> > > > oOpportunity.new_competitor =3D prmCompetitor;
> > > > oOpportunity.description =3D prmQuoteDesc;
> > > >
> > > > Customer customerid =3D new Customer();
> > > > customerid.Value =3D new Guid
> > > > ("22833946-a703-da11-b44f-0003ffaaec61");
> > > > customerid.type =3D EntityName.account.ToString(); //this is accoun=
tid
> > > > for the customer
> > > > oOpportunity.customerid =3D customerid;
> > > >
> > > > // oOpportunity.estimatedclosedate =3D prmEstCloseDate;
> > > >
> > > > //create opportunity in MSCRM
> > > > Guid opportunityGuid =3D service.Create(oOpportunity);
> > > >
> > > > return "success";
> > > >
> > > > }
> > > >
> > > > private void btnInsertOpp_Click(object sender, System.EventArgs e)
> > > > {
> > > >
> > > > string result;
> > > >
> > > > SqlConnection dataConnection =3D new SqlConnection ();
> > > > dataConnection.ConnectionString =3D "Integrated Security =3D true;
> > > > Initial Catalog =3D EMERSON; Data Source =3D DanubeCRM";
> > > > dataConnection.Open ();
> > > >
> > > > SqlCommand dataCommand =3D new SqlCommand ();
> > > > dataCommand.Connection =3D dataConnection;
> > > > dataCommand.CommandText =3D "SELECT QUOTENUM,
> > > > QUOTETOTAL,COMPETITOR,QUOTEDESCRIPTION FROM QUOTE";
> > > > // dataCommand.CommandText =3D "SELECT
> > > > QUOTENUM,COMPETITOR,QUOTEDESCRIPTION FROM QUOTE";
> > > >
> > > >
> > > > SqlDataReader dataReader =3D dataCommand.ExecuteReader();
> > > >
> > > > while (dataReader.Read())
> > > >
> > > > {
> > > >
> > > > string strProjectName =3D "This is a test Opportunity2";
> > > > string strQuoteNum =3D dataReader.GetString(0);
> > > > float fltQuoteTotal =3D dataReader.GetFloat(1); // WE ARE GETTING
> > > > STUCK HERE ON THIS LINE.
> > > > string strCompetitor =3D dataReader.GetString(2);
> > > > string strQuoteDesc =3D dataReader.GetString(3);
> > > >
> > > > result =3D
> > > > InsertOpportunity(strProjectName,fltQuoteTotal,strQuoteNum,strCompe=
titor,strQuoteDesc);
> > > > // result =3D
> > > > InsertOpportunity(strProjectName,strQuoteNum,strCompetitor,strQuote=
Desc);
> > > > }
> > > >
> > > > dataReader.Close ();
> > > > dataConnection.Close ();
> > > > lblMessage.Text =3D "Opportunities are now in Microsoft CRM";
> > > >
> > > > }
> > > >
> > > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> > > > Michael H=F6hne wrote:
> > > >
> > > > > Onkar,
> > > > >
> > > > > Can you provide some code? It sounds like a compiler error when
> > > > > trying
> > > > > to
> > > > > do
> > > > > something like the following:
> > > > >
> > > > > entity.dateField =3D DateTime.Now;
> > > > > entity.floatField =3D -1;
> > > > >
> > > > > Beside string fields, which are strings in the CRM objects as wel=
l,
> > > > > 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 =3D new CrmDateTime();
> > > > > entity.dateField.Value =3D dateValue.ToString("s");
> > > > >
> > > > > entity.floatField =3D new CrmFloat();
> > > > > entity.floatField.Value =3D -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.googlegroups.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:
> > > > >
> > > > > > Thank you Michael.
> > > > > > We will write code to run on the server side.
> > > > > >
> > > > > >
> > > > > > Michael H=F6hne wrote:
> > > > > > > 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 followi=
ng
> > > > > > > as a
> > > > > > > template:
> > > > > > >
> > > > > > > var connection =3D new ActiveXObject("ADODB.Connection");
> > > > > > > var connectionString =3D
> > > > > > > "Provider=3DSQLOLEDB;Server=3DSTUNNWARECRM;Database=3Dstunnwa=
re_mscrm;Integrated
> > > > > > > Security=3Dsspi";
> > > > > > > connection.Open(connectionString);
> > > > > > >
> > > > > > > var query =3D "SELECT stunnwar_name FROM Filteredstunnwar_b W=
HERE
> > > > > > > stunnwar_bid
> > > > > > > =3D '" + crmForm.ObjectId + "'";
> > > > > > >
> > > > > > > var rs =3D new ActiveXObject("ADODB.Recordset");
> > > > > > > rs.Open(query, connection, /*adOpenKeyset*/1,
> > > > > > > /*adLockPessimistic*/2);
> > > > > > >
> > > > > > > rs.moveFirst();
> > > > > > > var values =3D "";
> > > > > > >
> > > > > > > while (!rs.eof) {
> > > > > > >     values +=3D 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 databa=
se,
> > > > > > > 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.googlegroups.com...
> > > > > > > > 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 shou=
ld
> > > > > > > > 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 yo=
ur
> > > > > > > > help.
> > > > > > > >

0
Onkar
10/19/2006 6:35:08 AM
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.googlegroups.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:

> 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.aspx?id=FindingData1,
> http://www.stunnware.com/crm2/topic.aspx?id=FindingData2 and
> http://www.stunnware.com/crm2/topic.aspx?id=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.googlegroups.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:
>
> > Thanks Michael.
> > I will try that.
> >
> > Onkar
> >
> >
> > 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.googlegroups.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:
> > >
> > > > 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.googlegroups.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,strQuoteNum,strCompetitor,strQuoteDesc);
> > > > // result =
> > > > InsertOpportunity(strProjectName,strQuoteNum,strCompetitor,strQuoteDesc);
> > > > }
> > > >
> > > > dataReader.Close ();
> > > > dataConnection.Close ();
> > > > lblMessage.Text = "Opportunities are now in Microsoft CRM";
> > > >
> > > > }
> > > >
> > > > ====================================================================
> > > > Michael H�hne 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.googlegroups.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:
> > > > >
> > > > > > Thank you Michael.
> > > > > > We will write code to run on the server side.
> > > > > >
> > > > > >
> > > > > > Michael H�hne wrote:
> > > > > > > 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=stunnware_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.googlegroups.com...
> > > > > > > > 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.
> > > > > > > >


0
Michael
10/19/2006 8:02:39 AM
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=F6hne wrote:
> 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 =3D new ActiveXObject("ADODB.Connection");
> var connectionString =3D
> "Provider=3DSQLOLEDB;Server=3DSTUNNWARECRM;Database=3Dstunnware_mscrm;Int=
egrated
> Security=3Dsspi";
> connection.Open(connectionString);
>
> var query =3D "SELECT stunnwar_name FROM Filteredstunnwar_b WHERE stunnwa=
r_bid
> =3D '" + crmForm.ObjectId + "'";
>
> var rs =3D new ActiveXObject("ADODB.Recordset");
> rs.Open(query, connection, /*adOpenKeyset*/1, /*adLockPessimistic*/2);
>
> rs.moveFirst();
> var values =3D "";
>
> while (!rs.eof) {
>     values +=3D 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.googlegroups.com...
> > 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.
> >

0
Al
12/4/2006 10:36:25 PM
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.googlegroups.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:
> 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=stunnware_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.googlegroups.com...
> > 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.
> >


0
Michael
12/5/2006 9:56:33 AM
Reply:

Similar Artilces:

Cannot Connect To Database #2
We are using RMS... when we try to get on the software we get an error "cannot connect to database". It use to be that if we rebboted the system it would then work. Now when we get the error the only way we can make it work is to go into system administrator and go to FILE .. CONFIGURATION... then click the test database connection button. It then says connection good or something like that. After that we can start up the POS/RMS. Any ideas as to what the bleep is going on? Thanks for your help in advance. Neil Try redoing the paths of connecting to the database. Just...

MS Access vs Informix 4GL
Hello all; I am quite comfortable using Access via ODBC however I am now looking at a new position using Informix 4GL on top of SQL Technology on an HP9000 server. Has anyone had experience with Informix from a queries, data reporting, manipulation and GUI design perspective? Is it similar to Access and what would be the reasons for using Informix rather than Access. I am plowing through SQL for dummies in the case that I might have to take hours to do what I can do in minutes with Access. Any info would be greatly appreciated!! Thanks RJ "RJ" <RJ@discussio...

Random access of XML file
I have the XML file which has a structure similar to one I pasted at the end of this post. As you can see many elements are using different id attributes. For example element Window uses WinID attribute as ID, the ViewData uses ViewID attribute as an ID and so on. I need to read this file without assuming what is the order of the elements, in other words I can't read it sequentially. The reason for this requirement is that whole WindowsInfo element might be missing for example or Object Info might be first and WindowsInfo second etc. Since many elements have the same name and diffe...

how to add an outlook email to a text box in an access form?
Hi, I searched everywhere for a similar issue but in vain... I have a form used to enter computer issues. I would like to add to it a text box that will store emails. It could be a button that opens a link to the email. I hope this is clear. thanks. Ismail -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200803/1 ...

Tag and Structure info in Publisher 2003 for PDF conversion
Hello All, I wish to have my Publisher 2003 files structure show up after I convert them to PDF. It with Word 2003; that is, once I convert a Word doc to PDF the bookmarks are automatically carried over into the PDF doc, making for easy page navigation. Presently, I have to manually create my PDF bookmarks. That is a drag. There should be a way since Publisher preserves the Styles and Formatting when I import a Word doc into Publisher. Any ideas on this. Thanks in advance for your support. Publisher is strictly a page layout application, it will print to the PDF printer just as it i...

Need help with DST
After applying DST uptades at the O.S level and on Exchange Servers, Users are reporting meeting organizer is change after the update, and strange clock icon next to the appoinments, and duplicate appoinments entries in Outlook. Does anyone know how to fix the above issue? What additional updates do I need after patching OS and Exchage server with latest DST patches? Any help is appreciated. Thanks Have you rebased appointments already? This may help: http://exchangepedia.com/blog/2007/02/dst-2007-understanding-what-needs-to-be.html -- Bharat Suneja MVP - Exchange www.zenprise.com ...

Access and Outlook Security
All, I have an Access application on a Terminal Server. I would like to add logic to one of my forms that will send out an email to two specific recipients. I am able to write the appropriate VBA code on the event but when it logic executes, I get a Security message stating "A program is trying to automatically send email on your behalf"... I did some research and discovered that Microsoft updated their security. I also found two products that says they can eliminate the message from appearing: one is from Add-In-Express called Outlook Security Manager, and the other prod...

How do I turn on Autocomplete in Access 2003 Forms?
How do I turn on Autocomplete in Access 2003 Forms? Autocomplete ... what? Comboboxes have a property that will help you "autocomplete", but it isn't clear that's what you are looking for... Regards Jeff Boyce Microsoft Office/Access MVP "Jojo" <Jojo@discussions.microsoft.com> wrote in message news:6B3676EC-E257-41B8-83A6-BAEC6F3D1FD4@microsoft.com... > How do I turn on Autocomplete in Access 2003 Forms? Autocomplete a field in which text is being consistently repeated, ie: the name of a facility. I don't want to link to a table nor do I w...

Need help on this....thanks
I have an Excel worksheet which stored a survey outcome of over 1200 members. There are over 30 questions in the survey and the first column captured the member ID. The problem is 3 of the survey questions that allow multiple answers, instead the answer for those three questions are captured in seperate column, the answers are stored in a single column and it makes it very difficult to analysis those result. As a result I created addittional columns depending on the total number of the answers available for that question and assign the result manually to that column. It is very time consuming ...

need assistance with sorting a list of numbers
I've tried to achieve this problem with formulas and lookups and if statements and conditinal formatting however was not able to achieve desired results, so asking for help since I am not good with XL marcros..if someone can start me up I can navigate myself.. There is a list.. say of numbers and there is a possiblity that numbers will be repeats like: 12345 fail 12345 fail 12345 pass 11111 fail 11111 pass 22222 pass 33333 fail 33333 fail 44444 pass I want to read say A1 (numbers) disregard the repeats and put info A2 beside it 12345 fail fail pass 11111 fail pass 22222 pass 333...

I NEED A REALLY HELP FROM A MVP (PLZ HELP ME !!!)
I have the Access 2000 and the VB6. I have made a test application with these two products: I have made a form with an Image control that have an stretched big image (a .jpg file 50 Kb). I have put in this form a command button that opens another empty form that cover the first form. When I uncover the Form1 i see : 1) in VB the image and the button repainted again (!!!) That is in the beggining i cant see nothing. 2) in Access the image and button dont repainted. When i put 5-6 images on the form, then the repainting hold for about 2 seconds(!!!!) in the VB Form but in the Access nothing re...

Need Help to get Get Windows application list
Hai , I has to hook every application installed in the system.For that, I hav to list all applications I hav two paths to get applications list from registry.In first one, HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths It'll only get microsoft applications list only. I hav installed other software such as Dev-C++ its not listed here.In another path HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall In this i can get all applications display name.But here also some problem is there. I f i get Microsoft visual studio display name. ...

How do I bring Excel graph to illustrator (no cut and paste)
I am using Excel 2003 and Illustrator 9.0, how do I bring in a chart (like a 2D surface chart) from Excel into Illustrator, so that it can be edited in illustrator (so that it is not just an image)? Cutting and pasting is not working. I take it this can't be done? "Excel_lent" wrote: > I am using Excel 2003 and Illustrator 9.0, how do I bring in a chart (like a > 2D surface chart) from Excel into Illustrator, so that it can be edited in > illustrator (so that it is not just an image)? Cutting and pasting is not > working. Unless Illustrator understands the ...

HELP need index of the actual selected point
Hi, I need help. I must get the index of a the currently selectd point in the chart. I can't use the Mouseup event. It will be better to determinate the x and y value from the actual selected point but the index will also be good. Please help! thx Jens You need to use an old-style XLM command for this, as Stephen Bullen pointed out in his post 14-Jan-2001: Sub WhichPoint() Dim sPoint As String Dim iSeries As Integer, iPoint As Integer sPoint = ExecuteExcel4Macro("SELECTION()") If sPoint Like "S*P*" Then iSeries = Val(Mid$(sPoint, 2...

I have 36 legend entries but only need the ones actualy graphed
I have a pie graph created from 36 rows by 1 numeric 'count' column. I have created a pie chart which shows the count distribution based on the 36 rows. Where a row has zero count, there is no slice. I would like to turn off the legend entry automatically where a row has a zero count, thereby reducing the overall size of the legend table. Hi, Have a look at this example. http://www.andypope.info/charts/piezeros.htm Cheers Andy Simon wrote: > I have a pie graph created from 36 rows by 1 numeric 'count' column. I have > created a pie chart which shows the count...

Converting access to sql server
Hi, I have converted data from access to sql server. In one of my forms I have find method i.e DoCmd.DoMenuItem A_FORMBAR, A_EDITMENU, 10, , A_MENU_VER20. Conversion went fine but, now when I do a search page just hangs. I even tried to convert docmd.doMenuItem to DoCmd.RunCommand acCmdFind but, i still the get the same thing. Any suggestions are appreciated. Thanks in advance, BB ...

The need for speed
Hi, I'm am running queries to return the postcode. The New Zealand Post have supplied a data file that contains 1,829,432 distinct delivery points. Do you have any suggestions/recommendations to help speed up the performance of a search; particularly over a network. Both the data file and front end are in Access 2003. To add to the mix, some addresses have alternative street names and/or alternative suburb names and/or alternative town names. These alternatives are contained in separate tables. That is if a matching address is not found in the main table, a search is then made for ...

Need a special kind of "sort"... #3
Ron Rosenfeld Wrote: > On Mon, 8 Nov 2004 11:41:35 -0600 > > The following assumes your codes are all three characters long. > > Set up a "helper column" with the following formula: > > =(CODE(LEFT(A1,1))+100*(LEFT(A1,1)<"A"))*10^6+ > (CODE(MID(A1,2,1))+100*(MID(A1,2,1)<"A"))*10^3+ > CODE(RIGHT(A1,1))+100*(RIGHT(A1,1)<"A") > > Then, sort ascending on the helper column. > > Obviously, this could also be done as a VBA routine if you preferred. > > > --ron Ron, I tried your code and it works ...

Delivery Status Notification (Failure) sent to all external mail m
Hi, I wonder if someone can help me please. I have recently installed Exchange 2003, use individual POP3 accounts and the POP3 connector for retrieving e-mail. For some reason all external message senders receive a similar message as listed below from postmaster@domain.co.uk: Reporting-MTA: dns;SERVER.DOMAIN.local Received-From-MTA: dns;domain.co.uk Arrival-Date: Thu, 7 Jul 2005 08:31:00 +0100 Final-Recipient: rfc822;mspop3connector.<username>@domain.co.uk Action: failed Status: 5.1.1 The original message is delivered to the recipient sucessfully, but like I said the sender recei...

temp access including deleted items
We have a request from company CEO to have access to a staffer's email box to look for some commmunication threads... Exchange 2003 SP 2, Outlook 2003. What is the best way to setup temporary access? Also would like staffer not to know emails were being reviewed, AND would like ability to look at emails in "recover deleted items" - the retention period is 30 days. TIA - Dodge On Tue, 9 May 2006 10:51:02 -0700, Dodge <Dodge@discussions.microsoft.com> wrote: >We have a request from company CEO to have access to a staffer's email box to >look for some commmuni...

How to get the type code for a custom entity ? MSCRM v3
I created a new entity Called PRITem. When using theSiteMap.XML I need to specifity the EntityType to show it. Ity appears (Automatically) in the workspace. But i can't use it in the SiteMap.XML because i can't find the number. For example: <SubArea Id="nav_activities" EntityType="4200" Icon="/_imgs/ico_16_4212.gif" Url="/Workplace/home_activities.aspx" /> <SubArea Id="nav_calendar" Icon="/_imgs/area/18_calendar.gif" ResourceId="Homepage_Calendar" Url="/workplace/home_calendar.aspx" Clie...

merge access data into excel spreadsheet
I have payroll detail records that need to be summarized on excel spreadsheets. For example if I have 30 records representing 3 unique companies, I need to create 3 excel control total sheets with totals for each company merged into specific cells on the sheets. These sheets are not empty, they have extensive formatting and data already contained. The user then enters some additional data which is why I can't use a report. I have done this when merging to word but can't find info on excel. TransferSpreadsheet doesn't seem to do what I need. Thanks in advance... pb Per pburns...

Navigation Button error (using Access 2007)
I am using my own Navigation Buttons for my form with an lblNavigate label. (I created lblNavigate on the form by using three spaces.) I am using lblNavigate to try and duplicate the record "x of y" label that appears in the standard navigation toolbar). I found some code that works except for one problem: When I open the form it says 1 of 501 instead of 1 of 1564. I have to press the Next button and then the previous button to get the accurate count. Any ideas of what’s causing this error? I put the below code in the form's On Current Event: Private Sub Fo...

access report 03-29-10
i have a combo box in a form to select 1 of 5 values and i setup list so each selection has a number value ie, 1. table 2.dessert etc. i have check boxes on the report that have to be checked based on the value selected on the form. i have to show all check boxes on the report but only check the box for the value choose in fhe form. i have tried to do conditional statement on the report and check for numeric value and then set check value to true based on that but haven't had any luck. any help greatly appreciated. Submitted via EggHeadCafe - Software Developer Portal of Choice ...

Accessing an order
I am trying to access an order in SOP but I keep getting the message that another user has the order open even when everyone is logged out of the system. How do I fix this? Thanks Angie Hi Angie, You will want to go through TK# 866440 to correct this problem. thanks Don "Angie" wrote: > I am trying to access an order in SOP but I keep getting the message that > another user has the order open even when everyone is logged out of the > system. How do I fix this? > > Thanks > > Angie > > > I'm sorry, I am fairly new at this. How ...