What is the correct way of using (INSERT SELECT FROM WHERE) query in C#?

Question

I'm making a form on C# that inserts data in a table with a where clause, but it's not working. I think the syntax of my query is the problem but I can't solve it.

This is the one that I tried that's working, but I need an insert query with a where clause.

SqlCommand addEmp = new SqlCommand("INSERT INTO tbl_Expenses " +
                "(InvestedMoney,EstimatedServingCount,MealName) " +
                "VALUES (@inv, @est, @mname)", conn);
addEmp.Parameters.AddWithValue("@mname", textBox1.Text);
addEmp.Parameters.AddWithValue("@inv", textBox2.Text);
addEmp.Parameters.AddWithValue("@est", textBox3.Text);

conn.Open();
addEmp.ExecuteNonQuery();
conn.Close();

I tried this code below:

SqlCommand cmd = new SqlCommand("INSERT INTO tbl_Meal" +                                 
    " ExpensesID, MealName, MealPrice, ImageName, Imageblob)" +
    "SELECT ExpensesID, @mname, @mprice, @imname, @img " +
    "FROM tbl_Expenses" +
    "WHERE MealName = '"+textBox1.Text+"'",conn);

cmd.Parameters.AddWithValue("@mname", textBox1.Text);                      
cmd.Parameters.AddWithValue("@mprice", textBox4.Text);
cmd.Parameters.AddWithValue("@imname", textBox1.Text);
cmd.Parameters.Add("@img", SqlDbType.Image, photo.Length).Value = photo;

conn.Open();
cmd.ExecuteNonQuery();

But I get this error:

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

I **strongly** suspect your main issue is `"from tbl_Expenses" + "WHERE` You missed a space before `WHERE`. You can discover these kinds of things in future, by yourself, by looking at the value of `cmd.CommandText`. — mjwills, Mar 26 '19 at 23:02
I think I'm just really tired that's why i didn't see that I'm missing a space there, the deadline for my project is tomorrow that's why i didn't have a sleep in the past 12 hours. anyways thank you so much, your respond is faster than my group mates lol — krljde, Mar 26 '19 at 23:07
It happens to the best of us @krljde. Perhaps close your question? https://stackoverflow.com/questions/1100260/multiline-string-literal-in-c-sharp may help you avoid these kinds of issues in future. — mjwills, Mar 26 '19 at 23:08
using @ (escape sequence modifier) infront of the string you can do multiline steings and dont have to worry about such things as missing spaces — Bacon, Mar 26 '19 at 23:16
You should probably refactor this to not pass `textBox1.Text` through directly in your where clause. Doing so is outright dangerous. — user2366842, Mar 27 '19 at 02:07

Joel Coehoorn · Answer 1 · 2019-03-27T01:14:22.423

This section is missing a space between the lines:

"from tbl_Expenses" +
"WHERE MealName = '"

so the sql code references a table named tbl_ExpensesWHERE, gives the table an alias of MealName, and then has an out-of-place =.

But you should also already know from the use of parameters elsewhere it is NOT okay to substitute textBox1.Text into the query like that. NEVER do that. Not even once. Not even for practice/learning code!

There are some other poor practices in here, but that was the worst. Here's a better pattern:

string sql = @"
INSERT into tbl_Meal
    (ExpensesID,MealName,MealPrice,ImageName,Imageblob)
SELECT
    ExpensesID,@mname,@mprice,@mname,@img 
FROM tbl_Expenses
WHERE MealName = @mname";

using (var conn = new SqlConnection("connection string here"))
using (var cmd = new SqlCommand(sql, conn))
{
    //wild guess at column types. Use actual column types/size FROM THE DATABASE
    cmd.Parameters.Add("@mname", SqlDbType.NVarChar, 30).Value = textBox1.Text;  
    cmd.Parameters.Add("@mprice", SQlDbType.Decimal, 18, 8).Value = textBox4.Text;
    //use the size of the column here, not the length of the photo
    cmd.Parameters.Add("@img", SqlDbType.Image, 8000).Value = photo;

    conn.Open();
    cmd.ExecuteNonQuery();
}

score 0 · Answer 2 · answered Mar 26 '19 at 23:05

0

Instead of Parameters.AddWithValue() use Parameters.Add() and also use correct datatypes, for example EstimatedServingCount seems to be an int, but however AddWithValue can not know that:

addEmp.Parameters.Add("@mname", SqlDbType.VarChar).Value = textBox1.Text;
addEmp.Parameters.Add("@inv", SqlDbType.VarChar).Value = textBox2.Text;
addEmp.Parameters.Add("@est", SqlDbType.Int).Value = textBox3.Text;

answered Mar 26 '19 at 23:05

Ashkan Mobayen Khiabani

33,575
33
102
171

The code above is just a test of my original code, that's i didn't bother to fix the minor errors in that code. Your answer wont help with my main problem tho, thanks anyways – krljde Mar 26 '19 at 23:13

What is the correct way of using (INSERT SELECT FROM WHERE) query in C#?

2 Answers2