C# query and string coding error

Question

I tried to get 4 names from first table and check how frequent 4 of the names appeared in each of another 20 groups and then update it on groupevenfrequency. However, I have encountered error on this coding. Appreciate if someone can assist. Thanks.

From this coding, why str[1] and str[2] and str[3] and str[4] is same teachername? But the sql command SELECT DISTINCT is already resulted 4 different teachers. Please advice.

        dbConnect = new SQLiteConnection("Data Source=school.db;Version=3;");
        dbConnect.Open();
        cmd = new SQLiteCommand();
        cmd = dbConnect.CreateCommand();
        cmd.CommandText = "SELECT DISTINCT Teacher_Name from " + myTeacher + " Order by Sum_Weekly_Credit desc LIMIT 4";
        DataTable dt = new DataTable();
        SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
        da.Fill(dt);
        string[] str = new string[5];
        for (int i = 1; i <= 4; i++)
        {
            foreach (DataRow dr in dt.Rows)
            {
                str[i] = dr["Teacher_Name"].ToString();
                cmd.ExecuteNonQuery();
            }
        }
        dbConnect.Close();
        dbConnect = new SQLiteConnection("Data Source=school.db;Version=3;");
        dbConnect.Open();
        cmd2 = new SQLiteCommand();
        cmd2 = dbConnect.CreateCommand();
        cmd3 = new SQLiteCommand();
        cmd3 = dbConnect.CreateCommand();
        for (int j = 1; j <= 20; j++)
        {
        cmd2.CommandText = "SELECT Subject FROM Group_Even_" + j + " WHERE Teacher_Name = @Teacher_Name1 OR Teacher_Name = @Teacher_Name2 OR Teacher_Name = @Teacher_Name3 OR Teacher_Name = @Teacher_Name4";
        cmd2.Parameters.AddWithValue("@Teacher_Name1", str[1]);
        cmd2.Parameters.AddWithValue("@Teacher_Name2", str[2]);
        cmd2.Parameters.AddWithValue("@Teacher_Name3", str[3]);
        cmd2.Parameters.AddWithValue("@Teacher_Name4", str[4]);
        DataTable dt2 = new DataTable();
        SQLiteDataAdapter da2 = new SQLiteDataAdapter(cmd2);
        da2.Fill(dt2);
                        if (dt2.Rows.Count > 0)
                {
                    int TempCountFrequency = dt2.Rows.Count;

cmd2.CommandText = "UPDATE GroupEvenFrequency SET GroupEven_Frequency = @GroupEven_Frequency WHERE GroupEven_Name = Group_Even_" + j + "";
                    cmd2.Parameters.AddWithValue("@GroupEven_Frequency", TempCountFrequency);
                    cmd2.ExecuteNonQuery();
                }
                else
                {
                    continue;
                }

    }

When you're using numbered tables, that's generally a sign of a bad design. Why isn't that number you append to the table a column in one table instead? — CodeCaster
– CodeCaster, Commented May 25, 2017 at 9:45
Strictly speaking, at some point that "SELECT Subject FROM Group_Even_" + j tries to access a table called Group_Even_7, which however doesn't seem like it exists in your database. @CodeCaster is of course right, this code raises all sorts of red flags as far as database design goes. — s.m.
– s.m., Commented May 25, 2017 at 9:50
it seems you would have had a better design with group_even as 1 table with a field of "group" instead of 20 different tables — BugFinder
– BugFinder, Commented May 25, 2017 at 9:53
Thanks all. It can be run already, but the result outcome is unacceptable. Can someone please advice why str[1], str[2], str[3], str[4] will output as same teachername? But i test the sql select distinct it resulted 4 different teachers name. Please advice. Thanks. — Michael
– Michael, Commented May 25, 2017 at 11:11

Flexicoder · Accepted Answer · 2017-05-25 11:33:13Z

1

The code you have here looks wrong....

for (int i = 1; i <= 4; i++)
{
    foreach (DataRow dr in dt.Rows)
    {
        str[i] = dr["Teacher_Name"].ToString();
        cmd.ExecuteNonQuery();
    }
}

Surely you are expecting the dt.Rows to contain the 4 names you are interested in? So why have the outer loop.

So shouldn't it be more like...

string[] str = new string[5];
int i = 1;
foreach (DataRow dr in dt.Rows)
{
    str[i] = dr["Teacher_Name"].ToString();
    i++;
}

But as others have pointed out your overall approach could do with a rethink. The code won't cater for the fact that you might not have 4 distinct teacher names

answered May 25, 2017 at 11:33

Flexicoder

8,5615 gold badges45 silver badges58 bronze badges

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

4 Comments

Michael Over a year ago

Thanks so much for your help to advice. It works. Thank you thank you.

Michael Over a year ago

Btw, do u have any way how to coding copy a datatable to another datatable for all column and rows in C# sqlite in faster way? Thanks.

Flexicoder Over a year ago

@Michael, if this has fixed it can you mark this as the correct answer. If you have another question its best to ask a new one within SO

Flexicoder Over a year ago

@Michael, there should be a grey tick to the left of my answer, click it to accept, this will show that your question has been answered and other people can see that - meta.stackexchange.com/questions/147531/…

Collectives™ on Stack Overflow

C# query and string coding error

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related