How can i Check if data exists in MS Acess Database Column

Question

I need to check if a value exists in a ms access column if it does   it must throw an error|| if it doesn't it must generate a new form

i don't want anything in my column if threes data in a column it must show an error

..This is what i have done so far:

  bool BarCodeExsst = true;
           
            if (BarCodeExsst)
            {

                 MessageBox.Show("Continue");

                //Procced to next level

                using (OleDbConnection dbConn = new OleDbConnection(@"Provider=Microsoft.Jet.OleDb.4.0;Data Source=V:\Data\EDCON\tickets\Barcode_Import\Database.MDB\Scanner.mdb"))
                {

                    //Look for for this value in the database table
                    //*********************************************
                    //Create a command object to look for this value
                    OleDbCommand cmd = dbConn.CreateCommand();
                    cmd.CommandText = "select BarCode from Code2 where BarCode=?";

                    dbConn.Open();
                    OleDbDataReader rdr = cmd.ExecuteReader();

                    //Variable to hold the ID of the row with the scanned value
                    //in its BarCode column
                    Int32 rowOrderNo = 0;
                    while (rdr.Read())
                    {
                        rowOrderNo = rdr.GetInt32(0);
                        break;
                    }

                    {
                        dbConn.Close();
                    }

                }
               

            else
            {

                MessageBox.Show("Can not Generate Report all Records Must Be Scanned");
                
            }


        }

Thanx in advance....

Answer 1

Try like this

 using (OleDbConnection dbConn = new OleDbConnection(@"Provider=Microsoft.Jet.OleDb.4.0;Data Source=V:\Data\EDCON\tickets\Barcode_Import\Database.MDB\Scanner.mdb"))
{
  OleDbCommand cmd = dbConn.CreateCommand();
  cmd.CommandText = "select BarCode from Code2 where BarCode=?";
 dbConn.Open();
 OleDbDataReader rdr = cmd.ExecuteReader();
 //Check records are exixts or not
 if (rdr.HasRows == true)
 {
        //Your While Loop 
  }
 else
  {
       //No records found
   }        
}

---

With Thanks and Regards
Sambath Raj.C
click "Proposed As Answer by" if this post solves your problem or "Vote As Helpful" if a post has been useful to you
Happy Programming!

Answer 2

You can check a column value for null with IsDbNull

if(rdr.IsDbNull(0))
{
    throw new Exception("Order number is null");
}
else
{
    rowOrderNo = rdr.GetInt32(0);
}

On another note. You don't need to close the connection. The "using" block will handle it.

---

Bob - www.crowcoder.com

Answer 3

Im getting this error, i'm not sure if my database is closing before code is executed or what......

could you correct my errors or mybe explain in dept , so that i understand better.....

using (OleDbConnection dbConn = new OleDbConnection(@"Provider=Microsoft.Jet.OleDb.4.0;Data Source=C:\Documents and Settings\tsiphambo\Desktop\Scanner.mdb"))
            {
                OleDbCommand cmd = dbConn.CreateCommand();
                cmd.CommandText = "select BarCode from Code2 where BarCode=?";
                dbConn.Open();

                OleDbDataReader rdr = cmd.ExecuteReader();
                //Check records are exixts or not
                if (rdr.HasRows == true)
                {
                    while (rdr.Read())
                    {
                        throw new Exception("Scan Not Complete, Please Check If all Values are empty and Rescan");
                    }
                }
                else
                {
                    MessageBox.Show("Continue");
                    Report thando = new Report();
                    thando.Show();
                    this.Hide();
                }
                
            }

            
        }

    }
}

Thanx

Answer 4

First off, what error are you referring to? I think you forgot to show us.

Your query takes a parameter but you are not setting it.  Before you open the connection add your parameter, something like: 

cmd.Parameters.Add(new OleDbParamter("yourbarcodevalue"));

And why are you throwing an exception in the while loop? If you get rows back you are going to throw that exception every time.

---

Bob - www.crowcoder.com

Answer 5

