Re: problem registering in the access database - C# Discussion Boards

Re: problem to retrieve an info in a sql request / problème pour recéper une info dans une requête sql

17-Mar-20 23:20

thank you sir i will do it now

Richard Deeming16-Mar-20 9:28

OleDbCommand.ExecuteScalar Method (System.Data.OleDb) | Microsoft Docs[^]

16-Mar-20 9:28

As Griff already pointed out, your first command needs to use a parameter. You should also wrap the OleDbCommand object in a using block, and get rid of the ExecuteQuery method.

using (var cmd = sql_con.CreateCommand())
{
    cmd.CommandText = "INSERT INTO Commandes (montant_com) VALUES (@montant_com)";
    cmd.Parameters.AddWithValue("@montant_com", TxtTotalCmd.Text);
    cmd.ExecuteNonQuery();
}

OleDbParameterCollection.AddWithValue(String, Object) Method (System.Data.OleDb) | Microsoft Docs[^]

The second command does not need any parameters. But you do need to execute the command and read the returned value.

long numCmd;
using (var cmd = sql_con.CreateConnection())
{
    cmd.CommandText = "SELECT MAX(num_com) AS dernier_num FROM Commandes";
    
    object result = cmd.ExecuteScalar();
    if (result is null || Convert.IsDBNull(result))
    {
        numCmd = 0L;
    }
    else
    {
        numCmd = Convert.ToInt64(result);
    }
}

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

problem registering in the access database

ago248617-Mar-20 0:58

17-Mar-20 0:58

Hello sir and thank you for your help. I did as you advised me but I have a microsoft jet engine error. I will see another tutorial on this subject if I will find a solution ...

Richard Deeming17-Mar-20 1:01

17-Mar-20 1:01

If you want someone to help you fix an error, you need to give us the full details of the error.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

ago248617-Mar-20 1:08

17-Mar-20 1:08

ok thank you sir. But there is not much detail except that when I submit the button to add I receive the message microsoft jet engine.

