c# - How to solve this Declare variable Sql?

Question

I am making a quiz app where the user can edit its questions from the database.

What I am trying to do in the code below is that when the user opens the QuizEditForm, and makes a change in the datagridview that is displaying the current data from the questionbank, the user can edit it e.g add question, delete, update.

When the user makes a change I want it to be inserted back into the Database with his UserID so if multiple users use the app they can only see their questions.

However I keep getting numerous errors that I tried to fix in my SQL statements such as declare scalar variable, incorrect syntax and such. Does anyone have any idea on how to approach this?

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Quiz_Game_Official
{
    public partial class QuizEditForm : Form
    {
        private string path = Path.Combine(System.IO.Directory.GetCurrentDirectory(), "Database1.mdf");
        private string dbConnectionPath;
        private SqlConnection connectionString;
        private Form mParent;
        public QuizEditForm(Form parent)
        {
            InitializeComponent();
            int UserID = Program.UserID;
       
            
            this.mParent = parent;
            this.dbConnectionPath = String.Format(@"Data Source = (LocalDB)MSSQLLocalDB; AttachDbFilename = {0}; Integrated Security = True", path);
            this.connectionString = new SqlConnection(dbConnectionPath);

            string SQL = "UPDATE QuestionBank SET UserID = "+UserID +
                "SELECT UserID,QuestionID , QuestionText,CorrectAnswer,WrongAnswer1,WrongAnswer2,WrongAnswer3 FROM QuestionBank";

        //    string SQL = "SELECT UserID,QuestionID , QuestionText,CorrectAnswer,WrongAnswer1,WrongAnswer2,WrongAnswer3 FROM QuestionBank WHERE UserID =" +UserID ; // selecting questions

            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(SQL, connectionString);
            DataTable quizDataTable = new DataTable();     // prepare datatable to handle data for gridview
            sqlDataAdapter.Fill(quizDataTable);   // sqlDataAdapter respresents question Table
            quizTable.DataSource = quizDataTable;  // datatable is stil data , not UI representaion so we use this line to show it on the UI , this means  table's datasource is quizDataTable variable 
        }

        private void quizTable_CellValueChanged(object sender, DataGridViewCellEventArgs e)   // one of the events , triggered when the user edits cell value
        {
           
            if (quizTable.CurrentRow != null)
            {
                this.connectionString = new SqlConnection(dbConnectionPath);
                using (connectionString)
                {
                    DataGridViewRow quizRow = quizTable.CurrentRow;// this line gets current editing table row
                    int UserID = Program.UserID;
                    string SQL = "DECLARE @QuestionBank TABLE (UserID INT)" +
                        "IF @QuestionID = 0 INSERT INTO QuestionBank(UserID,QuestionText, CorrectAnswer, WrongAnswer1, WrongAnswer2, WrongAnswer3) " +
                                   "OUTPUT INSERTED.UserID INTO @QuestionBank(UserID) VALUES ("+UserID +")"+
                                    "output INSERTED.QuestionID VALUES(@UserID,@QuestionText, @CorrectAnswer, @WrongAnswer1, @WrongAnswer2, @WrongAnswer3)  ELSE " +
                                    "UPDATE QuestionBank SET QuestionText = @QuestionText, CorrectAnswer = @CorrectAnswer, WrongAnswer1 = @WrongAnswer1, WrongAnswer2 = @WrongAnswer2, WrongAnswer3 = @WrongAnswer3 , UserID = @UserID " +
                                    "output INSERTED.QuestionID WHERE QuestionID = @QuestionID";  //Question Add or Edit  // update / insert
                    using (SqlCommand command = new SqlCommand(SQL, connectionString)) 
                    {
                        if (quizRow.Cells[0].Value == System.DBNull.Value)  //the select row doesnt have question id meaning it is for new questions
                        {
                            command.Parameters.Add("@QuestionID", SqlDbType.Int).Value = 0;  //  we set question ID to 0 otherwise
                        } 
                        else
                        {
                            command.Parameters.Add("@QuestionID", SqlDbType.Int).Value = quizRow.Cells[0].Value;
                        }
                        
                        command.Parameters.Add("@QuestionText", SqlDbType.VarChar, 200).Value = quizRow.Cells[1].Value != System.DBNull.Value ? quizRow.Cells[1].Value : "";
                        command.Parameters.Add("@CorrectAnswer", SqlDbType.VarChar, 200).Value = quizRow.Cells[2].Value != System.DBNull.Value ? quizRow.Cells[2].Value : "";
                        command.Parameters.Add("@WrongAnswer1", SqlDbType.VarChar, 200).Value = quizRow.Cells[3].Value != System.DBNull.Value ? quizRow.Cells[3].Value : "";
                        command.Parameters.Add("@WrongAnswer2", SqlDbType.VarChar, 200).Value = quizRow.Cells[4].Value != System.DBNull.Value ? quizRow.Cells[4].Value : "";
                        command.Parameters.Add("@WrongAnswer3", SqlDbType.VarChar, 200).Value = quizRow.Cells[5].Value != System.DBNull.Value ? quizRow.Cells[5].Value : "";

                        try
                        {
                            this.connectionString.Open();
                            int result = (int)command.ExecuteScalar();   //when sql query is ran , it ouputs last inserted or updated id ,  get the id and store it to results variable
                            int modified = (int)result;
                            UserID = Program.UserID;
                            if (modified == 0)
                            {
                                MessageBox.Show("Quiz Table Update Failed");
                            }
                            quizTable.CurrentRow.Cells[0].Value = modified;                            
                            this.connectionString.Close();
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message);
                        }
                    }
                }
            }
        }

        private void quizTable_UserDeletingRow(object sender, DataGridViewRowCancelEventArgs e)  // event triggered when user deletes a question 
        {
            //e.Cancel = true;
            if (quizTable.CurrentRow.Cells[0].Value != System.DBNull.Value)
            {
                if (MessageBox.Show("Are you sure to Delete this question?", "Warning", MessageBoxButtons.YesNo) == DialogResult.Yes)  // user is asked if he is suere about deleting the question
                {
                    this.connectionString = new SqlConnection(dbConnectionPath);
                    using (connectionString)
                    {
                        DataGridViewRow quizRow = quizTable.CurrentRow;
                        string SQL = "DELETE from QuestionBank WHERE QuestionID = @QuestionID";  //Question Add or Edit
                        using (SqlCommand command = new SqlCommand(SQL, connectionString))
                        {
                            command.Parameters.Add("@QuestionID", SqlDbType.Int).Value = quizRow.Cells[0].Value;
                            try
                            {
                                this.connectionString.Open();
                                int affectedRow = command.ExecuteNonQuery();
                                if (affectedRow == 0)
                                {
                                    e.Cancel = true;
                                    MessageBox.Show("Question delete failed");
                                }
                                this.connectionString.Close();
                            }
                            catch (Exception ex)
                            {
                                e.Cancel = true;
                                MessageBox.Show(ex.Message);
                            }
                        }
                    }
                }
            }
        }

        private void QuizEditForm_FormClosing(object sender, FormClosingEventArgs e)
        {
            this.mParent.Show();
        }
    }
}

