problem to retrieve an info in a sql request / problème pour recéper une info dans une requête sql
-
System.Data.OleDb.OleDbException: 'No value given for one or more of the required parameters.'
-
Ok, thank you sir. I removed my block of code in the try and here is the error: at the level of sql_cmd.ExecuteNonQuery ();
System.Data.OleDb.OleDbException: 'No value given for one or more of the required parameters.'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);ago2486 wrote:
'No value given for one or more of the required parameters.'
That is not "some error", it tells you exactly what the problem is. You need to make sure you are submitting a value for all parameters.
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.
-
Right: and what do you think that message means? Hint: What do you do immediately after you add the parameter values to
sql_cmd? (This is not a trick question)"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
-
Right: and what do you think that message means? Hint: What do you do immediately after you add the parameter values to
sql_cmd? (This is not a trick question)"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
I even tried this if, I have the same error
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)";
OleDbParameter refprod = sql_cmd.Parameters.AddWithValue("@ref_det", OleDbType.VarChar);
refprod.Value = TxtRefProduit.Text;
OleDbParameter qtecmd = sql_cmd.Parameters.AddWithValue("@qute_det", OleDbType.VarChar);
qtecmd.Value = TxtQteCmd.Text;
OleDbParameter design = sql_cmd.Parameters.AddWithValue("@Designation", OleDbType.VarChar);
design.Value = TxtDesignation.Text;
OleDbParameter prixU = sql_cmd.Parameters.AddWithValue("@Prix_unitaire_HT", OleDbType.Integer);
prixU.Value = TxtPrixUnitaire.Text;
OleDbParameter prixT = sql_cmd.Parameters.AddWithValue("@Prix_total_HT", OleDbType.Integer);
prixT.Value = total;sql\_cmd = new OleDbCommand(txtQuery, sql\_con); sql\_cmd.ExecuteNonQuery(); } -
I even tried this if, I have the same error
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)";
OleDbParameter refprod = sql_cmd.Parameters.AddWithValue("@ref_det", OleDbType.VarChar);
refprod.Value = TxtRefProduit.Text;
OleDbParameter qtecmd = sql_cmd.Parameters.AddWithValue("@qute_det", OleDbType.VarChar);
qtecmd.Value = TxtQteCmd.Text;
OleDbParameter design = sql_cmd.Parameters.AddWithValue("@Designation", OleDbType.VarChar);
design.Value = TxtDesignation.Text;
OleDbParameter prixU = sql_cmd.Parameters.AddWithValue("@Prix_unitaire_HT", OleDbType.Integer);
prixU.Value = TxtPrixUnitaire.Text;
OleDbParameter prixT = sql_cmd.Parameters.AddWithValue("@Prix_total_HT", OleDbType.Integer);
prixT.Value = total;sql\_cmd = new OleDbCommand(txtQuery, sql\_con); sql\_cmd.ExecuteNonQuery(); }Please, look closely at your code. What does this line do exactly:
sql_cmd = new OleDbCommand(txtQuery, sql_con);
Not a trick question: Here it is as multiple choice: 1) Nothing 2) Throw away the existing content of the variable and replace it with a new, empty one. 3) Catch fire and die. 4) Create a new instance and assign all the old data to it. Extra hint: It's less than (3) and greater than (1).
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
-
ago2486 wrote:
'No value given for one or more of the required parameters.'
That is not "some error", it tells you exactly what the problem is. You need to make sure you are submitting a value for all parameters.
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.
Look at his code and see what he does immediately after adding the parameters and their values...
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
-
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();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_cmdvariable; - Throws the
sql_cmdvariable away and sets it to a newOleDbCommandinstance; - Attempts to execute the
sql_cmdwithout adding any parameters to it;
This is yet another reason not to store the
OleDbConnectionandOleDbCommandobjects in class-level fields. The first bit of your code is manipulating a command from a previous method. If thesql_cmdfield hasn't been initialized, you may even get aNullReferenceException. Change your code to create and use a newOleDbCommandinstance, wrapped in ausingblock: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
OleDbConnectionas a local variable wrapped in ausingblock too, and delete thesql_co -
Look at his code and see what he does immediately after adding the parameters and their values...
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
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." :doh:
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.
-
Please, look closely at your code. What does this line do exactly:
sql_cmd = new OleDbCommand(txtQuery, sql_con);
Not a trick question: Here it is as multiple choice: 1) Nothing 2) Throw away the existing content of the variable and replace it with a new, empty one. 3) Catch fire and die. 4) Create a new instance and assign all the old data to it. Extra hint: It's less than (3) and greater than (1).
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
-
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_cmdvariable; - Throws the
sql_cmdvariable away and sets it to a newOleDbCommandinstance; - Attempts to execute the
sql_cmdwithout adding any parameters to it;
This is yet another reason not to store the
OleDbConnectionandOleDbCommandobjects in class-level fields. The first bit of your code is manipulating a command from a previous method. If thesql_cmdfield hasn't been initialized, you may even get aNullReferenceException. Change your code to create and use a newOleDbCommandinstance, wrapped in ausingblock: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
OleDbConnectionas a local variable wrapped in ausingblock too, and delete thesql_co -
Wrong.
Quote:
Extra hint: It's less than (3) and greater than (1).
Think: What does the
newkeyword actually do?"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
-
Wrong.
Quote:
Extra hint: It's less than (3) and greater than (1).
Think: What does the
newkeyword actually do?"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
-
Ok. Suppose we do this:
List myList = new List();
myList.Add("One");
myList.Add("Two");
myList.Add("Three");
myList.Add("Four");
myList.Add("Five");
foreach (string s in myList)
{
Console.WriteLine(s);
}You would expect it do print five lines, and indeed it will:
One
Two
Three
Four
FiveBut what if I do this:
List myList = new List();
myList.Add("One");
myList.Add("Two");
myList.Add("Three");
myList.Add("Four");
myList.Add("Five");
myList = new List();
foreach (string s in myList)
{
Console.WriteLine(s);
}How many lines of print do you expect to get now? (Run the code if you have to, I don't mind.)
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
-
Ok. Suppose we do this:
List myList = new List();
myList.Add("One");
myList.Add("Two");
myList.Add("Three");
myList.Add("Four");
myList.Add("Five");
foreach (string s in myList)
{
Console.WriteLine(s);
}You would expect it do print five lines, and indeed it will:
One
Two
Three
Four
FiveBut what if I do this:
List myList = new List();
myList.Add("One");
myList.Add("Two");
myList.Add("Three");
myList.Add("Four");
myList.Add("Five");
myList = new List();
foreach (string s in myList)
{
Console.WriteLine(s);
}How many lines of print do you expect to get now? (Run the code if you have to, I don't mind.)
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
-
you are right sir I doubted but after having compiled I realized that the variable s is empty.
Yes - and that's because the
newkeyword creates a new, empty instance of the List and throws away the old one when it is assigned to the variable. And your code does exactly the same thing:...
sql_cmd.Parameters.AddWithValue("@Prix_total_HT", total);
sql_cmd = new OleDbCommand(txtQuery, sql_con);
sql_cmd.ExecuteNonQuery();You load up the parameters into an OleDbCommand instance, and then throw it all away to create a new, empty instance and store it in the same variable. To be honest, if you are "just a beginner who learns through some tutorials" then you are doing it all wrong, particularly if these are YouTube tutorials - I've yet to see one that is of any real use whatsoever. Instead, look for a good book on the subject - Apress, Wrox, Addison Wesley, Microsoft Press - they all do excellent beginner volumes though I don't know if any of them are available in French. If you can, look for a copy of "Pro C# 8.0" (APress, I believe), or "C# in a nutshell" (O'Reilly?) - I learnt from those one many, many years ago when .NET was at V2! Books introduce the material in a structured way, building on what has been taught before - and aren't written just to get views and subscribers...
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
-
Yes - and that's because the
newkeyword creates a new, empty instance of the List and throws away the old one when it is assigned to the variable. And your code does exactly the same thing:...
sql_cmd.Parameters.AddWithValue("@Prix_total_HT", total);
sql_cmd = new OleDbCommand(txtQuery, sql_con);
sql_cmd.ExecuteNonQuery();You load up the parameters into an OleDbCommand instance, and then throw it all away to create a new, empty instance and store it in the same variable. To be honest, if you are "just a beginner who learns through some tutorials" then you are doing it all wrong, particularly if these are YouTube tutorials - I've yet to see one that is of any real use whatsoever. Instead, look for a good book on the subject - Apress, Wrox, Addison Wesley, Microsoft Press - they all do excellent beginner volumes though I don't know if any of them are available in French. If you can, look for a copy of "Pro C# 8.0" (APress, I believe), or "C# in a nutshell" (O'Reilly?) - I learnt from those one many, many years ago when .NET was at V2! Books introduce the material in a structured way, building on what has been taught before - and aren't written just to get views and subscribers...
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
really thank you for your advice i will inquire to see if the book is available in french. I did a bit of programming at school but not the csharp, and since it is a langauge that I am passionate about, I decided to take it easy but I really want to know the role of each method used. And for its I thank you again because I followed your advice. But with your permission I would like to send you some project that I have realized thanks to courses on Csharp to just give me your opinion on the presentation of my codes and if there is an improvement to be made.
-
really thank you for your advice i will inquire to see if the book is available in french. I did a bit of programming at school but not the csharp, and since it is a langauge that I am passionate about, I decided to take it easy but I really want to know the role of each method used. And for its I thank you again because I followed your advice. But with your permission I would like to send you some project that I have realized thanks to courses on Csharp to just give me your opinion on the presentation of my codes and if there is an improvement to be made.
Don't send it, I won't look at it. I don't have time to be a mentor to anyone - and I get a couple of request for it a month, and certainly couldn't do it for everyone who asks: so I don't do it at all to be as fair as I can to everybody.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
-
Don't send it, I won't look at it. I don't have time to be a mentor to anyone - and I get a couple of request for it a month, and certainly couldn't do it for everyone who asks: so I don't do it at all to be as fair as I can to everybody.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
-
hello sir all my apologies for the delay ... i was putting my code clean as you advised me. I have an error like microsoft jet engine
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);
}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. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
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. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
