ADO.Net SQL Server Stored Proc Parameters 1

[TheInsider]

Hi,
I'm coding my first ASP.Net/C# web application. I knew ASP 3 and ADO like the back of my hand, but I'm having problems with ADO.Net. When I execute the code below, I get an error stating "Procedure 'spAddGuest' expects parameter '@UserName', which was not supplied.". I've looked at several examples on the Web and cannot figure out what I'm doing wrong.

		private void saveData()
		{
			SqlConnection connection = new SqlConnection(Application["ConnectionString"].ToString());
			SqlCommand command = new SqlCommand();

			command.Connection = connection;
			command.CommandType = CommandType.StoredProcedure;
			command.CommandText = "spAddGuest";

			SqlParameter parameter;
			parameter = command.Parameters.Add("@GuestID", SqlDbType.BigInt);
			parameter.Direction = ParameterDirection.ReturnValue;
			parameter = command.Parameters.Add("@UserName", SqlDbType.VarChar, 25, txtUserName.ToString().Trim());
			parameter.Direction = ParameterDirection.Input;
			parameter = command.Parameters.Add("@Password", SqlDbType.VarChar, 12, txtPassword2.ToString().Trim());
			parameter.Direction = ParameterDirection.Input;
			parameter = command.Parameters.Add("@FirstName", SqlDbType.VarChar, 15, txtFirstName.ToString().Trim());
			parameter.Direction = ParameterDirection.Input;
			parameter = command.Parameters.Add("@Initials", SqlDbType.Char, 3, txtInitials.ToString().Trim().Length > 0 ? txtInitials.ToString().Trim() : null);
			parameter.Direction = ParameterDirection.Input;
			parameter = command.Parameters.Add("@LastName", SqlDbType.VarChar, 20, txtLastName.ToString().Trim());
			parameter.Direction = ParameterDirection.Input;
			parameter = command.Parameters.Add("@Address", SqlDbType.VarChar, 255, txtAddress.ToString().Trim());
			parameter.Direction = ParameterDirection.Input;
			parameter = command.Parameters.Add("@ZipCode", SqlDbType.Char, 5, txtZipCode.ToString().Trim());
			parameter.Direction = ParameterDirection.Input;
			parameter = command.Parameters.Add("@Telephone", SqlDbType.Char, 10, txtTelephone.ToString().Trim());
			parameter.Direction = ParameterDirection.Input;
			parameter = command.Parameters.Add("@Cellular", SqlDbType.Char, 10, txtCellular.ToString().Trim().Length > 0 ? txtCellular.ToString().Trim() : null);
			parameter.Direction = ParameterDirection.Input;
			parameter = command.Parameters.Add("@Fax", SqlDbType.Char, 10, txtFax.ToString().Trim().Length > 0 ? txtFax.ToString().Trim() : null);
			parameter.Direction = ParameterDirection.Input;
			parameter = command.Parameters.Add("@EMail", SqlDbType.VarChar, 255, txtEMail.ToString().Trim());
			parameter.Direction = ParameterDirection.Input;

			try
			{
				connection.Open();
				command.ExecuteNonQuery();

				if ((long)command.Parameters["@GuestID"].Value != 0)
				{
					Session["GuestID"] = (long)command.Parameters["GuestID"].Value;
				}
			}
			catch(Exception e)
			{
				Response.Write(e.Message);
			}

			command.Dispose();
			connection.Close();
			connection.Dispose();
		}

The stored procedure is in MSDE/SQL Server and returns a BigInt value.

Thanks,

Rob

 

[Craig0201]

Could you post the sql for the SP too, please.

 

[tperri]

Well, what is happening is that you have one paramerter and you keep reassigning it's value, so when you get down to running your stored proc, you have only provided the @Email paramter.

Here is how I write my code for parameters for my Stored Procs. I don't use the SqlParameter type at all, making it a lot easier.

        public static bool UpdateOrderStatus(int iOrderID, int iOrderStatus)
        {
            SqlConnection myConn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["strConn"]);
            SqlCommand myCmd = new SqlCommand("sp_UpdateOrderStatus", myConn);
            myCmd.CommandType = CommandType.StoredProcedure;

            myCmd.Parameters.Add("@OrderID", SqlDbType.Int).Value = iOrderID;
            myCmd.Parameters.Add("@orderstatus", SqlDbType.Int).Value = iOrderStatus;
