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

ago2486

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]

OriginalGriff

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!

ago2486

merci pour votre réponse je vais revoir mon code et je vous reviens

ago2486

Et surtout merci pour vos conseils

OriginalGriff

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!

Richard Deeming

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

ago2486

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);
}

ago2486

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 Deeming

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

ago2486

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();

ago2486

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 Deeming

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

ago2486

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

ago2486

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)

OriginalGriff

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!

Richard Deeming

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

ago2486

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

ago2486

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.

OriginalGriff

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!

Lost User

But what is the error message?