Stack Exchange
problem to save data in a SQL database from a results table via web service
I have a problem and I need your help. As my web service describes I want to get some data and add them to my database.
[WebMethod(Description = "This will input computers into the database", EnableSession = false)]
public string orderItem(int CUS_ID, string COM_ID, int Quantity,double COMPrice)
{

    try
    {

        dbConn = new DbConnection();
        SqlConnection conn = dbConn.OpenConnection();
        SqlCommand orderItem = new SqlCommand("OrderComputer", conn);
        orderItem.CommandType = CommandType.StoredProcedure;


        SqlParameter add_CUS_ID = orderItem.Parameters.Add("@CUS_ID", SqlDbType.Int, 4);
        add_CUS_ID.Value = CUS_ID;

        SqlParameter addBK_ISBN = orderItem.Parameters.Add("@COM_ID", SqlDbType.Char, 80);
        addBK_ISBN.Value = COM_ID;

        SqlParameter add_Quantity = orderItem.Parameters.Add("@Quantity", SqlDbType.Int, 2);
        add_Quantity.Value = Quantity;

        SqlParameter add_COMPrice = orderItem.Parameters.Add("@COMPrice", SqlDbType.Money, 8);
        add_COMPrice.Value = COMPrice;


        return this.ExecuteQuery(orderItem);
    }
    catch (Exception e)
    {
        return e.ToString();
    }
}

The OrderComputer is a stored procedure:
ALTER  Procedure OrderComputer
(
    @CUS_ID int,
    @COM_ID int,
    @Quantity int,
    @COMPrice money
)

AS

declare @Date datetime
declare @ShipDate datetime
declare @OR_ID int


select @Date = getdate()
select @ShipDate = getdate()

begin tran NewComputer


INSERT INTO Orders
(
    CUS_ID, 
    Date, 
    ShipDate
)

VALUES
(   
    @CUS_ID, 
    @Date, 
    @ShipDate 
)

SELECT @OR_ID = @@Identity 


INSERT INTO ComputerOrders
(
    OR_ID, 
    COM_ID, 
    Quantity, 
    COMPrice
)

VALUES
(
    @OR_ID,
    @COM_ID,
    @Quantity,
    @COMPrice
)

commit tran NewComputer

The following part is the final step of my shopping cart. It returns a table with the order details. My problem is why the line 
order.orderItem(customerID, Computer_ID, quantity, price);

cannot get the record to add it to the database?. Is something missing?
computerOrder1.computerOrder order = new computerOrder1.computerOrder();
int quantity = 2;

XmlDocument customer_Order = ComputerCart.getCartDescription();
while (customer_Order.SelectNodes("//Computers").Count > 0)
{

    string Computer_ID = customer_Order.GetElementsByTagName("Computers").Item(0).SelectSingleNode("//com_id").InnerText;

   double price = double.Parse(customer_Order.GetElementsByTagName("Computers").Item(0).SelectSingleNode("//price").InnerText);

   string Model = customer_Order.GetElementsByTagName("Computers").Item(0).SelectSingleNode("//model").InnerText;

   order.orderItem(customerID, Computer_ID, quantity, price);
}


if you are using mssql server do NOT use @@identity as it returns the latest identity from ANY insert made wether it be in the transaction it is doing or from another, use SCOPE_IDENTITY() instead, also where does "order" come from?

"order" came from computerOrder1.computerOrder order = new computerOrder1.computerOrder(); where computerOrder is the name of the Web service...I will edit it to my question

side comments, are you closing the connections? Can we see your implementation for this.ExecuteQuery? Which should probably have been called ExecuteNonQuery for your insert!

This is my execute query protected string ExecuteQuery(SqlCommand QueryObject)
{
    int queryResult = QueryObject.ExecuteNonQuery();

    if (queryResult != 0)
    {

        return "Your request is CORRECT";
    }
    else
    {
        return "error: QueryResult= " + queryResult;
    }
}


Juding by your comment: 
protected string ExecuteQuery(SqlCommand QueryObject) { 
   int queryResult = QueryObject.ExecuteNonQuery(); 
   if (queryResult != 0) { 
      return "Your request is CORRECT"; 
   } 
   else { 
      return "error: QueryResult= " + queryResult; 
   } 
}

You would need not only your SqlCommand but also your connection
protected string ExecuteQuery(SqlCommand QueryObject, SqlConnection conn) { 
   try
   {
      conn.open();
      int queryResult = QueryObject.ExecuteNonQuery(); 
      if (queryResult != 0) { 
         return "Your request is CORRECT"; 
      } 
      else { 
         return "error: QueryResult= " + queryResult; 
      }
   }
   finally
   { 
      conn.close();
   }
}


I have the followind error in both conn.open and conn.close. System.Data.SqlClient.SqlConnection does not contain a definition for close and no exception method 'close' excepting a first type of argument System.Data.SqlClient.SqlConnection

@theo1985 Sqlconnection does contain these methods, http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx so if you can't call them you must have done something wrong. Remember that they are case sensitive, conn.Open()  and conn.Close() should work.

yes the case Sensitive was one of my problems...but again it doesnt put the order into the database..Thanks for your help...

@theo1985 Ok when you are adding sql parameters try "command.Parameters.Add(new SqlParameter("@CUS_ID",SqlDbType.Int));" and so on, also if it is still not working I suggest you take a look at your "dbConn.OpenConnection();" as it could be the issue(I can't tell since I haven't seen the code).
