I am trying to update database from datagridview using stored procedure and it keeps on saying The stored procedure expects a parameter BookID which was not supplied. Below is my code
-
My stored procedure ALTER PROCEDURE [dbo].[UpdateAllBooks] @BookID int OUTPUT, @GeneralID INT OUTPUT, @FileName nvarchar(max), @FilePath nvarchar(max), @FileSize nvarchar(max), @DateAdded date, @MediaLength nvarchar(max), @MediaType nvarchar(max), @MediaSubType nvarchar(max), @Thumbnail image, @DateAcquired datetime, @AuthorName nvarchar(50), @ISBN nvarchar(max), @Title nvarchar(max), @Genre nvarchar (max), @Series nvarchar(max), @YearOfPublication date, @Description text AS BEGIN Update dbo.General SET FileName = @FileName, FilePath = @FilePath, FileSize = @FileSize, DateAdded = @DateAdded, MediaLength = @MediaLength, MediaType = @MediaType, MediaSubType = @MediaSubType, Thumbnail = @Thumbnail, DateAcquired = @DateAcquired Where GeneralID = @GeneralID Update dbo.Books SET AuthorName = @AuthorName, ISBN = @ISBN, Title = @Title, Genre = @Genre, Series = @Series, YearOfPublication = @YearOfPublication , Description = @Description Where BookID = @BookID END My code private void Update_Click(object sender, EventArgs e) { if (connect.State == ConnectionState.Open) { connect.Close(); } connect.Open(); cmd = new SqlCommand("dbo.UpdateAllBooks", connect); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@BookID", SqlDbType.Int, 4, "BookID"); cmd.Parameters.Add("@GeneralID", SqlDbType.Int,4,"GeneralID"); cmd.Parameters.Add("@FileName", SqlDbType.NVarChar, 50, "FileName"); cmd.Parameters.Add("@AuthorName", SqlDbType.NVarChar, 50,"AuthorName"); cmd.Parameters.Add("@ISBN", SqlDbType.NVarChar, 50, "ISBN"); cmd.Parameters.Add("@Title", SqlDbType.NVarChar, 50, "Title"); cmd.Parameters.Add("@Genre", SqlDbType.NVarChar, 50, "Genre"); cmd.Parameters.Add("@Series", SqlDbType.NVarChar, 50, "Series"); cmd.Parameters.Add("@YearOfPublication", SqlDbType.Date, 50, "YearOfPublication"); cmd.Parameters.Add("@Thumbnail", SqlDbType.Image); cmd.Parameters.Add("@Description", SqlDbType.NVarChar, 50, "Description"); cmd.Parameters.Add("@FileSize", SqlDbType.NVarChar, 50, "FileSize"); cmd.Parameters.Add("@FilePath", SqlDbType.NVarChar, 50, "FilePath"); cmd.Parameters.Add("@DateAdded", S
-
My stored procedure ALTER PROCEDURE [dbo].[UpdateAllBooks] @BookID int OUTPUT, @GeneralID INT OUTPUT, @FileName nvarchar(max), @FilePath nvarchar(max), @FileSize nvarchar(max), @DateAdded date, @MediaLength nvarchar(max), @MediaType nvarchar(max), @MediaSubType nvarchar(max), @Thumbnail image, @DateAcquired datetime, @AuthorName nvarchar(50), @ISBN nvarchar(max), @Title nvarchar(max), @Genre nvarchar (max), @Series nvarchar(max), @YearOfPublication date, @Description text AS BEGIN Update dbo.General SET FileName = @FileName, FilePath = @FilePath, FileSize = @FileSize, DateAdded = @DateAdded, MediaLength = @MediaLength, MediaType = @MediaType, MediaSubType = @MediaSubType, Thumbnail = @Thumbnail, DateAcquired = @DateAcquired Where GeneralID = @GeneralID Update dbo.Books SET AuthorName = @AuthorName, ISBN = @ISBN, Title = @Title, Genre = @Genre, Series = @Series, YearOfPublication = @YearOfPublication , Description = @Description Where BookID = @BookID END My code private void Update_Click(object sender, EventArgs e) { if (connect.State == ConnectionState.Open) { connect.Close(); } connect.Open(); cmd = new SqlCommand("dbo.UpdateAllBooks", connect); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@BookID", SqlDbType.Int, 4, "BookID"); cmd.Parameters.Add("@GeneralID", SqlDbType.Int,4,"GeneralID"); cmd.Parameters.Add("@FileName", SqlDbType.NVarChar, 50, "FileName"); cmd.Parameters.Add("@AuthorName", SqlDbType.NVarChar, 50,"AuthorName"); cmd.Parameters.Add("@ISBN", SqlDbType.NVarChar, 50, "ISBN"); cmd.Parameters.Add("@Title", SqlDbType.NVarChar, 50, "Title"); cmd.Parameters.Add("@Genre", SqlDbType.NVarChar, 50, "Genre"); cmd.Parameters.Add("@Series", SqlDbType.NVarChar, 50, "Series"); cmd.Parameters.Add("@YearOfPublication", SqlDbType.Date, 50, "YearOfPublication"); cmd.Parameters.Add("@Thumbnail", SqlDbType.Image); cmd.Parameters.Add("@Description", SqlDbType.NVarChar, 50, "Description"); cmd.Parameters.Add("@FileSize", SqlDbType.NVarChar, 50, "FileSize"); cmd.Parameters.Add("@FilePath", SqlDbType.NVarChar, 50, "FilePath"); cmd.Parameters.Add("@DateAdded", S
It looks like you've defined your BookID as OUTPUT, but trying to pass in the BookID in the line:
Where BookID = @BookID
Do you mean for the database to use BookID as an Identity object (in other words automatically set the ID number)? If so Google @@IDENTITY to see how to define this. If not, you might want to consider removing the OUTPUT keyword.
======================= Every experience in life is a lesson to be learned A. Stevens B.S., Computer Science
-
My stored procedure ALTER PROCEDURE [dbo].[UpdateAllBooks] @BookID int OUTPUT, @GeneralID INT OUTPUT, @FileName nvarchar(max), @FilePath nvarchar(max), @FileSize nvarchar(max), @DateAdded date, @MediaLength nvarchar(max), @MediaType nvarchar(max), @MediaSubType nvarchar(max), @Thumbnail image, @DateAcquired datetime, @AuthorName nvarchar(50), @ISBN nvarchar(max), @Title nvarchar(max), @Genre nvarchar (max), @Series nvarchar(max), @YearOfPublication date, @Description text AS BEGIN Update dbo.General SET FileName = @FileName, FilePath = @FilePath, FileSize = @FileSize, DateAdded = @DateAdded, MediaLength = @MediaLength, MediaType = @MediaType, MediaSubType = @MediaSubType, Thumbnail = @Thumbnail, DateAcquired = @DateAcquired Where GeneralID = @GeneralID Update dbo.Books SET AuthorName = @AuthorName, ISBN = @ISBN, Title = @Title, Genre = @Genre, Series = @Series, YearOfPublication = @YearOfPublication , Description = @Description Where BookID = @BookID END My code private void Update_Click(object sender, EventArgs e) { if (connect.State == ConnectionState.Open) { connect.Close(); } connect.Open(); cmd = new SqlCommand("dbo.UpdateAllBooks", connect); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@BookID", SqlDbType.Int, 4, "BookID"); cmd.Parameters.Add("@GeneralID", SqlDbType.Int,4,"GeneralID"); cmd.Parameters.Add("@FileName", SqlDbType.NVarChar, 50, "FileName"); cmd.Parameters.Add("@AuthorName", SqlDbType.NVarChar, 50,"AuthorName"); cmd.Parameters.Add("@ISBN", SqlDbType.NVarChar, 50, "ISBN"); cmd.Parameters.Add("@Title", SqlDbType.NVarChar, 50, "Title"); cmd.Parameters.Add("@Genre", SqlDbType.NVarChar, 50, "Genre"); cmd.Parameters.Add("@Series", SqlDbType.NVarChar, 50, "Series"); cmd.Parameters.Add("@YearOfPublication", SqlDbType.Date, 50, "YearOfPublication"); cmd.Parameters.Add("@Thumbnail", SqlDbType.Image); cmd.Parameters.Add("@Description", SqlDbType.NVarChar, 50, "Description"); cmd.Parameters.Add("@FileSize", SqlDbType.NVarChar, 50, "FileSize"); cmd.Parameters.Add("@FilePath", SqlDbType.NVarChar, 50, "FilePath"); cmd.Parameters.Add("@DateAdded", S
As suggested the OUTPUT is a waste of time (I'm not aware that you cannot send a value in via n output parameter, I never use them), you never consume them just pass information in. The error indicates that you are NOT passing in a BookID, so remove the OUTPUT and debug to parameter add value and make sure it has a value.
Never underestimate the power of human stupidity RAH