Check if record exists in MSA using C#

[Calaesto]

Hi,

No excuses: my problem is that I can't get my code to check if a certain record exists in a MSA database.

My code needs to check if the database contains a row where 2, 3 or 4 specified fields exist.
But, as mentioned, at the moment it only gives errors about missing parameters.
Before I post the code I'd like to know what the correct way is, as the code is very secret :).

[all4peace]

Secret! :P In any case, you can give us a sample code without telling us what's going on.

MSA is Microsoft access? Hmmm... never heard of this jargon before. Anyways! What you're asking for is not hard. You need to connect C# to the database and run a query. You need to tell me first if you know how to run a query! If you don't, then I have to show you that too. In that case, you need to show me more details.

[DdoubleD]

Hi,

No excuses: my problem is that I can't get my code to check if a certain record exists in a MSA database.

My code needs to check if the database contains a row where 2, 3 or 4 specified fields exist.
But, as mentioned, at the moment it only gives errors about missing parameters.
Before I post the code I'd like to know what the correct way is, as the code is very secret :).

Very secret...OK :cool: Here is an example of getting a count of records meeting a specific ID in a where clause:

using (OleDbConnection conn = new OleDbConnection(connStr))
                {
                    try
                    {
                        conn.Open();
                    }
                    catch (Exception Ex)
                    {
                        MessageBox.Show(Ex.Message);
                        System.Diagnostics.Debugger.Break();
                    }

                    string cmdStr = "Select count(*) from customers where id = 4";
                    OleDbCommand cmd = new OleDbCommand(cmdStr, conn);
                    int count = (int)cmd.ExecuteScalar();
                }

[Calaesto]

Good point about that connection and stuff .
I already made an app in C# using MSA (which is MS Access) to alter a database and showing that database in a datagrid.
That database is the same that I'm using now.
The database consists 4 tables (items_1, items_2, combo, combine) and the first 3 tables mentioned have the columns (tablename)Id and Name.
The combine-table has the following columns:
Id, comboId, itemId_1, itemId_2

What I want is that I fill in 2 Names (from the items_x tables) and that my database checks if those 2 Names are use in 1 row in the combine-table.
What's needs to be done then, I already know.

[DdoubleD]

Good point about that connection and stuff .
I already made an app in C# using MSA (which is MS Access) to alter a database and showing that database in a datagrid.
That database is the same that I'm using now.
The database consists 4 tables (items_1, items_2, combo, combine) and the first 3 tables mentioned have the columns (tablename)Id and Name.
The combine-table has the following columns:
Id, comboId, itemId_1, itemId_2

What I want is that I fill in 2 Names (from the items_x tables) and that my database checks if those 2 Names are use in 1 row in the combine-table.
What's needs to be done then, I already know.

I cannot tell from your last line whether you have already solved your problem. If so, please mark the thread as solved. If not, I will try to determine more clearly what it is you are asking...

Are you trying to ensure that the "combine" table only has distinct values for two of the columns when the records are selected and inserted from the other three?

Or, are you only wanting to perform a query on the "combine" table (after it has been built) to determine if it has a row containing certain values for two of the columns?

Or, have I missed completely what it is you want?:(

[Calaesto]

I cannot tell from your last line whether you have already solved your problem. If so, please mark the thread as solved. If not, I will try to determine more clearly what it is you are asking...

Are you trying to ensure that the "combine" table only has distinct values for two of the columns when the records are selected and inserted from the other three?

Or, are you only wanting to perform a query on the "combine" table (after it has been built) to determine if it has a row containing certain values for two of the columns?

Or, have I missed completely what it is you want?

First, it is my mistake for using that line of text, but I haven't solved the problem.
Second, that's exactly what I tried to describe in my post. (I'm very bad at describing stuff)

[DdoubleD]

Hey there, I'm still not sure what you are asking because I asked a couple of different questions, and I'm not sure which one you accepted as your issue.:D Select a choice:

A) Are you trying to ensure that the "combine" table only has distinct values for two of the columns when the records are selected and inserted from the other three?

B) Or, are you only wanting to perform a query on the "combine" table (after it has been built) to determine if it has a row containing certain values for two of the columns?

In either case, and in order to expedite potential solution, please include the SQL statement(s) you are using to build the "combine" table.

[Calaesto]

I choose option B :P
I will send you the code, but I prefer to send I through a private message. I also send you the MSA database in question as an attachment.

[DdoubleD]

I choose option B :P
I will send you the code, but I prefer to send I through a private message. I also send you the MSA database in question as an attachment.

OK, I'll be looking for the attachment in the thread per your PM.

[Calaesto]

Here are the Project file and the database.

[DdoubleD]

Here are the Project file and the database.

Yep, just the project file (".csproj") and the mdb ;)... Cannot really use the project file by itself; you need to include the source files (*.cs, etc.) too. Sorry about the miscommunication... when we say zip up the project, we mean the "whole" project. I will wait for your updated attachment.

[Calaesto]

Lol, oops
Here's the update

[DdoubleD]

Lol, oops
Here's the update

