1

how do i retrieve the data from database when code is selected? E.g: on my database, i got Code column and Description column.. and for the Code column, i got "0001" and for the Description column, i got "Abc", or it could be said that the description for "0001" is "Abc".

I already tried many times, but it keep failed, anyone know how? I want to do when the user's select "0001", the "Abc" come out in the Description column

Here is the code:

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;");
int y = 16;

UpdateTextPosition();
OleDbDataReader dReader;
OleDbConnection conn = new OleDbConnection(connectionString);
conn.Open();
OleDbCommand cmd = new OleDbCommand("SELECT [Code] FROM [Data] WHERE Code='@Code'", conn);
cmd.Parameters.Add(new OleDbParameter("@Code", OleDbType.VarChar, 200, "Code"));
cmd.Parameters["@Code"].Value = textBoxCodeContainer[0][y].Text;

dReader = cmd.ExecuteReader();

AutoCompleteStringCollection codesCollection = new AutoCompleteStringCollection();

while (dReader.Read())
{
    string numString = dReader[0].ToString().PadLeft(4, '0');
    codesCollection.Add(numString);
}

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

Here is link of images: https://www.dropbox.com/s/5q8pyztqy7ejupy/Capture.PNG (This image is database that connected to program)

https://www.dropbox.com/s/mlwkvuvtxww2hjm/Untitled_2.png (This image show the code column is working well, but when the code is selected, the description not come out in description column)

Here is my problem: I want when the code is selected as displayed in Untitled_2.png, the description for "0001" (shown in Untitled_2.png) is displaying a description for "0001", which is "A" (refer to the database, Capture.PNG)

Improve this question

2 Answers 2

Reset to default
2

Supposing that Description is a column of the table where you read the code, then it is mandatory that you add that column in the list of fields retrieved by your query

OleDbDataReader dReader;
string cmdText="SELECT [Code], [Description] FROM [Data] WHERE Code=@Code"
using(OleDbConnection conn = new OleDbConnection(connectionString))
using(OleDbCommand cmd = new OleDbCommand(cmdText, conn))
{
    conn.Open();
    cmd.Parameters.Add(new OleDbParameter("@Code", OleDbType.VarChar, 200, "Code"));
    cmd.Parameters["@Code"].Value = textBoxCodeContainer[0][y].Text;
    using(OleDbDFataReader dReader = cmd.ExecuteReader())
    {
        AutoCompleteStringCollection codesCollection = new AutoCompleteStringCollection();
        while (dReader.Read())
        {
            string numString = dReader[1].ToString());
            codesCollection.Add(numString);
        }
    }
}

I have also fixed your command text where you put single quotes around the @Code parameter. They are not needed because you already specify the datatype when you build the Parameter.
Notice also the using statement around the disposable objects. In thisway they will be correctly close and disposed by the framework also in case of exceptions

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Hi Steve, thanks for replying. i already tried your code, but it is not working, the form are blank
Can you explain how do you define the textBoxCodeContainer variable? It seems to be an array of textBox Controls but....
firstly, i create a array of nothing inside it, and i create another array inside of array that nothing inside it. so, it is like first array is only a placeholder, and inside a placeholder, there are array of textBoxes, which is textBoxes of code
Sorry, but I can't understand if these textboxes are added to the controls collection of the container form or not. If they are created dinamically (e.g. TextBox t = new TextBox()) then you need to add them to a form container via form.Controls.Add(t) and so on for every control created dinamically, otherwise your textboxes exists only in memory without any display surface to show their contents
i already did that.. the code column, desc column are displayed when i run the program. However, the problem i am facing now is: i cannot get the desc data when i type the code "0001". I want to when i type "0001", the desc for it come out in the desc column, which is "A". Please refer to my question, i attach some images on there
2

problem is here Code='@Code'" remove '' and then it will consider as parameter

and also change the code as below 

OleDbCommand cmd = new OleDbCommand("SELECT [Code] FROM [Data] WHERE Code=?", conn);
cmd.Parameters.AddWithValue("p1", textBoxCodeContainer[0][y].Text);

The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used.

i don't see you set AutoCompleteCustomSource to a control, do as below 

textbox1.AutoCompleteMode = AutoCompleteMode.Append;
textbox1.AutoCompleteSource = AutoCompleteSource.CustomSource;
textbox1.AutoCompleteCustomSource = codesCollection;
Improve this answer

6 Comments

+1 for the change @Code to ?,for the OP: MS Access doesn't support named parameter, all the parameter holders will be the same ? and the parameters should be added to parameter collection in the order of the parameter holders.
Hi, Damith Thanks for the correction, it is very helpful, but the code is not working, when i run the program, the window are blank (nothing inside it)
@FuhansPujiSaputra which control you need to set AutoCompleteStringCollection ? check my updated answer
@Damith i already did the same way you told me to do, but for the textBox1 i changed to textBoxDescContainer[0][y]
@KingKing while it is true that OleDb doesn't support named parameters, it is also true that you can use @code as a parameter placeholder for MS Access.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.