|
OriginalGriff wrote: Look at his code
Ya, I know. I was more referring to the fact that three separate people asked what the error was and the OP just kept saying "some error." 
Social Media - A platform that makes it easier for the crazies to find each other.
Everyone is born right handed. Only the strongest overcome it.
Fight for left-handed rights and hand equality.
|
|
|
|
|
Hi,
please stop doing
catch(OleDbException ex)
{
MessageBox.Show(ex.Source);
}
instead do
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}
It will (1) be able to catch more problems, and (2) will provide much more information when something goes wrong (including the exact line number when running in Visual Studio); you may not understand all of its output right away, but that typically is the info one needs to easily pinpoint what went wrong.
|
|
|
|
|
Hello sir and thank you for the info, I think I saw it when I was doing my research. And it was written only to see the problems with connection to the database. So I would like to know if we could use it only at the connection of the database or not?
|
|
|
|
|
All exceptions derive from the Exception class, so catching Exception will catch "everything".
Whatever your code is about, it is most often wise to put it inside a try-catch block that catches
and displays all information about all exceptions.
And when your code misbehaves and you don't yet have a try-catch, adding one is the first thing you should do.
modified 18-Mar-20 11:15am.
|
|
|
|
|
thank you sir i will do it now
|
|
|
|
|
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);
}
} OleDbCommand.ExecuteScalar Method (System.Data.OleDb) | Microsoft Docs[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
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 ...
|
|
|
|
|
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
|
|
|
|
|
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;
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
{
if (Int32.TryParse(TxtQteCmd.Text, out int value) && TxtRefProduit.Text != "")
{
int a = 0;
int b = 0;
int c;
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
{
{
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();
}
}
catch(OleDbException ex)
{
MessageBox.Show(ex.Source);
}
try
{
LoadDB();
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
total_achat += Convert.ToDecimal(dataGridView1.Rows[i].Cells[4].Value);
}
TxtTotalCmd.Text = total_achat.ToString();
TxtDesignation.Text = "";
TxtQteCmd.Text = "";
TxtPrixUnitaire.Text = "";
TxtRefProduit.Text = "";
TxtQteCmd.Text = "";
}
catch (OleDbException ex)
{
MessageBox.Show(ex.Source);
sql_con.Close();
}
finally
{
sql_con.Close();
}
}
else
{
MessageBox.Show("Veuillez verifier le stock du produit!!!");
}
}
}
}
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
thank you sir i was afraid i didn't do what you told me.
|
|
|
|
|
private void BtnAjouter_Click(object sender, EventArgs e)
{
int total;
decimal total_achat;
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
{
if (Int32.TryParse(TxtQteCmd.Text, out int value) && TxtRefProduit.Text != "")
{
int a = 0;
int b = 0;
int c;
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
{
{
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();
}
}
catch(OleDbException ex)
{
MessageBox.Show(ex.Source);
}
try
{
LoadDB();
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
total_achat += Convert.ToDecimal(dataGridView1.Rows[i].Cells[4].Value);
}
TxtTotalCmd.Text = total_achat.ToString();
TxtDesignation.Text = "";
TxtQteCmd.Text = "";
TxtPrixUnitaire.Text = "";
TxtRefProduit.Text = "";
TxtQteCmd.Text = "";
}
catch (OleDbException ex)
{
MessageBox.Show(ex.Source);
sql_con.Close();
}
finally
{
sql_con.Close();
}
}
else
{
MessageBox.Show("Veuillez verifier le stock du produit!!!");
}
}
}
}
|
|
|
|
|
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:
{
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();
}
- 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
|
|
|
|
|
Thank you very much to you I try it and I come back to you
|
|
|
|
|
and here is my connection
private void setConnection()
{
try
{
string connetionString = null;
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);
}
}
|
|
|
|
|
But what is the error message?
|
|
|
|
|
System.Data.OleDb.OleDbException: 'No value given for one or more of the required parameters.'
|
|
|
|
|
That cannot be true since there is no SQL command in the code you posted.
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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";
BasicHttpBinding _binding = new BasicHttpBinding();
_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;
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();
My C# attempt to Update or Create a new record in the table using the ODATA web service:
string _url = "https://api.businesscentral.dynamics.com/v2.0/SomeFunkyGuid/Sandbox/ODataV4/Company('CRONUS%20NL')/WorkersWS";
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 = "PATCH";
string _etag = rtbE_Tag.Text;
_request.Headers["If-Match"] = String.Format("W/\"{0}\"", _etag);
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;
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";
BasicHttpBinding _binding = new BasicHttpBinding();
_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
|
|
|
|
|
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
|
|
|
|
|
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";
string _userName = "UserName";
string _wsKey = "Password";
_client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue(_userName, _wsKey);
HttpResponseMessage response = await _client.PostAsJsonAsync(_url, _worker);
response.EnsureSuccessStatusCode();
return response.Headers.Location;
}
I tried the following:
var body = new
{
E_Tag = string.Empty,
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
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
