Hazzza Comments - Answer Overflow

Hazzza

Posts Comments

CC#

•Created by Hazzza on 12/3/2024 in #help

SQL gives Missing operator error

They replace all the question marks which the recipe string adds

14 replies

CC#

•Created by Hazzza on 12/3/2024 in #help

SQL gives Missing operator error

The parameters are sent off the the SQL string

C#
DataTable result = databaseUtils.ExecuteSqlQuery(_sSqlString, Parameters);

C#
DataTable result = databaseUtils.ExecuteSqlQuery(_sSqlString, Parameters);

Then are inserted as parameters into the command

C#
public DataTable ExecuteSqlQuery(String sSqlString, string[] Parameters)
{
    OleDbConnection cnn = new OleDbConnection(CONNECTION_STRING); // Create a new connection to the database
    OleDbCommand cmd = new OleDbCommand(sSqlString, cnn); // sSqlString string contains a SQL statement to run on the database

    if (Parameters != null)
    {
        for (int i = 0; i < Parameters.Length; i++)
        {
            cmd.Parameters.Add(new OleDbParameter { Value = Parameters[i] });
        }
    }

    OleDbDataAdapter da = new OleDbDataAdapter(cmd);

    DataTable dt = new DataTable();

    try
    {
        cnn.Open();
        da.Fill(dt);
        cnn.Close();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Product Management System", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
    }
    return dt; // Return the data retrieved by the query
}

C#
public DataTable ExecuteSqlQuery(String sSqlString, string[] Parameters)
{
    OleDbConnection cnn = new OleDbConnection(CONNECTION_STRING); // Create a new connection to the database
    OleDbCommand cmd = new OleDbCommand(sSqlString, cnn); // sSqlString string contains a SQL statement to run on the database

    if (Parameters != null)
    {
        for (int i = 0; i < Parameters.Length; i++)
        {
            cmd.Parameters.Add(new OleDbParameter { Value = Parameters[i] });
        }
    }

    OleDbDataAdapter da = new OleDbDataAdapter(cmd);

    DataTable dt = new DataTable();

    try
    {
        cnn.Open();
        da.Fill(dt);
        cnn.Close();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Product Management System", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
    }
    return dt; // Return the data retrieved by the query
}

14 replies

CC#

•Created by Hazzza on 12/3/2024 in #help

SQL gives Missing operator error

C#
string RecipeString = "";            
int count = 0;
foreach (string name in Recipes.elements)
{
   if(name != "")
   {
      RecipeString += "?,";
      Parameters[count] = name;
      count++;
   }
}

C#
string RecipeString = "";            
int count = 0;
foreach (string name in Recipes.elements)
{
   if(name != "")
   {
      RecipeString += "?,";
      Parameters[count] = name;
      count++;
   }
}

This is the code to generate RecipeString

14 replies

CC#

•Created by Hazzza on 12/3/2024 in #help

SQL gives Missing operator error

https://tenor.com/view/blink-twice-pointing-beard-eyes-gif-16953299

14 replies

CC#

•Created by Hazzza on 12/3/2024 in #help

SQL gives Missing operator error

The recipe string and ingredients string are adding in the number of parameters required so its not prone to SQL injection

14 replies

CC#

•Created by Hazzza on 12/2/2024 in #help

SQL: Trying to obtain all rows that have a primary key inside a inputted table

thanks

8 replies

CC#

•Created by Hazzza on 12/2/2024 in #help

SQL: Trying to obtain all rows that have a primary key inside a inputted table

tried it again seemed to fix it

8 replies

CC#

•Created by Hazzza on 12/2/2024 in #help

SQL: Trying to obtain all rows that have a primary key inside a inputted table

I tried making a new parameter for each value however that didn't seem to work due to the second scenario here

8 replies

CC#

•Created by Hazzza on 12/2/2024 in #help

SQL: Trying to obtain all rows that have a primary key inside a inputted table

This works

C#
string[] Parameters = new string[1];
Parameters[0] = "bread";

string _sSqlString = $"SELECT * FROM IngredientRecipe WHERE ProductName IN ('{Parameters[0]}')";

DataTable result = databaseUtils.ExecuteSqlQuery(_sSqlString, null);

C#
string[] Parameters = new string[1];
Parameters[0] = "bread";

string _sSqlString = $"SELECT * FROM IngredientRecipe WHERE ProductName IN ('{Parameters[0]}')";

DataTable result = databaseUtils.ExecuteSqlQuery(_sSqlString, null);

This doesn't

C#
string _sSqlString = $"SELECT * FROM IngredientRecipe WHERE ProductName IN ('?')";

string[] Parameters = new string[1];
Parameters[0] = "bread";

DataTable result = databaseUtils.ExecuteSqlQuery(_sSqlString, Parameters);

C#
string _sSqlString = $"SELECT * FROM IngredientRecipe WHERE ProductName IN ('?')";

string[] Parameters = new string[1];
Parameters[0] = "bread";

DataTable result = databaseUtils.ExecuteSqlQuery(_sSqlString, Parameters);

but the parameters do work in other SQL requests

8 replies

CC#

•Created by Hazzza on 12/2/2024 in #help

SQL: Trying to obtain all rows that have a primary key inside a inputted table

Sorry I didn't explain it well, I am currently using parameters in all my sql requests (code for the ExectuteSqlQuery method below), the parameters code is all working however as I am including the ' ' either side of the recipe names inside the parameter, the SQL code doesn't recognise the ' ' which are needed for the SQL to recognise each individual recipe name, so the IN method doesn't work. (There are no error messages aswell)

C#
public DataTable ExecuteSqlQuery(String sSqlString, string[] Parameters)
{
    OleDbConnection cnn = new OleDbConnection(CONNECTION_STRING); // Create a new connection to the database
    OleDbCommand cmd = new OleDbCommand(sSqlString, cnn); // sSqlString string contains a SQL statement to run on the database

    if (Parameters != null)
    {
        for (int i = 0; i < Parameters.Length; i++)
        {
            cmd.Parameters.Add(new OleDbParameter { Value = Parameters[i] });
        }
    }

    OleDbDataAdapter da = new OleDbDataAdapter(cmd);

    DataTable dt = new DataTable();

    try
    {
        cnn.Open();
        da.Fill(dt);
        cnn.Close();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Product Management System", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
    }
    return dt; // Return the data retrieved by the query
}

C#
public DataTable ExecuteSqlQuery(String sSqlString, string[] Parameters)
{
    OleDbConnection cnn = new OleDbConnection(CONNECTION_STRING); // Create a new connection to the database
    OleDbCommand cmd = new OleDbCommand(sSqlString, cnn); // sSqlString string contains a SQL statement to run on the database

    if (Parameters != null)
    {
        for (int i = 0; i < Parameters.Length; i++)
        {
            cmd.Parameters.Add(new OleDbParameter { Value = Parameters[i] });
        }
    }

    OleDbDataAdapter da = new OleDbDataAdapter(cmd);

    DataTable dt = new DataTable();

    try
    {
        cnn.Open();
        da.Fill(dt);
        cnn.Close();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Product Management System", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
    }
    return dt; // Return the data retrieved by the query
}