CODEWITHSUNDEEP
c#sqlstored-procedures
user3423060
user3423060

Reputation: 15

Getting system's time

I want to store data of sold items into database I'm using this stored procedure 

ALTER proc [dbo].[StoredProd]
(@prdctName nvarchar(50),
 @ordrdQnty int,
 @ordrPrice money,
 --@TrnId int,
 --@OrdrId int,
 @TrnDate datetime,
 @TrnTotal money)

AS BEGIN TRANSACTION

INSERT INTO [OrderProduct]
           ([prdctName]
           ,[ordrdQnty]
           ,[ordrPrice])
     VALUES
           (@prdctName,
            @ordrdQnty, 
            @ordrPrice )
           --GETDATE() ),
if @@ERROR<>0 goto Err_

INSERT INTO [Transaction]
           (/*[TrnId]
           ,[OrdrId],*/
           [TrnDate]
           ,[TrnTotal])
     VALUES
           (/*@TrnId, 
           @OrdrId,*/
          (SELECT  CURRENT_TIMESTAMP), 
           @TrnTotal)

           if @@ERROR<>0 goto Err_

commit tran
return 0

Err_:
rollback
return 1

enter code here

This C# Code

  private void btnCount_Click(object sender, EventArgs e)
        { 
            string conn = "server=.;uid=sa;pwd=123;database=PharmacyDB;";

            SqlConnection con = new SqlConnection();

            for (int i = 0; i < dgvSelectedItem.Rows.Count; i++)
            {

                SqlCommand cmd = new SqlCommand("storedP");
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@prdctName", dgvSelectedItem.Rows[i].Cells[0].Value));
                cmd.Parameters.Add(new SqlParameter("@ordrdQnty", dgvSelectedItem.Rows[i].Cells[2].Value));
                cmd.Parameters.Add(new SqlParameter("@ordrPrice", dgvSelectedItem.Rows[i].Cells[3].Value));


                con.ConnectionString = conn;
                cmd.Connection = con;

                con.Open();

                cmd.ExecuteNonQuery();

                con.Close();

            }

I have 2 tables and this SP should stored the OrderProduct table data first then filling Transaction table the relationship between them is many orders to one Transaction ID , but this error is appeared:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll Additional information: Procedure or function 'storedP' expects parameter '@TrnDate', which was not supplied.

Upvotes: 0

Views: 82

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

This is too long for a comment. The problem is your calling the stored procedure not the code itself. The stored procedure has the following definition:

ALTER proc [dbo].[StoredProd] (
    @prdctName nvarchar(50),
    @ordrdQnty int,
    @ordrPrice money,
    @TrnDate datetime,
    @TrnTotal money
) . . .

It is expecting five arguments, one of which is @TrnDate (positionally, this should be the fourth). You don't seem to be including that argument, and you are getting an error on the stored procedure call itself.

EDIT:

If you want @TrnDate to default to the system time, then use default:

ALTER proc [dbo].[StoredProd] (
    @prdctName nvarchar(50),
    @ordrdQnty int,
    @ordrPrice money,
    @TrnDate datetime default getdate(),
    @TrnTotal money
) . . .

If you don't want it as a parameter, remove it entirely. You can declare it in the body of the stored procedure:

declare @TrnDate datetime = getdate();

Upvotes: 1

Related Questions