"Stephen Lynch" <raider1raider@yahoo.com> wrote in message
news:44f6018f$0$25788$815e3792@news.qwest.net...
> Bill:
>
> Thanks, here is my error:
>
> Line 71: OleDbParameter parameterContributionID = new
> OleDbParameter("@ContributionID", OleDbType.Integer, 4);
> Line 72: parameterContributionID.Direction =
> ParameterDirection.Output
>
> I am using sql2005express, I don't know how to code, so I grabbed what I
> found on the net as it is a small project but after a few hours of reading
> a sql and c# book I decided to ask you guys. I realize this is old
> structure but I could find an example for net 2.
>
> Here's my full code:
>
> private void PopulateMassPostTable (object sender, System.EventArgs e)
> {
> // Create Instance of Connection and Command Object
> OleDbConnection myConnection = new
> OleDbConnection(ConfigurationManager.AppSettings["DatabaseDataInput1"]);
> OleDbCommand myCommand = new
> OleDbCommand("a_spAppendNewContributionID", myConnection);
>
> // Mark the Command as a SPROC
> myCommand.CommandType =
> System.Data.CommandType.StoredProcedure;
>
> // Add Parameters to SPROC
>
> OleDbParameter parameterCompanyId = new
> OleDbParameter("@CompanyId", OleDbType.Integer, 4);
> parameterCompanyId.Value = SystemUtils.GetUserID();
> myCommand.Parameters.Add(parameterCompanyId);
>
> OleDbParameter parameterPayPeriod = new
> OleDbParameter("@PayPeriod", OleDbType.Date, 8);
> parameterPayPeriod.Value = this.PayPeriod2.Text;
> myCommand.Parameters.Add(parameterPayPeriod);
>
> OleDbParameter parameterPlanYear = new
> OleDbParameter("@PlanYear", OleDbType.Integer, 4);
> parameterPlanYear.Value = this.PlanYear2.Text;
> myCommand.Parameters.Add(parameterPlanYear);
>
> OleDbParameter parameterContributionDate = new
> OleDbParameter("@ContributionDate", OleDbType.Date, 8);
> parameterContributionDate.Value =
> this.ContributionDate2.Text;
> myCommand.Parameters.Add(parameterContributionDate);
>
> OleDbParameter parameterContributionID = new
> OleDbParameter("@ContributionID", OleDbType.Integer, 4);
> parameterContributionID.Direction =
> ParameterDirection.Output
>
>
> try
> {
> // Open the connection and execute the Command
> myConnection.Open();
> myCommand.ExecuteNonQuery();
> myConnection.Close();
> }
> catch (Exception ex)
> {
> BaseClasses.Utils.MiscUtils.RegisterJScriptAlert(this, "Duplicate!",
> "Any anticipated error" + ex);
> return;
> }
>
> }
>
>
>
>
>
>
>
> "William (Bill) Vaughn" <billvaRemoveThis@nwlink.com> wrote in message
> news:OjkPciHzGHA.476@TK2MSFTNGP06.phx.gbl...
>> 1) What exception is thrown when you get to the offending line?
>> 2) What does the CommandText contain?
>> 3) Is this SQL Server? Which version? If so, why aren't you using
>> SqlClient?
>>
>> And no, in ADO.NET OUTPUT parameters are not available until all of the
>> rows have been fetched. You don't have to close the connection first,
>> just fetch the rowset before the Parameters collection is populated with
>> OUTPUT parameter values. If you set the direction to IO, you have to
>> provide a value when calling the SP. This is not necessary and in my
>> experience rarely the issue.
>>
>> --
>> ____________________________________
>> William (Bill) Vaughn
>> Author, Mentor, Consultant
>> Microsoft MVP
>> INETA Speaker
>> www.betav.com/blog/billva
>> www.betav.com
>> Please reply only to the newsgroup so that others can benefit.
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> __________________________________
>>
>> "Jim Hughes" <NOSPAMJ3033@Hotmail.com> wrote in message
>> news:uRxB4IGzGHA.1936@TK2MSFTNGP06.phx.gbl...
>>> Just a couple of observations.
>>>
>>> Try using ParameterDirection.InputOutput instead of
>>> ParameterDirection.Output
>>>
>>> In your stored proc, you should store the @@Identity in a variable and
>>> use that variable in appropriate places later in the proc and not use
>>> @@Identiy again unless you want to get a new value. (e.g use the new
>>> variable in the WHERE clause and not @@Identity) It's value may
>>> inadvertently be changed by a trigger or subsequent insert.
>>>
>>> If using SQL Server you should use Scope_Identity instead of @@Identity.
>>>
>>> Remember that the value of output parameters are not available until you
>>> close the connection.
>>>
>>> "Stephen Lynch" <raider1raider@yahoo.com> wrote in message
>>> news:44f5d44a$0$34081$815e3792@news.qwest.net...
>>>> All:
>>>>
>>>> I am running a stored procedure via code but need help with the sytax
>>>> for the output parameter as mine is wrong, Sorry, but I am just
>>>> learning. Here's my incorrect section, more is listed below:
>>>>
>>>> OleDbParameter parameterContributionID = new
>>>> OleDbParameter("@ContributionID", OleDbType.Integer, 4);
>>>> parameterContributionID.Direction =
>>>> ParameterDirection.Output
>>>>
>>>>
>>>>
>>>> Here's my Procedure:
>>>>
>>>> ******************************************************************
>>>> USE DataInput
>>>>
>>>> GO
>>>>
>>>> CREATE PROC a_spAppendNewContributionID
>>>>
>>>> @CompanyId int,
>>>>
>>>> @PayPeriod datetime,
>>>>
>>>> @PlanYear int,
>>>>
>>>> @ContributionDate datetime,
>>>>
>>>> @ContributionId int OUTPUT
>>>>
>>>> AS
>>>>
>>>> /* Create the new record */
>>>>
>>>> INSERT INTO Contributions
>>>>
>>>> VALUES
>>>>
>>>> (
>>>>
>>>> @CompanyId,
>>>>
>>>> @PayPeriod,
>>>>
>>>> @PlanYear,
>>>>
>>>> @ContributionDate
>>>>
>>>> )
>>>>
>>>> /* Move the identity value from the newly inserted record into
>>>>
>>>> our output variable */
>>>>
>>>> SELECT @ContributionId = @@IDENTITY
>>>>
>>>>
>>>>
>>>> INSERT INTO MassPost
>>>>
>>>> (ContributionId,EmployeeUID,FullName,Post)
>>>>
>>>>
>>>>
>>>> SELECT
>>>> Contributions.ContributionId,Employees.EmployeeUID,Employees.Last +',
>>>> '+ Employees.First +' '+ ISNULL( Employees.Middle,'' ) AS
>>>> 'FullName','0'
>>>>
>>>> FROM Contributions, Employees
>>>>
>>>> WHERE ContributionId = @@IDENTITY AND
>>>>
>>>> Employees.CompanyId=@CompanyId And
>>>>
>>>> Employees.AllowContributions = 1
>>>>
>>>> ORDER BY FullName
>>>>
>>>> ***************************************************************
>>>>
>>>> Here's part of my code in asp. The @contributionID section is where I
>>>> am