VS Tools Forum » Dynamics GP » eConnect for Dynamics GP » Getting company info
Site Search:
Sign in | Join | Help

Getting company info

Last post 07-17-2007 8:54 AM by Joe Cogan. 5 replies.
Page 1 of 1 (6 items)
Sort Posts: Previous Next

  • 07-04-2007 12:07 PM

    Getting company info

    Reply Contact

    Hi, I need to get the company info that I see when I go to Tools->Setup->Company->Company.

    I'm using the eConnect .NET assemblies with GP 9.0 SQL Server 2005 and the sample company TWO.
     
    I was able to get Customers, Vendors, Items, Purchase_Order_Transactions, etc with the eConnectRequester method, specifying the DOCTYPE.
     
    I was expecting to find a doctype to get the company data, something like
    eConnectOut.DOCTYPE = "Company_Data", but I didn't find anything like that.
     
    eConnect's interface doesn't seem to include a method to execute a SQL query directly such as SELECT * FROM SY01000.
    Should I run my query using the data source or is there another option?
     
    Thank you in advance for any input.
    Filed under:
    • 205.205.29.62

  • 07-05-2007 8:01 AM In reply to

    Re: Getting company info

    Reply Contact

    eConnect allows you to run a 'PRE' query, you could do it there, or you could just run a query directly in your code before you call econnect (that's what I do)

     Select * from DYNAMICS..SY01500 will give you a list of companies. You should know which company you want before hand, since you'll need the db name to call eConnect

     Does that help?

    Steve Gray, MCDBA
    Technical Editor
    steve@VSToolsForum.com
    • 216.64.79.2

  • 07-05-2007 10:31 AM In reply to

    Re: Getting company info

    Reply Contact

    Thank you for your reply Steve.

    I'll stick to running my queries directly because I feel it will simplify the installation of our software on our client's GP servers.

    I've included my code. It works fine, but I was wondering if it would still work on GP implementations running on Oracle. Also, I'm planning to use the sa (system admin) account to run my query, is that good practice?

    Thanks again!

    using System.Data.SqlClient;

    SqlConnection oSqlConnection = new SqlConnection();oSqlConnection.ConnectionString = "Data Source=" + Configuration.Server + ";Initial Catalog=" + Configuration.CompanyDB + ";User=" + Configuration.DBUserName + ";Password=" + Configuration.DBPassword;

    oSqlConnection.Open();

    SqlCommand oSqlCommand = oSqlConnection.CreateCommand();

    String sSQL = String.Empty;

    sSQL += "SELECT CITY, STATE, ZIPCODE, CMPCNTRY FROM SY00600 ";

    sSQL += "WHERE LOCATNID = '" + oPurchaseOrder.oPOLinesNodeList[iLineNumber - 1].SelectSingleNode("LOCNCODE").InnerText + "'";

    oSqlCommand.CommandText = sSQL;

    SqlDataReader oSqlDataReader = oSqlCommand.ExecuteReader();

    oSqlDataReader.Read();

    sCity = oSqlDataReader.GetString(0);
    Filed under:
    • 205.205.29.62

  • 07-05-2007 11:46 AM In reply to

    Re: Getting company info

    Reply Contact

    antoinebaudouin:
    but I was wondering if it would still work on GP implementations running on Oracle.

    I've never seen Oracle code, but I'm sure the tables are the same. As long as you have the data access correct, the theory is the same.

    antoinebaudouin:
     Also, I'm planning to use the sa (system admin) account to run my query, is that good practice?

    DBAs all over the world are cringing right now. Using the sa account is dangerous on lots of levels - A user could view your code and get a powerful password, the same for hackers. If the account gets used and gets corrupted, you have no fallback. Even as a system admin I never us sa. I only use it when I have to - it's my fallback.

    HTH...

    Steve Gray, MCDBA
    Technical Editor
    steve@VSToolsForum.com
    • 216.77.101.2

  • 07-06-2007 4:43 PM In reply to

    Re: Getting company info

    Reply Contact

    Thank you for your feedback Steve.

    DBAs all over the world can relax now coz I won't be applying for a dba position anytime soon!!!

    Have a good one! 

    ps: Notice how the GP installation guide suggests the use of the sa account on page 59...

     
    Filed under:
    • 205.205.29.62

  • 07-17-2007 8:54 AM In reply to

    Re: Getting company info

    Reply Contact

    Antoine,

     Concerning your "Doc Types" reference:  The eConnect_Out_Setup table contains all the available doc types you can use in the eConnectOut transaction.  I wrote a .Net wrapper (in C#) to grab all available Doc Types and return them as a string array:

    private static string QUERY_DOCTYPES1 = @"SELECT DISTINCT DOCTYPE FROM dbo.eConnect_Out_Setup ORDER BY DOCTYPE ASC";

    /// <summary>
    /// RequesterDocTypes: returns an array of names of all available
    /// DocTypes that can be retrieved through the eConnect requester
    /// interface.
    /// </summary>
    /// <param name="connection"></param>
    /// <returns></returns>
    public static string[] RequesterDocTypes(string connection)
       {
       // query the database for the available requester types
       SqlConnection scon;
       SqlCommand scmd;
       SqlDataReader sdr;
       List<string> s;
       scon =     new SqlConnection(connection);
       scon.Open();
       scmd =     new SqlCommand(QUERY_DOCTYPES1, scon);
       sdr = scmd.ExecuteReader();
       s =     new List<string>()
       while (sdr.Read())
       {
       s.Add(sdr.GetString(0));
       }
       return (s.ToArray());
    }

    Hope this helps.  Take care!

    Joe Cogan
    Real Time Intelligence, Inc.
    www.rtintel.com
    • 76.189.252.140
Page 1 of 1 (6 items)