CODEWITHSUNDEEP
c#sqlado.net
Jitender Singh
Jitender Singh

Reputation: 21

i am try to run sql command in c# but getting errors

i am trying to insert values to db by using insert command in my .net project

command:

string[] dist_name = new string[] { "Yuma", "Wickenburg", "Verde", "Prescott", "Pinal", "Payson", "Paradise Valley/Eastern", "Navajo", "Mountain", "La Paz", "Goodyear/Western", "Deer Valley/Central","Coconino","Cochise","Buckeye" };

int[] cust_count = new int[] { int.Parse(TextBox11.Text), int.Parse(TextBox11.Text), int.Parse(TextBox12.Text), int.Parse(TextBox13.Text), int.Parse(TextBox14.Text), int.Parse(TextBox15.Text), int.Parse(TextBox16.Text), int.Parse(TextBox17.Text), int.Parse(TextBox18.Text), int.Parse(TextBox19.Text), int.Parse(TextBox20.Text), int.Parse(TextBox21.Text), int.Parse(TextBox22.Text), int.Parse(TextBox23.Text), int.Parse(TextBox24.Text), int.Parse(TextBox25.Text) };
       
 string[] dist_code = new string[] { "YU","WG","VR","VP","PL","GP","PV","NN","GM","YP","YP","GY","DV","NC","CO","WB" };
        
        SqlConnection con = new SqlConnection(_connectionString);
        con.Open();
        
        for (int i = 0; i < dist_name.Length; i++) {

            SqlCommand sc1 = new SqlCommand("insert into tempdivcustcount(dist_name,dist_code,cust_count,year) Values("+dist_name[i]+","+dist_code[i]+","+cust_count[i]+","+int.Parse(thresholdYear.Text)+") ", con);
            object o1 = sc1.ExecuteNonQuery();
            }

but when i run it, i got this error: "Invalid column_name "YUMA" "Invalid column_name "YU"

"Yuma" and "Yu" are my going to be inserted in my db as records Please let me know how i can fix it. thanks for your help

Upvotes: 0

Views: 55

Answers (1)

Joel Coehoorn
Joel Coehoorn

Reputation: 416149

Yikes! That looks scary-vulnerable to sql injection issues. Try this:

// Not a fan of matching arrays by index, but that's an issue for another day.
string[] dist_name = new string[] { "Yuma", "Wickenburg", "Verde", "Prescott", "Pinal", "Payson", "Paradise Valley/Eastern", "Navajo", "Mountain", "La Paz", "Goodyear/Western", "Deer Valley/Central","Coconino","Cochise","Buckeye" };
int[] cust_count = new int[] { int.Parse(TextBox11.Text), int.Parse(TextBox11.Text), int.Parse(TextBox12.Text), int.Parse(TextBox13.Text), int.Parse(TextBox14.Text), int.Parse(TextBox15.Text), int.Parse(TextBox16.Text), int.Parse(TextBox17.Text), int.Parse(TextBox18.Text), int.Parse(TextBox19.Text), int.Parse(TextBox20.Text), int.Parse(TextBox21.Text), int.Parse(TextBox22.Text), int.Parse(TextBox23.Text), int.Parse(TextBox24.Text), int.Parse(TextBox25.Text) };      
string[] dist_code = new string[] { "YU","WG","VR","VP","PL","GP","PV","NN","GM","YP","YP","GY","DV","NC","CO","WB" };

// Define SQL outside the loop with named parameter placeholders. This could even be const.
string sql = "INSERT INTO tempdivcustcount (dist_name,dist_code,cust_count,year) VALUES ( @DistName, @DistCode, @CustCount, @Year);";

// using blocks make sure the items are disposed, **even if an exception is thrown**
using (var con = new SqlConnection(_connectionString))
using (var sc1 = new SqlCommand(sql, con)) 
{
    // I have to guess at types/lengths here.
    // You should use the actual types and lengths to match the database columns
    sc1.Paramerers.Add("@DistName", SqlDbType.NVarChar, 50);
    sc1.Parameters.Add("@DistCode", SqlDbType.VarChar, 10);
    sc1.Parameters.Add("@CustCount", SqlDbType.Int);
    sc1.Parameters.Add("@Year", SqlDbType.Int).Value = int.Parse(thresholdYear.Text);

    con.Open();
    for (int i = 0; i < dist_name.Length; i++) 
    {
        // Setting parameter values this way avoids issues with things like out-of-place apostrophe characters
        sc1.Parameters["@DistName"].Value = dist_name[i];
        sc1.Parameters["@DistCode"].Value = dist_code[i];
        sc1.Parameters["@CustCount"].Value = cust_count[i];
        sc1.ExecuteNonQuery();
    }
}  // using block takes care of closing the connection

Upvotes: 1

Related Questions