{"id":7289,"date":"2022-09-07T23:04:36","date_gmt":"2022-09-07T17:34:36","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-c-inserting-values-into-database-using-switch-case\/"},"modified":"2022-09-07T23:04:36","modified_gmt":"2022-09-07T17:34:36","slug":"solved-c-inserting-values-into-database-using-switch-case","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-c-inserting-values-into-database-using-switch-case\/","title":{"rendered":"[Solved] C# Inserting values into database using switch case"},"content":{"rendered":"

[ad_1]
\n<\/p>\n

\n
\n
<\/div>\n
\n
\n

Let’s clean up the code a little bit and make it a little more reusable first:<\/p>\n

foreach (int thisColor in allColors)\n{\n    List<Subject> subjectsForThisColor = subjects.Where(x => x.Color == thisColor).ToList();\n    foreach (Subject s in subjectsForThisColor)\n    {\n        test += s.SubjectName + \" -\" + s.Color + \"\\n\";\n        switch (s.Color)\n        {\n            case 1:\n                updateOneRow(\"Monday\", \"first\", s.SubjectName, null);\n                break;\n            case 2:\n                updateOneRow(\"Monday\", \"second\", s.SubjectName, null);\n                break;\n            case 6:\n                updateOneRow(\"Monday\", \"sixth\", s.SubjectName, null);\n                break;\n        }\n    }\n}\n<\/code><\/pre>\n
SQL Method<\/p>\n
public void updateOneRow(string tableName, string columnName, string value){        \n    con.Open();\n    query = \"SELECT count (\"+columnName+\") FROM \"+tableName;\n    SqlCommand cmd = New SqlCommand(query, con);\n    Int32 count = (Int32)cmd.ExecuteScalar();\n    if(count == 0){\n        cmd = new SqlCommand(\"INSERT INTO \" + tableName + \"(\"+columnName+\") values (@value)\"),con);\n    }\n    else{\n        query = \"SELECT top 1 \"+columnName+\" FROM \"+tableName;\n        cmd = New SqlCommand(query, con);\n        string dbValue = (string)cmd.ExecuteScalar();\n        if (dbValue != null && dbValue != string.Empty)\n            value += \";\"+dbValue;\n        cmd = new SqlCommand(\"UPDATE  \" + tableName + \" set [\"+columnName+\"] = @value\", con);\n    }\n    cmd.Parameters.AddWithValue(\"value\",value);\n    cmd.ExecuteNonQuery();\n    con.Close();\n}\n<\/code><\/pre>\n
That’ll help clear up your code, as to why you are getting Elective-5<\/code>, it is because you were using an UPDATE<\/code> SQL statement on your case 6 instead of an INSERT<\/code>. Update will overwrite previous values that were in the table and replace all current values that match your where clause, of which you had none. So all values in the table would have been overwritten. I think you should have written INSERT<\/code> and not UPDATE<\/code><\/p>\n
[Edit]
\nI don’t like it, but updated the code to do what you are expecting. You may need to debug it, but should give you the idea. It checks to see if a row already exists, if not it does an insert. If it does, it checks the column for a value and if one exists, adds it to the new value to insert. Then it does the update.<\/p>\n
Hope that helps!<\/p>\n
Final warning<\/strong>
\nThis code won’t be perfect, far from it, but I hope it will guide you in the right direction of some slightly better coding practices. Good luck!
\nAlso this is not the best design for a database, you should really look at doing this a different way!<\/p>\n
[OP’s CODE]<\/strong><\/p>\n
case 6:\n                            con.Open();\n                            string query = \"SELECT count (  sixth  ) FROM  Monday  \";\n                            SqlCommand cmd3 = new SqlCommand(query, con);\n                            Int32 count = (Int32)cmd3.ExecuteScalar();\n                            if (count == 0)\n                            {\n                                cmd = new SqlCommand(\"INSERT INTO Monday (sixth) values (@value)\", con);\n\n                            }\n                            else\n                            {\n                                query = \"SELECT top 1 sixth FROM Monday\";\n                                cmd = new SqlCommand(query, con);\n                                string dbValue = (string)cmd.ExecuteScalar();\n                                if (dbValue != null && dbValue != string.Empty)\n                                {\n                                    value += \";\" + dbValue;\n                                    \/\/value += s.SubjectName;\n                                    \/\/cmd = new SqlCommand(\"UPDATE  Monday set [sixth] = @value\", con); <-This is in the wrong place\n\n                                }\n                                cmd = new SqlCommand(\"UPDATE  Monday set [sixth] = @value\", con);\/\/Should be here\n                            }\n                            cmd.Parameters.AddWithValue(\"value\",value);\n                            cmd.ExecuteNonQuery();\n                            con.Close();\n                            break;\n<\/code><\/pre>\n
After making the necessary changes and executing it, I am getting the following error.<\/p>\n
The parameterized query '(@value nvarchar(4000))INSERT INTO Monday (sixth) values (@value' expects the parameter '@value', which was not supplied.\n<\/code><\/pre>\n<\/p><\/div>\n
<\/div>\n<\/div>\n
            17<\/span> <\/p><\/div>\n<\/div>\n
[ad_2]<\/p>\n
solved C# Inserting values into database using switch case <\/p>\n","protected":false},"excerpt":{"rendered":"
[ad_1] Let’s clean up the code a little bit and make it a little more reusable first: foreach (int thisColor in allColors) { List<Subject> subjectsForThisColor = subjects.Where(x => x.Color == thisColor).ToList(); foreach (Subject s in subjectsForThisColor) { test += s.SubjectName + ” -” + s.Color + “\\n”; switch (s.Color) { case 1: updateOneRow(“Monday”, “first”, s.SubjectName, … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[320],"tags":[324],"class_list":["post-7289","post","type-post","status-publish","format-standard","hentry","category-solved","tag-c"],"yoast_head":"\n[Solved] C# Inserting values into database using switch case - JassWeb<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/jassweb.com\/solved\/solved-c-inserting-values-into-database-using-switch-case\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] C# Inserting values into database using switch case - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] Let’s clean up the code a little bit and make it a little more reusable first: foreach (int thisColor in allColors) { List<Subject> subjectsForThisColor = subjects.Where(x => x.Color == thisColor).ToList(); foreach (Subject s in subjectsForThisColor) { test += s.SubjectName + " -" + s.Color + "n"; switch (s.Color) { case 1: updateOneRow("Monday", "first", s.SubjectName, ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-c-inserting-values-into-database-using-switch-case\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-09-07T17:34:36+00:00\" \/>\n<meta name=\"author\" content=\"Kirat\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kirat\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-c-inserting-values-into-database-using-switch-case\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-c-inserting-values-into-database-using-switch-case\\\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/person\\\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] C# Inserting values into database using switch case\",\"datePublished\":\"2022-09-07T17:34:36+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-c-inserting-values-into-database-using-switch-case\\\/\"},\"wordCount\":250,\"publisher\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#organization\"},\"keywords\":[\"c++\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-c-inserting-values-into-database-using-switch-case\\\/\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-c-inserting-values-into-database-using-switch-case\\\/\",\"name\":\"[Solved] C# Inserting values into database using switch case - JassWeb\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#website\"},\"datePublished\":\"2022-09-07T17:34:36+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-c-inserting-values-into-database-using-switch-case\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-c-inserting-values-into-database-using-switch-case\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-c-inserting-values-into-database-using-switch-case\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] C# Inserting values into database using switch case\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#website\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/\",\"name\":\"JassWeb\",\"description\":\"Build High-quality Websites\",\"publisher\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#organization\",\"name\":\"Jass Web\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/jassweb.com\\\/wp-content\\\/uploads\\\/2021\\\/02\\\/jass-website-logo-1.png\",\"contentUrl\":\"https:\\\/\\\/jassweb.com\\\/wp-content\\\/uploads\\\/2021\\\/02\\\/jass-website-logo-1.png\",\"width\":693,\"height\":132,\"caption\":\"Jass Web\"},\"image\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/logo\\\/image\\\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/person\\\/65c9c7b7958150c0dc8371fa35dd7c31\",\"name\":\"Kirat\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/wp-content\\\/litespeed\\\/avatar\\\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/wp-content\\\/litespeed\\\/avatar\\\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400\",\"contentUrl\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/wp-content\\\/litespeed\\\/avatar\\\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\\\/\\\/jassweb.com\"],\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/author\\\/jaspritsinghghumangmail-com\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] C# Inserting values into database using switch case - JassWeb","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/jassweb.com\/solved\/solved-c-inserting-values-into-database-using-switch-case\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] C# Inserting values into database using switch case - JassWeb","og_description":"[ad_1] Let’s clean up the code a little bit and make it a little more reusable first: foreach (int thisColor in allColors) { List<Subject> subjectsForThisColor = subjects.Where(x => x.Color == thisColor).ToList(); foreach (Subject s in subjectsForThisColor) { test += s.SubjectName + \" -\" + s.Color + \"n\"; switch (s.Color) { case 1: updateOneRow(\"Monday\", \"first\", s.SubjectName, ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-c-inserting-values-into-database-using-switch-case\/","og_site_name":"JassWeb","article_published_time":"2022-09-07T17:34:36+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-c-inserting-values-into-database-using-switch-case\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-c-inserting-values-into-database-using-switch-case\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] C# Inserting values into database using switch case","datePublished":"2022-09-07T17:34:36+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-c-inserting-values-into-database-using-switch-case\/"},"wordCount":250,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["c++"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-c-inserting-values-into-database-using-switch-case\/","url":"https:\/\/jassweb.com\/solved\/solved-c-inserting-values-into-database-using-switch-case\/","name":"[Solved] C# Inserting values into database using switch case - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-09-07T17:34:36+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-c-inserting-values-into-database-using-switch-case\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-c-inserting-values-into-database-using-switch-case\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-c-inserting-values-into-database-using-switch-case\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] C# Inserting values into database using switch case"}]},{"@type":"WebSite","@id":"https:\/\/jassweb.com\/solved\/#website","url":"https:\/\/jassweb.com\/solved\/","name":"JassWeb","description":"Build High-quality Websites","publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/jassweb.com\/solved\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/jassweb.com\/solved\/#organization","name":"Jass Web","url":"https:\/\/jassweb.com\/solved\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/","url":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","contentUrl":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","width":693,"height":132,"caption":"Jass Web"},"image":{"@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31","name":"Kirat","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400","url":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400","caption":"Kirat"},"sameAs":["http:\/\/jassweb.com"],"url":"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/"}]}},"_links":{"self":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/7289","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/comments?post=7289"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/7289\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=7289"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=7289"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=7289"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}