SQLDataAdapter's UpdateCommand not working

[Guest]

Folks

I've been working on this since yesterday, mined msdn and the usual discussion groups to no avail

I'm trying to do some maintenance on our database, and clear out certain phrases from the JobInstructions field in our database. sCriteria will include some year-specific phrases like "Okay to do. - " and "Left message on machine - " but leave the rest of the field values intact

The code appears to run fine except that the changes aren't reflected back to the database. No exceptions are raised, unless I remove ds.AcceptChanges() in which case a concurrency violation is raised

I'm fairly stumped here, and would appreciate any and all help

Thanks

Andr

private void MyProcess(string sCriteria

tr

string sSQL = "SELECT JobInstructions , ServiceID FROM tblMainServices " +
" WHERE (JobInstructions LIKE '%" + sCriteria + "%') AND (DateSold >= '1/1/2004') AND (AlreadyCompleted = 0);"
//Connect to database and set SelectComman
SqlConnection cn = new SqlConnection(sConnect)
SqlDataAdapter da = new SqlDataAdapter()
SqlCommand cmd = cn.CreateCommand()
da.SelectCommand = new SqlCommand(sSQL, cn)
da.SelectCommand.CommandTimeout=0

//Set DataAdapter's Update Command to parameterized values
sSQL = "UPDATE tblMainServices SET JobInstructions = @JobInstructions "
"WHERE (serviceID = @ServiceID);"
da.UpdateCommand = new SqlCommand(sSQL, cn)
da.UpdateCommand.CommandText = sSQL
da.UpdateCommand.Parameters.Add("@JobInstructions", SqlDbType.NVarChar, 400, "JobInstructions")
da.UpdateCommand.Parameters.Add("@ServiceID", SqlDbType.NVarChar,1, "ServiceID")

DataSet ds = new DataSet()
da.Fill(ds, "Services")

foreach (DataRow dsRow in ds.Tables["Services"].Rows


string sJobInstructions = dsRow["JobInstructions"].ToString()
string sServiceId = dsRow["ServiceID"].ToString()

int iStartIndex = sJobInstructions.IndexOf(sCriteria)
while (iStartIndex != -1

sJobInstructions = sJobInstructions.Remove(iStartIndex, sCriteria.Length)
iStartIndex = sJobInstructions.IndexOf(sCriteria)

dsRow["JobInstructions"] = sJobInstructions

ds.AcceptChanges()
da.Update(ds, "services")
ds.Tables["Services"].Clear()


catch (Exception ex

MessageBox.Show(ex.Source + "\r\r\n" + ex.Message + "\r\r\n" + ex.StackTrace, "MyProcess()", MessageBoxButtons.OK, MessageBoxIcon.Error)

}

 

[Jon Skeet [C# MVP]]

The code appears to run fine except that the changes aren't reflected
back to the database. No exceptions are raised, unless I remove
ds.AcceptChanges() in which case a concurrency violation is raised.

You shouldn't call AcceptChanges yourself - that basically makes the
DataSet think that there aren't any changes to send back up to the
database.

You need to sort out why you're getting a concurrency violation...

 

[Guest]

I figured out my problem, I'd set the varchar length of my ServiceID parameter to 1 instead of 10. That's why I was getting a concurrency exception without the ds.AcceptChanges command

It took a trip through the SQL Profiler to figure out my problem - the T-SQL code problem was plain as day there