Answered by:
ExecuteNonQuery: Connection property has not been initialized Error

Question
-
laptop spec: XP proPrograms using: ASP.NET Web MatrixI 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!
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
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 5, 2010 9:47 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 =/
-
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
-
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.)
-
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.
-
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.
-
-
-
-
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();
}
-
-
-