asked on

Checking for existing record before Inserting in C#

Hello Experts,

I would like to double check that a table does not contain a particular record before inserting. Data is collected from textboxes. The table Vehicule in which I need to insert to has a primary key: Vehicule_ID which is an autonumber, this doesn’t help in terms of checking or avoiding duplicate records though...
So to check that the record exists, I need in fact to check that txtMake.Text, txtModel.Text, txtColor.Text, txtbuiltYear.Text, etc.. is the same as an existing record in the table.

Using the following pseudo code might get me there: IF EXISTS (SELECT * FROM VEHICULE WHERE Make = ....and Model = ... and Color = ... etc...)

or

SELECT COUNT (*) FROM VEHICULE WHERE Make = ... and Model = ... etc..

But I am struggling with the correct syntax to do this in C Sharp and using MS Access 2007.

I want to check in a Try ...Catch ...Finally block like below:

private void btnInsert_Click(object sender, EventArgs e)
{
string InsertString = @"INSERT INTO Vehicule (Make, Model, Color, Built_year, Tank_capacity, Wheels, Seats)
VALUES (" + "'" + txtMake.Text + "','" + txtModel.Text+ "','" + txtColor.Text+ "','" + txtYear.Text+ "'," + txtTank.Text+ "," + txtWheels.Text+ "," + txtSeats.Text + " )";
OleDbCommand commandAdd = new OleDbCommand(InsertString, con);
Try
{
//check if record exists
}
Catch ()
{
//record already exists, abort operation
}
Finally
{
//insert record into database table
commandAdd.ExecuteNonQuery();
MessageBox.Show("Record added");
txtMake.Text = "";
txtModel.Text = "";
txtColor.Text = "";
txtYear.Text = "";
txtTank.Text = "";
txtWheels.Text = "";
txtSeats.Text = "";
txtMake.Focus();
}
}

Could you please help me out with the right syntax to achieve this checking? Thanks.

borki

I would not use a Try/Catch construct.

I think you would be better off with running the check query first

SELECT cVehicule = COUNT(primary key)...

Then run some code like

if cVehicule == 0
// insert record
else
// optionally notify user

Smanyx

ASKER

>> SELECT cVehicule = COUNT(primary key)...
The primary key is an autonumber. As such more then one record can have exactly the same details: same Make, Model, Color etc...
This doesn't prevent a duplicate record to be entered as it will be different from an existing one only by the Vehicule_ID. Data is to be collected from textboxes and inserted to the table on a button click command.
The above test wouldn't work.

borki

I think you misunderstood. I just illustrated some psudocode like you did in your statement

SELECT COUNT (*) FROM VEHICULE WHERE Make = ... and Model = ... etc..

I am just saying that it is better to use COUNT(primary key) instead of COUNT(*), particulary if you have lots of records.

Of course you need a WHERE statement with that select statement that specifies your exact criteria to verify there are no duplicates.

Without knowledge of your DB table structure, and criteria we can't give you any better code.

joriszwaenepoel

