ExecuteNonQuery: Connection property has not been initialized Error

Question

laptop spec: XP pro

Programs using: ASP.NET Web Matrix

 

 

I am trying to create a webpage using ASP.NET Web Matrix that will accept 2 fields and then upload into the database.  This a portion of code I have in C#:

 

<%@ Page Language="C#" Debug="true" %>

<%@ import Namespace="System.IO" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">

 

    void Upload(Object s, EventArgs e) {
        SqlCommand dataCommand = new SqlCommand(
            "INTERT INTO SongDetails (SongTitle, SongArtist, Song" +
            "VALUES (@SongTitle, @SongArtist)");
   
        dataCommand.Parameters.Add("@SongTitle", txtSongTitle.Text);
        dataCommand.Parameters.Add("@SongArtist", txtSongArtist.Text);
   
        SqlConnection dataConnection = new SqlConnection();
       
       
        dataConnection.ConnectionString = "User ID=sa;Password=turtles;Initial Catalog=RUPit;Data Source=localhost";
   
   
        dataConnection.Open();
        dataCommand.ExecuteNonQuery();
        dataConnection.Close();
   
        }

When I attempt to execute, I will be able to enter data into text boxes.  When I hit the "upload" button to insert the data into the SQL database, I get the following error:

 

ExecuteNonQuery: Connection property has not been initialized.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: ExecuteNonQuery: Connection property has not been initialized.

Source Error:

Line 20: Line 21: dataConnection.Open(); Line 22: dataCommand.ExecuteNonQuery(); Line 23: dataConnection.Close(); Line 24:

Source File: c:\Documents and Settings\Stephanie\My Documents\UploadTest.aspx    Line: 22

Stack Trace:

[InvalidOperationException: ExecuteNonQuery: Connection property has not been initialized.] System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async) +291 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +72 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +117 ASP.uploadtest_aspx.Upload(Object s, EventArgs e) in c:\Documents and Settings\Stephanie\My Documents\UploadTest.aspx:22 System.Web.UI.HtmlControls.HtmlInputButton.OnServerClick(EventArgs e) +75 System.Web.UI.HtmlControls.HtmlInputButton.RaisePostBackEvent(String eventArgument) +97 System.Web.UI.HtmlControls.HtmlInputButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +172 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4919

 

Can anyone help me solve this?  Thanks!

Answer 1

You've opened up a SqlConnection, but you haven't told the SqlCommand object to use it. Try adding this line:

 

dataCommand.Connection = dataConnection;

 

before you execute your query.

 

You could also use the overloaded SqlCommand constructor:

 

SqlCommand dataCommand = new SqlCommand(sql, dataConnection);

 

You would of course have to move your dataConnection instantiation logic before your dataCommand constructor, but you wouldn't have to open the connection until you were ready to execute the command.

Answer 2

I've tried putting in dataCommand.Connection = dataConnection; before the query like so:

