2

I am writing a software I use method (parameters.add) but when I am adding data to a database I get the error below:

The parameterized query expects the parameter '@Id', which was not supplied.

I saw several topic but I can't solve my problem.

The parameterized query expects the parameter ###### which was not supplied

The parameterized query expects the parameter , which was not supplied

I put c# code and stored procedure code below:

private void btnSave_Click(object sender, EventArgs e)
        {
            string cs = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TranscactionAccountNumber;Data Source=DESKTOP-5DJFFQP";
            string name = txtName.Text;
            int shomarepeygiri = int.Parse(txtShomarePeygiri.Text);
            string date = dtpDate.Shamsi;
            decimal mablagh = decimal.Parse(txtMablagh.Text);
            string comment = txtComment.Text;
            using (cn = new SqlConnection(cs))
            using (cmd = new SqlCommand("InsertTransaction", cn))
            {
                cmd.Parameters.Add("@Id",SqlDbType.Int);
                cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 100).Value = name;
                cmd.Parameters.Add("@ShomarePeygiri", SqlDbType.Int).Value = shomarepeygiri;
                cmd.Parameters.Add("@Mablagh", SqlDbType.Decimal).Value = mablagh;
                cmd.Parameters.Add("@Comment", SqlDbType.NVarChar).Value = comment;
                cmd.Parameters.Add("@Date", SqlDbType.VarChar).Value = date;
                if (cn.State == ConnectionState.Closed)
                    cn.Open();
                cmd.ExecuteNonQuery();
                MessageBox.Show("Transaction Added Successfully..", "Register Transaction", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }


        }

Create procedure InsertTransaction
(
@id int,
@Name nvarchar(100),
@ShomarePeygiri int,
@Mablagh decimal(18, 0),
@Comment nvarchar(MAX),
@PersianDate varchar(10)

) 
AS
Insert Into TransactionTable(id,[Name],ShomarePeygiri,Mablagh,Comment,PersianDate)
 values (@id,@Name,@ShomarePeygiri,@Mablagh,@Comment,@PersianDate)
Community
  • 1
  • 1
behnam
  • 1,095
  • 2
  • 11
  • 30
  • you aren't giving a value to Id. you're just declaring it. You give all the other parameters a value, but not that one. – ADyson Nov 14 '16 at 10:14
  • Seems like you didn't provide a value for parameter: `cmd.Parameters.Add("@Id",SqlDbType.Int);` – Fabio Nov 14 '16 at 10:14
  • You don't set any value for the @ID parameter. Second, a command that calls a storedprocedure should say it in its CommandType property – Steve Nov 14 '16 at 10:14
  • This maybe shouldn't be an issue but... make it a capital " i ". – Danieboy Nov 14 '16 at 10:14
  • You are not assigning any value to `@Id`. If this value is intended to be auto-assigned, you should remove it entirely, from your procedure and your insert. – David Hedlund Nov 14 '16 at 10:14
  • because "@id" is a primary key I don't want to fill it manual. – behnam Nov 14 '16 at 10:16
  • 1
    Did you set the IDENTITY property on the database column ID for your `TransactionTable` ? If yes, then this field is populated automatically from your database engine and you don't need that parameter and you don't need to insert anything in your stored procedure for it. – Steve Nov 14 '16 at 10:19

2 Answers2

8
cmd.Parameters.Add("@Id",SqlDbType.Int);

You are Adding a parameter, but you are not giving a value for it. Because of that you are receiving this error.

If the ID column is Auto Incremented just remove it from the Stored Procedure.

EDIT: If you don't know how to create the column auto incremented check this answer: Auto increment primary key in SQL Server Management Studio 2012

Community
  • 1
  • 1
mybirthname
  • 17,949
  • 3
  • 31
  • 55
  • @alexshiro okay, if the ID is auto incremented like I wrote in my answer, just remove it from the stored procedure and don't give this parameter in the code. The sql server will auto increment the ID number and fill the ID for you. – mybirthname Nov 14 '16 at 10:18
  • @mybrithname,but, i get the error below:Procedure or function 'InsertTransaction' expects parameter '@Name', which was not supplied. – behnam Nov 14 '16 at 10:30
  • @alexshiro you need to remove the ID param from the code and from the stored procedure. The error tell you that you don't supply value from your name parameter – mybirthname Nov 14 '16 at 10:32
  • I removed 'ID' .but ,I get the error now : Procedure or function 'InsertTransaction' expects parameter '@Name', which was not supplied. – behnam Nov 14 '16 at 10:52
  • @alexshiro if you removed the id from both places, Check if you are giving value from Name parameter, this is what the error. – mybirthname Nov 14 '16 at 10:53
  • i get it ,I'm so sorry I forgot to write cmd.CommandType = CommandType.StoredProcedure; – behnam Nov 14 '16 at 11:51
2

Alter your procedure as below

Alter procedure InsertTransaction
(
@Name nvarchar(100),
@ShomarePeygiri int,
@Mablagh decimal(18, 0),
@Comment nvarchar(MAX),
@PersianDate varchar(10)
) 
AS
Insert Into TransactionTable([Name],ShomarePeygiri,Mablagh,Comment,PersianDate)
values (@Name,@ShomarePeygiri,@Mablagh,@Comment,@PersianDate)
mansi
  • 837
  • 5
  • 12