none
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!
    Thursday, February 02, 2006 12:07 AM

Answers

  • 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

     

    Thursday, February 02, 2006 3:20 AM

All replies

  • 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.
    • Proposed as answer by Gi65 Monday, July 05, 2010 9:47 AM
    Thursday, February 02, 2006 12:24 AM
  • 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 =/

     

    Thursday, February 02, 2006 1:39 AM
  • 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

     

    Thursday, February 02, 2006 3:20 AM
  • 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.)
    Thursday, February 02, 2006 3:32 AM
  • 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.

    Monday, June 02, 2008 7:23 AM
  • 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.

    Friday, June 04, 2010 4:20 PM
  • 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

    Tuesday, December 07, 2010 7:47 PM
  • the content provided by you has been really very helpful. though i got it after a long search m glad .
    thanx
    regards vishesh nigam ~~~

    Tuesday, July 12, 2011 10:35 AM
  • thanks Carlos..this s the code im searchin for..super..
    Friday, March 23, 2012 1:30 PM
  • 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();

            }

    Thursday, April 19, 2012 10:23 AM
  • this code is worked but i don't see any difference between this code and first code!!! why is just one of them worked?
    Thursday, May 24, 2012 2:34 PM
  • Thanks. It worked for me. I opening a connection and creating a command but not telling the command which connection to use. Thanks again.
    Saturday, September 14, 2013 3:26 AM