c# Insert statement - array as sql column name

Question

I have two tables, Vehicle_Status and Trans_Income.

Vehicle_Status contain two columns, Vehicle_Number and Status, and the Vehicle_Number consists the data which are the column name of Trans_Income.

I want to load the data from Vehicle_Number into an array and use it in the insert statement.

string[] vehicleActive = new string[100];

SqlCommand cmd = new SqlCommand("Select Vehicle_Number from [dbo].[Vehicle_Status] Where Status = 'Inactive'", con);
cmd.CommandType = CommandType.Text;

con.Open();
int a = 0;

SqlDataReader dr = cmd.ExecuteReader();

while (dr.Read())
{
    vehicleActive[a] = dr["Vehicle_Number"].ToString();
    a++;
}

con.close();

for (int b = 0; b < a; b++)
{
    cmd = new SqlCommand("Insert Into [dbo].[Trans_Income](Month, Particular,'"+vehicleActive[b]+"') VALUES (@Month, @Particular, @vehicleNo )", con);

    con.Open();

    cmd.Parameters.AddWithValue("@Month", textBox2.Text);
    cmd.Parameters.AddWithValue("@Particular", textBox2.Text);
    cmd.Parameters.AddWithValue("@vehicleNo", textBox3.Text);

    cmd.ExecuteNonQuery();
    con.Close();    
}

But I get the error

System.Data.SqlClient.SqlException: 'Incorrect syntax near 'BJW6719'.'

One of the column names is BJW6719 - can anyone help me with this? Or is there any other, better way for doing this? Thank you in advance.

It sounds like your mixing data and metadata together which is usually a bad sign for a data model. You have multiple columns containing the same "type" of data. Usually, that data should be stored in a single column and an additional column should contain the data that is currently being embedded in your column names. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Dec 20, 2017 at 7:29
Hi @Damien_The_Unbeliever Thank You for your advice, this is part of my program, I am trying to record transport income of the vehicle under same order number, and some time need to get the average and insert into all "active" vehicle, i am not sure if i am doing this correctly, so is it better to have more row than more column in this case? ThankYou — Th C
– Th C, Commented Dec 21, 2017 at 1:54

Sunil · Accepted Answer · 2017-12-20 07:31:13Z

1

I notice you have extra ' character around your column name, they are not required. Try with the below code:

cmd = new SqlCommand("Insert Into [dbo].[Trans_Income](Month, Particular,"+vehicleActive[b]+") VALUES (@Month, @Particular, @vehicleNo )", con);

Are you sure that your third column name is: BJW6719. If yes the above will work otherwise you need to add the correct column name.

answered Dec 20, 2017 at 7:31

Sunil

3,44210 gold badges27 silver badges32 bronze badges

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

Comments

Ankit · Accepted Answer · 2017-12-20 07:53:55Z

if you are using .net framework 4.5 up then you can simply convert table into arrey here is code

try
                {
                  var dt = new DataTable();
                  var cmd = new SqlCommand();
                  cmd.CommandText = @"Select Vehicle_Number from [dbo].[Vehicle_Status] Where Status = 'Inactive'";
                                   var da = new SqlDataAdapter(cmd);
                  da.Fill(dt);
                }
             catch (Exception ex)
                {

                }
             finally
                {
                  if (cmd.Connection.State == ConnectionState.Open)
                   {
                     cmd.Connection.Close();
                   }
                }

               var vehicleActive= dt.Rows[0].ItemArray.Select(x => x.ToString()).ToArray();

then you can loop through your array like this

for (int i = 0; i < vehicleActive.Length; i++)
                    {
                        cmd = new SqlCommand("Insert Into [dbo].[Trans_Income](Month, Particular,'"+vehicleActive[b]+"') VALUES (@Month, @Particular, @vehicleNo )", con);

                        con.Open();


                        cmd.Parameters.AddWithValue("@Month", textBox2.Text);
                        cmd.Parameters.AddWithValue("@Particular", textBox2.Text);
                        cmd.Parameters.AddWithValue("@vehicleNo", textBox3.Text);

                        cmd.ExecuteNonQuery();
                        con.Close();    
                     }

this is better way to loop through database from code and there might be issue with column name so check column name from sql server before defining in sql query from code

Collectives™ on Stack Overflow

c# Insert statement - array as sql column name

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related