C# & SQL Server : incomplete update (string type)

Question

I'm trying to update a column with a string and it doesn't update correctly.

I'm displaying the string right before the update function and even after it's passed as a parameter (tip_reparatii,from the function) it's the same string but it doesn't update in the database.

The update function:

public void updateInventar ( int ID_INVENTAR, int ID_CEAS, DateTime data_intrare, string tip_reparatii, string pret, int tip_contact1, int ok_refuse, int tip_contact2, DateTime ok_refuse_date, int ID_STATUS, int ID_LOCALIZARE, DateTime data_iesire, string note, int ID_TEHNICIAN ) {
    if ( IsOnline() ) {
        try {
            string sqlInsertQuery = "UPDATE dbo.Inventar SET data_intrare=@data_intrare, tip_reparatii=@tip_reparatii, pret=@pret, tip_contact1=@tip_contact1, ok_refuse=@ok_refuse, tip_contact2=@tip_contact2, ok_refuse_date=@ok_refuse_date, ID_STATUS=@ID_STATUS, ID_LOCALIZARE=@ID_LOCALIZARE, data_iesire=@data_iesire, note=@note,ID_TEHNICIAN=@ID_TEHNICIAN WHERE ID_INVENTAR=@ID_INVENTAR AND ID_CEAS=@ID_CEAS";
            SqlCommand cmd = new SqlCommand(sqlInsertQuery, DbCon);
            MessageBox.Show(tip_reparatii); //this is the same string and it's correct

            cmd.Parameters.AddWithValue("@data_intrare", data_intrare);
            cmd.Parameters.AddWithValue("@tip_reparatii", tip_reparatii);
            cmd.Parameters.AddWithValue("@pret", pret);
            cmd.Parameters.AddWithValue("@tip_contact1", tip_contact1);
            cmd.Parameters.AddWithValue("@ok_refuse", ok_refuse);
            cmd.Parameters.AddWithValue("@tip_contact2", tip_contact2);
            cmd.Parameters.AddWithValue("@ok_refuse_date", ok_refuse_date);
            cmd.Parameters.AddWithValue("@ID_STATUS", ID_STATUS);
            cmd.Parameters.AddWithValue("@ID_LOCALIZARE", ID_LOCALIZARE);
            cmd.Parameters.AddWithValue("@data_iesire", data_iesire);
            cmd.Parameters.AddWithValue("@note", note);
            cmd.Parameters.AddWithValue("@ID_TEHNICIAN", ID_TEHNICIAN);
            cmd.Parameters.AddWithValue("@ID_INVENTAR", ID_INVENTAR);
            cmd.Parameters.AddWithValue("@ID_CEAS", ID_CEAS);

            cmd.ExecuteNonQuery();
        } catch ( SqlException e ) {
            MessageBox.Show(null, "Could not Update! " + e.Message, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    } else {
        MessageBox.Show(null, "SQL Connection Throttled!", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

The call code:

private void btnActualiser_Click(object sender, EventArgs e)
{
    string tipReparatiiString = "";

    if (this.cblReparatii.CheckedItems.Count > 0)
    {
        foreach (DataRowView itm in cblReparatii.CheckedItems)
        {  
            tipReparatiiString += itm.Row.ItemArray[0].ToString() + " ";
        }

        MessageBox.Show(tipReparatiiString); //this is correct
        obj.updateInventar(int.Parse(this.cblReparatii.SelectedValue.ToString()), int.Parse(this.cbCeas.SelectedValue.ToString()), this.dtDataIn.Value, tipReparatiiString, this.tbPrix.Text, this.cbPrin.SelectedIndex, this.cbOkRef.SelectedIndex, this.cbPrin2.SelectedIndex, this.dtOkRef.Value, int.Parse(this.cbStatus.SelectedValue.ToString()), int.Parse(this.cbLocatie.SelectedValue.ToString()), this.dtDataOut.Value, this.tbNote.Text, int.Parse(this.cbTehnician.SelectedValue.ToString()));

    }
    else
    {
        //Msg 
    }      
}

Both message boxes display the correct string but it doesn't update in the database or it sometimes updates incorrectly(a piece of the string).

We added a string manually as a parameter and it worked:

obj.updateInventar(int.Parse(this.cblReparatii.SelectedValue.ToString()), 
                   int.Parse(this.cbCeas.SelectedValue.ToString()), 
                   this.dtDataIn.Value, "this works perfectly", 
                   this.tbPrix.Text, this.cbPrin.SelectedIndex, 
                   this.cbOkRef.SelectedIndex, this.cbPrin2.SelectedIndex, 
                   this.dtOkRef.Value, 
                   int.Parse(this.cbStatus.SelectedValue.ToString()),  
                   int.Parse(this.cbLocatie.SelectedValue.ToString()), 
                   this.dtDataOut.Value, this.tbNote.Text, 
                   int.Parse(this.cbTehnician.SelectedValue.ToString()) );

The other values update correctly.

What data type does the debugger how for the @tip_reparatii parameter on the cmd object? I'd always recommend using Add over AddWithValue and specifying the expected datatype and length — Rowland Shaw
– Rowland Shaw, Commented Sep 9, 2014 at 15:39
try using a smaller string. like @RowlandShaw says, I believe for extra long strings you'd need to specify the data type and length for the parameter — Al W
– Al W, Commented Sep 9, 2014 at 15:41
AddWithValue is actually what I would prefer and let the Database do the resolving of the datatype. which would then lead me to ask what is the datatype defined on the sever side sounds like a datatype and or casting issue here — MethodMan
– MethodMan, Commented Sep 9, 2014 at 15:42
.Add("@tip_reparatii", tip_reparatii) is depreciated, but .Add("@tip_reparatii", SqlDbType.VarChar, 200).Value = tip_reparatii is not, and doesn't make the runtime guess at what the server is expecting. — Rowland Shaw
– Rowland Shaw, Commented Sep 9, 2014 at 15:45
You should always specify the datatypes and length of your parameters. Don't use the implicit setting of AddWithValue. blogs.msmvps.com/jcoehoorn/blog/2014/05/12/… — Sean Lange
– Sean Lange, Commented Sep 9, 2014 at 15:46

Hammy · Accepted Answer · 2014-09-09 16:01:45Z

1

Instead of using cmd.Parameters.AddWithValue("@tip_reparatii", tip_reparatii);

Try:

cmd.Parameters.Add("@tip_reparatii",SqlDbType.NVarChar).Value=tip_reparatii;

or with a size parameter such as cmd.Parameters.Add("@tip_reparatii",SqlDbType.NVarChar,200).Value=tip_reparatii;

You should do this type of add for all your SQL command Parameters.

answered Sep 9, 2014 at 16:01

Hammy

863 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

ogrishmania Over a year ago

I tried with cmd.Parameters.Add("@tip_reparatii", SqlDbType.NVarChar).Value = tip_reparatii; then with 200 size and still nothing. It doesn't want to update. The type of the field in the DB is nvarchar(200), tried with nvarchar(MAX) for curiosity and still nothing. Edit: tried with text type and nope.

Hammy Over a year ago

Try using the .ToString().Trim() at the end of tip_reparatii, so cmd.Parameters.Add("@tip_reparatii", SqlDbType.NVarChar).Value = tip_reparatii.ToString().Trim();

Hammy Over a year ago

Also change all the .AddWithValues to .Add(parameter,sqlDBType.Type).Value=var; and see if that helped

ogrishmania Over a year ago

Tried with trim still nothing. Like I said previously I changed .AddWithValues to .Add. This doesn't work either cmd.Parameters.Add("@tip_reparatii", SqlDbType.NVarChar,300).Value = tip_reparatii.Trim();` Still no solution. Like I said in OP I added a string manually "1 2 3 4" and it inserts in the DB. With the string I try to send as a parameter it doesn't. I display the string I want to insert before the function call and inside the function and it displays correctly. Checked the length of the string and it's correct.

Hammy Over a year ago

I copied and pasted exactly what you have, made a database, and ran it on my machine....and it updated as expected...The only thing I have different is the (isOnline) function...I dont know what your function looks like so it is commented out. Also the values I passed, i assigned like this: int ID_INVENTAR=0; int ID_CEAS=0; DateTime data_intrare=DateTime.Today;; string tip_reparatii="1 2 3 4"; etc... because I don't have the other objects in the mock-up.

|

Collectives™ on Stack Overflow

C# & SQL Server : incomplete update (string type)

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related