private void BtnAjouter_Click(object sender, EventArgs e)
       {
           int total;
           decimal total_achat;
           //si les champs sont vides
           if (TxtDesignation.Text == "" || TxtPrixUnitaire.Text == "" || TxtQteCmd.Text == "" || TxtQteStock.Text == "" || TxtRefProduit.Text == "")
           {
               MessageBox.Show("Rassurez vous que tous les champs ont bien été rempli.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
           }
           else
           {
               //Vérifier si la valeur saisie est numeric
               if (Int32.TryParse(TxtQteCmd.Text, out int value) && TxtRefProduit.Text != "")
               {
                   //Vérifier si la commande est inferieur ou egale au stock
                   int a = 0;
                   int b = 0;
                   int c;

                   //Convertire les textBox en int
                   int.TryParse(TxtQteCmd.Text.Trim(), out a);
                   int.TryParse(TxtQteStock.Text.Trim(), out b);
                   int.TryParse(TxtPrixUnitaire.Text.Trim(), out c);

                   if (a <= b)
                   {
                       total = a * c;
                       total_achat = 0;

                       try
                       {
                           //setConnection();
                          // sql_con.Open();
                          // using (sql_cmd = sql_con.CreateCommand())
                           {
                              string txtQuery = "INSERT INTO Detail_temp (ref_det, qute_det, Designation, Prix_unitaire_HT, Prix_total_HT) VALUES (@ref_det,@qute_det,@Designation,@Prix_unitaire_HT,@Prix_total_HT)";
                              sql_cmd.Parameters.AddWithValue("@ref_det", TxtRefProduit.Text);
                              sql_cmd.Parameters.AddWithValue("@qute_det", TxtQteCmd.Text);
                              sql_cmd.Parameters.AddWithValue("@Designation", TxtDesignation.Text);
                              sql_cmd.Parameters.AddWithValue("@Prix_unitaire_HT", TxtPrixUnitaire.Text);
                              sql_cmd.Parameters.AddWithValue("@Prix_total_HT", total);
                              sql_cmd = new OleDbCommand(txtQuery, sql_con);
                              sql_cmd.ExecuteNonQuery();
                               //ExecuteQuery(txtQuery);
                           }
                       }
                       catch(OleDbException ex)
                       {
                           MessageBox.Show(ex.Source);
                       }

                       //Création de la requête sql pour inserer des valeurs dans DB
                      //string txtQuery = "INSERT INTO Detail_temp (ref_det, qute_det, Designation, Prix_unitaire_HT, Prix_total_HT) VALUES ('"+ TxtRefProduit.Text+ "', '" + TxtQteCmd.Text + "', '" + TxtDesignation.Text + "', '" + TxtPrixUnitaire.Text + "', '" + total + "')";

                      //ExecuteQuery(txtQuery);

                       try
                       {

                           LoadDB();

                           //ADDITIONNER UNE COLONNE DU DATAGRIDVIEW
                           for (int i = 0; i < dataGridView1.Rows.Count; i++) //Car tu fais une infériorité stricte
                           {
                               total_achat += Convert.ToDecimal(dataGridView1.Rows[i].Cells[4].Value);

                           }
                           TxtTotalCmd.Text = total_achat.ToString();//convertir total_achat en string^pour pouvoir l'afficher dans le textbox
                           TxtDesignation.Text = "";
                           TxtQteCmd.Text = "";
                           TxtPrixUnitaire.Text = "";
                           TxtRefProduit.Text = "";
                           TxtQteCmd.Text = "";
                           //SupDetail_Temp();

                       }

                       catch (OleDbException ex)
                       {
                           MessageBox.Show(ex.Source);
                           sql_con.Close();
                       }
                       finally
                       {
                           sql_con.Close();
                       }

                   }
                   else
                   {
                       MessageBox.Show("Veuillez verifier le stock du produit!!!");
                   }
               }

           }

       }

Richard Deeming17-Mar-20 1:10

17-Mar-20 1:10

That's not the full error message.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

ago248617-Mar-20 1:17

17-Mar-20 1:17

I followed your advice, sir, regarding possible SQL injection. I may be wrong but I made the parameter request as you told me, but if he has other errors, can you help me please

Richard Deeming17-Mar-20 1:35

17-Mar-20 1:35

Sorry, I posted that before I noticed that you'd commented-out the vulnerable code.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

ago248617-Mar-20 2:00

17-Mar-20 2:00

thank you sir i was afraid i didn't do what you told me.

ago248617-Mar-20 1:18

17-Mar-20 1:18

private void BtnAjouter_Click(object sender, EventArgs e)
       {
           int total;
           decimal total_achat;
           //si les champs sont vides
           if (TxtDesignation.Text == "" || TxtPrixUnitaire.Text == "" || TxtQteCmd.Text == "" || TxtQteStock.Text == "" || TxtRefProduit.Text == "")
           {
               MessageBox.Show("Rassurez vous que tous les champs ont bien été rempli.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
           }
           else
           {
               //Vérifier si la valeur saisie est numeric
               if (Int32.TryParse(TxtQteCmd.Text, out int value) && TxtRefProduit.Text != "")
               {
                   //Vérifier si la commande est inferieur ou egale au stock
                   int a = 0;
                   int b = 0;
                   int c;

                   //Convertire les textBox en int
                   int.TryParse(TxtQteCmd.Text.Trim(), out a);
                   int.TryParse(TxtQteStock.Text.Trim(), out b);
                   int.TryParse(TxtPrixUnitaire.Text.Trim(), out c);

                   if (a <= b)
                   {
                       total = a * c;
                       total_achat = 0;

                       try
                       {
                           //setConnection();
                          // sql_con.Open();
                          // using (sql_cmd = sql_con.CreateCommand())
                           {
                              string txtQuery = "INSERT INTO Detail_temp (ref_det, qute_det, Designation, Prix_unitaire_HT, Prix_total_HT) VALUES (@ref_det,@qute_det,@Designation,@Prix_unitaire_HT,@Prix_total_HT)";
                              sql_cmd.Parameters.AddWithValue("@ref_det", TxtRefProduit.Text);
                              sql_cmd.Parameters.AddWithValue("@qute_det", TxtQteCmd.Text);
                              sql_cmd.Parameters.AddWithValue("@Designation", TxtDesignation.Text);
                              sql_cmd.Parameters.AddWithValue("@Prix_unitaire_HT", TxtPrixUnitaire.Text);
                              sql_cmd.Parameters.AddWithValue("@Prix_total_HT", total);
                              sql_cmd = new OleDbCommand(txtQuery, sql_con);
                              sql_cmd.ExecuteNonQuery();
                               //ExecuteQuery(txtQuery);
                           }
                       }
                       catch(OleDbException ex)
                       {
                           MessageBox.Show(ex.Source);
                       }



                       try
                       {

                           LoadDB();

                           //ADDITIONNER UNE COLONNE DU DATAGRIDVIEW
                           for (int i = 0; i < dataGridView1.Rows.Count; i++) //Car tu fais une infériorité stricte
                           {
                               total_achat += Convert.ToDecimal(dataGridView1.Rows[i].Cells[4].Value);

                           }
                           TxtTotalCmd.Text = total_achat.ToString();//convertir total_achat en string^pour pouvoir l'afficher dans le textbox
                           TxtDesignation.Text = "";
                           TxtQteCmd.Text = "";
                           TxtPrixUnitaire.Text = "";
                           TxtRefProduit.Text = "";
                           TxtQteCmd.Text = "";
                           //SupDetail_Temp();

                       }

                       catch (OleDbException ex)
                       {
                           MessageBox.Show(ex.Source);
                           sql_con.Close();
                       }
                       finally
                       {
                           sql_con.Close();
                       }

                   }
                   else
                   {
                       MessageBox.Show("Veuillez verifier le stock du produit!!!");
                   }
               }

           }

       }

Richard Deeming17-Mar-20 2:57

17-Mar-20 2:57

Griff has already spotted the problem in the thread above, but in case it's not obvious, here's what your code is doing:

Quote:

// using (sql_cmd = sql_con.CreateCommand())
{
    string txtQuery = "INSERT INTO Detail_temp (ref_det, qute_det, Designation, Prix_unitaire_HT, Prix_total_HT) VALUES (@ref_det,@qute_det,@Designation,@Prix_unitaire_HT,@Prix_total_HT)";
    sql_cmd.Parameters.AddWithValue("@ref_det", TxtRefProduit.Text);
    sql_cmd.Parameters.AddWithValue("@qute_det", TxtQteCmd.Text);
    sql_cmd.Parameters.AddWithValue("@Designation", TxtDesignation.Text);
    sql_cmd.Parameters.AddWithValue("@Prix_unitaire_HT", TxtPrixUnitaire.Text);
    sql_cmd.Parameters.AddWithValue("@Prix_total_HT", total);
    sql_cmd = new OleDbCommand(txtQuery, sql_con);
    sql_cmd.ExecuteNonQuery();
    //ExecuteQuery(txtQuery);
}

Creates a string variable to hold the query;
Adds 5 parameters to the sql_cmd variable;
Throws the sql_cmd variable away and sets it to a new OleDbCommand instance;
Attempts to execute the sql_cmd without adding any parameters to it;

This is yet another reason not to store the OleDbConnection and OleDbCommand objects in class-level fields. The first bit of your code is manipulating a command from a previous method. If the sql_cmd field hasn't been initialized, you may even get a NullReferenceException.

Change your code to create and use a new OleDbCommand instance, wrapped in a using block:

using (OleDbCommand cmd = sql_con.CreateCommand())
{
    cmd.CommandText = "INSERT INTO Detail_temp (ref_det, qute_det, Designation, Prix_unitaire_HT, Prix_total_HT) VALUES (@ref_det,@qute_det,@Designation,@Prix_unitaire_HT,@Prix_total_HT)";
    
    cmd.Parameters.AddWithValue("@ref_det", TxtRefProduit.Text);
    cmd.Parameters.AddWithValue("@qute_det", TxtQteCmd.Text);
    cmd.Parameters.AddWithValue("@Designation", TxtDesignation.Text);
    cmd.Parameters.AddWithValue("@Prix_unitaire_HT", TxtPrixUnitaire.Text);
    cmd.Parameters.AddWithValue("@Prix_total_HT", total);
    
    sql_con.Open();
    cmd.ExecuteNonQuery();
}

Ideally, you should change your code to create the OleDbConnection as a local variable wrapped in a using block too, and delete the sql_con and sql_cmd fields.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

ago248617-Mar-20 3:14

17-Mar-20 3:14

Thank you very much to you I try it and I come back to you

ago248617-Mar-20 1:10

17-Mar-20 1:10

and here is my connection

private void setConnection()
     {
         try
         {
             string connetionString = null;
             //connection à la base de donnée avec mot de passe
             connetionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + Application.StartupPath + @"\DB_CaisseEnregistreuse.mdb;Persist Security Info=True;Jet OLEDB:Database Password=B@sta08091987";
             sql_con = new OleDbConnection(connetionString);
         }
         catch (Exception ex)
         {
             MessageBox.Show("Erreur de connexion à la base donnée" + ex.Message);
         }


     }

Richard MacCutchan17-Mar-20 2:18

Richard MacCutchan

17-Mar-20 2:18

But what is the error message?

ago248617-Mar-20 2:26

17-Mar-20 2:26

System.Data.OleDb.OleDbException: 'No value given for one or more of the required parameters.'

Java Api Callbacks crashes on dotnet framework version 4.5 and above

Richard MacCutchan17-Mar-20 2:36

Richard MacCutchan

17-Mar-20 2:36

That cannot be true since there is no SQL command in the code you posted.

isaketranjan16-Mar-20 0:06

isaketranjan

16-Mar-20 0:06

Hi All, trying to work with Java Access Bridge in c#, everything works fine however whenever i try to subscribe on dotnet framework version 4.5 and above e.g.

JabApi.MouseClickedDelegate mcd = new  JabApi.MouseClickedDelegate(HandleMouseClickedDelegate);
JabApi.setMouseClickedFP(mcd);

Application crashes, but same code works fine on dotnet framework version 4.0.
Checked Event log : Faulting module name: windowsaccessbridge-64.dll

Re: Java Api Callbacks crashes on dotnet framework version 4.5 and above

Gerry Schmitz16-Mar-20 5:05

Gerry Schmitz

16-Mar-20 5:05

The "Bridge" appears to involve components (for testing) that are no longer supported. I'd think a # COM server or something would be more forward-looking.

It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food

C# and ODATA CRUD communication with D365BC web service

clemenslinders12-Mar-20 0:13

clemenslinders

12-Mar-20 0:13

I have a problem where C# cannot Create/Update/Delete through an ODATA web service running from D365BC.

PS I am using VS 2019, C# winforms.

Our company is going to use Dynamics 365 Business Central (D365BC), which uses the programming language AL.

AL is a nice language but some things we will simply prefer to do in C#.

In D365BC you can easily create tables and pages (objects that consume these tables). And for each page you can create a web service by simply clicking a checkbox. These web service can be used to communicate via SOAP/ODATA v3 and ODATA v4. All are available at the same time.

So creating a web service that allows communication with a table created in D365BC is fairly easy.

If I use the SOAP webservice I can perform all CRUD functions. But when I use ODATA (either v3 or v4) I can read from the web service but I cannot Create/Update or Delete. The reason that we may occasionally want to use ODATA, is that ODATA is faster than SOAP.

The code I have in D365BC:
A simple table where every field is a record in a SQL table.

table 50109 "Workers"
{
    DataClassification = ToBeClassified;

    fields
    {
        field(1; "No."; Code[20])
        {
            DataClassification = ToBeClassified;
        }

        field(10; "First name"; Text[50])
        {
            DataClassification = ToBeClassified;
        }

        field(20; "Last Name"; Text[50])
        {
            DataClassification = ToBeClassified;

        }

        field(40; FunctionName; Text[50])
        {
            DataClassification = ToBeClassified;

        }
    }

    trigger OnInsert()
    var
        myInt: Integer;
    begin

    end;

    trigger OnModify()
    var
        myInt: Integer;
    begin

    end;

    trigger OnDelete()
    var
        myInt: Integer;
    begin

    end;
}

The card page that uses the table and has the ability to make a web service out of the used table.

page 50108 "Workers Card"
{
    PageType = Card;
    ApplicationArea = All;
    UsageCategory = Administration;
    SourceTable = Workers;

    layout
    {
        area(Content)
        {
            group(General)
            {
                field("No."; "No.")
                {
                    ApplicationArea = Basic;
                    Importance = Promoted;
                }

                field("First name"; "First name")
                {
                    ApplicationArea = Basic;
                }

                field("Last name"; "Last name")
                {
                    ApplicationArea = Basic;
                }

                field(FunctionName; FunctionName)
                {
                    ApplicationArea = Basic;

                }
            }
        }
    }
}

Than there is a list page, that is used in D365BC to get a list of records, not really needed for the web service, but I provide it just to be complete.

page 50109 "Workers List"
{
    PageType = List;
    ApplicationArea = All;
    UsageCategory = Lists;
    SourceTable = Workers;

    layout
    {
        area(Content)
        {
            repeater(Group)
            {
                field("No."; "No.")
                {
                    ApplicationArea = Basic;
                }

                field("First name"; "First name")
                {
                    ApplicationArea = Basic;
                }

                field("Last Name"; "Last Name")
                {
                    ApplicationArea = Basic;
                }

                field(FunctionName; FunctionName)
                {
                    ApplicationArea = Basic;
                }
            }
        }
    }
}

In order to be able to create the web service we need to provide a little xml file to D365BC. I provide this, just to be complete.

<?xml version = "1.0" encoding = "utf-8" ?>
<ExportedData>
    <TenantWebServiceCollection>
        <TenanatWebService>
            <ObjectType>Page</ObjectType>
            <ObjectID>50108</ObjectID>
            <ServiceName>WorkersWS</ServiceName>
            <Published>true</Published>
        </TenanatWebService>
    </TenantWebServiceCollection>
</ExportedData>

Using C3 to read from the ODATA web service is no problem:

listBox1.Items.Clear();
WorkersReadFromAlWebService = new List<WorkerClass>();
string _wsURL = "https://api.businesscentral.dynamics.com/v2.0/SomeFunfyGuid/Sandbox/WS/CRONUS%20NL/Page/WorkersWS";
string _userName = "UserName";
string _wsKey = "Password";

//Create an instance of the D365BC SOAP WS
BasicHttpBinding _binding = new BasicHttpBinding();

//Set https usage
_binding.Security.Mode = BasicHttpSecurityMode.Transport;
_binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Basic;

using (WorkersWS_PortClient _ws = new WorkersWS_PortClient(_binding, new EndpointAddress(_wsURL)))
{
    _ws.ClientCredentials.UserName.UserName = _userName;
    _ws.ClientCredentials.UserName.Password = _wsKey;

    //Filter
    List<WorkersWS_Filter> _filters = new List<WorkersWS_Filter>();
    WorkersWS_Filter _filter = new WorkersWS_Filter
    {
        Field = WorkersWS_Fields.No,
        Criteria = "*"
    };
    _filters.Add(_filter);

    try
    {
        foreach (WorkersWS _workerWS in _ws.ReadMultiple(_filters.ToArray(), "", 0))
        {
            WorkerClass _wc = new WorkerClass();
            _wc.E_Tag = _workerWS.Key;
            _wc.First_Name = _workerWS.First_name;
            _wc.Last_Name = _workerWS.Last_Name;
            _wc.FunctionName = _workerWS.FunctionName;
            WorkersReadFromAlWebService.Add(_wc);
            listBox1.Items.Add(_workerWS.Last_Name + " / " + _workerWS.First_name + " (No: " + _workerWS.No + ")");
        }
    }
    catch (Exception _ex)
    {

    }
}

ClearWorker();//Clears some textboxes

My C# attempt to Update or Create a new record in the table using the ODATA web service:

//using WorkersWS uses a List page in the background, which is capable of updating and inserting. WorkersWebService uses a card page, which is NOT capable of updating and inserting. But unfortunately the errors remain.

string _url = "https://api.businesscentral.dynamics.com/v2.0/SomeFunkyGuid/Sandbox/ODataV4/Company('CRONUS%20NL')/WorkersWS";
//string _url = "https://api.businesscentral.dynamics.com/v2.0/SomeFunkyGuid/Sandbox/ODataV3/Company('CRONUS%20NL')/WorkersWS";//ODATA V3, makes no difference
HttpWebRequest _request = (HttpWebRequest)WebRequest.Create(_url);//
_request.ContentType = "application/json; charset=utf-8";
_request.Headers["Authorization"] = "Basic " + Convert.ToBase64String(Encoding.GetEncoding("ISO-8859-1").GetBytes("UserName:Password"));
_request.Accept = "*/*";
_request.KeepAlive = true;
//_request.Method = "PUT";//error 405: Method is not allowed
_request.Method = "PATCH";//error 405: Methode is not allowed
string _etag = rtbE_Tag.Text;
_request.Headers["If-Match"] = String.Format("W/\"{0}\"", _etag);
//_request.Method = "POST";//(when using POST, the 4 lines above are commented out)error 400: invalid method

string body = "{" + Environment.NewLine +
                    "\"No\":" + tbNo.Text + "," + Environment.NewLine +
                    "\"First_name\":\"" + tbFirstName.Text + "\"," + Environment.NewLine +
                    "\"Last_Name\":\"" + tbLastName.Text + "\"," + Environment.NewLine +
                    "\"FunctionName\":\"" + tbFunctionName.Text + "\"," + Environment.NewLine +
                  "}";

byte[] data = Encoding.ASCII.GetBytes(body);

try
{
    _request.ContentLength = data.Length;

    Stream requestStream = _request.GetRequestStream();
    requestStream.Write(data, 0, data.Length);
    requestStream.Close();

    HttpWebResponse _response = _request.GetResponse() as HttpWebResponse;//Here we get the exception errors 400 or 405
    Console.WriteLine(_response.StatusCode);
}
catch (Exception ex)
{

}

So it makes no difference if I use Patch, Put or Post (except for the error number).

I tried to get an anser through D365BC forums, but I simply get no reply (at least not a reply that is of any help).

Finding answers yourselves is like the verbal 'needle in the haystack'. D365BC uses the relative new language AL. And if you find something it usually is for Navision, which uses C/AL, which is completely different.

I also tried a completely different approach by using the Nuget package manager and see what ODATA packages are there.

Many of these are created for MVC (I am using Win Forms).
But I tried:
- Microsoft.OData.Data (for use with ODATA v3, It doesn't simply install on VS 2019, but using something that I found online I got it to install on VS 2019, but all these sample assume an ODATA web service without authentication. Using authentication, requires setting some settings in the OdataClient.odata.config file. Well this file isn't created (perhaps only when using MVC), if I create it myself and I set the settings for authentication, it simply does nothing. I get an authentication error, password/username not set)
- Microsoft.OData.Core (for use with ODATA v4, behaves exactly like the .DATA version (so same problems), again no luck)
- Simple.Odata.Client (couldn't get authenticated, which is a show stopper)
- tried a third one as well (forgot the name), but also simply using the authentication is a problem.

When I speak about this problem with some people they more or less all claim that there isn't much difference between SOAP and ODATA. Yet SOAP works and ODATA doesn't.

I hope there is someone who has experience with this and is willing and capable to help me.

I will end with the SOAP code that works and which we can use to update a record:

string _wsURL = "https://api.businesscentral.dynamics.com/v2.0/SomeFunkyGuid/Sandbox/WS/CRONUS%20NL/Page/WorkersWS";
string _userName = "Username";
string _wsKey = "Password";

//Create an instance of the D365BC SOAP WS
BasicHttpBinding _binding = new BasicHttpBinding();

//Set https usage
_binding.Security.Mode = BasicHttpSecurityMode.Transport;
_binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Basic;

using (WorkersWS_PortClient _ws = new WorkersWS_PortClient(_binding, new EndpointAddress(_wsURL)))
{
    _ws.ClientCredentials.UserName.UserName = _userName;
    _ws.ClientCredentials.UserName.Password = _wsKey;

    try
    {
        WorkersWS _wws = _ws.Read(tbNo.Text);
        if (_wws.No == tbNo.Text)
        {
            _wws.First_name = tbFirstName.Text;
            _wws.Last_Name = tbLastName.Text;
            _wws.FunctionName = tbFunctionName.Text;
            _ws.Update(ref _wws);

            if (_wws.No == tbNo.Text)
            {
                tbResult.Text = "Update success.";
            }
        }
    }
    catch (Exception _ex)
    {

    }
}

Kind regards,

Clemens Linders

Re: C# and ODATA CRUD communication with D365BC web service

Richard Deeming12-Mar-20 1:12

Re: C# and ODATA CRUD communication with D365BC web service

12-Mar-20 1:12

Rather than building the request body by hand, you should use a JSON serializer to create it.

Add a NuGet package reference to JSON.NET[^], add a using directive for the Newtonsoft.Json namespace, and change your code to:

var body = new
{
    No = tbNo.Text,
    First_name = tbFirstName.Text,
    Last_Name = tbLastName.Text,
    FunctionName = tbFunctionName.Text,
};

string json = JsonConvert.SerializeObject(body);
byte[] data = Encoding.UTF8.GetBytes(json);

NB: You'll want to use the UTF8 encoding rather than ASCII, since that's what you've declared in your content-type header.

I'd also be inclined to use the HttpClient class[^] rather than the low-level HttpWebRequest class:
Call a Web API From a .NET Client (C#) - ASP.NET 4.x | Microsoft Docs[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

clemenslinders12-Mar-20 3:48

clemenslinders

12-Mar-20 3:48

Hi Richard,

Thanks for your reply.

If I used the code you profided me but I cannot pass 'data' to _client as it expects a class and not Json data.

If I create a class and fill it, like in the MS Docs page you provided, than I get error 401 not authorized.

WorkersClass _data = new WorkersClass();
_data.E_Tag = string.Empty;
_data.No = tbNo.Text;
_data.First_name = tbFirstName.Text;
_data.Last_Name = tbLastName.Text;
_data.FunctionName = tbFunctionName.Text;

var url = await CreateWorkerAsync(_data);

static async Task<Uri> CreateWorkerAsync(WorkersClass _worker)
{
    string _url = "https://api.businesscentral.dynamics.com/v2.0/SomeFunkyGuid/Sandbox/ODataV4/Company('CRONUS%20NL')/WorkersWebService";//Card Page
    string _userName = "UserName";
    string _wsKey = "Password";
    _client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue(_userName, _wsKey);
    HttpResponseMessage response = await _client.PostAsJsonAsync(_url, _worker);
    response.EnsureSuccessStatusCode();

    // return URI of the created resource.
    return response.Headers.Location;
}

I tried the following:

var body = new
{
    E_Tag = string.Empty,     // Unique key
    No = tbNo.Text,
    First_name = tbFirstName.Text,
    Last_Name = tbLastName.Text,
    FunctionName = tbFunctionName.Text,
};


string json = JsonConvert.SerializeObject(body);
byte[] data = Encoding.UTF8.GetBytes(json);

var url = await CreateWorkerAsync(data);

The error I get is: Argument 1: cannot convert from byte[] to WorkersClass.

If I look at _client.PostAsJsonAsync, than I don't see a other solution where it says I can pass Json data?

I feel that I am close, but I think I miss the final step.

Hope you can help me.

Kind regards,

Clemens Linders

Re: C# and ODATA CRUD communication with D365BC web service

Richard Deeming12-Mar-20 4:29

Re: C# and ODATA CRUD communication with D365BC web service

12-Mar-20 4:29

I was referring to the code from your original post, where you're constructing a JSON string manually:

Quote:

string body = "{" + Environment.NewLine +
                    "\"No\":" + tbNo.Text + "," + Environment.NewLine +
                    "\"First_name\":\"" + tbFirstName.Text + "\"," + Environment.NewLine +
                    "\"Last_Name\":\"" + tbLastName.Text + "\"," + Environment.NewLine +
                    "\"FunctionName\":\"" + tbFunctionName.Text + "\"," + Environment.NewLine +
                  "}";

byte[] data = Encoding.ASCII.GetBytes(body);

try
{
    _request.ContentLength = data.Length;

    Stream requestStream = _request.GetRequestStream();
    requestStream.Write(data, 0, data.Length);
    requestStream.Close();

    HttpWebResponse _response = _request.GetResponse() as HttpWebResponse;//Here we get the exception errors 400 or 405
    Console.WriteLine(_response.StatusCode);
}
catch (Exception ex)
{

}

You don't need to worry about the JSON serialization with the HttpClient and the PostAsJsonAsync method - it handles the serialization for you.

You're getting a 401 error with your new code because you're passing the wrong values to the AuthenticationHeaderValue. The first parameter is the scheme, which should be "BASIC", and the second is the parameter, which should be the combined username and password.
AuthenticationHeaderValue Constructor (System.Net.Http.Headers) | Microsoft Docs[^]

string _userName = "UserName";
string _wsKey = "Password";
byte[] authenticationParameter = Encoding.UTF8.GetBytes(_userName + ":" + _wsKey);
_client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Basic", Convert.ToBase64String(authenticationParameter));

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

clemenslinders12-Mar-20 4:54

clemenslinders

12-Mar-20 4:54

Hi Richard,

I changed the CreateWorkersAsync:

static async Task<Uri> CreateWorkerAsync(WorkersClass _worker)
{
    string _url = "https://api.businesscentral.dynamics.com/v2.0/SomeFunkyGuid/Sandbox/ODataV4/Company('CRONUS%20NL')/WorkersWebService";//Card Page
    string _userName = "UserName";
    string _wsKey = "Password";
    byte[] _authenticationParameter = Encoding.UTF8.GetBytes(_userName + ":" + _wsKey);
    _client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Basic", Convert.ToBase64String(_authenticationParameter));
    HttpResponseMessage response = await _client.PostAsJsonAsync(_url, _worker);
    response.EnsureSuccessStatusCode();

    // return URI of the created resource.
    return response.Headers.Location;
}

I didn't make any other changes.

I now no longer get the error 401 Not Authorized.

Now I get the error: 400 Bad request

We're one step closer.

Do you have any idea why I could get this Bad request??

Kind regards,

Clemens Linders

Re: C# and ODATA CRUD communication with D365BC web service

Richard Deeming12-Mar-20 5:43