|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss using the SalesLogix OLE DB Provider from external applications (including usage of built-in provider procedures). View the code of conduct for posting guidelines.
|
|
|
|
Connection Problem and Insert Query problem.
Posted: 12 Jan 09 1:52 PM
|
First, thanks for reading this I REALLY appreciate some help. Sorry it is long.
1) I CAN connect to my remote database with a user login. However, I CAN'T connect to it with a sysdba login. What should the second string look like? I also know how to do the SQL conns, but that does not really help out.
2) I have some code that queries some information from the opp table and comes back fine, however when I go to insert a new record into the table I get an error. The part that really confuses me is that if I use a SQL conn the History entry is created fine. Can I not use the keyword INSERT with OLE?
Problem 1 Details.
//works or at least connects
connDb = "Provider=SLXOLEDB.1 assword=\"altja\"ersist Security Info=True;User ID=altja;Initial Catalog=SALESLOGIX_SANDBOX_JOE;Data Source=us3grrp576a;Extended Properties=\"Port=1706;Log=On;\"";
//works or at least connects
//fails
//connDb = "Provider=SLXOLEDB.1assword=\"masterkey\"ersist Security Info=True;User ID=sysdba;Initial Catalog=SALESLOGIX_SANDBOX_JOE;Data Source=sqldev.amcs.tld;Extended Properties=\"Port=1706;Log=On;\"";
ERROR = {"Failed to connect to SLXServer. Check if server is running and is compatible with the current provider version."} System.Runtime.InteropServices.ExternalException {System.Data.OleDb.OleDbException}
Problem 2 Query details.
INSERT INTO [SalesLogix_SandBox_Joe].[sysdba].[HISTORY]([HISTORYID],[TYPE],[ACCOUNTID],[OPPORTUNITYID],[ACCOUNTNAME],[STARTDATE],[DURATION],[DESCRIPTION],[TIMELESS],[USERID],[USERNAME],[ORIGINALDATE],[CREATEDATE],[CREATEUSER],[MODIFYDATE],[MODIFYUSER],[COMPLETEDDATE],[COMPLETEDUSER],[NOTES],[LONGNOTES])VALUES('HAAAAA00JOE3','262148','AAAAAA000000','OAAAAA000002','**Alt Landscaping-Phase2',dateadd(hh, 5, getdate()),'0','Update From IPS','T','ADMIN','Administrator',dateadd(hh, 5, getdate()),dateadd(hh, 5, getdate()),'ADMIN',dateadd(hh, 5, getdate()),'ADMIN',dateadd(hh, 5, getdate()),'ADMIN','Update From IPS','Update From IPS')
ERROR = An unknown or internal error has occurred. ErrorCode -2147217900 System.Data.OleDb.OleDbException
|
|
|
|
Re: Connection Problem and Insert Query problem.
Posted: 12 Jan 09 1:55 PM
|
Entire Method
#region Private Members
IDataReader _idr; //used because I use both SQL and OLE in the app.
IDbCommand _icmd; //used because I use both SQL and OLE in the app.
#endregion
public void CreateHistoryEntry( )
{
StringBuilder SQL = new StringBuilder();
StringBuilder SQL2 = new StringBuilder();
String accountID = "";
String accountName = "";
String userID = "ADMIN";
String userName = "Administrator";
String tableID = "";
String now = "," + "dateadd(hh, 5, getdate())";// GMT 5 hour discrepency bug with SLQ 2005. Might need to be changed with SQL 2008
try
{
SQL.Append("Select ACCOUNTID, DESCRIPTION ");
SQL.Append("FROM Opportunity opp ");
SQL.Append("WHERE opp.OpportunityID = '");
SQL.Append(_oppID + " '");
tableID = "HAAAAA00JOE3";
//tableID = SLXFuntions.CreateIDFor(_idr, _icmd, "History");
_icmd.CommandText = SQL.ToString();
_idr = _icmd.ExecuteReader();
if (_idr.Read())
{
accountID = SLXDataReader.GetValue(_idr, "ACCOUNTID");
accountName = SLXDataReader.GetValue(_idr, "DESCRIPTION");
}
_idr.Close();
//accountID = "AAAAAA000000";
//accountName = "**Alt Landscaping";
//SQL2.Append("INSERT INTO [SalesLogix_SandBox_Joe].[sysdba].[HISTORY]");
SQL2.Append("INSERT INTO HISTORY");
SQL2.Append("(");
SQL2.Append("[HISTORYID],");
SQL2.Append("[TYPE],[ACCOUNTID],[OPPORTUNITYID],[ACCOUNTNAME]");
SQL2.Append(",[STARTDATE],[DURATION],[DESCRIPTION],[TIMELESS],[USERID]");
SQL2.Append(",[USERNAME],[ORIGINALDATE],[CREATEDATE],[CREATEUSER],[MODIFYDATE]");
SQL2.Append(",[MODIFYUSER],[COMPLETEDDATE],[COMPLETEDUSER],[NOTES],[LONGNOTES])");
SQL2.Append("VALUES(");
SQL2.Append("'" + tableID + "',");
SQL2.Append("'262148'");
SQL2.Append(",'" + accountID + "'");
SQL2.Append(",'" + _oppID + "'");
SQL2.Append(",'" + accountName + "'");
SQL2.Append(now);
SQL2.Append(",'0'");
SQL2.Append(",'Update From IPS'");
SQL2.Append(",'T'");
SQL2.Append(",'" + userID + "'");
SQL2.Append(",'" + userName + "'");
SQL2.Append(now);
SQL2.Append(now);
SQL2.Append(",'" + userID + "'");
SQL2.Append(now);
SQL2.Append(",'" + userID + "'");
SQL2.Append(now);
SQL2.Append(",'" + userID + "'");
SQL2.Append(",'Update From IPS'");
SQL2.Append(",'Update From IPS')");
_icmd.CommandText = SQL2.ToString();
_idr = _icmd.ExecuteReader();
if (_idr.RecordsAffected < 1)
_error.Append(_oppID + " UpdateIPSStatus Method did not update." + "-- ");
}
catch (Exception ex)
{
_error.Append(_oppID + " CreateToDo Method failed with internal reason " + ex.Message + "-- ");
throw ex;
}
finally
{
_idr.Close();
}
} |
|
|
| |
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|