That was quick! Hey, I just looked at the DB, and I noticed there are only two columns in the Combo table: ComboId and Naam... Is that how it is supposed to be?

[DdoubleD]

Well, you still left out the directories for the project (properties), but maybe I don't need it anyway. Here is how you would retrieve the record count from the Combo table where Naam='Hamer'... That is to say that I tested by passing in "Hamer" to the method.

public static int GetRecCount(string naam)
            {
                string database = @"c:\AccessDbs\Calaesto.mdb";
                string connParams = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}";
                string connStr = string.Format(connParams, database);

                OleDbConnection conn = new OleDbConnection(connStr);
                string SQLString = "SELECT Count(*) FROM Combo WHERE Naam = '" + naam + "'";

                try
                {
                    conn.Open();

                    using (OleDbCommand cmd = new OleDbCommand(SQLString, conn))
                    {
                        int rows = (int)cmd.ExecuteScalar();
                        return rows;

                    }
                }
                catch (OleDbException ex)
                {
                    Console.WriteLine("Exception: {0}\r\n   Stack Trace: {1}", ex.Message, ex.StackTrace);
                    System.Diagnostics.Debugger.Break();
                }
                finally
                {
                    conn.Close();
                }
                return -1;
            }

[Calaesto]

What will the code look like if I want to use something similar to this:

string SQLString = "SELECT Count(*) FROM Combineren WHERE Items_1.itemId=Combineren.itemId AND Items_2.itemId1=Combineren.itemId1 AND Items_1.Naam = '" + item1 + "' AND Items_2.Naam = '" + item2 + "'";

This piece gives the error of missing parameters.

[DdoubleD]

What will the code look like if I want to use something similar to this:

string SQLString = "SELECT Count(*) FROM Combineren WHERE Items_1.itemId=Combineren.itemId AND Items_2.itemId1=Combineren.itemId1 AND Items_1.Naam = '" + item1 + "' AND Items_2.Naam = '" + item2 + "'";

This piece gives the error of missing parameters.

Try this:

string select2 = "SELECT Count(*) FROM Combineren "
                    + "WHERE itemId "
                    + "IN (SELECT itemId1 FROM Items_2 WHERE Naam = '" + naam + "' ) "
                    + "AND ItemId "
                    + "IN (SELECT itemId FROM Items_1 WHERE Naam = '" + naam + "') ";

[Calaesto]

When I use this code it constantly returns with 0 :S
Any idea why?

[DdoubleD]

When I use this code it constantly returns with 0 :S
Any idea why?

I tested it with naam='Hout' because those were the only records in the Combineran table where itemId had a match because they are all set to '1' (itemId=1), which matches 'Hout' from the other two tables...

Returns 5 because there are 5 records with that id in the Combineran table.

[Calaesto]

When I test I set item1 to Hout and item2 to Steen
That combination exists twice so.....

[DdoubleD]

When I test I set item1 to Hout and item2 to Steen
That combination exists twice so.....

You are going to have to explain what it is that you are wanting to compare exactly because I couldn't decipher a match based on the example SQL string you supplied given the data I have from the downloaded attachment...

Are you wanting to compare cominaren where the itemid matches the records with naam having a match in Item_1.itemid and also a match on Combinaren.itemid1 in Item_2.itemId1? None of these will ever match given the data I have because the Items_1 and Items_2 table are exactly the same, but the id fields in the cominaren table don't seem to correspond to these...

[Calaesto]

I want that the user selects 2 items in textboxes which become item1 and item2.
Then I want to check if the ID from item1 (Items_1.itemId) and the ID from item2 (Items_1.itemId1) are together in 1 or more rows in the Combineren table.
Eventually I want to let the user select up to 4 items, but I'm happy if it works with just 2.

[DdoubleD]

LOL... Still some holes in your answer, but I think this might be closer to what you want:

string select3 = "SELECT Count(*) FROM Combineren "
                    + "WHERE itemId1 "
                    + "IN (SELECT itemId1 FROM Items_2 WHERE Naam = '" + naam2 + "' ) "
                    + "AND ItemId "
                    + "IN (SELECT itemId FROM Items_1 WHERE Naam = '" + naam1 + "') ";

This gives me a count of 3 for naam1 = "Hout" and naam2 = "Steen".

[DdoubleD]

When I test I set item1 to Hout and item2 to Steen
That combination exists twice so.....

FYI: That combination exists 3 times in my data...

[Calaesto]

That's my bad as I counted wrong :P
After applying that line it still gives 0 as returnvalue :(

[DdoubleD]

That's my bad as I counted wrong :P
After applying that line it still gives 0 as returnvalue :(

First, check to see you saw my edits cause I made a couple.;)

Second, compare data:

Id comboId itemId itemId1
3 1 1 3
5 5 1 2
7 4 1 2
10 6 1 2
13 8 1 4

In the above, I have 3 matching records because "Hout" matches itemId in Items_1 and "Steen" matches itemId1 from Items_2...

In the middle where ItemId = 1 and itemId1 = 2 there are three records..

[Calaesto]

With some changes to the excess code it worked :D
Thank you very much for the help.