okay i understand, well i removed my ("textbox"));  this is what it looks like now, it stil doesnt work, is there another way without using a textbox... i just want it to simply check if thers a value in that field then an error must show.......

  private void button1_Click(object sender, EventArgs e)
        {
           using (OleDbConnection dbConn = new OleDbConnection(@"Provider=Microsoft.Jet.OleDb.4.0;Data Source=C:\Documents and Settings\tsiphambo\Desktop\Scanner.mdb"))
            {
                OleDbCommand cmd = dbConn.CreateCommand();
                cmd.CommandText = "select BarCode from Code2 where BarCode=?";
                cmd.Parameters.AddWithValue("BarCodeValue","");
                 
                dbConn.Open();
                OleDbDataReader rdr = cmd.ExecuteReader();
                
                if (rdr.HasRows == true)
                {
                   string rowOrderNo = "";
                   while (rdr.Read())
                   {
                       rowOrderNo = rdr[0].ToString();
                     
                       MessageBox.Show("Scan Not Complete, Please Check If all Values are empty and Rescan");
                    }
                    
                }
                else
                {
                    MessageBox.Show("Continue");
                    Report tt = new Report();
                    tt.Show();
                    this.Hide();
                }
                
            }

            
        }

    }
}

thanx

Answer 6

I still don't really understand what you need to do with the data, but you can determine if the barcode was found this way:

  private void button1_Click(object sender, EventArgs e)
        {
           using (OleDbConnection dbConn = new OleDbConnection(@"Provider=Microsoft.Jet.OleDb.4.0;Data Source=C:\Documents and Settings\tsiphambo\Desktop\Scanner.mdb"))
            {
                OleDbCommand cmd = dbConn.CreateCommand();
                cmd.CommandText = "select BarCode from Code2 where BarCode=?";
                cmd.Parameters.AddWithValue("BarCodeValue","");
                 
                dbConn.Open();
    //if BarCode is unique, like a primary key then
    object barcode = cmd.ExecuteScalar();
    if(barcode == null)
    {
        //Then there is no such barcode in table Code2
        MessageBox.Show("Continue");
        Report tt = new Report();
        tt.Show();
        this.Hide();
    }
    else
    {
        //Else there is a barcode in table Code2
        rowOrderNo = barcode.ToString();
        MessageBox.Show("Scan Not Complete, Please Check If all Values are empty and Rescan");
    }
                 
    }
}

---

Bob - www.crowcoder.com

Answer 7

okay i understand, well i removed my ("textbox"));  this is what it looks like now, it stil doesnt work, is there another way without using a textbox... i just want it to simply check if thers a value in that field then an error must show.......

  private void button1_Click(object sender, EventArgs e)
        {
           using (OleDbConnection dbConn = new OleDbConnection(@"Provider=Microsoft.Jet.OleDb.4.0;Data Source=C:\Documents and Settings\tsiphambo\Desktop\Scanner.mdb"))
            {
                OleDbCommand cmd = dbConn.CreateCommand();
                cmd.CommandText = "select BarCode from Code2 where BarCode=?";
                cmd.Parameters.AddWithValue("BarCodeValue","");
                 
                dbConn.Open();
                OleDbDataReader rdr = cmd.ExecuteReader();
                
                if (rdr.HasRows == true)
                {
           		MessageBox.Show("Continue");
                        ........			                            
       	         }
                else
                {
                      your while loop
		
              

thanx

Please chenage the code like above

---

With Thanks and Regards
Sambath Raj.C
click "Proposed As Answer by" if this post solves your problem or "Vote As Helpful" if a post has been useful to you
Happy Programming!

Answer 8

thought i should give you exactly what i was looking for, for other users....... i manipulated the code like to archieve what i wanted:

 using (OleDbConnection dbConn = new OleDbConnection(@"Provider=Microsoft.Jet.OleDb.4.0;Data Source=V:\Data\EDCON\tickets\Barcode_Import\Database.MDB\Scanner.mdb"))
            {
                OleDbCommand cmd = dbConn.CreateCommand();
                cmd.CommandText = "Select * from Code2 where BarCode <> '' and BarCode like '" + (txtSelectDate.Text) + "%'";
                dbConn.Open();
                OleDbDataReader rdr = cmd.ExecuteReader();
                if (rdr.HasRows == true)
                {
                    MessageBox.Show("Please Check If All Records are Scanned For The Date Selected and Rescan If Not, Error!!!!!!");
                    	                            
       	         }
                else
                {
                    MessageBox.Show("Continue To Print CheckList");