WHy don(t you create a unique index on the set of columns that define a unique record. That was, the INSERT will fail if a record already exists, and an exception will be thrown.

Or you can check if it already exists, if you want to avoid the exception.

Anyway, the try-catch-finally that you have described is wrong: the finally block is always executed, even if an exception occurs. You can't abort the finally block from the catch-block.

Smanyx

ASKER

>> WHy don(t you create a unique index on the set of columns that define a unique record. That was, the INSERT will fail if a record already exists, and an exception will be thrown.

Could you please explain how to create the unique index. I don't know how to do it.

>> Anyway, the try-catch-finally that you have described is wrong: the finally block is always executed,
Yeah, thanks. I noticed it too. I should have only the textboxes clear methods in the finally block because it gets executed no matter what...

Back to the checking, this is what I have at the moment:
string InsertString = @"IF NOT EXISTS (SELECT * FROM Vehicule WHERE Make = '" + txtMake.Text + "'AND '" + txtModel.Text + "', '" + txtColor.Text + "', '" + txtYear.Text + "', " + txtTank.Text + "," + txtWheels.Text + ", " + txtSeats.Text + ")INSERT INTO Vehicule (Make, Model, Color, Built_year, Tank_capacity, Wheels, Seats) VALUES (" + "'" + txtMake.Text + "','" + txtModel.Text + "','" + txtColor.Text + "','" + txtYear.Text + "'," + txtTank.Text + "," + txtWheels.Text + "," + txtSeats.Text + " )";

OR

string InsertString = @"(SELECT COUNT(*) FROM Vehicule WHERE Make = '" + txtMake.Text + "'AND '" + txtModel.Text + "', '" + txtColor.Text + "', '" + txtYear.Text + "', " + txtTank.Text + "," + txtWheels.Text + ", " + txtSeats.Text + ")INSERT INTO Vehicule (Make, Model, Color, Built_year, Tank_capacity, Wheels, Seats) VALUES (" + "'" + txtMake.Text + "','" + txtModel.Text + "','" + txtColor.Text + "','" + txtYear.Text + "'," + txtTank.Text + "," + txtWheels.Text + "," + txtSeats.Text + " )";

Both these queries do run, and I can see the values using the Text Visualizer in Debug mode, but when it comes to commandAdd.ExecuteNonQuery(), it fails.
I get Invalid SQL statement; expected DELETE, INSERT, PROCEDURE, SELECT OR UPDATE error message or Syntax error in union query respectively.
Could you help me solve this? Thanks.

private void btnAdd_Click(object sender, EventArgs e)
        {
            //string InsertString = @"IF NOT EXISTS (SELECT * FROM Vehicule WHERE Make = '" + txtMake.Text + "'AND '" + txtModel.Text + "', '" + txtColor.Text + "', '" + txtYear.Text + "', " + txtTank.Text + "," + txtWheels.Text + ", " + txtSeats.Text + ")INSERT INTO Vehicule (Make, Model, Color, Built_year, Tank_capacity, Wheels, Seats) VALUES (" + "'" + txtMake.Text + "','" + txtModel.Text + "','" + txtColor.Text + "','" + txtYear.Text + "'," + txtTank.Text + "," + txtWheels.Text + "," + txtSeats.Text + " )";
            string InsertString = @"(SELECT COUNT(*) FROM Vehicule WHERE Make = '" + txtMake.Text + "'AND '" + txtModel.Text + "', '" + txtColor.Text + "', '" + txtYear.Text + "', " + txtTank.Text + "," + txtWheels.Text + ", " + txtSeats.Text + ")INSERT INTO Vehicule (Make, Model, Color, Built_year, Tank_capacity, Wheels, Seats) VALUES (" + "'" + txtMake.Text + "','" + txtModel.Text + "','" + txtColor.Text + "','" + txtYear.Text + "'," + txtTank.Text + "," + txtWheels.Text + "," + txtSeats.Text + " )";
            OleDbCommand commandAdd = new OleDbCommand(InsertString, con);
            
            commandAdd.ExecuteNonQuery();
            //if ()
            //{
                  //MessageBox.Show("Existing record");
            //}
            //else
            {
                MessageBox.Show("Record added");
            }
            
            txtMake.Text = "";
            txtModel.Text = "";
            txtColor.Text = "";
            txtYear.Text = "";
            txtTank.Text = "";
            txtWheels.Text = "";
            txtSeats.Text = "";
            txtMake.Focus();
    
        }

        private void btnClear_Click(object sender, EventArgs e)
        {
            txtMake.Text = "";
            txtModel.Text = "";
            txtColor.Text = "";
            txtYear.Text = "";
            txtTank.Text = "";
            txtWheels.Text = "";
            txtSeats.Text = "";
            txtMake.Focus();
        }

Open in new window

ASKER CERTIFIED SOLUTION

joriszwaenepoel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nativ

You can perform the whole thing in one query: I know it works in SQL Server - I am not sure if it works with ACCESS.

INSERT INTO Vehicule  (Make, Model, Color, Built_year, Tank_capacity, Wheels, Seats)SELECT + txtMake.Text + ..... ' WHERE NOT EXISTS (SELECT Vehicle_ID From Make = + txtMake.Text ...

Open in new window

Smanyx

ASKER

>> SELECT COUNT(*) should be executed with a seperate command, and use ExecuteScalar to get the resulting value. Then in your .NET code, you write an IF-statement to insert the record if it doesn't exist (and probably an else-block to inform the user that the record already exists).

I have implemented it. It works, thanks.

But the funny thing is that, I have noticed the data displayed in the textboxes, especially the date (built_year), is different from the built_year in the table.
What could cause such discrepancy?
All the other fields are correct, except the built_year.
Below is the code I use to check and add record into the table.

private void btnAdd_Click(object sender, EventArgs e)
        {
            string CheckString = @"(SELECT COUNT(*) FROM Vehicule WHERE Make = '" + txtMake.Text + " ' AND Model = '" + txtModel.Text + "' AND Color = '" + txtColor.Text + "' AND NumberPlates = '" + txtNbrPlates.Text + "' AND Built_year = " + txtYear.Text + " AND Tank_capacity = " + txtTank.Text + " AND Wheels = " + txtWheels.Text + " AND Seats = " + txtSeats.Text + ")";//
            string InsertString = @"INSERT INTO Vehicule (Make, Model, Color, NumberPlates, Built_year, Tank_capacity, Wheels, Seats) VALUES (" + "'" + txtMake.Text + "','" + txtModel.Text + "','" + txtColor.Text + "','"+ txtNbrPlates.Text + "'," + txtYear.Text + "," + txtTank.Text + "," + txtWheels.Text + "," + txtSeats.Text + " )";
            OleDbCommand commandAdd = new OleDbCommand(InsertString, con);
            OleDbCommand commandCheck = new OleDbCommand(CheckString, con);

            int Count = (int)commandCheck.ExecuteScalar();

            if (Count > 0)
            {
                MessageBox.Show("Existing record.");
            }
            else
            {
                commandAdd.ExecuteNonQuery();
                MessageBox.Show("Record added");
            }
                                  
            txtMake.Text = "";
            txtModel.Text = "";
            txtColor.Text = "";
            txtNbrPlates.Text = "";
            txtYear.Text = "";
            txtTank.Text = "";
            txtWheels.Text = "";
            txtSeats.Text = "";
            txtMake.Focus();
    
        }

Open in new window

joriszwaenepoel

Can you give an example of the year that is in the textbox, and what gets inserted in de database? I don't see where the error could be. Maybe with an example, I could have an idea.

Also, do you really want to clear all the textboxes in case the record already exists? Maybe one field was entered incorrect, but the user cannot check what was entered and then instead of correcting the wrong data, he has to input every field again. I would suggest to only clear the textboxes when the insert has succeeded.

Smanyx

ASKER

>> INSERT INTO Vehicule (Make, Model, Color, Built_year, Tank_capacity, Wheels, Seats)SELECT + txtMake.Text + ..... ' WHERE NOT EXISTS (SELECT Vehicle_ID From Make = + txtMake.Text ...

I have an issue with the date as I said. For example, dates do change inadvertently in the database.
For car1, in the database I have built_year 3/8/2002, in the textbox it shows 22/20/01_1
For car2, " " 12/30/1899, " " 33/20/04_1
For car3, " " 5/25/2006, " " 38/20/02_1
etc...

In the data table, the date field is set/formatted to short date: MM/DD/YYYY
and I use a masked textbox to get the input. The masked textbox is also set to MM/DD/YYYY

All dates in the table are not the correct dates I entered. They've been somehow changed. But still they don't show up exactly the same in the textbox. Whatever date I insert is changed in the table...
This is very weird.
Any clue(s)?

nativ

It seems Built_Year is saved as a Date in the database, but you are only passing through the Year.
is txtYear.Text the whole date?

when you inserting the date you have ',' + txtYear.Text + ",'
You should format your date to YYYY/MM/DD and put it in quotations.
Otherwise txtYear.Text just contains a number (as the formatting is part of your masked textbox)
And the number is interpreted by SQL to be some other date you did not expect.

Smanyx

ASKER

I think I just got around the problem. I have changed the data type of the built_year field from date/time to text and provided an input mask for it.
In my Form, I have formatted the masked textbox to MM/DD/YYYY
I have added the quotations in my sql queries around txtYear.Text

It's working fine now.

joriszwaenepoel

Watch out with storing dates as text. For example, because of the formatting that is applied, you won't be able to sort on that column.

You should keep your datetime datatype in the database. Then the best option is to modify your SQL and use a parameter to give the value to the database. That way, there can't be any conversion problem in the database. Still you'll have to convert the text entered in the textbox to a .NET datetime datatype, but that should not be a probem using DateTime.Parse(). Always work with datetime datatypes internally, and convert to text as late as possible (just before showing on screen or before printing) and from text to datetime as early as possible (using the datetime format used by the client).

Can you show the code you used to get the data from the database and show it on the screen, since that is where it goes wrong: there is valid date in the database, but the textbox shows a value that is not even a valid date?

Smanyx

ASKER

>> Can you show the code you used to get the data from the database and show it on the screen, since that is where it goes wrong: there is valid date in the database, but the textbox shows a value that is not even a valid date?

I haven' t written any code to format the input. I was relying on the masked textbox for that, knowing that the field is also formatted in the table as short date: MM/DD/YYYY

Below is the code I use to display and get data to the database...

private void btnConnect_Click(object sender, EventArgs e)
        {
            try
            {                                
                string conn;
                
                conn = ConfigurationSettings.AppSettings["ConnectionString"];
                con.ConnectionString = conn;
                con.Open();
                                         
            }
            catch(Exception ex)
            { 
                //if there is a problem, display an error message
                MessageBox.Show(ex.Message);
            
            }
            finally
            {                
                OleDbCommand comm = new OleDbCommand();
                comm.Connection = con;
                string sqlString = "Select * from Vehicule";
                myDataSet = new DataSet();
                myAdapter = new OleDbDataAdapter(sqlString, con);
                OleDbCommandBuilder myBuilder = new OleDbCommandBuilder(myAdapter);
                try
                {
                    //fill the dataset with the table 'Vehicule'
                    myAdapter.Fill(myDataSet, "Vehicule");
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error in connecting..." + ex.ToString(), "Error");
                }
                finally
                { 
                    //bind textboxes to data in the table
                    txtMake.DataBindings.Add("Text", myDataSet, "Vehicule.Make");
                    txtModel.DataBindings.Add("Text", myDataSet, "Vehicule.Model");
                    txtColor.DataBindings.Add("Text", myDataSet ,"Vehicule.Color");
                    txtNbrPlates.DataBindings.Add("Text", myDataSet, "Vehicule.NumberPlates");
                    txtYear.DataBindings.Add("Text", myDataSet, "Vehicule.Built_year");
                    txtTank.DataBindings.Add("Text", myDataSet, "Vehicule.Tank_capacity");
                    txtWheels.DataBindings.Add("Text", myDataSet, "Vehicule.Wheels");
                    txtSeats.DataBindings.Add("Text", myDataSet,"Vehicule.Seats");
                    lblID.DataBindings.Add("Text", myDataSet, "Vehicule.Vehicule_ID");
                    //set the BindingManagerBase
                    myBind = this.BindingContext[myDataSet, "Vehicule"];
                }
                               
            }

        }

private void btnAdd_Click(object sender, EventArgs e)
        {
            string CheckString = @"(SELECT COUNT(*) FROM Vehicule WHERE Make = '" + txtMake.Text + " ' AND Model = '" + txtModel.Text + "' AND Color = '" + txtColor.Text + "' AND NumberPlates = '" + txtNbrPlates.Text + "' AND Built_year = '" + txtYear.Text + "' AND Tank_capacity = " + txtTank.Text + " AND Wheels = " + txtWheels.Text + " AND Seats = " + txtSeats.Text + ")";//
            string InsertString = @"INSERT INTO Vehicule (Make, Model, Color, NumberPlates, Built_year, Tank_capacity, Wheels, Seats) VALUES (" + "'" + txtMake.Text + "','" + txtModel.Text + "','" + txtColor.Text + "','"+ txtNbrPlates.Text + "','" + txtYear.Text + "'," + txtTank.Text + "," + txtWheels.Text + "," + txtSeats.Text + " )";
            OleDbCommand commandAdd = new OleDbCommand(InsertString, con);
            OleDbCommand commandCheck = new OleDbCommand(CheckString, con);

            int Count = (int)commandCheck.ExecuteScalar();

            if (Count > 0)
            {
                MessageBox.Show("Existing record.");
            }
            else
            {
                commandAdd.ExecuteNonQuery();
                MessageBox.Show("Record added");
            }
                                  
            txtMake.Text = "";
            txtModel.Text = "";
            txtColor.Text = "";
            txtNbrPlates.Text = "";
            txtYear.Text = "";
            txtTank.Text = "";
            txtWheels.Text = "";
            txtSeats.Text = "";
            txtMake.Focus();
    
        }

        private void btnClear_Click(object sender, EventArgs e)
        {
            txtMake.Text = "";
            txtModel.Text = "";
            txtColor.Text = "";
            txtNbrPlates.Text = "";
            txtYear.Text = "";
            txtTank.Text = "";
            txtWheels.Text = "";
            txtSeats.Text = "";
            txtMake.Focus();
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            string UpdateString = @"UPDATE Vehicule" + "SET" +
                "Make = '" + txtMake.Text +
                "Model = '" + txtModel.Text +
                "Color = '" + txtColor.Text +
                "NumberPlates = '" + txtNbrPlates.Text +
                "Built_year = '" + txtYear.Text +
                "Tank_capacity = " + txtTank.Text +
                "Wheels = " + txtWheels.Text +
                "Seats = " + txtSeats.Text +
            "WHERE ID = @Vehicule_ID";
                      
            OleDbCommand commandAdd = new OleDbCommand(UpdateString, con);

            commandAdd.ExecuteNonQuery();
           
            MessageBox.Show("Record updated");
            txtMake.Text = "";
            txtModel.Text = "";
            txtColor.Text = "";
            txtNbrPlates.Text = "";
            txtYear.Text = "";
            txtTank.Text = "";
            txtWheels.Text = "";
            txtSeats.Text = "";
            txtMake.Focus();

        }

Open in new window

screenshot.docx

joriszwaenepoel

I don't think it is related to the problem you have described, but again you have code in the finally-block that should be in the try-block.

Most of your code should be in the try-block, and very often you don't need a finally-clock, except to clean up things (dispose or close database connection for example).

Also, you should avoid repeating the same code again and again: the clearing of the textboxes can be in a seperate procedure that is called from multiple locations in your code, instead of repeating those 10 lines of code every time. Again, I think it is not related with this particular problem, but clean code helps a lot when trying to solve a bug.

Specifically about the displaying of the date: you use databinding, that is something I have never used, so I can't help you if that is where the problem is. But maybe you can try to replace the masked textbox with a standard textbox. If this helps, then the masked textbox is the culprit, if it doesn't help, then it is probably the databinding.

SOLUTION

nativ

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial