Return alll rows from column in datatable

[john_93]

   try

        {
            OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;" + @"Data Source=c:\Users\PC\Documents\Visual Studio 2013\Projects\WindowsFormsApplication1\WindowsFormsApplication1\Main database.accdb");
            connection.Open();
            OleDbCommand lala = new OleDbCommand();
            lala.Connection = connection;
            string query = "SELECT * FROM [Firebird m0 Damage];";

            lala.CommandText = query;

            OleDbDataReader reader = lala.ExecuteReader();

            while (reader.Read()) 
            {
                  var parameter = new OleDbParameter("@Totalprice", OleDbType.SmallInt);
                  parameter.Value = "comboBox6.SelectedValue";
                  lala.Parameters.Add(parameter);   
                  textBox15.Text = reader["Totalprice"].ToString();
            }
            connection.Close();
        }
        catch (Exception es)
        {
            MessageBox.Show("Error  " + es);
        }

    }

Im trying to return all the rows of the column to textbox when a list is selected from combobox, it only returns 1 value

[JOSheaIV]

You sir are in luck. I have a piece of code I wrote for work that returns a DataTable's column as a collection. Built it as an extension even

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;

/// <summary>
/// Contains a collection of Extensions in relation to DataTables (as well as DataRows and DataColumns)
/// </summary>
public static class DataTableExtensions
{
    #region ColumnToArray
    /// <summary>
    /// Returns all the values for a Column in the DataTable as a collection
    /// </summary>
    /// <typeparam name="T">Type of the collection to Return</typeparam>
    /// <param name="value">DataTable to retireve the value from</param>
    /// <param name="ColumnName">The name of the Column to retrieve values from</param>
    /// <returns>Returns a collection of values (or null, should an error occur)</returns>
    public static IEnumerable<T> ColumnToArray<T> (this DataTable value, string ColumnName)
    {
        try
        {
            return value.Rows.Cast<DataRow>().Select(row => (T) Convert.ChangeType(row [ColumnName].ToString(), typeof (T)));
        }
        catch
        {
            return null;
        }
    }
}

This thing works really nice. I included the whole class, so you could make this as an extension as well. To use this I do a call like
string [] results = MyDataTable.ColumnToArray<string>("MyColumn1").ToArray();. Of course since it does return an IEnumerable you can do as well like ToList, or whatever. Note that I return null if it fails, because should it fail, I want to know, because that means there is a bug somewhere. You could replace that with return Enumerable.Empty<string>(); if you would rather like, which returns a blank collection.

Also, the reason you see this code doing so much. At our work we can be connecting to SQL Server, Oracle, or AS400, and fetching results and placing them in a DataTable. One annoyance with all that is that Oracle likes to return what is called an "Oracle Double". If you try and type cast that to a C# double, it blows an error. Because of that I had to modify the code into what you see above

[necrovore]

it only returns 1 value

Perhaps that is because you are overwriting the contents of the textbox. Try changing line: textBox15.Text = reader["Totalprice"].ToString(); to textBox15.Text = textBox15.Text + " - " reader["Totalprice"].ToString();

then it will show all the nultiple values seperated by "-". If u do this and still getting only 1 value then probably ur result set has only that one value.

[JOSheaIV]

Yeah I'm an idiot for missing that. You are overwriting your TextBox each time in that while loop. So it's going to take the last value fetched, and uses that (so to speak).

There could be multiple fixes. One as necrovore mentioned, is to do something like textBox15.Text += String.Format("-{0}", reader["Totalprice"]);. The only thing that you might not like with that is first of all, if you have old text in the textBox15, you're going to only append to it. Also, you'll have that '-' on the front of the first value.

While the other approach I gave you could work as well, you'd need to first store the results in a DataTable. You could do something like this (however, not entirely sure what you are doing with the parameters there, and this example would NOT include them) (also note it uses the extension I provided above)

using(DataTable results = new DataTable())
{
    using(OleDbDataReader reader = lala.ExecuteReader())
    {
        results.Load(reader);
        textBox15.Text = String.Join("-", results.ColumnToArray<string>("Totalprice"));
    }
}

Again the only problem with that is I don't apply the parameter. Which speaking of, what is the exact intent of that parameter, if I may ask? Looking at you code, it really looks like you are doing un-necessary work by creating a parameter, after a fetch, assigning it a value, and then setting that value to a TextBox. But I must ask, why exactly are you doing the fetch then? The while(reader.Read()) is looping through each row that is pulled back from your fetch. Also, from a quick scan online, it looks like that parameter is supposed to be used, before the fetch, not after (when you call, ExecuteReader, you are querying your database).

Not trying to be rude, I just feel like you might be throwing code at the screen and hoping it works ... unless I am an idiot and totally missing something (which I have not ruled out). Can you please explain what is your objective here? I would love to help, and I feel you might be a little unsure exact what all this code is doing (kind of like an overwhelming feeling, I say this, as I have been guilty of this one to many times myself)

[john_93]

fixed thanks

[JOSheaIV]

Out of curiousity, mind sharing what it was? In case for instance someone were to find this thread looking for help to a similar question

[john_93]

I just started c#, also there was an error in the sql query, my net framework limits it to do so

[JOSheaIV]

Mind posting the code?

[john_93]

try
        {

            connection.Open();
            OleDbCommand command = new OleDbCommand();
            command.Connection = connection;
            string query = "SELECT [Totalprice] FROM [Firebird m0 Damage]" + "WHERE Totalprice= " + comboBox6.SelectedValue + "";

[JOSheaIV]

Ahhh now that makes sense. (in relation to your question and older code)