Problems with SELECT with 2 Wheres

Asked

Viewed 162 times

4

I’m having a bit of an amateur problem here. I’m not getting this SELECT from 2 CONDITIONS, and I can’t find the ERROR! Someone please give me a light there!

Follows the Code:

OleDbConnection Con = new OleDbConnection();
Con.ConnectionString = Properties.Settings.Default.dbConnection;

Con.Open();
OleDbCommand Cmm = new OleDbCommand();

Cmm.CommandText = "SELECT CodAutomaticProd, DescProd, BrandCod FROM tbProdutos WHERE ModelProd LIKE '%" + txtModel.Text + "%' AND YearProd LIKE '%" + txtYear.Text + " %' ";

Cmm.CommandType = CommandType.Text;
Cmm.Connection = Con;

OleDbDataReader DR;
DR = Cmm.ExecuteReader();

listBox1.Items.Clear();

while (DR.Read())
{
    listBox1.Items.Add(DR.GetInt32(0) + " - " + DR.GetString(1) + " -    " + DR.GetString(2));
}

The code runs perfectly inside the BANK, and is inside the TRY which does not indicate ERROR by "Exception".

  • 1

    What would be the mistake? You shouldn’t be concatenating strings. Should be using parameters.

  • Is your select working if it is run directly in the bank? Is your code inside a Try/catch block? If yes check what the catch message says and post in your question.

  • Gypsy, the program is not indicating error itself, it just doesn’t bring the information. It’s probably something from AND, because when you put only 1 condition to "Modelprod" it brings the information perfectly.

  • 1

    See about injection of SQL.

  • 1

    And also this: https://xkcd.com/327/

3 answers

8


There is a blank space in the string formation before finishing the last LIKE

txtYear.Text + "(here) %'

  • Putz Daniel ! That’s right! That’s right! Thank you very much brother ! ; )

  • huauhauha, you’re welcome, man. ;)

7

The correct way to do this is by using parameterization. This way, you can inject unwanted SQL code into your query:

Cmm.CommandText = "SELECT CodAutomaticProd, DescProd, BrandCod FROM tbProdutos WHERE ModelProd LIKE '%@ModelProd%' AND YearProd LIKE '%@YearProd%' ";
Cmm.Parameters.Add(new SqlParameter("@ModelProd", txtModel.Text));
Cmm.Parameters.Add(new SqlParameter("@YearProd", txtYear.Text));
  • Very good too, Cigado, worked and became more organized ! Thanks !

  • Not only is it more organized, it’s a safer practice for your system. Avoid using string concatenation to mount your select statement. Thus avoiding headaches such as SQL Injection.

0

Blank space " %' "

Like should be '%value%'

Browser other questions tagged

You are not signed in. Login or sign up in order to post.