Saving data depending on the text contained in another column

Question

SQL1 portion works just fine, but I need help figuring out if my statements in SQL2, 3 & 4 are correct because it currently doesn't work. I want to insert 'Audience View 1' in VIEW if the words 'Camera 1' is present in VIDEOPATH, 'Audience View 2' in VIEW if the words 'Camera 2' is present in VIDEOPATH and so on.

        string[] files = Directory.GetFiles("C:/Users/sit/Videos/Done/");
        string view1 = "Audience View 1";
        string view2 = "Audience View 2";
        string view3 = "Lecturer View";

        foreach (string file in files) {

            string SQL1 = "INSERT INTO TBL_LESSONCAM(VIDEOPATH)VALUES('" + (file) + "')";
            string SQL2 = "UPDATE TBL_LESSONCAM(VIEW)VALUES('" + (view1) + "')" + "WHERE VIDEOPATH LIKE '%Camera 1%'";
            string SQL3 = "UPDATE TBL_LESSONCAM(VIEW)VALUES('" + (view2) + "')" + "WHERE VIDEOPATH LIKE '%Camera 2%'";
            string SQL4 = "UPDATE TBL_LESSONCAM(VIEW)VALUES('" + (view3) + "')" + "WHERE VIDEOPATH LIKE '%Camera 3%'";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = SQL1;
            cmd.CommandText = SQL2;
            cmd.CommandText = SQL3;
            cmd.CommandText = SQL4;
            cn.Open();
            cmd.ExecuteNonQuery();
            cn.Close();

        }

UPDATE: Is my structure for running multiple SQL statements correct?

Possible duplicate of [What are good ways to prevent SQL injection?](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection) — mjwills, Jul 09 '18 at 07:54
Is `VIDEOPATH` another table? If it is a **column** you should be using `UPDATE` rather than `INSERT`. — mjwills, Jul 09 '18 at 07:55
@mjwills yes it is a column. I tried changing it to UPDATE but it didn't work. — whitemustang13, Jul 09 '18 at 08:34
`UPDATE TBL_LESSONCAM(VIEW)VALUES` this is not the syntax of an UPDATE statement. It would be something like `UPDATE TBL_LESSONCAM SET VIEW = ` etc. Any basic tutorial or example will show you this... — ADyson, Jul 09 '18 at 09:18
And no this will not execute all your statements. It will only execute the last one. You keep overwriting the CommandText property with a different command, before you executed the earlier one. — ADyson, Jul 09 '18 at 09:19
@ADyson thanks for your help. but in this case, how do I structure it in such a way that the commands don't get overwritten? I've looked up examples (like this: https://stackoverflow.com/questions/13677318/how-to-run-multiple-sql-commands-in-a-single-sql-connection) and I can't seem to understand them. — whitemustang13, Jul 09 '18 at 09:56
@whitemustang13 well you need to execute each query, so just run ExecuteNonQuery each time before you change the commandText. Or create a separate SqlCommand object for each query and execute them all separately. If they all need to succeed/fail together (i.e. if one fails then they all must fail in order to maintain the integrity of the data), then learn about Transactions. P.S. None of the answers in that link you posted are really very good, apart from https://stackoverflow.com/a/13677409/5947043 possibly. — ADyson, Jul 09 '18 at 10:09

score 0 · Answer 1 · answered Jul 09 '18 at 09:17

your update:

string SQL2 = "UPDATE TBL_LESSONCAM(VIEW)VALUES('" + (view1) + "')" + "WHERE VIDEOPATH LIKE '%Camera 1%'";

should be

string SQL2 = "UPDATE TBL_LESSONCAM set view = '" + (view1) + "'" + " WHERE VIDEOPATH LIKE '%Camera 1%'; ";

Please have a look at What are good ways to prevent SQL injection?

Saving data depending on the text contained in another column

1 Answers1