CODEWITHSUNDEEP
c#asp.netsql-servervisual-studio
user8048827
user8048827

Reputation:

How to add foreign key in SQL Server in ASP.NET C#

I want to know how to add a foreign key into my website through the payment section which is I created a list Seminar for user to choose first including a schedule. After that, user can click register and redirect page into payment details form which is contains credit card or cash. after user make an payment and click confirm button then the database should be increase by one include the primary key of an id (auto generate).

My problem is why I am still getting an id which is -1 not even +1 (I checked, the database has not updated in SQL Server) because of an id is -1.

Here is my code regarding for the payment section

if (tbxName.Text != null && tbxCC.Text != null && ddlCCType.Text != null && tbxExpDate.Text != null)
{
        ShoppingCart sc = (ShoppingCart)Session["cart"];
        sc.Name = tbxName.Text;

        if (rdbCash.Checked == true)
        {
            sc.OptionPay = rdbCash.Text;
        }
        else
        {
            sc.OptionPay = rdbCC.Text;
        }

        sc.CreditCard = tbxCC.Text;
        sc.CreditCardType = ddlCCType.Text;
        sc.SecurityCode = tbxCode.Text;
        sc.CCExpiryDate = tbxExpDate.Text;
        //sc.Registration.RegId = sc.Id;

        int id = ShoppingCartDBMgr.purchaseSeminar(sc);

        lblOutput.Text = "Confirm. order id is " + id;
        //display output for payment successfully
        //lblOutput.Text = "Payment Successfully!";
        //make it null for amount, date and session of cart after transaction are being successful
        lblAmount.Text = null;
        lblDate.Text = null;
        Session["cart"] = null;

My payment database manager code:

public static int purchaseSeminar(ShoppingCart sc)
{
    int id = -1;
    SqlConnection con = new SqlConnection(conStr);

    try
    {
        SqlCommand command = new SqlCommand();
        command.Connection = con;
        command.CommandText = "insert into Payment (payment_id, payment_name, payment_ccNo, payment_ccType, payment_ccCode, payment_expDate, payment_price, payment_optionPay, payment_date, reg_id) values (@payment_id, @payment_name, @payment_ccNo, @payment_ccType, @payment_ccCode, @payment_expDate, @payment_price, @payment_optionPay, @payment_date, @reg_id)";
        command.Parameters.AddWithValue("@payment_id", sc.Id);
        command.Parameters.AddWithValue("@payment_ccNo", sc.CreditCard);
        command.Parameters.AddWithValue("@payment_ccType", sc.CreditCardType);
        command.Parameters.AddWithValue("@payment_ccCode", sc.SecurityCode);
        command.Parameters.AddWithValue("@payment_expDate", sc.CCExpiryDate);
        command.Parameters.AddWithValue("@payment_price", sc.TotalAmount);
        command.Parameters.AddWithValue("@payment_optionPay", sc.OptionPay);
        command.Parameters.AddWithValue("@reg_id", sc.Registration.RegId);

        DateTime da = DateTime.Now;
        command.Parameters.AddWithValue("@payment_date", da);

        con.Open();

        command.CommandText = "SET IDENTITY_INSERT Payment ON";

        if (command.ExecuteNonQuery() > 0)
        {
            command.CommandText = "Select @@identity";
            id = Convert.ToInt32(command.ExecuteScalar());
        }

        return id;
    }
    finally
    {
        con.Close();
    }
}

Upvotes: 0

Views: 902

Answers (2)

Floxy
Floxy

Reputation: 137

This is not a good way of doing it however for your answer you should add

This should be on your first line 

command.CommandText = "SET IDENTITY_INSERT Payment On"; 

command.CommandText = "insert into Payment (payment_id, payment_name, payment_ccNo, payment_ccType, payment_ccCode, payment_expDate, payment_price, payment_optionPay, payment_date, reg_id) values (@payment_id, @payment_name, @payment_ccNo, @payment_ccType, @payment_ccCode, @payment_expDate, @payment_price, @payment_optionPay, @payment_date, @reg_id)";

command.Parameters.AddWithValue("@payment_id", sc.Id);
command.Parameters.AddWithValue("@payment_ccNo", sc.CreditCard);
command.Parameters.AddWithValue("@payment_ccType", sc.CreditCardType);
command.Parameters.AddWithValue("@payment_ccCode", sc.SecurityCode);
command.Parameters.AddWithValue("@payment_expDate", sc.CCExpiryDate);
command.Parameters.AddWithValue("@payment_price", sc.TotalAmount);
command.Parameters.AddWithValue("@payment_optionPay", sc.OptionPay);
command.Parameters.AddWithValue("@reg_id", sc.Registration.RegId);

And this should be your last line 

command.CommandText = "SET IDENTITY_INSERT Payment OFF";

Upvotes: 1

Shully
Shully

Reputation: 78

I don't see where sc.Id is being assigned a value unlike the others(eg: sc.Name). Then inserting that(sc.Id) into the database will insert a null value. So when you query it from the database, converting it to an int32 value, returns '-1'.

That's what I think might be causing the problem along with the structure of your code.

Since your id is auto-generated, exclude the 'payment_ID' from your insert command. Example:

command.CommandText = "insert into Payment (payment_name, payment_ccNo, 
                       payment_ccType, payment_ccCode, payment_expDate, payment_price, 
                       payment_optionPay, payment_date, reg_id) 
                values (@payment_name, @payment_ccNo, @payment_ccType, @payment_ccCode, 
                        @payment_expDate, @payment_price, @payment_optionPay, 
                        @payment_date, @reg_id)";

command.Parameters.AddWithValue("@payment_ccNo", sc.CreditCard);
command.Parameters.AddWithValue("@payment_ccType", sc.CreditCardType);
command.Parameters.AddWithValue("@payment_ccType", sc.CreditCardType);
command.Parameters.AddWithValue("@payment_ccCode", sc.SecurityCode);
command.Parameters.AddWithValue("@payment_expDate", sc.CCExpiryDate);
command.Parameters.AddWithValue("@payment_price", sc.TotalAmount);
command.Parameters.AddWithValue("@payment_optionPay", sc.OptionPay);
command.Parameters.AddWithValue("@reg_id", sc.Registration.RegId);

As an identity column, 'payment_ID' doesn't expect a value so exclude it from your insert statement. That way, you won't need to turn IDENTITY INSERT on. So you'll remove those statements too from your code.

Upvotes: 0

Related Questions