-1

I'm trying to update 4 columns in my database where the UID matches, the query runs fine with no errors output but the database remains unchanged. The database is fully closed and nothing else has it open except the code. I'm quite new to using databases in code so I'm completely stumped at trying to find the error here.

The update query code is as a follows:

conn.Open();
OleDbCommand UpdateUserCMD = new OleDbCommand("Update Accounts_Info Set Unique_ID=@Unique_ID, Last_Login_Date=@Last_Login_Date, Last_IP=@Last_IP, Last_MAC=@Last_MAC" + " Where Unique_ID=@Unique_ID", conn);
UpdateUserCMD.Parameters.AddWithValue("@Last_Login_Date", DateTime.Now.ToString("yyyy.MM.dd_hh:mm:ss"));
UpdateUserCMD.Parameters.AddWithValue("@Last_IP", GetIPAddress(Dns.GetHostName()).ToString());
UpdateUserCMD.Parameters.AddWithValue("@Last_MAC", GetMACAddress());
UpdateUserCMD.Parameters.AddWithValue("@Unique_ID", TempGUIDHolder);
UpdateUserCMD.ExecuteNonQuery();
conn.Close();

The connection string is as follows:

OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:/A-Level/Development/A_Level_AI_Project/A_Level_AI_Project/resources/Accounts.accdb");

Other query's work fine so i don't think its the connection string at fault.

Steve
  • 213,761
  • 22
  • 232
  • 286
  • Just to double check, because youre using a file based database: a) are you absolutely sure you're looking in the same database file your code is changing? And b) are you certain there is nothing (like the build process) that is copying an earlier database over the top of the database your program is changing – Caius Jard Nov 12 '19 at 22:23
  • Fairly Certain, I can replace the update with either an insert or delete query using the same table name and they both work fine. All other instances of a connection being opened to the database are closed after each query and commands after this query in the same table work fine so i dont think anything is copying an earlier database. –  Nov 12 '19 at 22:28
  • Are you changing the Unique_ID? Because if you change it to a new value then you cannot hope to find the correct record in the where statement. And are you sure that you don't have an empty try/catch around that block of code. As written you should get an exception – Steve Nov 12 '19 at 22:30

1 Answers1

0

I think you should remove the part of your update query where you SET the unique id

Update Accounts_Info Set Unique_ID=@Unique_ID, Last_Login_Date ...
                         ^^^^^^^^^^^^^^^^
                         don't put this in

Reason I say this is because I'm near certain that OLE does NOT support named parameters even though you've written them and it "seems to work" for other queries. OLE only supports positional parameters so the order of AddWithValue calls must match the order of parameter appearance in the query

because your query contains an extra Uniqueid parameter the order you've called AddWithValue, men's that your logindate is being assigned to the uid, your ip is assigned to the logon date, mac to ip. It shouldn't actually run, because there is no value provided for the 5th parameter.

If you're having trouble getting what I mean, change all the @parameter names in the query to ?

After you've sorted out the parameters, capture the return value from ExecuteNonQuery and it will tell you how many records were affected. If it's 0 then the where clause matched nothing; double check that the value in tempGuidHolder appears in the table

If you're catching and swallowing an exception (and I believe this code should cause an exception because not all parameters are filled), don't ;)

Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • The use of named parameters in the correct way of doing this as far as I know - can you point to anything that says OLEDB does not support it? I'm sure I've used this many times myself in the past. But there is certainly no reason to update the unique ID since that was used to select the row in the first place. – TomC Nov 13 '19 at 00:02
  • https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/configuring-parameters-and-parameter-data-types says *When using parameters with an OleDbCommand or OdbcCommand, the order of the parameters added to the Parameters collection must match the order of the parameters defined ... .* - I believe you can use named parameters but internally they will just be treated as if you'd made them all ? and you could put anything you like in the name of the Add - named or otherwise the order is vital, and for this statement there are 4 parameters supplied to a statement that contains 5 placeholders – Caius Jard Nov 13 '19 at 06:41
  • Also https://learn.microsoft.com/en-us/dotnet/api/system.data.oledb.oledbcommand.parameters?view=netframework-4.8 remarks section *OLE does not support named...* (the other link talks about stored procedures, though I wouldn't expect any difference..) – Caius Jard Nov 13 '19 at 06:52
  • Hey, thanks @CaiusJard. I'll have to test it out but it looks pretty clear. Most of my work has been with sql client so this hasn't been an issue. Thanks for pointing to the doc. – TomC Nov 13 '19 at 22:51