problem to retrieve an info in a sql request / problème pour recéper une info dans une requête sql
-
Don't do it like that! Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead. When you concatenate strings, you cause problems because SQL receives commands like:
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
DROP TABLE MyTable;
A perfectly valid "delete the table" command
--'
And everything else is a comment. So it does: selects any matching rows, deletes the table from the DB, and ignores anything else. So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you? And when you've fixed that throughout you app, start looking at the problem you have noticed. And that is probably in your ExecuteQuery method: if you are creating an SQL Reader ther, then you can't issue any more commands on that connection until the Reader is closed...
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
-
You're welcome - but please use English in future; this is an English language site and we only normally accept questions in that language. Google Translate does a pretty good job and it saves everybody else from having to do it for you!
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
-
Bonsoir les amis veuillez m’excuser mais un problème pour recéper une info dans une requete sql. Good evening friends please excuse me but a problem to receive information in a sql request.
try
{
setConnection();
sql_con.Open();
sql_cmd = sql_con.CreateCommand();
string CommandText = "INSERT INTO Commandes (montant_com) VALUES ('" + TxtTotalCmd.Text + "')";
ExecuteQuery(CommandText);long numCmd; string CommandText1 = "SELECT MAX(num\_com) AS dernier\_num FROM Commandes"; sql\_cmd = new OleDbCommand(CommandText1, sql\_con); numCmd = sql\_cmd.Parameters.Add("dernier\_num"); (j'ai une erreur à ce niveau) }[edit]Google Translate added - OriginalGriff[/edit]
As Griff already pointed out, your first command needs to use a parameter. You should also wrap the
OleDbCommandobject in ausingblock, and get rid of theExecuteQuerymethod.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
-
Don't do it like that! Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead. When you concatenate strings, you cause problems because SQL receives commands like:
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
DROP TABLE MyTable;
A perfectly valid "delete the table" command
--'
And everything else is a comment. So it does: selects any matching rows, deletes the table from the DB, and ignores anything else. So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you? And when you've fixed that throughout you app, start looking at the problem you have noticed. And that is probably in your ExecuteQuery method: if you are creating an SQL Reader ther, then you can't issue any more commands on that connection until the Reader is closed...
"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);
} -
As Griff already pointed out, your first command needs to use a parameter. You should also wrap the
OleDbCommandobject in ausingblock, and get rid of theExecuteQuerymethod.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
-
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;
//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(); -
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();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
-
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
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);
}} -
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
-
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
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) -
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);
}Quote:
I have an error like microsoft jet engine
Is pretty meaningless on it's own ...
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
-
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
-
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
-
Quote:
I have an error like microsoft jet engine
Is pretty meaningless on it's own ...
"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 but frankly I don't know how to explain it to you. but once I submit the add button after filling in all the fields as it should be this is the message I receive. And nothing in the access database.
What is the message you recieve? What line does it happen on? What does the debugger show you is going on? So far, all I have is "I have an error like microsoft jet engine" which tells me nothing at all!
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
-
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);
}} -
What is the message you recieve? What line does it happen on? What does the debugger show you is going on? So far, all I have is "I have an error like microsoft jet engine" which tells me nothing at all!
"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. 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); -
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);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!
