Comparing textbox value to database

Question

HI ! I would like to compare values from a textbox with data from a table. I tried this code but i got the error that the input string was in the wrong format! code:

string connectionString = 
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=save.mdb";

try
{

   database = new OleDbConnection(connectionString);
   database.Open();
   string queryString = "SELECT zivila.naziv,users.user_name FROM (obroki_save "
       + " LEFT JOIN zivila ON zivila.ID=obroki_save.ID_zivila) "
       + " LEFT JOIN users ON users.ID=obroki_save.ID_uporabnika "
       + " WHERE users.ID='" +Convert.ToInt16(id.iDTextBox.Text)+"'"; 
   loadDataGrid(queryString);
}
catch (Exception ex)
{
   MessageBox.Show(ex.Message);
   return;
}

Answer 1

Hi there,

your connectionstring should look like this:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User Id=admin;Password=;

Further information on connectionstrings you can find on connectionstrings.com

Answer 2

when building your sql, you're trying to concatenate a string and an Int16 value. try removing the Convert.ToInt16(), leaving just the string value of the textbox.

Answer 3

Try changing your last line to

+ " WHERE users.ID=" +Convert.ToInt16(id.iDTextBox.Text);

basically removing the single quotes from around the Int16

Answer 4

You could try this:

string connectionString = 
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=save.mdb";

try
{
       Int16 id = Int16.Parse(id.iDTextBox.Text);

   database = new OleDbConnection(connectionString);
   database.Open();
   string queryString = "SELECT zivila.naziv,users.user_name FROM (obroki_save "
       + " LEFT JOIN zivila ON zivila.ID=obroki_save.ID_zivila) "
       + " LEFT JOIN users ON users.ID=obroki_save.ID_uporabnika "
       + " WHERE users.ID=" + id.ToString(); 
   loadDataGrid(queryString);
       database.Close();
}
catch (FormatException fex)
{
   MessageBox.Show(fex.Message);
   return;
}
catch (Exception ex)
{
   MessageBox.Show(ex.Message);
   return;
}

This way you would intercept bad data and protect yourself from Sql Injection.

Also, don't forget to Close your connection, or read up on the using the using keyword.

Also try to run the query with a hardcoded id value in your sql editor to make sure it works:

SELECT zivila.naziv,users.user_name 
FROM (obroki_save AS os LEFT JOIN zivila AS z ON z.ID=os.ID_zivila) 
LEFT JOIN users ON users.ID=obroki_save.ID_uporabnika 
WHERE users.ID=16

Answer 5

if your ID field is an int, you shouldn't be putting ' around it.

so change

 + " WHERE users.ID='" +Convert.ToInt16(id.iDTextBox.Text)+"'"; 

to

 + " WHERE users.ID=" +Convert.ToInt16(id.iDTextBox.Text);