SqlDataReader filling column in datagridview

Question

I have been wondering for quiet long time how can I set my SqlDataReader to fill column "pocet" in my datagridview "dtg_ksluzby". I thought that my code should look something like this:

 SqlCommand novyprikaz2 = new SqlCommand("SELECT pocet FROM klisluz WHERE id='"+vyberradek+"'",spojeni); // I also have column "pocet" in table klisluz, vyberradek is string which selectrs id number
            spojeni.Open();
            SqlDataReader precti2 = novyprikaz2.ExecuteReader();
            if (precti2.Read())
            {
                dtg_ksluzby.Columns["pocet"]; // this part I need to improve to fill
            }

Would you please help me to improve my code? Thanks so much in advance.

Edit for trippino:

In "klisluz" which is in the table that includes columns (id,akce,subkey,text,pocet). I need to:
1. select * from klisluz where subkey = '"+vyberradek+"'
2. Take column pocet and fill it into dtg_ksluzby where i find the text is similar in column "text" in dtg_ksluzby

I hope my description is understandabla, sorry for my weak english.

are you going to update column of existing datagridview with already bind with data? — Damith, Jul 18 '13 at 09:04

Steve · Answer 1 · 2013-07-18T11:43:17.407

Do not use string concatenation to build sql commands, (to avoid Sql Injection and parsing problem)

using(SqlConnection spojeni = new SqlConnection(conString))
using(SqlCommand novyprikaz2 = new SqlCommand("SELECT pocet FROM klisluz " + 
                                              "WHERE id = @id",spojeni); 
{
     novyprikaz2.Parameters.AddWithValue("@id", vyberradek);
     spojeni.Open();
     using(SqlDataAdapter da = new SqlDataAdapter(novyprikaz2))
     {
        DataTable dt = new DataTable();
        da.Fill(dt);
        dtg_ksluzby.DataSource = dt;

     }
 }

Use a DataAdapter to load your data from database and pass this DataAdapter instance to the DataSource of your DataGridView

Tobia Zambon · Accepted Answer · 2013-07-18T09:45:24.747

1

Use something like this:

        for (int i = 0; i < dtg_ksluzby.Rows.Count; i++)
        {
            var row = dtg_ksluzby.Rows[i];
            using(var novyprikaz2 = new SqlCommand("SELECT pocet FROM klisluz WHERE text LIKE @t AND subkey=@s", spojeni))
            {
                novyprikaz2.Parameters.AddWithValue("@t", row.Cells["text"].Value.ToString());
                novyprikaz2.Parameters.AddWithValue("@s", vyberradek);
                spojeni.Open();
                SqlDataReader precti2 = novyprikaz2.ExecuteReader();
                if (precti2.Read())
                {
                    row.Cells["pocet"].Value = precti2["pocet"];
                }
            }
        }

edited Jul 18 '13 at 09:45

answered Jul 18 '13 at 08:47

Tobia Zambon

7,479
3
37
69

Well I wouldn't like to add rows, but fill existing column, is it possible? Thanks so much for answer. – Blaze M Jul 18 '13 at 09:02
I've edited my answer, but I can't understand how many rows you have into the DB *and* into the gridView – Tobia Zambon Jul 18 '13 at 09:06
Your second code works fine, but when I fill multiple rows it only fills the value which is on top. I think I badly described my problem. I load dtg_ksluzby with like 5 rows. And I would like to fill each row with it's value from "pocet". What should I change please? – Blaze M Jul 18 '13 at 09:13
do you have a key into the database in order to understand which is the related row into the datagridview? – Tobia Zambon Jul 18 '13 at 09:13
Well in "klisluz" which is the table that includes are columns (id,akce,subkey,text,pocet) and I need to insert "pocet" where "text" is – Blaze M Jul 18 '13 at 09:18
pay attention to the command string (the id comes from the current row now). – Tobia Zambon Jul 18 '13 at 09:19
Thanks so much for spending that much time with me, I tried your code but it gives this error: Column named id cannot be found. Parameter name: columnName – Blaze M Jul 18 '13 at 09:23
Yes, it was only a guess. What is the name of the DataGridViewColumn where the id is stored? – Tobia Zambon Jul 18 '13 at 09:24
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/33687/discussion-between-trippino-and-blaze-m) – Tobia Zambon Jul 18 '13 at 09:24

SqlDataReader filling column in datagridview

2 Answers2