Hello friends, I've been trying this for hours and don't know what to do at all. I'm using
Visual Studio 2003, programming in Visual Basic.net side to make an ASP.NET Application. This is what I need help with, I am getting this error:
Exception Details:
System.InvalidOperationException: ExecuteNonQuery: CommandText property has not been initialized
Line 358: Dim strSqlCommandText As String = "UPDATE TT_Users SET UserLanguage = '" & accountSystem.Language & "', YearFilter = '" & dtmYear & _
Line 359: "' WHERE UserName = '" & accountSystem.UserName & "'"
Line 360: SqlHelper.ExecuteNonQuery(Application("connString"), CommandType.Text, strSqlCommandText)
Line 361:
Line 362: ' Redirect browser back to originating page
Here's the piece of code that generates the error:
Public Overloads Shared Function ExecuteNonQuery(ByVal connectionString As String, _
ByVal commandType As CommandType, _
ByVal commandText As String, _
ByVal ParamArray commandParameters() As SqlParameter) As Integer
'create & open a SqlConnection, and dispose of it after we are done.
Dim cn As New SqlConnection(connectionString)
Try
cn.Open()
'call the overload that takes a connection in place of the connection string
Finally
cn.Dispose()
End Try
End Function 'ExecuteNonQuery
I try setting a debug point before and after the call of the function to examine the contents of the variable
strSqlCommandText that I pass to the function and then
the parameter commandText. This are the results:
Before: In the assignment
strSqlCommandText = "UPDATE TT_Users SET UserLanguage = 'EN', YearFilter = 'May, 2005' WHERE UserName = 'jreguero'"
After: In the function
ExecuteNonQuery
commandText = Nothing
I just want to do this without the use of an Store Procedure. That's why I pass commandType.Text and not commandType.StoredProcedure, I MEAN THIS
ExecuteNonQuery(connString, CommandType.Text, "SELECT * FROM TABLE WHERE ...")
AND NOT THIS
ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24))
Could any help me urgent?
Feel free to offer alternate functions or modifications to the actual if there is a better way to do this.
David I can't understand what you are you trying to say me? I need the kind of solution that tell me what happens.
First I got a codebehind page that contain amount other stuff an OnClick event. In this place i call the famous function ExecuteNonQuery and as result i obtain this error.
I dont want to create a Store Procedure any time I need to Query the SQL Server. What i need is could pass a query string in line, like this,
SqlHelper.ExecuteNonQuery(Application("connString"), CommandType.Text, "SELECT * FROM ...bla bla bla")
and not like this where you can see i pass the name of my SP and besides an array of parameters
I guess I'm also confused on what you're trying to do. David isn't telling you to use a store procedure. He is just telling you how to use to assign a parameterized query to command object. Isn't this what you want to do? For your purposes, can't you just
dynamically build your SQL statement, assign it to a string object and then use that string object to instantiate your command object?
The method I proposed was a generic proposal - - use the structure to get what you want -- when you get the message that the command has no been properly initialized - that means it's not getting the SQL statement OR sProc you are intending.
This method also can use a sProc or inline SQL - - either way - - it's the structure I was presenting to you to see what needs to be done to display your data without error.
SqlHelper is a sealed class (can't be inherited) with a private constructor so that it can't be instantiated. The entire interface to SqlHelper is through static member functions. Those functions provide 100% of the data access
needs of most applications.
ExecuteNonQuery Executes a SQL command that doesn't return a result set. This could be a stored procedure that doesn't return anything, an INSERT, UPDATE, or DELETE, or even a table modification command.
The ExecuteNonQuery method has the following overloads: (I pretend to use the second)
int ExecuteNonQuery (ConnectionString, CommandType, CommandText)
int ExecuteNonQuery (ConnectionString, CommandType, CommandText, Parameters)
int ExecuteNonQuery (ConnectionString, spName, Parameters)
int ExecuteNonQuery (SqlConnection, CommandType, CommandText)
int ExecuteNonQuery (SqlConnection, CommandType, CommandText, Parameters)
int ExecuteNonQuery (SqlConnection, spName, Parameters)
int ExecuteNonQuery (SqlTransaction, CommandType, CommandText)
int ExecuteNonQuery (SqlTransaction, CommandType, CommandText, Parameters)
int ExecuteNonQuery (SqlTransaction, spName, Parameters)
And below is the code of the ExecuteNonQuery Method
My question still is this, and let me say that I founded the perfect Forum for this question "Discuss the MS Data Access Application
Block", but i want to try with this too:
Maybe this can help to analize the situation: ..."These overloads allow you to supply the connection as a connection string, a
SqlConnection object, or as a SqlTransaction object. The command can be a stored procedure or an ad-hoc SQL query, with or without parameters"
Public Overloads Shared Function ExecuteNonQuery(ByVal connectionString As String, _
ByVal commandType As CommandType, _
ByVal commandText As String, _
ByVal ParamArray commandParameters() As SqlParameter) As Integer
'create & open a SqlConnection, and dispose of it after we are done.
Dim cn As New SqlConnection(connectionString)
Try
cn.Open()
'call the overload that takes a connection in place of the connection string
Return ExecuteNonQuery(cn, commandType, commandText, commandParameters)
Finally
cn.Dispose()
End Try
End Function 'ExecuteNonQuery
-------------------------------------------------------------------------------
Public Overloads Shared Function ExecuteNonQuery(ByVal connectionString As String, _
ByVal spName As String, _
ByVal ParamArray parameterValues() As Object) As Integer
Dim commandParameters As SqlParameter()
'if we receive parameter values, we need to figure out where they go
If Not (parameterValues Is Nothing) And parameterValues.Length > 0 Then
'pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
'assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues)
'call the overload that takes an array of SqlParameters
Return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters)
'otherwise we can just call the SP without params
Else
Return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName)
End If
End Function 'ExecuteNonQuery
----------------------------------------------------------------------------------
Public Overloads Shared Function ExecuteNonQuery(ByVal connection As SqlConnection, _
ByVal commandType As CommandType, _
ByVal commandText As String, _
ByVal ParamArray commandParameters() As SqlParameter) As Integer
'create a command and prepare it for execution
Dim cmd As New SqlCommand
Dim retval As Integer
jreguero
Member
20 Points
4 Posts
ExecuteNonQuery: CommandText property has not been initialized
May 10, 2005 06:53 PM|LINK
Hello friends, I've been trying this for hours and don't know what to do at all. I'm using
Visual Studio 2003, programming in Visual Basic.net side to make an ASP.NET Application. This is what I need help with, I am getting this error:
********************************************************
Exception Details: System.InvalidOperationException: ExecuteNonQuery: CommandText property has not been initialized
Line 358: Dim strSqlCommandText As String = "UPDATE TT_Users SET UserLanguage = '" & accountSystem.Language & "', YearFilter = '" & dtmYear & _
Line 359: "' WHERE UserName = '" & accountSystem.UserName & "'"
Line 360: SqlHelper.ExecuteNonQuery(Application("connString"), CommandType.Text, strSqlCommandText)
Line 361:
Line 362: ' Redirect browser back to originating page
Source File: c:\inetpub\wwwroot\cowowi\LogonForm.ascx.vb Line: 360
********************************************************
Here's the piece of code that generates the error:
Public Overloads Shared Function ExecuteNonQuery(ByVal connectionString As String, _
ByVal commandType As CommandType, _
ByVal commandText As String, _
ByVal ParamArray commandParameters() As SqlParameter) As Integer
'create & open a SqlConnection, and dispose of it after we are done.
Dim cn As New SqlConnection(connectionString)
Try
cn.Open()
'call the overload that takes a connection in place of the connection string
Return ExecuteNonQuery(cn, commandType, commandText, commandParameters)
Finally
cn.Dispose()
End Try
End Function 'ExecuteNonQuery
I try setting a debug point before and after the call of the function to examine the contents of the variable strSqlCommandText that I pass to the function and then the parameter commandText. This are the results:
Before: In the assignment
strSqlCommandText = "UPDATE TT_Users SET UserLanguage = 'EN', YearFilter = 'May, 2005' WHERE UserName = 'jreguero'"
After: In the function ExecuteNonQuery
commandText = Nothing
I just want to do this without the use of an Store Procedure. That's why I pass commandType.Text and not commandType.StoredProcedure, I MEAN THIS
ExecuteNonQuery(connString, CommandType.Text, "SELECT * FROM TABLE WHERE ...")
AND NOT THIS
ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24))
Could any help me urgent?
Feel free to offer alternate functions or modifications to the actual if there is a better way to do this.
Regards
Julio Antonio Reguero
augustwind
All-Star
35860 Points
4900 Posts
ASPInsiders
Moderator
Re: ExecuteNonQuery: CommandText property has not been initialized
May 10, 2005 07:41 PM|LINK
one alternate way would be to separate your items a little:
Dim strConn as String = "server=...."
Dim MySQL as string = "..."
Dim MyConn as New SQLConnection(strConn)
Dim Cmd as New SQLCommand(MySQL, MyConn)
'add all your parameters here
MyConn.Open()
cmd.ExecuteNonQuery()
All Things Dot Net
Stored Procs and Code in a Flash!
ASP.Net Sitemap Creator
jreguero
Member
20 Points
4 Posts
Re: ExecuteNonQuery: CommandText property has not been initialized
May 10, 2005 08:46 PM|LINK
David I can't understand what you are you trying to say me? I need the kind of solution that tell me what happens.
First I got a codebehind page that contain amount other stuff an OnClick event. In this place i call the famous function ExecuteNonQuery and as result i obtain this error.
I dont want to create a Store Procedure any time I need to Query the SQL Server. What i need is could pass a query string in line, like this,
SqlHelper.ExecuteNonQuery(Application("connString"), CommandType.Text, "SELECT * FROM ...bla bla bla")
and not like this where you can see i pass the name of my SP and besides an array of parameters
userName =
CStr(SqlHelper.ExecuteScalar(Current.Application("connString"), "TT_UserLogin", user, password, company))Did you heard before of SQLHelper class? I want to know how to use all his methods, maybe someone has samples of this ....
pcdanno
Participant
1932 Points
399 Posts
Re: ExecuteNonQuery: CommandText property has not been initialized
May 11, 2005 01:14 PM|LINK
augustwind
All-Star
35860 Points
4900 Posts
ASPInsiders
Moderator
Re: ExecuteNonQuery: CommandText property has not been initialized
May 11, 2005 01:38 PM|LINK
The method I proposed was a generic proposal - - use the structure to get what you want -- when you get the message that the command has no been properly initialized - that means it's not getting the SQL statement OR sProc you are intending.
This method also can use a sProc or inline SQL - - either way - - it's the structure I was presenting to you to see what needs to be done to display your data without error.
All Things Dot Net
Stored Procs and Code in a Flash!
ASP.Net Sitemap Creator
jreguero
Member
20 Points
4 Posts
Re: ExecuteNonQuery: CommandText property has not been initialized
May 11, 2005 02:57 PM|LINK
SqlHelper is a sealed class (can't be inherited) with a private constructor so that it can't be instantiated. The entire interface to SqlHelper is through static member functions. Those functions provide 100% of the data access needs of most applications.
ExecuteNonQuery Executes a SQL command that doesn't return a result set. This could be a stored procedure that doesn't return anything, an INSERT, UPDATE, or DELETE, or even a table modification command.
The ExecuteNonQuery method has the following overloads: (I pretend to use the second)
int ExecuteNonQuery (ConnectionString, CommandType, CommandText)
int ExecuteNonQuery (ConnectionString, CommandType, CommandText, Parameters)
int ExecuteNonQuery (ConnectionString, spName, Parameters)
int ExecuteNonQuery (SqlConnection, CommandType, CommandText)
int ExecuteNonQuery (SqlConnection, CommandType, CommandText, Parameters)
int ExecuteNonQuery (SqlConnection, spName, Parameters)
int ExecuteNonQuery (SqlTransaction, CommandType, CommandText)
int ExecuteNonQuery (SqlTransaction, CommandType, CommandText, Parameters)
int ExecuteNonQuery (SqlTransaction, spName, Parameters)
And below is the code of the ExecuteNonQuery Method
My question still is this, and let me say that I founded the perfect Forum for this question "Discuss the MS Data Access Application Block", but i want to try with this too:
Can I use the method in this way?
Application("connString")=ConfigurationSettings.AppSettings(Web.Global.CfgKeyConnString)
Dim
strSql As String = "UPDATE TT_Users SET UserLanguage = 'EN'"SqlHelper.ExecuteNonQuery(Application("connString"), CommandType.Text, strSql)
Maybe this can help to analize the situation: ..."These overloads allow you to supply the connection as a connection string, a SqlConnection object, or as a SqlTransaction object. The command can be a stored procedure or an ad-hoc SQL query, with or without parameters"
Thank you anyway for your comments
Julio Antonio
-----------------------------------------------------------------------------
Public Overloads Shared Function ExecuteNonQuery(ByVal connectionString As String, _
ByVal commandType As CommandType, _
ByVal commandText As String, _
ByVal ParamArray commandParameters() As SqlParameter) As Integer
'create & open a SqlConnection, and dispose of it after we are done.
Dim cn As New SqlConnection(connectionString)
Try
cn.Open()
'call the overload that takes a connection in place of the connection string
Return ExecuteNonQuery(cn, commandType, commandText, commandParameters)
Finally
cn.Dispose()
End Try
End Function 'ExecuteNonQuery
-------------------------------------------------------------------------------
Public Overloads Shared Function ExecuteNonQuery(ByVal connectionString As String, _
ByVal spName As String, _
ByVal ParamArray parameterValues() As Object) As Integer
Dim commandParameters As SqlParameter()
'if we receive parameter values, we need to figure out where they go
If Not (parameterValues Is Nothing) And parameterValues.Length > 0 Then
'pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName)
'assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues)
'call the overload that takes an array of SqlParameters
Return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters)
'otherwise we can just call the SP without params
Else
Return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName)
End If
End Function 'ExecuteNonQuery
----------------------------------------------------------------------------------
Public Overloads Shared Function ExecuteNonQuery(ByVal connection As SqlConnection, _
ByVal commandType As CommandType, _
ByVal commandText As String, _
ByVal ParamArray commandParameters() As SqlParameter) As Integer
'create a command and prepare it for execution
Dim cmd As New SqlCommand
Dim retval As Integer
PrepareCommand(cmd, connection, CType(Nothing, SqlTransaction), commandType, commandText, commandParameters)
'finally, execute the command.
retval = cmd.ExecuteNonQuery()
'detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear()
Return retval
End Function 'ExecuteNonQuery
----------------------------------------------------------------------------------