Executenonquery: commandtext property has not been initialized'

Question

1.00/5 (1 vote)

See more:

hi! im having a problem with my project, it was working in the morning and then now (evening) its not and i dont where the problem is. here are my codes and i hope you guys could help me with this. the problem says "

System.InvalidOperationException: 'ExecuteNonQuery: CommandText property has not been initialized'

private void submitBtn_Click(object sender, EventArgs e)
        {
            con.Open();

            string a = "Accept";
            string b = "Reject";
            string queryUpdate1 = "";
            string queryUpdate2 = "";

            if (accptBtn.Checked)
            {
                if(type_rdonly.Text == "SL")
                {
                    if (ifEmployeeExist(con, emptime_rdonly.Text))
                    {
                        queryUpdate1 = @"UPDATE [LEAVE_EMP] SET EMP_STATUS ='" + a + "'WHERE [EMP_TIME] ='" + emptime_rdonly.Text + "'";
                    }
                        queryUpdate2 = string.Format("UPDATE LEAVE_ADMIN SET L_SPENT_SL = (L_SPENT_SL + 1), L_REM_SL = (L_REM_SL - 1)");
                }
                if (type_rdonly.Text == "VL")
                {
                    if (ifEmployeeExist(con, emptime_rdonly.Text))
                    {
                        queryUpdate1 = @"UPDATE [LEAVE_EMP] SET EMP_STATUS ='" + a + "'WHERE [EMP_TIME] ='" + emptime_rdonly.Text + "'";
                    }
                    queryUpdate2 = string.Format("UPDATE LEAVE_ADMIN SET L_SPENT_VL = (L_SPENT_VL + 1),L_REM_VL = (L_REM_VL - 1)");
                }
                SqlCommand cmd1 = new SqlCommand(queryUpdate1, con);
                SqlCommand cmd2 = new SqlCommand(queryUpdate2, con);
                cmd1.ExecuteNonQuery();
                cmd2.ExecuteNonQuery();

What I have tried:

i checked my other back up and its the same.

Posted 22-Jan-19 4:10am

Pauline Agawin

Updated 22-Jan-19 4:47am

Add a Solution

Comments

F-ES Sitecore 22-Jan-19 10:22am

Use the debugger to step through the code to see why your command text isn't being set. It's likely to be something to do with your inputs not being what you expect, but we don't know what those inputs are.

Pauline Agawin 22-Jan-19 10:30am

hi! can we talk through email so i can show you my inputs? the error says

cmd 1 = system.data.sqlclient.sqlcommand
cmd 2 = system.data.sqlclient.sqlcommand
con = system.data.sqlclient.sqlcommand
queryUpdate2 = "UPDATE [LEAVE_ADMIN] SET L_SPENT_SL = (L_SPENT_SL + 1), L_REM_SL = (L_REM_SL - 1)"

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2019-01-22T04:27:00

There is a path through your code such that either or both of queryUpdate1and queryUpdate2 can still be the empty string after the if conditions: Specifically if typerdonly.Text is not "SL" or "VL", or if one of the inner if conditions fails.

You will need to either use the debugger to find out exactly what is happening, or add logging code to record exactly what is happened for analysis after the problem occurs.

You can then look back in your code to find out why values are not as you expect.

Sorry, but we can't do any of that for you!

Oh, and do yourself a favour: Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

SQL

DROP TABLE MyTable;

A perfectly valid "delete the table" command

SQL

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

MadMyche · Answer 2 · 2019-01-22T04:47:00

First things first... Your code is vulnerable to SQL Injection attacks. This is because you are concantenating constant strings and end-user inputs. This has been a known vulnerability for over 20 years and is still in the top 10 active website hack methods still.
The way to fix this is going to use Parameters for the queries. Besides eliminating the vulnerability, this will also auto-type the parameters based on the variable type assigned- if you give it text it will take care of the single quotes, or other identifiers as needed by data type or connected database type. The downside is that if you have a number going in you need to make sure it is of the proper type, so if it is coming from a text box make sure it is conveted to the appropriate type.
Only a few lines need to be changed in your current code

C#

if(type_rdonly.Text == "SL")
{
  if (ifEmployeeExist(con, emptime_rdonly.Text))
  {
    // queryUpdate1 = @"UPDATE [LEAVE_EMP] SET EMP_STATUS ='" + a + "'WHERE [EMP_TIME] ='" + emptime_rdonly.Text + "'";
    queryUpdate1 = @"UPDATE [LEAVE_EMP] SET EMP_STATUS = @EmpStatus WHERE [EMP_TIME] = @EmpTime";
  }
  queryUpdate2 = "UPDATE LEAVE_ADMIN SET L_SPENT_SL = (L_SPENT_SL + 1), L_REM_SL = (L_REM_SL - 1)";
}
if (type_rdonly.Text == "VL")
{
   if (ifEmployeeExist(con, emptime_rdonly.Text))
   {
     // queryUpdate1 = @"UPDATE [LEAVE_EMP] SET EMP_STATUS ='" + a + "'WHERE [EMP_TIME] ='" + emptime_rdonly.Text + "'";
     queryUpdate1 = @"UPDATE [LEAVE_EMP] SET EMP_STATUS = @EmpStatus WHERE [EMP_TIME] = @EmpTime ";
   }
   queryUpdate2 = "UPDATE LEAVE_ADMIN SET L_SPENT_VL = (L_SPENT_VL + 1),L_REM_VL = (L_REM_VL - 1)";
}

SqlCommand cmd1 = new SqlCommand(queryUpdate1, con);
// new lines for parameters
cmd1.Parameters.AddWithValue("@EmpStatus", 1);
cmd1.Parameters.AddWithValue("@EmpTime", emptime_rdonly.Text);

When changing over to parameters I noticed a lack of a space between the single-quotes and the WHERE clause in Query #1
I also edited Query #2 as it did not need to be wrapped in string.Format()

Try this out and if you are still haveing problems, please try to troubleshoot and determine which command is failing; makes it easier to get a resolution

Executenonquery: commandtext property has not been initialized'

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0