Community
Showing results for 
Search instead for 
Do you mean 
Reply

Access to the ACT MS SQL-Express database via Visual C# 2008

Tuned Listener
Posts: 21
Country: Germany

Access to the ACT MS SQL-Express database via Visual C# 2008

Hello folks,

 

When I try to execute SQL-queries only the SELECT Option works correctly. That means I can access the database, logon and retrieve data from the tables.

But when I try to employ other queries especially INSERT, UPDATE or DELETE I receive the following error message:

 

1.jpg

 

Who can help me with this? Thank you very much inn advance, Janus.

Nickel Contributor
Posts: 175
Country: USA

Re: Access to the ACT MS SQL-Express database via Visual C# 2008

Well, it appears that you have a bad SQL statement, since it failed to parse the SQL. I haven't translated the German, so I'm not sure if there are more details there. Could you please post the SQL statements you are passing?
Employee
Posts: 1,163
Country: USA

Re: Access to the ACT MS SQL-Express database via Visual C# 2008

The OLEDB is read only, only SELECT statements should work.

Matthew Wood
Act! SDK Support
Community Moderator
Platinum Elite Contributor
Posts: 14,384
Country: Australia

Re: Access to the ACT MS SQL-Express database via Visual C# 2008

You cannot write directly to the ACT! database ... to do that, you must use the SDK
Bronze Elite Contributor
Posts: 2,115
Country: United_Kingdom

Re: Access to the ACT MS SQL-Express database via Visual C# 2008

Though it's possible to connect via SSMS or run code under Admin profile on the server that can run sqlcmd commands directly via the SQL client, it would be against the EULA to do so. 

 

Just as Matthew has suspected, you're most probably accessing via the OLEDB which is read only, so all INSERT, DELETE and UPDATE cmds would fail.

Vivek Gargav
Caldere Associates Ltd.
www.caldere.com
vgargav@caldere.com
My Blog
Tuned Listener
Posts: 21
Country: Germany

Re: Access to the ACT MS SQL-Express database via Visual C# 2008

Thank you very much,

 

Maybe someone can quickly take a look at my coding and tell me where to change my approach:

 

 

public void CreateCommand()

    {

        ActFramework ACTFM1 = new Act.Framework.ActFramework();

        try

        {

            ACTFM1.LogOn(xmlPADFile, userName, password);

        }

        catch (System.Exception ex)

        {

            System.Console.WriteLine(ex.ToString());

            throw ex;

        }

 

        OleDbConnection verbin = new OleDbConnection(ACTFM1.CurrentACTOLEDB2);

        verbin.ConnectionString = "Provider=ACTOLEDB2.1;Data Source=C:\\Users\\Admin\\Documents\\ACT\\ACT Data\\Databases\\DB_Test.pad;" +

         "User ID=user;Password=" + password + ";Database=DB_Test;Persist Security Info=True";

        verbin.Open();

 

        OleDbCommand befel = new OleDbCommand();

        befel = verbin.CreateCommand();

        befel.CommandText = "SELECT [E-MAIL], [CONTACTID] FROM CONTACT WHERE [E-MAIL] = 'e-mail_adresse@mail.abc'";

        OleDbDataReader leser = befel.ExecuteReader();

     

        while (leser.Read())

        {

            MessageBox.Show(leser["E-MAIL"].ToString() + "  " +   leser["CONTACTID"].ToString());

        }

        leser.Close();

       

        //                     SQL-Befehl UPDATE                                       

        try

        {

            string sqlBefel = "UPDATE CONTACT SET Brief=@Brief WHERE [E-MAIL]=@EMAIL ";

            OleDbCommand befehl = new OleDbCommand(sqlBefel, verbin);

            befehl.Parameters.Add("@EMAIL",OleDbType.VarChar,99,"[E-MAIL]");

            befehl.Parameters.Add("@Brief", OleDbType.VarChar, 299, "Brief");

            befehl.Parameters["@Brief"].Value = "Text…";

            befehl.Parameters["@EMAIL"].Value = "eMail adress"xt..._adres;

            int zahl = befehl.ExecuteNonQuery();  //  ERROR

            MessageBox.Show(zahl.ToString());

        }

        catch (System.Exception ex)                                                                                           

        {                                                                                                                 

            MessageBox.Show("Error:  " + ex.ToString());                                                                 

        }                                                                                                                  

 

        verbin.Close();

    }

 

I also tried using this instead but received the same error message:

 

befehl.Parameters.AddWithValue("@EMAIL","[E-MAIL]");

Bronze Elite Contributor
Posts: 2,115
Country: United_Kingdom

Re: Access to the ACT MS SQL-Express database via Visual C# 2008

You're using the OLEDB to connect to the backend and that is READ ONLY. You cannot use that connection to carryout UPDATE commands. To do that you'll need to use the ACT! SDK (API), or if you're brave and willing to overlook the EULA directly via a SQL connection.

Vivek Gargav
Caldere Associates Ltd.
www.caldere.com
vgargav@caldere.com
My Blog
Tuned Listener
Posts: 21
Country: Germany

Re: Access to the ACT MS SQL-Express database via Visual C# 2008

Thank you,

I will look for other ways

Employee
Posts: 1,163
Country: USA

Re: Access to the ACT MS SQL-Express database via Visual C# 2008

You can set a contacts email address through the API like so:

 

DBFieldDescriptor emailField = _ActApp.ActFramework.Contacts.GetContactFieldDescriptor("TBL_CONTACT.BUSINESS_EMAIL");
emailField.SetValue(value, contact);

 

Hope that helps.

Matthew Wood
Act! SDK Support
Community Moderator
Tuned Listener
Posts: 21
Country: Germany

Re: Access to the ACT MS SQL-Express database via Visual C# 2008

Thank you Matthew,

I want to try this code

 

Act.Framework.Contacts.ContactList KontList = ACTFM1.Contacts.GetContactsByID(null, new System.Guid[] { idUp });  // idUp - CONTACTID

KontList[0].ContactFields["TBL_CONTACT.BUSINESS_EMAIL", true] = "mail@email.de";

KontList[0].Update();

 

But i have a little problem with

 ACTFM1.LogOn(PADFile, userName, password);  //  Application error

 

I don't understand what happened