2 Replies - 732 Views - Last Post: 19 May 2013 - 12:36 PM

#1 Ghandi_Of_Codez  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 29
  • Joined: 26-March 13

Fill in a null column when data is submitted on sql server 2008

Posted 30 April 2013 - 07:41 PM

Hey guys, so I am having an issue with a shopping cart. I am trying to transfer the Title and Price columns out of a cart table (tblCart) and send them into a purchased items table (tblPurchaseItem). This works fine like it is suppose to. Now the problem is that the tblPurchaseItem has an extra column called PurchaseID that is not in the tblCart. Now on a button "Checkout" click this takes place. My issue I am having is that I am trying to set a value to the new rows added into tblPurchaseItem each time the table from cart is transferred over. So say tblCart is added with a list of movies I would like all of them to have the same value in the tblPurchaseItem. The next time somebody clicks the "Checkout" button I would like that new table from tblCart to be sent to tblPurchaseItem to be the value of the last one +1. When I try this my PurchaseID column is staying = to null and is taking no values. Any advice would be greatly appreciated!

Here is my code for the "Checkout" button:
        private void Button_Click(object sender, RoutedEventArgs e)
        {
            purID += 1;
            cn.Open();
            String cmdString = "INSERT INTO tblPurchaseItem(Title, Price) Select Title, Price from tblCart";
            String cmdString3 = "DELETE from tblCart";
            String cmdString4 = "Select * from tblPurchaseItem";
            SqlCommand cmd = new SqlCommand(cmdString, cn);
            SqlCommand cmd3 = new SqlCommand(cmdString3, cn);
            SqlCommand cmd4 = new SqlCommand(cmdString4, cn);
            da = new SqlDataAdapter(cmd4);
            dt = new DataTable("tblPurchaseItem");
            da.Fill(dt);
            foreach (DataRow row in dt.Rows)
                {
                    String cmdString2 = "UPDATE tblPurchaseItem set PurchaseID = '" + purID + "' where PurchaseID is Null";
                    SqlCommand cmd2 = new SqlCommand(cmdString2, cn);
                }
            cmd.ExecuteNonQuery();
            cmd3.ExecuteNonQuery();
            int nr = RandomNumber(0, 999999999);

            string ns = nr.ToString();


So all in all, say the first table sent to the tblPurchaseItem when the user clicks "Checkout" would be assigned the purID of 1. The next time the Checkout button is clicked I would like those values to be assigned to 2, and so on. So each transaction would have a its own purID.

Is This A Good Question/Topic? 0
  • +

Replies To: Fill in a null column when data is submitted on sql server 2008

#2 Ghandi_Of_Codez  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 29
  • Joined: 26-March 13

Re: Fill in a null column when data is submitted on sql server 2008

Posted 30 April 2013 - 07:48 PM

Pleas disregard the last two line, they are irrelevant ;)
Was This Post Helpful? 0
  • +
  • -

#3 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Fill in a null column when data is submitted on sql server 2008

Posted 19 May 2013 - 12:36 PM

You're able to stick a literal value into a select query: SELECT Title, Price, 1 from tblCart. (This will tack a column on the end of your query result that always has the value of 1.) So, for your line 5:
    String cmdString = "INSERT INTO tblPurchaseItem(Title, Price) Select Title, Price, " + purID + " from tblCart";
Assuming that purID is an integer. If not, you'll have to enclose the variable in single quotes:
    String cmdString = "INSERT INTO tblPurchaseItem(Title, Price) Select Title, Price, '" + purID + "' from tblCart";
which you can do anyway if you like.

This post has been edited by BobRodes: 19 May 2013 - 12:37 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1