0

I am actually trying to get the primary key after the insertion by using ExecuteScalar(). Since it returns the first column of the first row after the insertion. But I am getting 0. I do not know why it is happening. Please help me out.

query = "Insert into Admissions(Admission_date, Student_name, Father_name, Mother_name, DOB, Gender, Address, State, City, Pincode, Admission_for, Previous_school, Fees) values ('" + txtAdmDate.Text + "','" + txtStudentName.Text + "','" + txtFatherName.Text + "','" + txtMotherName.Text + "','" + dob + "','" + gender + "','" + txtAddress.Text + "','" + txtState.Text + "','" + txtCity.Text + "','" + txtPincode.Text + "','" + cmbClass.Text + "','" + txtPreviousSchool.Text + "','" + txtFees.Text + "')";

cmd = new SqlCommand(query, con);
con.Open();

int admid = Convert.ToInt32(cmd.ExecuteScalar());
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
SATYA
  • 35
  • 1
  • 5
  • 2
    [SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection - check out [Little Bobby Tables](https://xkcd.com/327/) – marc_s Jun 22 '17 at 14:02
  • 2
    Also: `INSERT INTO` returns nothing - therefore, you get 0. You need to have a statement that actually **selects** something that gets returned from the query, like `SELECT COUNT(*) .....` or something like that – marc_s Jun 22 '17 at 14:03
  • https://stackoverflow.com/questions/12080324/getting-the-primary-key-of-an-newly-inserted-row-in-sql-server-2008 – Dmitry Bychenko Jun 22 '17 at 14:21

3 Answers3

2

There are some issues with your code/question.

  1. Your code is vulnerable to SQL Injection attacks. You need to parameterize your queries.

  2. The INSERT statement by design is not meant to return anything, if you want to return the primary key of what you just inserted you need an output parameter in your query (better yet, a stored procedure).

  3. A quick google for "return primary key on sql insert c#" would have given you a ton of results. Your question is asked almost verbatim here. In fact my answer is basically the top answers code (modified for your use).

Here is my answer

//Create an Admission class that represents your data
public static int Save(Admission admission)
{
    var conn = DbConnect.Connection();
    const string sqlString = "Admissions(Admission_date, Student_name, Father_name, Mother_name, DOB, Gender, " +
        "Address,   State, City, Pincode, Admission_for, Previous_school, Fees) values (@AdmissionDate, @StudentName, " + 
        "@FatherName, @MotherName, @DOB, @Gender, @Address, @State, @City, @Pincode, @AdmissionFor, @PreviousSchool, " +
        "@Fees) SELECT SCOPE_IDENTITY()";

    using (conn)
    {
        using (var cmd = new SqlCommand(sqlString, conn))
        {
            cmd.Parameters.AddWithValue("@AdmissionDate", admission.AdmissionDate);
            cmd.Parameters.AddWithValue("@StudentName", admission.StudentName);
            cmd.Parameters.AddWithValue("@FatherName", admission.FatherName);
            cmd.Parameters.AddWithValue("@MotherName", admission.MotherName);
            cmd.Parameters.AddWithValue("@DOB", admission.DOB);
            cmd.Parameters.AddWithValue("@Gender", admission.Gender);
            cmd.Parameters.AddWithValue("@Address", admission.Address);
            cmd.Parameters.AddWithValue("@State", admission.State);
            cmd.Parameters.AddWithValue("@City", admission.City);
            cmd.Parameters.AddWithValue("@Pincode", admission.Pincode);
            cmd.Parameters.AddWithValue("@AdmissionFor", admission.AdmissionFor);
            cmd.Parameters.AddWithValue("@PreviousSchool", admission.PreviousSchool);
            cmd.Parameters.AddWithValue("@Fees", admission.Fees);

            cmd.CommandType = CommandType.Text;
            conn.Open();
            return (int)(decimal)cmd.ExecuteScalar();
        }
    }
}
maccettura
  • 10,514
  • 3
  • 28
  • 35
0

Try using an OUTPUT clause in your SQL command to return information about your command.

Eduardo Pelais
  • 1,627
  • 15
  • 21
DavidWaldo
  • 661
  • 6
  • 24
  • ExecuteScalar() executes the query and returns the first column of the first row in the result set returned by the query. Why I am I getting 0 ? – SATYA Jun 22 '17 at 14:08
  • @SATYA try executing the query in SQL. What are you expecting to return? – KSib Jun 22 '17 at 15:57
0
public int NewProperty(PropertyData propertyData)
    {
        using (SqlConnection con = new SqlConnection(CS))
        {
            SqlCommand cmd = new SqlCommand("InsertUpdateProperty", con);

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@id", propertyData.ID);
            cmd.Parameters.AddWithValue("@ListPropertyFor", propertyData.ListPropertyFor);
            cmd.Parameters.AddWithValue("@PropertyTypeId", propertyData.PropertyTypeId);
            cmd.Parameters.AddWithValue("@PropertyLoction", propertyData.PropertyLocation);
            cmd.Parameters.AddWithValue("@Locality", propertyData.Locality);
            cmd.Parameters.AddWithValue("@ProjectName", propertyData.ProjectName);
            cmd.Parameters.AddWithValue("@PropertyDescription", propertyData.PropertyDescription);
            cmd.Parameters.AddWithValue("@SuperBulidupArea", propertyData.SuperBulidupArea);
            cmd.Parameters.AddWithValue("@SuperBulidupId", propertyData.SuperBulidupAreaId);
            cmd.Parameters.AddWithValue("@BulidupArea", propertyData.BulidupArea);
            cmd.Parameters.AddWithValue("@BulidupAreaId", propertyData.BulidupAreaId);
            cmd.Parameters.AddWithValue("@CarpetArea", propertyData.CarpetArea);
            cmd.Parameters.AddWithValue("@CarpetAreaId", propertyData.CarpetAreaId);
            cmd.Parameters.AddWithValue("@Bathrooms", propertyData.Bathrooms);
            cmd.Parameters.AddWithValue("@Bedrooms", propertyData.Bedrooms);
            cmd.Parameters.AddWithValue("@Balconies", propertyData.Balconies);
            cmd.Parameters.AddWithValue("@FurnishedId", propertyData.FurnishedId);
            cmd.Parameters.AddWithValue("@TotalFloors", propertyData.TotalFloors);
            cmd.Parameters.AddWithValue("@PropertyOnFloors", propertyData.PropertyOnFloor);
            cmd.Parameters.AddWithValue("@Parking", propertyData.Parking);
            cmd.Parameters.AddWithValue("@AvalibiltyId", propertyData.AvalibiltyId);
            cmd.Parameters.AddWithValue("@AgeOfProperty", propertyData.AgeOfProperty);
            cmd.Parameters.AddWithValue("@OwnerShip", propertyData.OwenerShip);
            cmd.Parameters.AddWithValue("@Price", propertyData.Price);
            cmd.Parameters.AddWithValue("@IsActive", propertyData.IsActive);

            con.Open();

            int i = Convert.ToInt32(cmd.ExecuteScalar()); 

            con.Close();
            return i;
        }
    }
VISHAL GOTI
  • 1
  • 1