SqlCommand returns 1 but database doesn't get updated.. what am i doing wrong?

Question

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand cmd = new SqlCommand("INSERT INTO Alarm (artistname, tijd) VALUES (@artistname, @tijd)", connection);
    cmd.CommandType = CommandType.Text;
    cmd.Connection = connection;
    cmd.Parameters.AddWithValue("@artistname", alarm.ArtistName);
    cmd.Parameters.AddWithValue("@tijd", alarm.Time);
    connection.Open();
    int a = cmd.ExecuteNonQuery();
    System.Windows.Forms.MessageBox.Show(a.ToString()); 
    // returns 1 but database doesn't actually update..
 }

The code above is what i'm using to update my database's Alarm table. int a returns a 1 but the database doesn't actually get updated. what am i doing wrong? visual studio doesn't give me any error messages either.

some databases like sqllite always use a transaction, you need to commit your transaction to 'save' changes to database (or maybe close connection) — Joel Harkes, Jan 06 '17 at 14:09
how do you know that the database is not updated? you are querying and nothing is inserted? — NicoRiff, Jan 06 '17 at 14:11
what makes you think it wasn't updated (inserted)? how are you checking to see whether it was inserted? what type of database is this? is it a standalone file? or is it a dedicated server? note: when using standalone database files, devs *frequently* look in the wrong file - you want to look at the database in the *run* (/deploy) folder, not the *source* folder — Marc Gravell, Jan 06 '17 at 14:12
@Nicholas the code clearly shows the connection from creation thru disposal, and there is no transaction demonstrated — Marc Gravell, Jan 06 '17 at 14:14
@JoelHarkes The `using` statement will close the connection. — juharr, Jan 06 '17 at 14:34
@MarcGravell First of all thanks for the quick responses (all of you), I'm still a rookie with C# so sorry for that.. I check if it was updated (inserted) as follows: Server explorer > festiapp.mdf (which is my database) > tables > alarms > show table data. I even tried refreshing but that doesn't help either.. I've filled one row to 'test' if my listview2 even shows the table at all (which it did). the only problem now is if i select an item from listview1(which shows another table from that database), press the add alarm button, than it doesn't work... hope this makes the problem more clear. — Nino V. Gerwen, Jan 06 '17 at 15:45
@Nino I wonder - have you checked whether there is more than one `Alarm` table? for example, a `dbo.Alarm` and a `Nino.Alarm` ? — Marc Gravell, Jan 06 '17 at 16:24
Along the lines of what @MarcGravell is saying above, are you sure you're looking at the right database? I don't see anything obvious in your code. — stroebele, Jan 06 '17 at 16:39

score -2 · Answer 1 · edited Jan 06 '17 at 14:59

-2

ExecuteNonQuery will return a 1 of successful. You are performing an INSERT statement not an UPDATE statement. If you want to the id of the new record that was added use

INSERT INTO Alarm (artistname, tijd) VALUES (@artistname, @tijd); 
Select Scope_Idenitity();

then use

int a = (int)cmd.ExecuteScalar();

edited Jan 06 '17 at 14:59

Right leg

16,080
7
48
81

answered Jan 06 '17 at 14:22

Jason Nowicki

1
2

2

`ExecuteNonQuery` returns the number of affected rows not a 1 if it was successful. I think OP just used the term _updated_ in the meaning of "something came into my database" – Tim Schmelter Jan 06 '17 at 14:24
Yes, I use the term ''updated'' as Tim stated with the meaning ''something came into my database'' To give u a more clear view on the app/problem: I have 2 listviews> ( 1 is filled with artist names and times they play) ( 1 is empty) If i select an artist from listview1 and press ''add alarm'' i want to write this artist with the time to my table.alarms and view it in listview2 (the one that shows all my alarms i've set for diffrent artists). The problem: Listview1 works fine Listview2 can view the alarms.table im my database (tested) But i can't insert items into it from form. – Nino V. Gerwen Jan 06 '17 at 15:05

SqlCommand returns 1 but database doesn't get updated.. what am i doing wrong?

1 Answers1