...... (cut)

 

[Walid]

Tperri,


I really don’t think this is true. Multiple calls was made to the Add() method of the parameters collections, each with different parameter name. The reference to the parameter was retained only to set its direction. Then Add() was called again to add a NEW parameter to the collection, the old reference was then overridden to point to the newly added one for the purpose of setting its direction, an so on.

With multiple calls to command.Parameters.Add(), how can he ends up with one parameter in the collection as you said??!!


Walid Magd (MCP)

The primary challenge of every software development team is to engineer the illusion of simplicity in the face of essential complexity.
-Grady Booch

 

[tperri]

Where are his paramters then? I defintely could be wrong about my assumption, I'm not an expert. In any case, the way I code my paramters never gives me the errors that TheInsider is getting.

 

[Guru7777]

I don't think that TheInsider would have a problem if he didn't have an output parameter. The direction of the first parameter is getting lost by being overwritten. I believe that the @GuestID is getting treated like an incoming parameter.

I suggest that TheInsider change his code more like tperri suggested, or use unique names. Either one will solve the problem.

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

TWljcm8kb2Z0J3MgIzEgRmFuIQ==

 

[TheInsider]

Hi,

Thank you for all of your replies and sorry for taking a while to respond.

tperri stated:

what is happening is that you have one paramerter and you keep reassigning it's value, so when you get down to running your stored proc, you have only provided the @Email paramter.

That thought had crossed my mind, but I was thinking that C# would handle this as Walid stated:

Multiple calls was made to the Add() method of the parameters collections, each with different parameter name. The reference to the parameter was retained only to set its direction. Then Add() was called again to add a NEW parameter to the collection, the old reference was then overridden to point to the newly added one for the purpose of setting its direction, an so on.

I assumed this because C# bears many similarities to Java (which I have more experience with) and this is how Java would handle the reassigning of the reference. Perhaps I would drive most of my programming colleagues crazy, but I hate creating more variables than necessary...especially when the variable is only going to have a usefull lifespan of 2 lines. In retrospect this does seem like a good place to create a procedure called addParameter(), seeing as the code is repetitive enough.

Craig0201 asked to see the stored proc code, so here it is:

CREATE PROCEDURE spAddGuest
	@UserName VARCHAR(25),
	@Password VARCHAR(12),
	@FirstName VARCHAR(15),
	@Initials CHAR(3),
	@LastName VARCHAR(20),
	@Address VARCHAR(255),
	@ZipCode CHAR(5),
	@Telephone CHAR(10),
	@Cellular CHAR(10),
	@Fax CHAR(10),
	@EMail VARCHAR(255)
AS
	DECLARE @Count BIGINT

	SELECT @Count = COUNT(*) FROM tblGuest
	WHERE fldUserName = @UserName

	IF @Count = 0 BEGIN
		INSERT INTO tblGuests (fldUserName, fldPassword, fldFirstName, fldInitials, fldLastName, fldAddress, fldZipCode, fldTelephone, fldCellular, fldFax, fldEMail)
		VALUES (@UserName, @Password, @FirstName, @Initials, @LastName, @Address, @ZipCode, @Telephone, @Cellular, @Fax, @EMail)

		RETURN SCOPE_IDENTITY()
	END
	ELSE BEGIN
		RAISERROR('The user name %s already exists. Please choose another user name.', 16, 1, @UserName)
		RETURN 0
	END
GO

I will try the suggestions that you have made, and will post my results.

Thanks again!

Rob

 

[tperri]

I'm really not sure if I'm totally correct or not, but I don't use that method of reusing a variable because I think I never could get it to work right a long time ago. I abandoned creating SqlParameter variables all together unless I'm retrieving a return value from a stored proc.

 

[Walid]

Using one variable to hold the parameter object, the way you do it, is totally OK. Maybe it is not some one’s favorite way of doing it, but it is not the source of the problem.

The problem in your code is in the way you set the value to the parameter during the call to Add().

