executenonquery connection property has not been initialized. in asp.net

Question

0.00/5 (No votes)

See more:

Code-

C#

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString);
con.Open();
      
SqlCommand cmd= new SqlCommand("INSERT INTO Employee (Dept_Name, Dept_ID, Emp_Name) SELECT Dept_ID, Dept_Name FROM Department WHERE Dept_Name = ddlDeptName.SelectedValue ");
cmd.Parameters.AddWithValue("Emp_Name", txtName.Text.Trim());
cmd.Parameters.AddWithValue("Dept_Name", ddlDeptName.SelectedValue);
cmd.ExecuteNonQuery();
con.Close();

Connection String-

XML

<connectionstrings>
  <add name="constr">
       connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=c:\users\rohit kumar\documents\visual studio 2013\Projects\Data Grid1\Data Grid1\App_Data\Employee.mdf;Integrated Security=True" 
       providerName="System.Data.SqlClient"/&gt;
</add></connectionstrings>

Posted 24-Aug-15 18:06pm

Rohitk2409

Updated 24-Aug-15 18:12pm

Wendelius

v2

Add a Solution

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)

Wendelius · Accepted Answer · 2015-08-24T18:14:00

You haven't set the connection for the command. Add the following line to your code

C#

cmd.Connection = con;

Or define the connection when creating the command

C#

SqlCommand cmd= new SqlCommand("INSERT INTO Employee (Dept_Name, Dept_ID, Emp_Name) SELECT Dept_ID, Dept_Name FROM Department WHERE Dept_Name = ddlDeptName.SelectedValue ", con);

As a side note the command doesn't look right. If you need to use values from a drop down list, you need to define them as parameters.

On the other hand, the values for Dept_Name, Dept_ID come from the SELECT statement so those aren't needed as parameters.

It also seems that you have a mismatch with column count. Insert defines 3 columns but the select provide only 2

DamithSL · Accepted Answer · 2015-08-24T18:15:00

Solution 2

you need to specify which connection to use in sql command.

C#

SqlCommand cmd= new SqlCommand("INSERT INTO Employee (Dept_Name, Dept_ID, Emp_Name) SELECT Dept_ID, Dept_Name FROM Department WHERE Dept_Name = ddlDeptName.SelectedValue ", con);

and also you haven't include parameters in your sql statement, it should be like below

SQL

INSERT INTO Employee (Dept_ID,Dept_Name) SELECT Dept_ID, Dept_Name FROM Department WHERE Dept_Name = @Dept_Name

since you have one parameter, you can set it as below

C#

cmd.Parameters.AddWithValue("@Dept_Name", ddlDeptName.SelectedValue);

Posted 24-Aug-15 18:15pm

DamithSL

Updated 24-Aug-15 18:27pm

v3

Comments

Wendelius 25-Aug-15 0:24am

Exactly, 5.

DamithSL 25-Aug-15 0:30am

Thank you, Mika :)

Rohitk2409 25-Aug-15 0:40am

it again showing me the error message, i tried updated code

Must declare the scalar variable "@Dept_Name".

DamithSL 25-Aug-15 0:41am

what is the sql statement you have used?

Rohitk2409 25-Aug-15 0:45am

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString);
con.Open();
SqlCommand cmd = new SqlCommand("Insert into Employee values(@Emp_Name)", con);
SqlCommand cmd1= new SqlCommand("INSERT INTO Employee (Dept_Name, Dept_ID) SELECT Dept_ID, Dept_Name FROM Department WHERE Dept_Name = @Dept_Name", con);
cmd1.Parameters.AddWithValue("Emp_Name", txtName.Text.Trim());
cmd1.Parameters.AddWithValue("Dept_Name", ddlDeptName.SelectedValue);
cmd.ExecuteNonQuery();
cmd1.ExecuteNonQuery();
con.Close();

DamithSL 25-Aug-15 0:50am

try using single command with sql statement as below
SqlCommand cmd = new SqlCommand(INSERT INTO Employee (Dept_ID,Dept_Name,Emp_Name) SELECT Dept_ID, Dept_Name, @Emp_Name FROM Department WHERE Dept_Name = @Dept_Name);

now set both parameters to cmd
cmd.Parameters.AddWithValue("@Emp_Name", txtName.Text.Trim());
cmd.Parameters.AddWithValue("@Dept_Name", ddlDeptName.SelectedValue);
cmd.ExecuteNonQuery();

remove cmd1 related code.

Rohitk2409 25-Aug-15 0:57am

Thank You,
no error but data is not saved in database

DamithSL 25-Aug-15 1:03am

may be ddlDeptName.SelectedValue contains some other value, debug and check.
it all depend which column you bind as value member of ddlDeptName

Rohitk2409 25-Aug-15 1:11am

i debug, value is taking correctly but still not saving in database