Insert Stored Procedure Help

OK I have a stored procedure that inserts information into a database table. Here is what I have so far:

I think I have the proper syntax for inserting everything, but I am having problems with two colums. I have Active column which has the bit data type and a Notify column which is also a bit datatype. If I run the procedure as it stands it will insert all the information correctly, but I have to manually go in to change the bit columns. I tried using the set command, but it will give me a xyntax error implicating the "=" in the Active = 1 How can I set these in the stored procedure?

1SET ANSI_NULLSON2GO3SET QUOTED_IDENTIFIERON4GO5-- =============================================6-- Author:xxxxxxxx7-- Create date: 10/31/078-- Description:Insert information into Registration table9-- =============================================10ALTER PROCEDURE [dbo].[InsertRegistration]1112@.Name nvarchar(50),13@.StreetAddressnchar(20),14@.Citynchar(10),15@.Statenchar(10),16@.ZipCodetinyint,17@.PhoneNumbernchar(20),18@.DateOfBirthsmalldatetime,19@.EmailAddressnchar(20),20@.Gendernchar(10),21@.Notifybit2223AS24BEGIN25-- SET NOCOUNT ON added to prevent extra result sets from26-- interfering with SELECT statements.27SET NOCOUNT ON;2829INSERT INTO Registration3031(Name, StreetAddress, City, State, ZipCode, PhoneNumber, DateOfBirth, EmailAddress, Gender, Notify)3233VALUES3435(@.Name, @.StreetAddress, @.City, @.State, @.ZipCode, @.PhoneNumber, @.DateOfBirth, @.EmailAddress, @.Gender, @.Notify)3637--SET38--Active = 13940END41GO

Can u post the data with execute method

Thank u

Baba

|||

You cannot set "Active" = 1 in t-sql.

That is because "Active" is not a valid variable name. It should be @.Active and, of course, you would have to declare it before doing so.

That said, I suspect you are trying to set the Active column value to 1 in the record you are inserting.

If so, you need to add (assuming Active is the column name) Active to the list of column names in the insert statement.

In the corresponding slot in the list of values in the insert statement, place a 1.

|||

It didn't correct the problem, but let me make sure I am on the right track (different table here):

1set ANSI_NULLSON2set QUOTED_IDENTIFIERON3GO4-- =============================================5-- Author:xxxxx

6-- Create date: 10/21/077-- Description:Insert Users8-- =============================================9ALTER PROCEDURE [dbo].[InsertUser]101112@.FirstNamenvarchar(50),13@.LastNamenvarchar(50),14@.MiddleNamenvarchar(50),15@.Activebit16AS17BEGIN18SET NOCOUNT ON;1920INSERT INTO Users21(FirstName, LastName, MiddleName, Active)22VALUES23(@.FirstName, @.LastName, @.MiddleName, 1)24--SET25--Active =126END

I get this message when you try to insert a user:

Procedure or function 'InsertUser' expects parameter '@.Active', which was not supplied.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Procedure or function 'InsertUser' expects parameter '@.Active', which was not supplied.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Procedure or function 'InsertUser' expects parameter '@.Active', which was not supplied.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +921162 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800038 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +947 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +149 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +404 System.Web.UI.WebControls.SqlDataSourceView.ExecuteInsert(IDictionary values) +447 System.Web.UI.DataSourceView.Insert(IDictionary values, DataSourceViewOperationCallback callback) +72 System.Web.UI.WebControls.DetailsView.HandleInsert(String commandArg, Boolean causesValidation) +390 System.Web.UI.WebControls.DetailsView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +602 System.Web.UI.WebControls.DetailsView.OnBubbleEvent(Object source, EventArgs e) +95 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.DetailsViewRow.OnBubbleEvent(Object source, EventArgs e) +109 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +132 System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +177 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746

Am I on the right track?|||

Tharnid:

12 @.FirstNamenvarchar(50),
13 @.LastNamenvarchar(50),
14 @.MiddleNamenvarchar(50),
15 @.Activebit

Tharnid:

Procedure or function 'InsertUser' expects parameter '@.Active', which was not supplied.

You declared an input parameter @.Active, according to your SQL statement this should be 1 by default, so you should not declare it at all!

12 @.FirstNamenvarchar(50),
13 @.LastNamenvarchar(50),
14 @.MiddleNamenvarchar(50)
15

|||

try:

ALTER PROCEDURE [dbo].[InsertUser]
10
11
12@.FirstNamenvarchar(50),
13@.LastNamenvarchar(50),
14@.MiddleNamenvarchar(50),
15@.Activebit = 1
16AS

BEGIN

...

END 

|||

Procedure or function 'InsertUser' expects parameter '@.Active', which was not supplied.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Procedure or function 'InsertUser' expects parameter '@.Active', which was not supplied.

Simply is telling you that you are not supplying the code with the value for the Active Parameter which in your case is an INPUT parameter and not an OUTPUT parameter. If you need it to be OUTPUT, I believe you have to indicate that in your procedure.

|||

Yes, you can give @.Active a default value as Spark is suggesting, but why would you do that? In the SQL statement active is hardcoded to be 1. So if you do supply a value (0 or 1) for it, the procedure will ignore it!

And yes, you can make it an output parameter as Dollarjunkie wrote, but then the return statement should be

SET @.active = 1

But why would you want an output parameter that will always return 1?

In this case, to skip the parameter is the logical solution, although the other 2 approaches will also work...

|||

I appreciate the respone :-) I will try them as soon as I can, indicate who gave the correct answer, and check answered