Click here to Skip to main content
16,017,857 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi guys i have the following code:
C#
for (int i = 0; i < DGStock.Rows.Count; i++)
{

    conn.Open();
    OleDbCommand cmd2 = new OleDbCommand();
    cmd2 = conn.CreateCommand();
    cmd2.CommandText = @"SELECT [Location] FROM [Barcodes] WHERE [Barcode] = '" + DGStock.Rows[i].Cells["Location"].Value + "'";
    cmd2.ExecuteNonQuery();
    conn.Close();


    if (DGStock.Rows[i].Cells["Location"].Value == "[Location]")
    {
        conn.Open();
        OleDbCommand cmd3 = new OleDbCommand();
        cmd3 = conn.CreateCommand();
        cmd3.CommandText = @"UPDATE [Barcodes] SET [Location] = '" + DGStock.Rows[i].Cells["Location"].Value + "' WHERE [Barcode] = '" + DGStock.Rows[i].Cells["Barcode"].Value + "'";
        cmd3.ExecuteNonQuery();
        conn.Close();                    
    }
    else
    {                   
        conn.Open();
        //string query =
        OleDbCommand cmd = new OleDbCommand();
        cmd = conn.CreateCommand();
        cmd.CommandText = @"INSERT INTO [Barcodes] (Location, Barcode) Values ('" + DGStock.Rows[i].Cells["Location"].Value + "','" + DGStock.Rows[i].Cells["Barcode"].Value + "')";
        cmd.ExecuteNonQuery();
        conn.Close();
    }

}

I need to Select the Barcode from the database, i then need to check if that barcode has the same location as the barcode i'm uploding from the datagrid i'm using, if it is not the same then it needs to update the location, otherwise it just needs to add the location and barcode to the database.

Please if there is any help you can give me?
Posted
Updated 30-Jul-12 4:15am
v2
Comments
bbirajdar 30-Jul-12 10:26am    
What help do you want ?
lewax00 30-Jul-12 10:26am    
So what exactly is the problem? We don't have access to your database, it's not like we can paste the code and run it.
[no name] 30-Jul-12 10:28am    
What exactly do you think that DGStock.Rows[i].Cells["Location"].Value == "[Location]" is supposed to be doing?
BulletVictim 30-Jul-12 10:35am    
I just need help to have the code run into the IF statement, but only if the barcode is the same in the database as it is on the datagrid but the location is different.
If that makes any sense.

I know i'm not using the "Location" in the if statement correctly, but that was my last try before asking for help. And that is where i need help.
Richard MacCutchan 30-Jul-12 10:56am    
Why are you comparing the value field to the string "[Location]"? You need to compare the two actual locations, the one in the database and the one that you are getting for the new barcode.

1 solution

Hi, not sure what error you are getting but I think I see some problems with your code. You seem to be matching the wrong column in the following code:

Quote:
cmd2.CommandText = @"SELECT [Location] FROM [Barcodes] WHERE [Barcode] = '" + DGStock.Rows[i].Cells["Location"].Value + "'";


shouldn't the above be:

Quote:
cmd2.CommandText = @"SELECT [Location] FROM [Barcodes] WHERE [Location] = '" + DGStock.Rows[i].Cells["Location"].Value + "'";


and also you should not be using ExecuteNonQuery to select data from a table, its only used by insert, update and delete and similar commands:

Quote:
cmd2.ExecuteNonQuery();


it should be:

Quote:
OledbDataReader rdr= cmd2.ExecuteReader();


and then you should be able to access the right column from an index from the reader. Following which the following check :

Quote:
if (DGStock.Rows[i].Cells["Location"].Value == "[Location]")


would become this:

Quote:
if (DGStock.Rows[i].Cells["Location"].Value == rdr["Location"].ToString())


Overall i think the code could benefit from a little refactoring and tidying up as well. But do try these changes first and let us know if that solved your problm, if it doesn't i will delete my solution and we will re-think.
 
Share this answer
 
Comments
BulletVictim 30-Jul-12 10:40am    
Thanks for the response. The problem i'm having is that my code doesn't want to enter the if statement even thought i know it should.
If i change the if statement too DGStock.Rows[i].Cells["Location"].Value != "[Location]" then it enters the statement, but then it enters the statement every time, and does not go to the INSERT statement
Rob Philpott 30-Jul-12 11:05am    
Well that if statement is string matching "[Location]" which I doubt is what you really want to do. A more likely thing is you want to see whether it has a value or not rather than the value of "[Location]". In which case you probably want to comparison to be != DBNull.Value.

BTW, the insert/update function would be better in a stored proc if available.
I.explore.code 30-Jul-12 11:30am    
If you read my solution above, i say something about OleDbDataReader which is what you want to do. You cannot compare just a literal string thinking that's the value thats returned by your select query. The query reseults have to be in a data reader for you to be able to fetch the value of the column and compare.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900