I am trying to create a web form that will be used to create new users. The
first step that I am taking is creating a web form that can check the
username against a database to see if it already exists. I would it to do
this on the fly, if possible. When I execute my current code, I get the
following error:
ExecuteNonQuery: Connection property has not been initialized
Below is the code from the page itself:
--
<!-- #INCLUDE FILE="../include/context.inc" -->
<!-- #INCLUDE FILE="../include/db_access.inc" --
<script language="VB" runat="server"
Sub CheckButton_Click(Sender as Object, e as EventArgs)
Dim result As Int32
Dim cmd As OdbcCommand
cmd = new OdbcCommand( "(? = CALL CheckUserExists(?))", db_conn )
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add( "result", OdbcType.Int ).Direction =
ParameterDirection.ReturnValue
cmd.Parameters.Add( "@.userName", OdbcType.VarChar, 100 ).Value =
Request.Form("userName")
cmd.ExecuteNonQuery()
result = cmd.Parameters("result").Value
If result <> 1 Then
CheckResults.Text="<font color=""#ff0000"">Username already
exists!</font>"
Else
CheckResults.Text="<font color=""#009900"">Username is
available.</font>"
End If
end Sub
</script
<html><body>
<form runat="server">
<asp:TextBox id=userName runat="server" />
<asp:Button id=CheckButton runat="server" Text="Check Username"
onClick="CheckButton_Click" /
<p>
<asp:Label id=CheckResults runat=server />
</form>
</body></html>
--
Can anyone see why I might get this error? Here are some more details of
the error:
Line 15: cmd.Parameters.Add( "@.userName", OdbcType.VarChar, 100 ).Value =
Request.Form("userName")
Line 16:
*Line 17: cmd.ExecuteNonQuery()
Line 18: result = cmd.Parameters("result").Value
Thank You,
Jason WilliardHi Jason,
To me it seems that you are still using asp type techniques of data access. You will have to initate your dbconnection object before you can actually use any database related functions.
With your code you are missing ofdb_conn variable. Which I am presuming that you have in include file, but this will not work with asp.net.
HTH|||It's looks like your maye be geeting error because of following reason.
1.Your connection string is not correct or not open as we can't see when and where it was intialize/open .
2.check your store procdure and see if you are passing correct parameter,correct typen etc.
3.In your code it looks likes may be you have open your connection in db_access.inc but i don't think it's good approach .you should open the connection in same sub and close as soon as you you finished.Or if you want to write neat and clean code and also don't want to wire same code agaian then create data layer class where you can perform all the database realted operation.
Arvind Malik|||I made some changes to the page so that the db connection is all done from within the Sub. Below are the code changes that I made:
--
Sub CheckButton_Click(Sender as Object, e as EventArgs)
Dim db_conn_str As String
Dim db_conn As OdbcConnection
Dim resultAs Int32
Dim cmdAs OdbcCommand
Dim context_dsn_nameAs String = "adwarefilter"
db_conn_str = "dsn=" & context_dsn_name & ";"
db_conn = New OdbcConnection( db_conn_str )
db_conn.Open()
cmd = new OdbcCommand( "(? = CALL CheckUserExists(?))", db_conn )
cmd.CommandType = CommandType.StoredProcedure
--
Now I am getting a new error message:
--
Exception Details: System.Data.Odbc.OdbcException: ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '='.
Source Error:
Line 28: cmd.Parameters.Add( "@.username", OdbcType.VarChar, 100 ).Value = Request.Form("userName")
Line 29:
Line 30: cmd.ExecuteNonQuery()
--
Any suggestions?|||What is this query: "(? = CALL CheckUserExists(?))", ? Where is the SQL Query?
Brian|||This is a call to a Stored Procedure. The SQL query is within the Stored Proc.|||I am pretty sure that your stored procedure call should look like this:
cmd = new OdbcCommand( "{CALL CheckUserExists(?)}", db_conn )
Note that I made these changes:
-- removed the question mark (?) and the equal sign (=)
-- changed a set of parentheses to a set of curly braces
But, I have a question -- why oh why are you using the System.Data.Odbc class instead of System.Data.SqlData? Are you using SQL Server 7?
Terri|||Actually, I hadn't noticed you are using a ReturnValue, sorry!! The question mark and equal sign can remain. The problem is likely just the use of parentheses instead of curly braces.
cmd = new OdbcCommand( "{? =CALL CheckUserExists(?)}", db_conn )
Terri