void upload(Object s, EventArgs e) {

SqlConnection dataConnection = new SqlConnection();
    dataConnection.ConnectionString = "User ID=sa;Password=turtles;Initial Catalog=RUPit;Data Source=localhost";

    SqlCommand dataCommand = new SqlCommand(
   
   
   
        "INSERT INTO SongDetails (SongTitle, SongArtist, Song" +
        "VALUES (@SongTitle, @SongArtist)", dataConnection);

    dataCommand.Parameters.Add("@SongTitle", txtSongTitle.Text);
    dataCommand.Parameters.Add("@SongArtist", txtSongArtist.Text);

    dataConnection.Open();
    dataCommand.Connection = dataConnection;
    dataCommand.ExecuteNonQuery();
    dataConnection.Close();

but I still get the same error about the ExecutionNonQuery =/

 

Answer 3

I think it should work ideally, but try a different version that is a little bit easier to read and properly disposes of any unmanaged resources in case exceptions are thrown:

 

string connectionString = "...Your Connection String...";

using (SqlConnection dataConnection = new SqlConnection(connectionString))
{
    using (SqlCommand dataCommand = dataConnection.CreateCommand())
    {
        dataCommand.CommandText = "INSERT INTO SongDetails (SongTitle, SongArtist) " +
            "VALUES (@SongTitle, @SongArtist)";

        dataCommand.Parameters.Add("@SongTitle", txtSongTitle.Text);
        dataCommand.Parameters.Add("@SongArtist", txtSongArtist.Text);

        dataConnection.Open();
        dataCommand.ExecuteNonQuery();
        dataConnection.Close();
    }
}

 

Here I have used the connection object to create the command via

dataConnection.CreateCommand()

so we know the connection is properly initialized.

If you are using this in .NET 2.0, dataCommand.Parameters.Add should now be dataCommand.Parameters.AddWithValue, but that won't cause it not to work.

You may also want to be careful about displaying your ID and Passwords in your connection strings in forum posts.

I hope this helps.

Regards,

Dave

 

Answer 4

That should work, although you're duplicating the assignment of the connection string. Although it doesn't matter, you should either use the overloaded constructor (my personal preference) or set the Connection property. David's recommendation regarding enclosing your statements in a using statement is also good as it frees connections quicker in the event of an exception. (Otherwise you have to wait for the GC to realize that your SqlConnection object is unreachable, place it on the finalization queue, and wait for the finalizer thread to release the connection back to the connection pool.)

Answer 5

I think you miss one parameter which is SQL Connection on SqlCommand define.

You should define data connection first.

 

SqlConnection dataConnection = new SqlConnection();
dataConnection.ConnectionString = "User ID=sa;Password=turtles;Initial Catalog=RUPit;Data Source=localhost";

 

SqlCommand dataCommand = new SqlCommand(
        "INSERT INTO SongDetails (SongTitle, SongArtist, Song" +
        "VALUES (@SongTitle, @SongArtist)", this.dataConnection);

    dataCommand.Parameters.Add("@SongTitle", txtSongTitle.Text);
    dataCommand.Parameters.Add("@SongArtist", txtSongArtist.Text);

 

 

Good luck friend.

Answer 6

Excuseme boys, my english is very bad.

I make one class to create a conection to my database and every error are fixed.

--------------------------------------------------------------------------------------------------------------

namespace MyConexion
{   
    class ClsConexion
    {
        SqlConnection  Coneccion;
        public string Source;


        public SqlConnection Conexion()
        {
            Source = "Data Source = (local); Initial Catalog = Mammals;" +
                        "Integrated Security=SSPI";

            this.Coneccion = new SqlConnection(Source);

            return Coneccion;
       
        }

        public void Open()
        {

          

            try
            {
                if (Coneccion.State != ConnectionState.Open)
                {
                 Coneccion.Open();
                }
            }

            catch(Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.ReadKey();
            }
        }

        public void Close()
        {
            try
            {
                if (Coneccion.State != ConnectionState.Closed)
                {
                    Coneccion.Close();
                }
            }
            catch(Exception ex)
            {
           
                Console.WriteLine(ex.Message);
                Console.ReadKey();
            }

        }

    }
}

 

and this is my Operation to the database in other class.

----------------------------------------------------------------------------------------------------------------

namespace MyConexion
{
    class ClsPrincipal
    {
        static void Main()
        {
            ClsConexion Connect = new ClsConexion();

            Console.Write(" Inserte el nombre de su mascota. ");


            string Query = "Insert into Mammals(ID,Nombre,Tipo) Values(2, 'Ponky', 'Canino')";

            SqlCommand Command = new SqlCommand(Query, Connect.Conexion());

            Connect.Open();
            Command.ExecuteNonQuery();
            GC.Collect();
        }
     }
}

 

God Bless you................................... God is the source of the wisdom, follow him.

Answer 7

Check your Insert command. This is what you have: ...."INTERT INTO SongDetails (SongTitle, SongArtist, Song" +
            "VALUES (@SongTitle, @SongArtist)");"

 

It should be "INSERT", not "INTERT"

 

Best regards,

 

Tran Pham

Answer 8

the content provided by you has been really very helpful. though i got it after a long search m glad .
thanx
regards vishesh nigam ~~~

Answer 9

thanks Carlos..this s the code im searchin for..super..

Answer 10

You forgot to write connection object inside the Insert Statement , I have put dataConnection in insert statement and underlined it.

 void Upload(Object s, EventArgs e)

{

 dataConnection.ConnectionString = "User ID=sa;Password=turtles;Initial Catalog=RUPit;Data Source=localhost";    

SqlConnection dataConnection = new SqlConnection();

                                       
        SqlCommand dataCommand = new SqlCommand(
            "INTERT INTO SongDetails (SongTitle, SongArtist, Song" +
            "VALUES (@SongTitle, @SongArtist)",dataConnection);

        dataCommand.Parameters.Add("@SongTitle", txtSongTitle.Text);
        dataCommand.Parameters.Add("@SongArtist", txtSongArtist.Text);







        dataConnection.Open();
        dataCommand.ExecuteNonQuery();
        dataConnection.Close();

        }

Answer 11

this code is worked but i don't see any difference between this code and first code!!! why is just one of them worked?

Answer 12

Thanks. It worked for me. I opening a connection and creating a command but not telling the command which connection to use. Thanks again.