Insert multiple values into SQL Server column from C# Winforms

Question

I have a table for translators, and each translator is capable of translating from one source language to many target languages. I am creating a Windows form application for such a table, and have tried using a listbox for such an entry, but it gives me the following error

The variable name '@lang_code' has already been declared. Variable names must be unique within a query batch or stored procedure.

Code:

List<String> SelectedItems=new List<String>();

foreach (System.Data.DataRowView s in listBox1.SelectedItems)
{           
    string select = s.ToString();
    SelectedItems.Add(select);
    myCmd.Parameters.AddWithValue("@lang_code", select);
}

Where do you execute myCmd? Inside or outside the foreach loop? — ekad
– ekad, Commented Jul 2, 2015 at 13:05
This is a horrible design - do NOT put multiple values into a single database cell ! This is against even the first normalization rule of database design! — marc_s
– marc_s, Commented Jul 2, 2015 at 13:35
Aside from the horrible database design you should take a look at this article about using AddWithValue. blogs.msmvps.com/jcoehoorn/blog/2014/05/12/… — Sean Lange
– Sean Lange, Commented Jul 2, 2015 at 14:17
Let's say there are two items selected, abc and def. You only have one parameter (@lang_code), what is your expected value of @lang_code? — ekad
– ekad, Commented Jul 2, 2015 at 14:20

Mike · Accepted Answer · 2015-07-02 13:05:41Z

1

Based on the error, looks like you have added the @lang_code parameter to myCmd (SqlCommand) and have not cleared that parameter collection. So the SqlCommand already has a parameter called @lang_code. You could create multiple commands within one SqlConnection, or clear the parameters collection prior to re-adding @lang_code

answered Jul 2, 2015 at 13:05

Mike

5502 silver badges17 bronze badges

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

1 Comment

Panagiotis Kanavos Over a year ago

Or add the parameter outside the loop and change its value inside it before executint the command

Bradley Uffner · Accepted Answer · 2015-07-02 13:19:42Z

You are adding a parameter named "@lang_code" multiple times to the same query. This isn't legal in any SQL language I'm aware of. This would be the equivalent of declaring multiple c# variables with the same name in the same scope.

It sounds to me like you actually want 3 separate tables with relationships between them.

A table of Languages, a table with Translators, and a table containing the languages that each Translator knows. The Languages table is just a list of all the languages that your application is aware of. The Translator table should have a private key, with one row per translator. The LanguagesKnown table should have one row per translator per language known, with a foreign key pointing to the primary key of the translator and the primary key of the language.

You will end up with a very shallow tree structure that a relational database can easily handle. It will look something like this:

Language

LanguageId
DisplayName

Translator

TranslatorId (pk)
Name

LanguageKnown

LanguageKnownId
TranslatorId
LanguageId

Collectives™ on Stack Overflow

Insert multiple values into SQL Server column from C# Winforms

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related