Parameters.Add() has 5 overloaded constructors, non of them will allow you to do it the way you did it. The constructor # 5 which takes 4 parameters requires the 4th parameter to be [COLOR=red ]string ColumnSouceName for the parameter not the value of it[/color]. Read the documentation for more info about that.

Your code will work fine if you remove the 4th parameter from Add() and set it in a separate line like this

            parameter = command.Parameters.Add("@UserName", SqlDbType.VarChar, 25 );
            parameter.Direction = ParameterDirection.Input;
            parameter.Value = txtUserName.ToString().Trim();

            parameter = command.Parameters.Add("@Password", SqlDbType.VarChar, 12);
            parameter.Direction = ParameterDirection.Input;
            parameter.Value = txtPassword2.ToString().Trim();
            
            parameter = command.Parameters.Add("@FirstName", SqlDbType.VarChar, 15);
            parameter.Direction = ParameterDirection.Input;
            parameter.Value = txtFirstName.ToString().Trim();

            parameter = command.Parameters.Add("@Initials", SqlDbType.Char, 3);
            parameter.Direction = ParameterDirection.Input;
            parameter.Value = txtInitials.ToString().Trim().Length > 0 ? txtInitials.ToString().Trim() : null;


            parameter = command.Parameters.Add("@LastName", SqlDbType.VarChar, 20);
            parameter.Direction = ParameterDirection.Input;
            parameter.Value = txtLastName.ToString().Trim();
            
            parameter = command.Parameters.Add("@Address", SqlDbType.VarChar, 255);
            parameter.Direction = ParameterDirection.Input;
            parameter.Value = txtAddress.ToString().Trim();

            parameter = command.Parameters.Add("@ZipCode", SqlDbType.Char, 5);
            parameter.Direction = ParameterDirection.Input;
            parameter.Value = txtZipCode.ToString().Trim();

            parameter = command.Parameters.Add("@Telephone", SqlDbType.Char, 10);
            parameter.Direction = ParameterDirection.Input;
            parameter.Value = txtTelephone.ToString().Trim();

            parameter = command.Parameters.Add("@Cellular", SqlDbType.Char, 10);
            parameter.Direction = ParameterDirection.Input;
            parameter.Value = txtCellular.ToString().Trim().Length > 0 ? txtCellular.ToString().Trim() : null;

            parameter = command.Parameters.Add("@Fax", SqlDbType.Char, 10 );
            parameter.Direction = ParameterDirection.Input;
            parameter.Value = txtFax.ToString().Trim().Length > 0 ? txtFax.ToString().Trim() : null;

            parameter = command.Parameters.Add("@EMail", SqlDbType.VarChar, 255);
            parameter.Direction = ParameterDirection.Input;
            parameter.Value = txtEMail.ToString().Trim();

Hope this will help


Walid Magd (MCP)

The primary challenge of every software development team is to engineer the illusion of simplicity in the face of essential complexity.
-Grady Booch

 

[TheInsider]

Great! I'll give that a try tonight. I could see from debugging that the parameters collection was getting populated , so that part didn't seem to be the problem. The "ColumnSouceName" parameter for the Add() method did make me scratch my head initially, but I just assumed that it was the value of the parameter. In the old ADO Command object there was a CreateParameter() method that was very similar, and the value to pass in was the last parameter:

Function CreateParameter([Name As String], [Type As DataTypeEnum = adEmpty], [Direction As ParameterDirectionEnum = adParamInput], [Size As Long], [Value]) As Parameter
    Member of ADODB.Command

My bad . I have a stack of Wrox books on C# and ASP.Net that I'm working on...so my ignorance is temporary ;-)

 

[Walid]

We all have the same problem, temporary ignorance. By the way, I have already tried it by constructing a full set of test objects, tables, stored procedures and C# application, so I am sure it will work. Just a word of advice, stay away from Wrox’s books, full of blah blah blah.
Read ADO.NET by David Sceppa, it is the best on the subject. For C# stick to Andrew Troelsen’s latest edition. No one can explain C# like him. Good luck


Walid Magd (MCP)

The primary challenge of every software development team is to engineer the illusion of simplicity in the face of essential complexity.
-Grady Booch

 

[TheInsider]

The changes you made did the trick, Walid! Thank you to everyone for replying and to Walid for the solution...and I'll definately have a look at those books.

Thanks again,

Rob