Updating Database using a Stored procedure

[Guest]

I am trying to do a simple update of a database using a stored procedure. I
have get the following error when executing the code:

"Procedure or function UpdateDetails has too many arguments specified."

Here is my SP:

ALTER PROCEDURE dbo.UpdateDetails
@KeyID int

AS
SET NOCOUNT ON;
SELECT dbo.Events.KeyID, dbo.Events.AvailableSeats,
dbo.Reservations.KeyID AS ReserveID, dbo.Reservations.EventID,
dbo.Reservations.Status, dbo.Reservations.AuthStatus,
dbo.Reservations.AuthCode,
dbo.Reservations.AuthMessage
FROM dbo.Events INNER JOIN
dbo.Reservations ON dbo.Events.KeyID =
dbo.Reservations.EventID
WHERE (dbo.Reservations.KeyID = @KeyID)

Here is the executing code:

this.sqlUpdateCommand1.CommandText = "UpdateDetails";
this.sqlUpdateCommand1.Connection = this.sqlConnection1;
this.sqlUpdateCommand1.CommandType =
System.Data.CommandType.StoredProcedure;
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@KeyID", System.Data.SqlDbType.Int, 4,
System.Data.ParameterDirection.ReturnValue, false, ((System.Byte)(0)),
((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));

this.sqlUpdateCommand1.Parameters.Add("@KeyID",
System.Data.SqlDbType.NVarChar).Value = ReserveID;
this.sqlUpdateCommand1.Parameters.Add("@AuthStatus",
System.Data.SqlDbType.NVarChar, 50, "AuthStatus").Value = "ads";
this.sqlUpdateCommand1.Parameters.Add("@AuthCode",
System.Data.SqlDbType.NVarChar, 50, "AuthCode").Value = "asdf";
this.sqlUpdateCommand1.Parameters.Add("@AuthMessage",
System.Data.SqlDbType.NVarChar, 50, "AuthMessage").Value = "asdf";
this.sqlUpdateCommand1.Parameters.Add("@Status",
System.Data.SqlDbType.VarChar, 10, "Status").Value = "asdf";
this.sqlUpdateCommand1.Parameters.Add("@AvailableSeats",
System.Data.SqlDbType.Int, 4, "AvailableSeats").Value = int.Parse("123");
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_Status",
System.Data.SqlDbType.Char, 10, System.Data.ParameterDirection.Input, false,
((System.Byte)(0)), ((System.Byte)(0)), "Status",
System.Data.DataRowVersion.Original, null));

sqlUpdateCommand1.Connection.Open();
sqlUpdateCommand1.ExecuteNonQuery();
sqlUpdateCommand1.Connection.Close();

Please suggest any articles that my help out.

Thanks, Justin.

 

[bruce barker]

your sp only takes one parameter @KeyID, but your code passed a bunch of
others hence the error. you may have wanted a sp that had a parameter for
each field and update the row based on that/

-- bruce (sqlwork.com)



| I am trying to do a simple update of a database using a stored procedure.
I
| have get the following error when executing the code:
|
| "Procedure or function UpdateDetails has too many arguments specified."
|
| Here is my SP:
|
| ALTER PROCEDURE dbo.UpdateDetails
| @KeyID int
|
| AS
| SET NOCOUNT ON;
| SELECT dbo.Events.KeyID, dbo.Events.AvailableSeats,
| dbo.Reservations.KeyID AS ReserveID, dbo.Reservations.EventID,
| dbo.Reservations.Status,
dbo.Reservations.AuthStatus,
| dbo.Reservations.AuthCode,
| dbo.Reservations.AuthMessage
| FROM dbo.Events INNER JOIN
| dbo.Reservations ON dbo.Events.KeyID =
| dbo.Reservations.EventID
| WHERE (dbo.Reservations.KeyID = @KeyID)
|
| Here is the executing code:
|
| this.sqlUpdateCommand1.CommandText = "UpdateDetails";
| this.sqlUpdateCommand1.Connection = this.sqlConnection1;
| this.sqlUpdateCommand1.CommandType =
| System.Data.CommandType.StoredProcedure;
| this.sqlUpdateCommand1.Parameters.Add(new
| System.Data.SqlClient.SqlParameter("@KeyID", System.Data.SqlDbType.Int, 4,
| System.Data.ParameterDirection.ReturnValue, false, ((System.Byte)(0)),
| ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
|
| this.sqlUpdateCommand1.Parameters.Add("@KeyID",
| System.Data.SqlDbType.NVarChar).Value = ReserveID;
| this.sqlUpdateCommand1.Parameters.Add("@AuthStatus",
| System.Data.SqlDbType.NVarChar, 50, "AuthStatus").Value = "ads";
| this.sqlUpdateCommand1.Parameters.Add("@AuthCode",
| System.Data.SqlDbType.NVarChar, 50, "AuthCode").Value = "asdf";
| this.sqlUpdateCommand1.Parameters.Add("@AuthMessage",
| System.Data.SqlDbType.NVarChar, 50, "AuthMessage").Value = "asdf";
| this.sqlUpdateCommand1.Parameters.Add("@Status",
| System.Data.SqlDbType.VarChar, 10, "Status").Value = "asdf";
| this.sqlUpdateCommand1.Parameters.Add("@AvailableSeats",
| System.Data.SqlDbType.Int, 4, "AvailableSeats").Value = int.Parse("123");
| this.sqlUpdateCommand1.Parameters.Add(new
| System.Data.SqlClient.SqlParameter("@Original_Status",
| System.Data.SqlDbType.Char, 10, System.Data.ParameterDirection.Input,
false,
| ((System.Byte)(0)), ((System.Byte)(0)), "Status",
| System.Data.DataRowVersion.Original, null));
|
| sqlUpdateCommand1.Connection.Open();
| sqlUpdateCommand1.ExecuteNonQuery();
| sqlUpdateCommand1.Connection.Close();
|
| Please suggest any articles that my help out.
|
| Thanks, Justin.

 

[Random]

Where is your UPDATE clause? I don't see how you intend for this procedure
to do any updating. Change it to do an update, add in the other parameters
to your definition, and you should be golden.

 

[Jeff Dillon]

Yes, you specified too many arguments