-1

I have been stuck on this for weeks, and i've been all over forums trying solutions to the same problem that other people had, but so far it still won't work. I'm not sure what i'm doing wrong.

I'm making a user-interactive page that gets data and submits it / inserts it into the SQL database when the button is clicked to save the data.

EmpID is the data coming from a textbox that generates the users employee ID. EmpID is then stored in the SQL database as the EmployeeID value.

I keep getting the error when I run it and try to save the data;

"Must declare scalar variable "@EmpID"

void UpdateInternal0()
{
    try
    {
        using (SqlConnection con = new SqlConnection(Properties.Settings.Default.adminConnectionString))

        {
            con.Open();

            foreach (DataRowView drv in InternalGrid0.SelectedItems)
            {

                DataRow row = drv.Row;
                bool isSelected = Convert.ToBoolean(drv.Row[0]);
                if (isSelected)
                {

                    string sqlString = "INSERT INTO ChecklistTransitionTable (RelatedEmployeeID, RelatedDocIdx) " +
                                       "VALUES (@EmpID, @DOC_Number)";

                    using (SqlCommand cmd = new SqlCommand(sqlString, con))
                    {

                        for (int i = 0; i < InternalGrid0.Columns.Count; i++)
                        {


                            int DocIdx = (int)drv.Row[i];
                            int EmployeeID = Convert.ToInt32(EmpID.Text);


                            cmd.Parameters.AddWithValue("@EmpID", EmployeeID);
                            cmd.Parameters.AddWithValue("@DOC_Number", DocIdx);


                            cmd.ExecuteScalar(); cmd.Parameters.Clear();
                            int result = cmd.ExecuteNonQuery();


                            Console.WriteLine("Test");
                            //Check Error
                            if (result < 0)
                            {

                                System.Windows.Forms.MessageBox.Show("Error inserting data into database!");
                            }
                        }

                    }
                }
            }
        }

        System.Windows.Forms.MessageBox.Show("Employee data successfully updated.");
    }

    catch (SqlException ex)
    {

        System.Windows.Forms.MessageBox.Show(string.Format("\nMessage ---\n{0}", ex.Message));
    }
}

private void BtnGridUpdate_Click(object sender, EventArgs e)
{
    UpdateInternal0();
}
Damon W
  • 25
  • 1
  • 6
  • You said that `EmployeeID` is coming from a `TextBox`. Is the `ID` stored in the database as a string or an int? – daShier Sep 03 '19 at 13:23
  • @daShier EmployeeID is stored as an int, the textbox ID is EmpID. It's why I had the line 'int EmployeeID = Convert.ToInt32(EmpID.Text);' to convert it. – Damon W Sep 03 '19 at 13:24
  • 1
    Also, [Can we stop using AddWithValue() already?](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) – Zohar Peled Sep 03 '19 at 13:24
  • 1
    The error's correct. You're executing your command (`cmd.ExecuteScalar()`), you're clearing the parameters (`cmd.Parameters.Clear()`), then you're executing your command again (`cmd.ExecuteNonQuery()`). The parameter's missing by the time you execute your command again. – Patrick Tucci Sep 03 '19 at 13:26
  • Also, this code line `int EmployeeID = Convert.ToInt32(EmpID.Text);` fires alarm bells. What if the user entered `Damon` in the text box? – Zohar Peled Sep 03 '19 at 13:35
  • @Zohar Peled the employeeID is auto generated into the textbox when the emplyoee is selected and doesn't get edited. – Damon W Sep 03 '19 at 13:36

1 Answers1

1

You're executing the query twice. The first time using ExecuteScalar() and afterwards you clear the parameter values and re-execute the query (now without values for the parameters) in the ExecuteNonQuery() call. The latter throws the exception

Maybe this altered version will work as intended.

void UpdateInternal0(int employeeId)
{
    try
    {
        using (SqlConnection con = new SqlConnection(Properties.Settings.Default.adminConnectionString))
        {
            con.Open();

            foreach (DataRowView drv in InternalGrid0.SelectedItems)
            {
                DataRow row = drv.Row;
                bool isSelected = Convert.ToBoolean(drv.Row[0]);
                if (isSelected)
                {
                    string sqlString = "INSERT INTO ChecklistTransitionTable (RelatedEmployeeID, RelatedDocIdx) VALUES (@EmpID, @DOC_Number)";

                    using (SqlCommand cmd = new SqlCommand(sqlString, con))
                    {
                        for (int i = 0; i < InternalGrid0.Columns.Count; i++)
                        {
                            int DocIdx = 0;
                            try { 
                                 DocIdx = (int)drv.Row[i];
                            } catch (InvalidCastException ice) {
                                 // You should handle this here...
                            }

                            cmd.Parameters.AddWithValue("@EmpID", employeeId);
                            cmd.Parameters.AddWithValue("@DOC_Number", DocIdx);

                            int result = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();

                            Console.WriteLine("Test");
                            //Check Error
                            if (result < 0)
                            {
                                System.Windows.Forms.MessageBox.Show("Error inserting data into database!");
                            }
                        }
                    }
                }
            }
        }
        System.Windows.Forms.MessageBox.Show("Employee data successfully updated.");
    }

    catch (SqlException ex)
    {
        System.Windows.Forms.MessageBox.Show(string.Format("\nMessage ---\n{0}", ex.Message));
    }
}

private void BtnGridUpdate_Click(object sender, EventArgs e)
{
    int employeeID = Convert.ToInt32(EmpID.Text);
    UpdateInternal0(employeeID);
}
Christiaan Nieuwlaat
  • 1,349
  • 9
  • 14
  • Running this code gives me a different error of- System.InvalidCastException: 'Specified cast is not valid.' on the line int DocIdx = (int)drv.Row[i]; – Damon W Sep 03 '19 at 13:32
  • You might want to do some extra checking before casting values to other types. – Christiaan Nieuwlaat Sep 03 '19 at 13:33
  • 1
    @DamonW That is a different issue. This is the answer for your original question. You should write a new question (although the answer is 99% that `drv.Row[i]` is not always an `int`). – mjwills Sep 03 '19 at 13:37
  • Wondering if something with the employeeID isn't communicating with the datagrid properly at this point since i'm getting that error? – Damon W Sep 03 '19 at 13:38
  • @Christian Nieuwlaat I can post a new question, but the errors seem to swap around a lot with all the trial and error i've done, one goes away just for the scalar to come back again. I appreciate the help though. – Damon W Sep 03 '19 at 13:42
  • @DamonW The cast of the `drv.Row[i]` is failing. Might be that it returns null (which is not valid as int). – Christiaan Nieuwlaat Sep 03 '19 at 13:43
  • I've added a small fraction of try - catch control flow around the drv.Row[i] cast. You should be able to catch and diagnose the issue using the catch clause. – Christiaan Nieuwlaat Sep 03 '19 at 13:49
  • @Christian Nieuwlaat Thanks for the help. Will post a separate question for the datagrid issues. – Damon W Sep 03 '19 at 14:09