Databases
StackOverflow2013
Postsdbo
POInsert-Update Error from SQLDataReader in Webservice
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POInsert-Update Error from SQLDataReader in Webservice
    primarykey
    data
    text
    <p>I have a webservice that either adds or updates room inventory based on whether it is available or not. (Code below). The problem I am having with this code is that it will either do a add or an update for the entire range or throw an error like for example if I am changing inventory for the period 18/8/2013 to 25/8/2013 and 18/8-23/8 has no inventory but 24/8-25/8 has, an error is thrown for 24/8 saying inventory is available and insert is not possible halting the entire process. I want my code to be able to add or update the entire date range rather then just doing an add or amend of everything. Is that possible? Do I need to change my code for that? How do I do that? Would be grateful for any insight on where I have gone wrong or how to change my code to be able to do this. This code is currently LIVE and so is currently affecting our business process so would be thankful for anything.</p> <pre><code>[WebMethod(Description = "Add or Amend Availability &amp; Rates")] public bool Avail(string Username, string Password, DateTime Dte, DateTime Dtm, int ID, string RoomType, int Qty, double CurPrice) { GetCredentials(Username, Password); int ID= Convert.ToInt16(GetCredentials(Username, Password).Tables[0].Rows[0]["strTypeID"]); bool retVal = false; GetCredentials(Username, Password); using (SqlConnection mySQLconnection = new SqlConnection(connStr)) { using (SqlCommand dbCommand = new SqlCommand("select * from Available where intID=@ID and dtm=@Dtm and strRoomType=@RoomType", mySQLconnection)) { SqlParameter dt = new SqlParameter("@Dtm", Dtm); SqlParameter RoomT = new SqlParameter("@RoomType", RoomType); SqlParameter typeI = new SqlParameter("@ID", ID); dbCommand.Parameters.Add(dt); dbCommand.Parameters.Add(RoomT); dbCommand.Parameters.Add(typeI); mySQLconnection.Open(); using (SqlDataReader reader = dbCommand.ExecuteReader()) { if (!reader.HasRows) { AddAvail(Username, Password, Dte, Dtm, RoomType, Qty, CurPrice); retVal = false; } else { AmdAvail(Username, Password, Dte, Dtm, RoomType, Qty, CurPrice); retVal = true; } mySQLconnection.Close(); dbCommand.Dispose(); mySQLconnection.Dispose(); return retVal; } } } } /*---------------------------------------------------- * Webmethod AddAvail Adds multiple availability for speicifed date range * ---------------------------------------------------*/ [WebMethod(Description = "Multiple Add of Availability", BufferResponse = true)] public void AddAvail(string Username, string Password, DateTime Dte,DateTime Dtm, string RoomType, int Qty, double CurPrice) { GetAuthCredentials(Username, Password); DateTime dat = Dtm; int strTypeID = Convert.ToInt16(GetAuthCredentials(Username, Password).Tables[0].Rows[0]["strTypeID"]); using (SqlConnection mySQLconnection = new SqlConnection(connStr)) { mySQLconnection.Open(); for (DateTime date = Dte; date &lt;= dat; date = date.AddDays(1.0)) { string sqlInsertString = "INSERT INTO Available (dtm,intResortID,strRoomType,intQty,curPrice) VALUES (@dat,@strTypeID,@strRoomType,@intQty,@CurPrice)"; using (SqlCommand command = new SqlCommand()) { command.Connection = mySQLconnection; command.CommandText = sqlInsertString; SqlParameter dt = new SqlParameter("@dat", date); SqlParameter intRID = new SqlParameter("@strTypeID", strTypeID); SqlParameter strRType = new SqlParameter("@strRoomType", RoomType); SqlParameter intQuty = new SqlParameter("@intQty", Qty); SqlParameter curpPrice = new SqlParameter("@curPrice", CurPrice); command.Parameters.AddRange(new SqlParameter[] { dt, intRID, strRType, intQuty, curpPrice }); command.ExecuteNonQuery(); } } } } /*----------------------------------------------------- * Webmethod AmdAvail Amends multiple Availability for specified date range * -----------------------------------------------------*/ [WebMethod(Description = "Multilple Updates", BufferResponse = true)] public DataSet AmdAvail(string Username, string Password, DateTime Dte, DateTime Dtm, string RoomType, int Qty, double CurPrice) { GetAuthCredentials(Username, Password); int strTypeID = Convert.ToInt16(GetAuthCredentials(Username, Password).Tables[0].Rows[0]["strTypeID"]); using (SqlConnection mySQLconnection = new SqlConnection(connStr)) { mySQLconnection.Open(); using (SqlCommand dbCommand = new SqlCommand()) { dbCommand.CommandText = "Update Available set intQty=@Qty,curprice=@CurPrice where dtm between @Dte and @Dtm and strRoomType=@Roomtype and intResortID=@strTypeID "; dbCommand.Connection = mySQLconnection; //Create new DataAdapter using (SqlDataAdapter da = new SqlDataAdapter()) { da.SelectCommand = dbCommand; SqlParameter typeI = new SqlParameter("@strTypeID", strTypeID); dbCommand.Parameters.Add(typeI); dbCommand.Parameters.AddWithValue("@Dtm", Dtm); dbCommand.Parameters.AddWithValue("@Dte", Dte); dbCommand.Parameters.AddWithValue("@Qty", Qty); dbCommand.Parameters.AddWithValue("@RoomType", RoomType); dbCommand.Parameters.AddWithValue("@curprice", CurPrice); dbCommand.Parameters.AddWithValue("@username", Username); dbCommand.Parameters.AddWithValue("@password", Password); DataSet ds = new DataSet(); da.Fill(ds); return ds; } } } } </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USuser1270384
    1. USuser1270384
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POInsert-Update Error from SQLDataReader in Webservice
      1. USuser1270384
      1. VTBountyStart
    1. COshow your AddAvail() method
      singulars
      1. POInsert-Update Error from SQLDataReader in Webservice
      1. USR.C
    2. CO@FlopScientist added in edit sections
      singulars
      1. POInsert-Update Error from SQLDataReader in Webservice
      1. USuser1270384
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload