How to add array to a SQL row in C#?

Question

I have an array that I made from an StringBuilder:

string[] input;
input = sb.ToString().Split(',');

Now I want to insert the data from this "input" array to a SQL row? The first element in the array must be an integer. How can I implement this in a fastest way?

Thanks in advance for any help or example.

I've read from SQL table, edited, and i've got to the sb and I have:

{50001,Pingu,C:\Users\Public\Pictures\Sample Pictures\Penguins.jpg,A,Skift 2,Requill,sQ,,,,,S5,,,,,,,Motvikt,,Kvalité_nr_2,,,,,,,,,,,,}


col(0)  col(1)   col(2) col(3)  col(4)  col(5)  col(6)  col(7)

50001   Pingu    Pathway    A   Skift 2  Requill    SQ    ""

Now i want to save back to SQL. The columns quantity and names in the SQL are changing. That's why I'm using array.

Vidor

I have tried the following (see code) and now I get the error:

Must declare the scalar variable "@Param"

The cbItems array contains the name of columns in the database.

            string[] cbItems;
        cbItems = sbItems.ToString().Split(',');

        string[] input;
        input = sb.ToString().Split(',');

        DataSet dataSet = new DataSet();
        dataTable = dataSet.Tables.Add();

        SqlConnection cn = new SqlConnection(connString);

        int firstElement;
        Int32.TryParse(input[0], out firstElement);

        string sql = "INSERT INTO UserData1 (Anställningsnummer) VALUES (@ID)";

        cn.Open();
        SqlCommand cmd = new SqlCommand(sql, cn);
        cmd.Parameters.AddWithValue("@ID", firstElement);
        cmd.ExecuteNonQuery();

        int i = 1;
        foreach (string r in input)
        {
            string paramName = cbItems[i].ToString();
            string anyElement= input[i].ToString();
            SqlCommand myInsertCmd = new SqlCommand("INSERT INTO UserData1(" + paramName + ") VALUES(@Param)", cn);

            cmd.Parameters.AddWithValue("@Param", anyElement);

            i++;
            myInsertCmd.ExecuteNonQuery();
        }

        cn.Close();

Am I missing something?

Vidor

Do you want to all the string array to a single cell or multiple cells? — Dave
– Dave, Commented Apr 22, 2013 at 11:45
We're going to need more information than this. What is the schema of the table you are trying to insert into, and how are the data in the array matched to each column? — tom.dietrich
– tom.dietrich, Commented Apr 22, 2013 at 11:47
When you are iterating thru each array you can user int.TryParse() method, which return a value if its a int, otherwise not. Give it a try and let me know in case you need detail. — foo-baar
– foo-baar, Commented Apr 22, 2013 at 11:47

Darren · Accepted Answer · 2013-04-22 11:47:18Z

5

You'll have to parse the first element in the array to an integer.

 int firstElement;
 Int32.TryParse(input[0], out firstElement);

You can then use firstElement and add it as a SQL Parameter

string sql = "INSERT INTO TABLE (ID) VALUES (@ID)";

SqlCommand cmd = new SqlCommand(sql, _connection);
cmd.Parameters.AddWithValue("@ID", firstElement);

answered Apr 22, 2013 at 11:47

Darren

71.2k24 gold badges141 silver badges146 bronze badges

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

Collectives™ on Stack Overflow

How to add array to a SQL row in C#?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related