Answer 1

I'm not sure where to start here. First, before you do anything, read up on MSDN how databases work and the correct syntax for each of the statements you want to make.

I will note a few things that come to me as issues in your code, there appear to be many. If any of these are not clear to you, do not proceed with running your app until you understand it:

1. AttachDbFileName is wrong in most contexts, except for testing. It creates a copy of the database and drops it when you close the connection. See Bad habits: Using AttachDBFileName
2. You should not cache the SqlConnection object, you should create it every time you need it from a connection string, which is just a string.
3. You should be disposing your connection, command, adapter and/or reader objects with using blocks
4. I have no idea why you would want to start your main display with "UPDATE QuestionBank SET UserID = "+UserID which will set the UserId the same on every row in that table.
5. In CellValueChanged it is normally best practice to get the cell value via the e parameter, not CurrentRow which only gets the selected row, as it may have been changed programmatically.
6. I'm not entirely sure what the Upsert (combined insert/update statement) was meant to look like, but that is definitely not correct syntax, nor, even if it was, would be the correct way to do it. See here for best practice.
7. If you are inserting a row and don't have a questionID, you need to specifically check that in the insert command. And you can combine the if...== System.DBNull.Value into one line: command.Parameters.Add("@QuestionID", SqlDbType.Int).Value = quizRow.Cells[0].Value; and check for null.
8. Do not create MessageBoxes or do anything that may take time while the connection is open.