none
error:Must declare the scalar variable “@ID” asp.net & sql server2014 RRS feed

  • General discussion

  •  string query = "insert into LegRent(ID, Day, Date, Name, Nationality, relegon, NatID, Name2, Nationality2, relegon2, NatID2, Type, Building, Flat, UseFor, Duration, StartFrom, EndIn, RentValue, OnlyAr, YearPercent, InsuranceValue, OnlyArInsur, Addres2, Addres) values (@ID, @Day, @Date, @Name, @Nationality, @relegon, @NatID, @Name2, @Nationality2, @relegon2, @NatID2,  @Type, @Building, @Flat, @UseFor, @Duration, @StartFrom, @EndIn, @RentValue, @OnlyAr, @YearPercent, @InsuranceValue, @OnlyArInsur,  @Addres2, @Addres)";
    
            try
            {
    
                string query2 = "Select @@Identity";
                int ID;
                string connect = "Provider=SQLOLEDB;Data Source=.;Initial Catalog=Construction;Integrated Security=SSPI;";
                using (OleDbConnection conn2 = new OleDbConnection(connect))
                {
                    using (OleDbCommand cmd = new OleDbCommand(query, conn2))
                    {
                        cmd.Parameters.AddWithValue("@ID", idtxt.Text);
                        cmd.Parameters.AddWithValue("@Day", daytxt.Text);
                        cmd.Parameters.AddWithValue("@Date", datetxt.Text);
                        cmd.Parameters.AddWithValue("@Name", namedd.Text);
                        cmd.Parameters.AddWithValue("@Nationality", ddNati.Text);
                        cmd.Parameters.AddWithValue("@relegon", ddRelgon.Text);
                        cmd.Parameters.AddWithValue("@NatID", NatIDtxt.Text);
                        cmd.Parameters.AddWithValue("@Name2", namedd2.Text);
                        cmd.Parameters.AddWithValue("@Nationality2", ddNati2.Text);
                        cmd.Parameters.AddWithValue("@relegon2", ddRelgon2.Text);
                        cmd.Parameters.AddWithValue("@NatID2", NatIDtxt2.Text);
                        cmd.Parameters.AddWithValue("@Type", typrdd.Text);
                        cmd.Parameters.AddWithValue("@Building", buileddd.Text);
                        cmd.Parameters.AddWithValue("@Flat", flatdd.Text);
                        cmd.Parameters.AddWithValue("@UseFor", usetxt.Text);
                        cmd.Parameters.AddWithValue("@Duration", durationtxt.Text);
                        cmd.Parameters.AddWithValue("@StartFrom", starttxt.Text);
                        cmd.Parameters.AddWithValue("@EndIn", endtxt.Text);
                        cmd.Parameters.AddWithValue("@RentValue", vluetxt.Text);
                        cmd.Parameters.AddWithValue("@OnlyAr", onlytxt.Text);
                        cmd.Parameters.AddWithValue("@InsuranceValue", insurtxt.Text);
                        cmd.Parameters.AddWithValue("@OnlyArInsur", insuronlytxt.Text);
                        cmd.Parameters.AddWithValue("@YearPercent", percenttxt.Text);
                        cmd.Parameters.AddWithValue("@Addres2", adresstxt2.Text);
                        cmd.Parameters.AddWithValue("@Addres", adresstxt.Text);
    
                        conn2.Open();
                        cmd.ExecuteNonQuery();
                        cmd.CommandText = query2;
                        ID = (int)cmd.ExecuteScalar();
    
                        lbl_msg.Text = "تـــم الحفظ";
    
                        int rentMonths = 0;
    
                        bool isInt = int.TryParse(durationtxt.Text, out rentMonths);
    
                        if (isInt)
                        {
                            for (int index = 0; index < rentMonths; index++)
                            {
                                OleDbCommand insertRentMonths = new OleDbCommand("INSERT INTO [dbo].[AccRentReceipt] ([ReceiptID] ,[ContractID] ,[Name]) VALUES (@ReceiptID ,@ContractID ,@Name)", conn2);
                                cmd.Parameters.AddWithValue("@ReceiptID", index + 1);
                                cmd.Parameters.AddWithValue("@ContractID", ID);
                                cmd.Parameters.AddWithValue("@Name", namedd.Text);
    
                            }
                        }
    
                    }
                }
    
            }
            catch (Exception ex)
            {
                lbl_msg.Text = "خطــــأ " + ex.Message;
    
            }
    Saturday, June 3, 2017 8:45 PM

All replies

  • You didn't say on which line you got that error, but in any case, you're going about it the wrong way. First, I wouldn't use "@@IDENTITY", You should use "SCOPE_IDENTITY()". And it should be "tacked on" to the end of your original query. The reason is that it may give you erroneous results if it's not part of the same "session" (for lack of a better word). Also, which column is your Identity column? You need to include that in your parameters, as an output parameter (I don't think it's the ID column, because it looks like you've got a value for that already).

    So, basically, you'd want something like this:

    string query = "insert into LegRent(ID, Day, Date, Name, Nationality, relegon, NatID, Name2, Nationality2, relegon2, NatID2, Type, Building, Flat, UseFor, Duration, StartFrom, EndIn, RentValue, OnlyAr, YearPercent, InsuranceValue, OnlyArInsur, Addres2, Addres) values (@ID, @Day, @Date, @Name, @Nationality, @relegon, @NatID, @Name2, @Nationality2, @relegon2, @NatID2,  @Type, @Building, @Flat, @UseFor, @Duration, @StartFrom, @EndIn, @RentValue, @OnlyAr, @YearPercent, @InsuranceValue, @OnlyArInsur,  @Addres2, @Addres); SELECT @MyPK = SCOPE_IDENTITY()";

    And, for the Identity (PrimaryKey) column, you should add the parameter, @MyPK, like this:

    // I'm assuming your PK is an int. Add it with the other parameters:
    cmd.Parameters.Add("@MyPK", OleDbType.Int);
    cmd.Parameters["@MyPK"].Direction = ParameterDirection.Output;

    Then, retrieve the SCOPE_IDENTITY from the @MyPK parameter:

    cmd.ExecuteNonQuery();
    ID = (int)cmd.Parameters["@MyPK"].Value;

    Another thing, if this is a SQL Server database, you should be using Sql classes and not OleDb classes (SqlConnection, SqlCommand, etc.)


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Sunday, June 4, 2017 1:02 AM