Displaying a description based on code in database
C#, database, WindowsApplication,

Hi everyone, i have a question how do i input the descriptions columns if i type the number based on my database in code columns, i already retrieve the code from database, but now i also want to display the description when the code shown too.

Here is the link for show you how is my retrieve the code from database:
"https://www.dropbox.com/s/it3z0bgnn4nmitv/Pictures.docx"

Here is my code for retrieve the "code" (0001, 0002, and so on)

string connectionString = (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\Archives\Projects\Program\Sell System\Sell System\App_Data\db1.accdb;Persist Security Info=False;");

OleDbDataReader dReader;
OleDbConnection conn = new OleDbConnection(connectionString);
            conn.Open();
            OleDbCommand cmd = new OleDbCommand("SELECT DISTINCT [Code] FROM [Data]", conn);
            dReader = cmd.ExecuteReader();
            AutoCompleteStringCollection codesCollection = new AutoCompleteStringCollection();

            while (dReader.Read())
            {
                string numString = "000" + dReader[0].ToString();
                codesCollection.Add(numString);
            }

            dReader.Close();
            conn.Close();

Edited 3 Years Ago by fuhanspujisaputra: Forgot to put the link

You need to drop your DISTINCT keyword. Your Code column should be marked as Unique in the database, preventing duplicate entries. After that, simply add [Description] to your select statement.

If you continue to use DISTINCT then you may end up losing data if you enter duplicates (it will only return the first it finds)

Edited 3 Years Ago by Ketsuekiame

@Ketsuekiame: how do i marked the Code column to be unique in the database?
So, the DISTINCT should not be used if we want to return the data twice or more than that?

You need to create a UNIQUE Constraint on the column you wish to make unique.

There are two ways you can do it.

create table MyTable
(
    [Id] int not null primary key,
    [Code] int not null unique,
    [Description] nvarchar(255) not null
);

or (my preferred way)

create table MyTable
(
    [Id] int not null,
    [Code] int not null,
    [Description] nvarchar(25),
    constraint PriKey_MyTable_Id primary key clustered ([Id]),
    constraint Unique_MyTable_Code unique nonclustered ([Code])
);

I prefer the second way because you can easily see what the constraints are and where they refer to just by looking at the name of them.

i already did that way, but not working also.
By the way, how do we let computer know if the selected code is the first line of description?
e.g: i have a code "12345", and the description for it was"Abc", these are stored in the database. when user select the "12345", computer know and recognized the description for "12345" is "Abc", and computer will display it to the program.. How do we do that?

This article has been dead for over six months. Start a new discussion instead.